|
|
 |
|
|
|
Microsoft® Office Excel 2003 Inside Out
|
|
|
Author
|
|
Craig Stinson, Mark Dodge
|
|
|
Pages
|
1056
|
|
Disk
|
1 Companion CD(s)
|
|
Level
|
Int/Adv
|
|
Published
|
09/03/2003
|
|
ISBN
|
9780735615113
|
|
Price
|
$44.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
Symbols and Numbers
" (double quotation mark) 114
' (apostrophe)
adding comments 863–64
numeric text entries 114
sorting sequence 728
& (ampersand) operator 360
####### (pound signs) 112
# (pound sign) 213
$ (dollar sign)
custom formats 214
formatting as you type 201
guidelines for 111
% (percent sign)
custom formats 201, 213
formatting percentages 208
guidelines for 111
^(caret) 114
( ) parentheses
arguments with 850
function syntax 362, 404
placement in formulas 352
* (asterisk)
custom AutoFilter wildcards 733
custom formats 214
specifying variables 182–83
, (comma)
formatting as you type 201
function syntax 404
guidelines for 111
number formats 203
thousands separator 213
. (decimal points)
currency formats 204, 217
custom formats 213
Fixed Decimal 170
general formats 202
guidelines for 111
number formats 203
... (ellipsis) 25
/ (forward slash)
activating menus 24
/ (forward slash) (continued)
custom formats 213, 214
guidelines for 111
? (question mark) 182–83, 213
@ (text placeholder) 214
\ (backslash) 114, 214
_ (underscore) 214, 850
+ (plus sign)
custom formats 214
entering formulas 109
guidelines for 110
outline levels 272
= (equals sign) 109–10, 351
>> (double fast forward arrow) 66–67
0 (digit placeholder) 213
1904 Date System 436
3-D
graphic effects 301
names 374–75
3-D charts
background formats 655–59
data point spacing 652
shape changes 652–53
viewing angles changes 659–60
3-D references 364, 374–75
A
A4 paper resizing 333
About Microsoft Excel, Help menu 63
ABS function 921
absolute references
copying 357–58
overview 353
working with 853–55
acceptable values 720–21
Access databases 761–62
accessibility aids 89–90
Accounting formats
applying 207–8
built-in custom 215
underline formats 208
ACCRINT function 459, 921
ACCRINTM function 459, 921
ACOS function 922
ACOSH function 922
Activate Product command, Help menu 63
active area, worksheets 22, 101
active cells 18, 27
ADD (Add mode) 103
Add or Remove Buttons command 66–67
Address box 583–84
ADDRESS function 430, 922
Add Watch feature. See Watch window
Advanced Filter command 734–42
computed criteria applied to 739–40
criteria range specified by 734
filtered rows extracted with 741–42
multiple criteria for column applied to 738–39
OR and AND applied to 738
ORs on columns applied to 734–37
"A" functions 466, 469
aggregate calculations
filtered data with 742
Query 788–90
subtotalling on multiple 745
alerts, specifying 721
alignment
chart data labels 631–32
chart titles 628–29
Horizontal 220–22
objects containing text 302–3
options 218–19
selecting with toolbars 225–26
Vertical 222–24
WordArt 295
Align Or Distribute submenu 305
Allow Cell Drag and Drop option 102, 157
Alt key
constraints created with 285
menus activated with 24–25
Alt Text 299
AMORDEGRC function 922
AMORLINC function 922
ampersand (&) operator 360
Analysis ToolPak 477–91
creating histograms 479–82
Descriptive Statistics tool and 477–79
generating random numbers 484–88
installing 403–4
Analysis ToolPak (continued)
moving averages 489–91
rank and percentiles 482–84
sampling numbers 488–89
AND function 922
AND operator 422–23, 738
angled text 224–25
animation
Animate! command 58
providing feedback with 90
anonymous access, FTP sites 553–54
Answer report 517–18
apostrophe. See ' (apostrophe)
area charts 654
AREAS function 433, 923
arguments
arrays 385, 406
custom functions 863, 864–67
defined 253
investment functions 449–50
lookup functions 427
MATCH function 431
naming to methods 850
syntax of functions 362, 404
types of 405–6
arrays
arguments 385
argument types 406
constants 385, 388–89
expansion 389
form, LOOKUP function 429
formulas 386
one-dimensional 385
Paste Special 154–55
range 385
single-cell 387–88
static charts created with 687
two-dimensional 386–87
arrow keys
AutoFilter command 730
formatting 300
menus activated with 24
Ascending Sort button 723, 724
ASC function 923
ASCII text functions 417–18
ASIN function 923
ASINH function 923
Assign Macro command 319–20
assignment statement 862
asterisk. See * (asterisk)
ATAN2 function 923
ATAN function 923
ATHANH function 923
audio feedback 89
auditing worksheets 251–56
error checking 251–53
evaluating/auditing formulas 253–55
watching formulas 255–56
working with cell tracers. see cell tracers
AutoCalculate 28
AutoComplete
enabling for cell values 90
lists created with 706
overview of 191–92
AutoCorrect
AutoFormat as you type 189
list objects and 708
options 188
rules of order 189
shorthand typing shortcuts 192
smart tag customization 189–91
AutoFill
dates and times 173
extending series with 174
series 173
suppressing 172
AutoFilter tool
creating list objects 709
filtering lists 730–33
hiding groups of rows 673
removing criteria 733
spreadsheet interactivity 564
AutoFormat
accounting and 208
creating outlines 271
format extension and 705–6
overview of 198–200
PivotTables 809
AutoFormat As You Type tab 189
automatic backup files 37–38
Automatic font color 227
automatic formula extension 775
Automatic Query 711
Auto Points 289
AutoRecover 8, 16–17
AutoRepublish 10
AutoShape rotation 10
AutoShapes menu
example 324
tear-off palettes 286
text added to created shapes 291
working with 291–93
AutoShow, PivotTables 814
AutoSort, PivotTables 812–13
AutoSum button
defined 10
overview of 412
working with 362–63
Auto Syntax Check 870–71
AVEDEV function 923
AVERAGEA function 466, 923
AVERAGE function
vs. AVG 416
description 923
overview of 464
axes, basic
category vs. time scaling 612–13
displaying 612
formatting options 613
hiding 612
titles 611
axes, enhanced 636–46
3-D chart viewing angles 660
color 637
gridlines 646
line style 637
scaling time-scaled category-axis 642–46
scaling value axis 639–42
tick-mark label format 638, 694
tick-mark labels positions 637–38
tick-mark positions 637–38
titles 626
weight 637
axes, PivotTables 805
B
background
charts 655–59
chart titles 628
images on worksheets 233–34, 324–25
Backspace, keyboard shortcuts 117
backup files, automatic 37–38
BAHTTEXT function 924
bar charts
markers changing data in 679–81
series lines added to 652
shapes adjusted in 652–53
spacing adjustments 650–51
Base Unit setting 643–44
Begin A Group command 71
Bernoulli Distribution 486–87
BESSELI function 924
BESSELJ function 924
BESSELK function 924
BESSELY function 924
BETADIST function 924
BETAINV function 924
BIN2DEC function 924
BIN2HEX function 924
BIN2OCT function 925
BINOMDIST function 925
Binomial Distribution 487
bin values 480–82
bitmap objects, pasting 575, 594
Black and White option 340
blank entries 728, 732
Blank Workbook icon 30
borders
angled 224, 230
applying with toolbar buttons 229
Border drawing tool 9
chart title 628
customized cell 227–31
drawing 231
example 325
marker 649–50
bounding rectangle 572
break mode 870
breakpoints, setting 873
Brightness and Contrast sliders 317
Bring to Front button, Order palette 303–4
browsing
Browsed Pages button 583–84
Browse the Web button 584
creating data browser 754–55
online Help 59–60
scenarios 501
viewing VBA with Object Browser 847–48
bubble charts 683, 687–89
bugs. See debugging
Built-In Menus 73
built-in statistical functions 464–65
Button Editor 75
buttons
3-D graphic effects 301–2
border format options 229
currency formatting 204
drawing tool options 284
font format options 227
footer options 335–36
header options 335–36
macros creating 319–20
picture format options 318–19
print preview options 343–44
sheets compared side-by-side 137–38
WordArt options 294–95
buttons, custom
adding comments 261–63
adding faces 72–73
adding hyperlinks 587
changing button faces 74–76
creating 79
displaying faces 71–72
increasing size 89
managing 65–66
overview of 66–67
rearranging 70–71
By Changing Cell box, Goal Seek 509
by-name syntax 850
by-position syntax 850
C
calculated columns 702–5, 714–17
calculated fields, PivotTables 824–27
calculations
date and time 444
Goal Seek 509
worksheets. see worksheet calculations
calculations, PivotTables
alternatives for 821
calculated fields and items 821–22
custom 822–24
grand totals 819–20
subtotals 820–21
summary functions 820–22
callouts, AutoShape 293
Camera button 320–21
Cancel button, Formula Bar 27
CAPS (Caps Lock mode) 103
caret (^), 114
case sensitivity
file names 36
passwords 132
sorts performed with 729–30
VBA compiler 871
VBA keyboard shortcuts 843
case text functions 418–19
categories
changing intervals between labels 641–42
consolidating by 278–80
multilevel chart 675–78
category axes
category vs. time scaling 612–13
displaying or hiding 612
manually scaling time-scaled 642–46
manually scaling value axis and 640, 642
CD, accompanying book
data tables 493
Solver 510
CEILING function 415, 925
cell entries 109–16
displayed values 111–12
editing/undoing 116–17
keyboard shortcuts 909–12
locking in 109
long text values 113
making in ranges 116
numeric text 113–14
overview of 109–10
simple numeric values 110
simple text values 110
special characters 110–11
symbols 115
underlying values 111–12
word wrapping 113
CELL function 925
cell ranges, naming. See cells, naming
cell references, in formulas 352–58
3-D references 364
changing type 354
copying 356–58
creating to other worksheets 354–55
cell references, in formulas (continued)
entering with mouse 353
inserting into functions 409
labels substituted for 367–68
overview 352
replacing with names 376–77
row-column style 355–56
syntax 359–60
types 353–54
cells
Autocomplete enabled for values 90
column width changes 234–35
deleting 161–63
hiding 132
hyperlinks created in 582–86
inserting 160–61
linking custom properties to 40
merging and unmerging 237–39
naming 499–500
new features 9
PivotTables 809–10
plotting hidden 617
row height changes 235–36
selecting 104–8
sorting 727–28
unlocking individual 128
worksheet facts about 18
Cells command 588
cells, editing 167–79
allowing access to specific cells 129–32
in cells 168
clearing 170–71
extending existing formats 178–79
filling and creating data series 171–79
finding 179–83
with formula bar 167–68
options 169–70
replacing 183
cell selection rectangle 165
cells, names 368–78
applying Go To with range 377–78
applying to formulas 369–70
creating list of 375–76
creating semiautomatically 373
defined by constants 374
defined by formulas 374
defining and managing 370–71
cells, names (continued)
editing 371
pasting into formulas 375
references replaced with 376–77
rules for 370
three-dimensional 374–75
workbook-wide vs. worksheet only 372
cell tracers 256–61
dependent cells and 257–58
errors 259–60
overview of 256
precedent cells and 258–59
precedents vs. dependents 257
references to other workbooks 260–61
tracer arrows 258
Change AutoShape command 293
Change Button Image command 74–75
change history 400–401, 402
change tracking
with document versioning 420
merging and 397
overview of 398–400
protecting change history 400–401
reviewing changes 401–2
sharing workbooks on networks 395–97
characters
fill series limited to 256 characters 172
formatting as you type 201
formatting individual 201, 630
special 110–11
text-alignment 114
wildcard. see wildcard characters
WordArt spacing 295
working with Text Import Wizard 758
CHAR function 417–18, 925
chart data 667–82
adding 667–68
changing/replacing 671–72
copying 668
dragging markers to change 679–81
error bars added to 679
pasting 668
plot order changes 675
plotting or marking every nth point 672–75
removing 671
series added to 669–71
trend lines added to 678
working with multilevel categories 675–77
Chart menu 623–24
Chart Objects list, Chart toolbar 624, 625
charts
in PowerPoint 605
in Word 598–602
charts, advanced 683–97
bubble charts 687–89
dynamic charts 683–85
formatting 693–97
Gannt charts 692–93
named ranges 683–85
plotting new data automatically 685–86
plotting only most recent points 686
radar charts 689–92
static charts with arrays 687
charts, creating 607–16
changing default type 618
changing position 616–17
choosing types 608–9
combination (overlay) 616
embedding 619
handling missing values 617–18
with Histogram tool 481
interactivity 557–59, 565–66
keyboard shortcuts 904
opening 619
options 611–16
plotting hidden cells 617
printing 618
protecting 619
saving 619
size 616–17
specifying data to plot 609–11
charts, enhancing 623–60
3-D viewing angles 659–60
axes. see axes
background formats 655–59
with Chart menu and toolbar 623–24
custom charts 625
data labels 631–36
data series. see data series, formatting
data tables 655
formats copied from other charts 625
gridlines 646
markers. see markers, formatting
new features 10
plot area 626–27
repositioning elements 626
charts, enhancing (continued)
selecting elements 625
text annotations 631
titles 627–30
Chart toolbar 624–25
Chart Type setting 647–48
Chart Wizard
creating new chart 607
custom charts 625
options 611–16
specifying data to plot 609–10
types of charts 608–9
working with Chart menu 623–24
Check For Updates command, Help menu 63
check in, SharePoint documents 416
check out, SharePoint documents 416
CHIDIST function 925
CHIINV function 925
CHITEST function 926
CHOOSE function 926
CHOOSE functions 430
Circle Invalid Data button 261
circular references, formulas 381–82
CLEAN function 926
CLEAN functions 418
clearing
cell contents/formats 170–71
cells with mouse 163–66
dashed lines on page breaks 342
deleting vs., 161
outlines 271
tracer arrows 258
worksheet cells 163–66
Clear Validation Circles button 261
clip art
examples 325–27
inserting 308–12
More AutoShapes selecting 293
new features 10
Clipboard
collecting multiple items on 148–49
embedding and linking from 573–76
Excel charts into Word 598
Excel tables into Word 589–91
parsing text with Text Import Wizard 761
saving copied items on 147
Clip Organizer
managing exhibitions 311–12
overview of 309
Clippit 56
Close Side By Side button 138
CMA (Competitive Market Analysis) 473
CODE function 926
CODE functions 417–18
codes
adding to existing macro 851
built-in custom format 214–15
customizing date and time 443
stepping through 872–73
View Code command 119
collaboration 393–424
e-mailing workbooks. see workbooks, e-mailing
Information Rights Management 410–13
merging changes 402–4
saving/retrieving files remotely 393–94
SharePoint Team Services site 413–22
sharing workbooks. see workbooks, sharing
Web discussions 422
collapsed dialog box 372
Collect and Copy
defined 9
editing with 148–49
gathering information with 149
collections, of objects 848–49
color
axis 637
to black and white for printing 340
dithered 217
gradients 656
graphic objects 298
gridlines 90, 646
highlighting syntax errors 871
line, xy and radar charts 653
markers 649–50
palette customization 87–89
patterns and 231–34
to selected cells 216–17
sheet tabs 9, 90, 120
column axis, PivotTables 805
column charts
dragging markers 679–81
series lines added to 652
spacing 650–51
COLUMN function 926
column headings, PivotTables 835
COLUMN or COLUMNS functions 433
columns
adding to outline 271
Advanced Filter command. see Advanced Filter command
calculated 702–5, 714–17
changing without leaving Print Preview 343
charts 649, 670–71
deleting 161–63
hiding 236
inserting 158–59
inserting list objects 708, 718
keyboard shortcuts 917
printing headings 340
printing on every page 338–39
selecting 105–6, 265–66
sorting lists 723–27
specifying AutoFilter criteria 731
subtotalling on 745
ungrouping/grouping 273–74
width 234–35
worksheet design for 93–94, 99
XML lists 764
COLUMNS function 926
combination (overlay) charts 616
COMBIN function 926
COMBIN functions 413–14
comma. see , (comma)
comma-delimited files 758
commands
accessing with keyboard 24–25
aggregate calculations 789
Chart menu 623
column 235
dialog box display 25–26
editing 147
handwriting recognition 893
Help menu 62–63
Insert Clip Art task pane 310
insert with keyboard shortcuts 159
row 236
shortcut menus 25–26
speech recognition 896–99
stepping through code 872
toolbar buttons issuing 68
Commands and Options tool 565
commands, custom
button faces 71–73
creating 79
managing 65–66
rearranging 70–71
showing on all menus at once 83
comments
cell 261–63
documenting worksheets with 97
editing scenarios 502
printing 339
recording macro 845, 863–64
Web discussion 422–23
Compare Side By Side command 4–5, 137–38
comparison criteria 786–87
compatibility
document types 36
with legacy systems 15
list object totals and 718
Competitive Market Analysis (CMA) 473
compiler, VBA
catching misspelled variable names 871–72
catching syntax errors 870–71
setting conditional breakpoints 873–74
stepping through code 872–73
COMPLEX function 927
computed criteria 739–40
CONCATENATE function 421, 927
concatenation 361
conditional breakpoints 873–74
conditional formats
applying 216, 241–43
creating formulas 244
conditional functions 424
conditional operators 218
conditional selection, Spreadsheet Assistant 267
Conditional Sum Wizard 393–95
conditional tests and 394
creating formulas with SUM and IF functions 393–95
working with 749–50
conditional tests 393–99
with Conditional Sum Wizard 394–97
lookup formulas 397–99
with Lookup Wizard 393–95
overview 393–94
CONFIDENCE function 927
connectors, AutoShape 292
Consolidate command 274–80
by category 278–80
linking to source worksheets 279
merging workbooks vs., 404
overview of 274–75
by position 275–77
consolidated worksheets. See Consolidate command
constants
defined 109
names defined with 374
parentheses enclosing 111
constrained objects, drawing 284–85
constraints, in Solver 512–14
Contact Us command, Help menu 63
CONVERT function 927
Copy command 412
copying
button faces 76
cell references 356–58
chart data 668
chart formats 625
Collect and Copy feature 9
creating automatic backup files 37–38
creating Web query with 796
filtered rows 741–42
in formula bar 167
inserting copied cells 160–61
between linked workbooks 392–93
with mouse 157–58
natural-language formulas 367
objects with cells 304
overview of 147
pasting and 148–50
workbook palettes 89
worksheets 322–23
Copy Picture command 322–23
Corner Point 290
CORREL function 927
COS function 927
COSH function 927
COUDAYSNC function 928
COUNTA function 465, 466, 927
COUNTBLANK function
description 928
extracting details from list 750
information functions 425
COUNT function 465, 927
COUNTIF function
description 928
extracting details from list 749–50
overview of 465
COUPDAYBS function 461–62, 928
COUPDAYS function 461–62, 928
COUPDAYSNC 461–62
COUPNCD function 461–62, 928
COUPNUM function 461–62, 928
COUPPCD function 461–62, 928
COVAR function 929
crash recovery 15–18
AutoRecover 16–17
features for 8
Microsoft Office Application Recovery 17–18
overview of 15–16
Create From File tab, Object dialog box 577–79
Create List setting 670–71
Create New tab, Object dialog box 576–77
credit card numbers 760
CRITBINOM function 929
Criteria button 722–23
Criteria Equals button 786
criteria, filtering in Query 709–10
criteria range
computed criteria 739–40
defined 734
multiple criteria applied to same column 738–39
three ORs on column 737
two columns joined by OR 734–37
cropping, pictures 317–18
.CSV file extension 50
Ctrl key 285
Ctrl+L key 608
CUMIPMT function 929
CUMPRINC function 929
Cumulative Percentage, Histogram tool 481
Currency formats
Accounting formats vs., 207
built-in custom format codes 215
Euro currency tools 204–7
overview of 203–4
currency symbols
Accounting formats 207
Currency formats 203–4
decimal points and 217
Euro formats 204
Curve button
adjusting shapes with Edit Points 287–88
defined 287
working with curves 289–90
Custom Button command 79
custom charts 608–9, 625
Customer Experience Improvement Program 61
Customer Feedback Options
Excel's new 4
overview of 60–62
Custom Footer button 335
custom formats
borders 227–31
colors 216–17
condition operators 218
four-part 215–16
number 211–15
custom functions 859–68
creating 859–61
creating with optional arguments 864–67
documenting 863–64
macros compared to 859
making available anywhere 867
rules 863
VBA keywords in 863
working with 861–62
custom functions, debugging 869–80
design-time error tools 869–71
with Immediate window 875–76
misspelled variable names 871–72
overview of 869
run-time error tools 876–80
setting breakpoints 873
setting conditional breakpoints 873–74
stepping through code 872–73
with Watch window 874–75
Custom Header button 335
Customize dialog box
adding or removing buttons 68–70
attaching custom toolbars to workbook 79–80
changing button faces 74–76
creating new menus 80–82
creating new toolbars 78
deleting custom toolbars 80
displaying 69
giving menu command a button face 72–73
rearranging buttons and commands 70–71
rearranging menus and submenus 73
restoring toolbars and menus 82
custom lists
creating 175–76, 707
importing 176
sorting 728–29
Custom Menu item command 79
custom menus
adding Custom Views 127
creating new 80–82
other options 83
preserving changes 81–82
rearranging 73
restoring 82
Custom option, AutoFilter 732–33
Custom Views feature 126–27, 141
cutting
in formula bar 167
inserting cut cells 160–61
between linked workbooks 392–93
objects with cells 304
pasting and 150–55
Cycle Through Totals button 788
Next
Last Updated: May 12, 2004
|
