Index
A
absolute cell references, 80
creating, 81
relative references, changing, 81
Accessibility Checker, 27
accounting format, 43
Acrobat Reader, 185
acronyms, spell-checking, 62
action items in meetings, 498
changing options, 60
deleting, 61
inserting information using, 61
Active controls
alerts, dealing with, 465
inserting, 464
movies, playing, 469
properties, setting, 467
security settings, 369
toolbox buttons for, 466
VBA code, adding, 468
viruses, avoiding, 362-363
Ad-aware, Lavasoft, 362
adding/removing. See also deleting
clip art, 194
data connections, 405
digital signatures, 359
passwords, 357
Quick Access Toolbar, items from, 7
Status bar, items from, 9
tab colors, 154
add-ins. See also AnalysisToolPak; COM add-ins; Conditional Sum Wizard; Excel add-ins; Solver
alerts, 449
loading/unloading, 325, 448-449
managing, 447
security options, 368
viewing, 446
Add-Ins tab, 4
adjacent cell contents, filling with, 45
Adobe Systems
Acrobat Reader, 185
Photoshop, 207
advanced document properties, 438
advanced options, setting, 436
adware, 362
alerts, 430. See also Groove
add-in alerts, 449
alignment
for cell styles, 167
in charts, 274
of chart text, 284
Format dialog box, changing with, 147
of objects, 252-253
Ribbon, changing with, 146
with SmartArt graphics, 220
Alignment Dialog Box Launcher
changing alignment with, 147
multiple alignment options, setting, 148
All Data Sources files, opening, 13
All Web Pages, opening, 13
Alt key, drag-and-drop with, 51
ampersands in headers and footers, 177
using, 326
AnalysisToolPakVBA, 447
animation
animated GIFs, inserting, 189
feedback with, 436
for organization charts, 228
anonymous FTP sites, 426
antivirus software, 363
Apply button, 8
Arabic, support for, 444
area charts, 263
array arguments, 105
with Formula AutoComplete, 76
lookup function arguments, 335
placeholder arguments, inserting, 103
arithmetic operators, 73
array arguments, 105
arrows
changing, 235
Chart Elements list arrow, 269
Counterbalance Arrows, 218
drawing, 234
Number Format list arrow, 43
Shape Outline button for, 234-235
tracer arrows, 100
up/down arrows, 8
artistic style, 197
ascending order sorts, 306-307
asterisk (*) character, 336
as you type corrections, 59
attachments
viruses, avoiding, 362-363
workbooks as, 386
XML Schema as, 388
Auditing feature
calculation errors, correcting, 97
worksheets, auditing, 100
AutoComplete, 40-41. See also Formula AutoComplete
enabling, 430
AutoCorrect, 35
adding entries, 59
for cell contents, 58-59
editing entries, 59
with spell-checking, 62
as you type, 59
AutoCorrect Exceptions dialog box, 58
AutoFill, 44-45
adjacent cell contents, filling with, 45
complex series, creating, 44
custom fill, creating, 45
formulas, copying, 78
List AutoFill, 305
repeating data, entering, 44
suppressing, 45
tables, entering data into, 305
AutoFill Options button, 44
AutoFilter
grouping dates in, 434
slicer, 316-317
PivotChart, 319
PivotTable, 314-317
table parts, displaying, 308
AutoFit for columns and rows, 120-121
Autoimport dialog box, 260
automated safe mode, 376-377
automatic calculations, 91
automatic page breaks, 178
AutoPublish, 420
AutoRecover, 31
AutoShapes, 258
AutoSum, 92-93
extended AutoSum, 93
subtotals, calculating, 92-93
average, formatting cell contents based on, 136-137
AVERAGE functions, 71, 72, 102
automatic calculations, 91
AutoSum calculations, 92-93
subtotals, calculation of, 337
3-D cell references with, 83
AVERAGEIF function, 338-339
axes. See chart axes
B
backgrounds
adding/removing, 154
cell backgrounds, colors for, 150
chart backgrounds, changing, 286
for pictures, 207
removing background picture, 210
as shape fills, 240
themes, background colors in, 156
worksheet backgrounds, changing, 112
backup files, opening, 13
bar charts, 263
formatting, 285
beveling
to charts, 275
for data series, 283
precedence of, 246
to SmartArt graphics, 225
to WordArt text, 214
BIFF12 file format, 24-25
bitmap images
modifying, 207
resizing, 203
black and white pictures, converting, 206
boldfacing
comments, 383
text, 134-135
borders
cells, adding to, 152-153
for cell styles, 167
in charts, 274
for data series, 283
drawing borders, 152-153
with Format Cells dialog box, 153
pictures, applying to, 200
Ribbon, adding or deleting with, 152
bottom-alignment of objects, 253
brightness, 205
Picture Manager, editing in, 443
bright room effects to shapes, 248-249
Bring to Forward
objects, 255-256
with SmartArt graphics, 220
buttons, 8
C
Calculation Options button, 72
calculations. See also functions
automatic calculations, 91
with AutoSum, 92-93
circular references, locating, 101
errors, correcting, 97
iterative calculation, enabling, 437
keyboard commands for, 79
links in, 402
multiple results, calculating, 105
one time calculations, performing, 94-95
options, setting, 437
performing, 72
Status bar, displaying with, 91
in tables, 301
capital letters. See case sensitivity
Caps Lock, 9
case sensitivity
AutoCorrect Exceptions dialog box for case, 58
table data, sorting, 306
text functions, 336
categories for clip art, 192
cell address, 35
cell notes. See comments
cell references, 80
circular references, locating, 101
examples, 72
cells, 35. See also alignment; cell references; charts; comments; labels; named ranges
applying names to, 86-87
average, formatting cell contents based on, 136-137
borders, adding, 152-153
colors, adding, 150
conditional formatting, 136-137
copying
contents, 50-51
formats, 155
correcting contents of, 58-59
cutting contents of, 52
deleting, 54-55
cell styles, 168-169
name ranges, 89
from Watch window, 98
worksheet, cells in, 54-55
drag-and-drop copying contents with, 50-51
enabling, 430
cell styles, 168-169
names of cells, 89
filling backgrounds with colors, 150
finding and replacing, 56-57
inserting blank cells, 54
locking/unlocking, 354
managing names, 88-89
moving cell contents, 52-53
names, 84-85
changing, 89
entering, 86-87
managing, 88-89
organizing names, 88-89
orientation of cell contents, 146-147
page breaks in, 178
pasting cell data, 48
patterns, applying, 150
pointing to, 75
ranking, formatting cell contents based
on, 136-137
searching for values, 123
selecting, 37
storing contents of, 49
viewing names, 88-89
Watch window, using, 98
wrapping text in, 74
zero value cells, 435
cell styles, 131
applying, 166
custom styles, creating, 167
deleting, 168-169
editing, 168-169
for hyperlinks, 416
merging, 168-169
Cell Styles gallery, 167
centering objects, 253
certification authority (CA), 358
chart area, 269
changing, 277
editing, 267
titles, changing, 279
chart floor, 286
charts, 261. See also data series; legends; pie charts; PivotChart
background, changing, 286
creating and inserting, 264
data labels, 280-281
data source, editing, 287-288
data tables, showing/hiding, 280-281
default charts, creating, 265
deleting objects, 270
editing, 266-267
chart data, 288-289
text, 265
elements
formatting, 274-275
names, showing, 433
selecting, 269
embedded charts, 264
empty cells, displaying, 289
enhancing charts, 287
enlarging chart objects, 269
error bars, 285
formatting
bar charts, 285
elements, 274-275
line charts, 285
text, 284
gridlines, changing, 276
hidden data, displaying, 289
labels, 280-281
layout, applying, 272
moving, 268
options, 432
other worksheets, moving to, 265
pictures, inserting, 287
resizing, 268
styles, 272-273
shape styles, applying, 275
templates
applying, 293
creating, 292
managing, 294
terminology, use of, 262
text
adding, 287
editing, 265
formatting, 284
text boxes in, 287
titles, 262
axes titles, changing, 279
changing, 278-279
Charts Dialog Box Launcher, 264
Chart Type dialog box, 293-294
chart wall, 286
check boxes, 8
for Active controls, 466
Check for Updates button, 33
Check Office Documents that are from or link to suspicious Web sites option, 363, 372
Chinese, support for, 444
circles, drawing, 230
circular references, locating, 101
class modules in VBA (Visual Basic for Applications), 451
clearing
conditional formatting, 141
data tables, 297
print area, 182
validation circles, 322
Clear Rules commands, 141
clear surfaces for shapes, 248-249
clip art, 187
adding/removing clips, 194
categories, organizing into, 192
downloading clips, 191
locating and inserting, 188
media clips, 189
objects, 188
properties, changing, 193
Web for clips, 191
Web, accessing on, 190-191
Clip Art task pane, 188
properties of clip, changing, 193
working with, 192-193
Clipboard
cell contents, storing, 49
formulas, copying, 78-79
multiple items, cutting, 52
objects, copying and moving, 237
pasting data with, 48
Clipboard Dialog Box Launcher, 49, 237
Clip Organizer, 188
adding/removing clips with, 194
motion clips, playing, 189
properties of clip, changing, 193
shapes, adding, 260
sound clips, playing, 189
working with, 192-193
closing workbooks, 34
code for VBA, 450
Collapse Dialog button, 85
chart data, editing, 288-289
for data validation, 322
colors. See also fonts; pictures; shapes; themes
for borders, 152-153
cells, adding to, 150
custom colors, adding, 151
formatting with color scales, 138-139
hyperlink colors, 156
for menus, 151
in organization charts, 228
Picture Manager, editing in, 443
properties of, 159
schemes, selecting, 428
SmartArt graphics, changing for, 223
tabs, adding or removing colors to, 154
WordArt fill colors, changing, 212-213
of worksheet tab names, 115
Column Differences option, 38
columns and rows. See also tables
data series, plotting, 288
default column width, changing, 120
deleting, 119
display options, 435
freezing/unfreezing, 122
height of rows, adjusting, 120-121
hiding/unhiding, 117
inserting, 118
multiple columns and rows
deleting, 119
inserting, 118
selecting, 116
pasting cells from, 53
printing
options, 180-181
print area for, 182
selecting, 116
showing/hiding, 117
text to columns, converting, 321
Use row and column names option, 87
width of columns, adjusting, 120-121
COM add-ins, 447
loading/unloading, 448-449
combo boxes for Active controls, 466
COM (Component Object Model). See Active controls; COM add-ins
comma delimiter, 321
command buttons, 4
for Active controls, 466
commands
from Office menu, 5
Quick Access Toolbar, adding to, 441
from Shortcut menu, 5
Comma Style button, 132
adding comments, 382
clearing, 47
deleting, 383
display options, 433
editing, 383
reading comments, 382
comparison
formatting cell contents for, 136
operators, 73
of functions, 347
of workbooks, 26
complex series, creating, 44
compressing pictures, 204
in Picture Manager, 443
Compression Settings dialog box, 204
conditional formatting, 136-137
clearing, 141
with color scales, 138-139
with data bars, 138
editing rule precedence, 142
finding cells with, 143
with icon sets, 138-139
rules, creating, 140
specialized conditional formatting, applying, 138-139
Conditional Formatting Rules Manager, 142
conditional formulas
examples of, 106
with nested functions, 106
connecting objects, 254
Connection Properties dialog, 404-405
Connection Status, checking, 21
consolidating data, 400-401
constants
defined, 72
in names, 108
contextual spelling, checking, 63
contextual tabs, 4
contours
for data series, 283
for shapes, 248-249
contrast, 205
Picture Manager, editing in, 443
Convert to Columns Wizard, 321
Copy command for worksheets, 112
copying. See also Clipboard; pasting
clip art, 193
dialog box, files in, 13
hyperlinks, 416
macro modules, 459
objects, 237
Web tables to worksheet, 421
window contents, 198
worksheets, 112-113
correcting. See also AutoCorrect; errors
cell contents, 58-59
Counterbalance Arrows, 218
COUNT functions, 102
automatic calculations, 91
AutoSum calculations, 92-93
3-D cell references with, 83
COUNTIF function, 338-339
Create a copy check box, 112
cropping. See pictures
CSS, Web pages allowing, 417
CSV format, 25
Ctrl key, 237
currencies
Euro Conversion Wizard, 327
Euro Currency Tools, 447
format for, 43
formatting numbers in, 133
Currency Style button, 132
curved connectors, 254
customizing, 427. See also fills; page layout; themes
advanced properties, 438
cell styles, 167
colors, 151
dictionaries, 64-65
margin settings, 175
Quick Access Toolbar, 7
shadows, 251
shapes, creation of, 440
templates, 130
text objects, creation of, 440
themes, colors for, 159
workbook views, 126
cut, copy, paste options, 431
cutting cell contents, 52
Cyrillic, keyboard configuration for, 444
D
DAISY (digital Accessible Information System), 27
dark edges for shapes, 248-249
dark variations for pictures, 206
data. See also alignment; external data
labels, 280-281
for PivotTable, 319
simplifying entry, 43
validation, 322
Data Analysis dialog box, 326
data bars, 138
data connections, security settings for, 374
Data Connection Wizard, 403
data maps. See XML (Extensible Markup Language)
data markers
for charts, 262
editing, 266
adding, 290
changing, 291
deleting, 290
editing, 288
formatting, 283
order, changing, 291
plotting, 288
type of chart, changing, 271
Data tab, 4
Data Validation dialog box, 323
dates and times
formats for, 43
long date format, 43
short date format, 43
worksheets, entering on, 42
dBASE, 403
opening files, 13
DDE (Dynamic Data Exchange), 436
debugging macros, 458
Debug toolbar, 458
decimals, automatic insertion of, 430
Decrease Decimal button, 132
defined names, 84
Delete Cropped Areas Of Picture options, 204
deleting. See also cells; Groove; pictures; ranges; shapes; tables; themes
borders, 152-153
chart objects, 270
clip art, 194
columns and rows, 119
comments, 383
data series, 290
dialog box, files in, 13
digital signatures, 359
file encryption, 355
font attributes, 133
macros, 457
name ranges, 89
numeric formats, 133
page breaks, 179
passwords, 357
PivotChart, 318
PivotTable, 310
Quick Access Toolbar, items from, 7
SmartArt graphics, shapes from, 219
Status bar, items from, 9
text boxes, 233
Text pane, deleting line in, 219
themes, custom colors in, 159
values, keeping formulas, 96
vertex on freeform, 236
Watch window, cells from, 98
WordArt text, 211
workbooks, custom views in, 126
worksheets, 111
delimiters, 321
dependent cells, 80
depth
for data series, 283
shapes, adding to, 248-249
descending order sorts, 306-307
designation files, 394
detailed data, 337
Developer tab. See Ribbon
diagnosing problems. See repairing problems
dialog boxes. See also specific types
navigating, 8
options, choosing, 8
custom dictionaries, 64-65
for foreign languages, 444
Research task pane for accessing, 68
DIF format, 25
opening files, 13
digital cameras, inserting pictures from, 195
digital signatures
adding/removing, 359
on macros, 460
for workbooks, 358-359
XPS support for, 186
direction
for gradient fills to shape, 245
moving, 430
of WordArt text, 215
Disabled Items dialog box, 447
display view options, setting, 433
distributing objects, 252
Document Information Panel, 28
Mark as Final command, 378
template, selecting, 470
Document Inspector, 350-351
Document Management Servers
Saving to SharePoint Server, 498
Opening from SharePoint Server, 499
documents
advanced properties, changing, 438
digital signatures, adding, 359
Document Management task pane
inspecting, 350-351
visible signature line, adding, 360-361
documents (Word)
Office Web App, creating, 485
DocumentThemes folder, 163-164
deleting custom themes from, 164
Download.com, 363
downloading
clip art, 191
privacy options, setting, 372
Publish as PDF or XPS add-in, 185
viruses, avoiding, 362-363
Web updates, 33
workbook templates, 11
drag-and-drop. See also cells
to different worksheets, 51
moving cell contents, 52
in Text pane, 217
drawing
borders, 152-153
freeforms, 236
shapes, 230
ungrouping/regrouping drawings, 259
Drawing tab, 4
drop-down lists
creating, 323
tables, entering data into, 305
Duplicate command, 237
duplicate words
checking for, 62
flagging, 63
E
Edit Data Source dialog box, 288-289
editing. See also cells; charts
AutoCorrect entries, 59
clip art keywords, 193
comments, 383
conditional formatting rule precedence, 142
embedded objects, 398
of final workbook, 378
formulas, 78-79
freeforms, 236
hyperlinks, 414
macros, 459
options, setting, 430
in Picture Manager, 443
range names, 89
read-only workbook, 378
Refers to box contents, 108
SmartArt graphics, shapes in, 221
templates, 130
WordArt text, 211
Edit Points command, 236
eFax, 387
effects. See also 3-D effects
for organization charts, 228
picture effects, applying, 201
to shapes, 246-247
to SmartArt graphics, 225
theme effects, creating, 162
to WordArt text, 214
elbow connectors, 254
header or footer, inserting into, 177
security for, 352
table elements, formatting, 300
workbook elements, showing/hiding, 124
XML elements, 390
e-mail. See also attachments
hyperlinks to address, 414
spam, 362
viruses, avoiding, 362-363
embedding, 394
in charts, 264
editing embedded objects, 398
existing object, embedding, 398-399
new object, embedding, 398-399
Enable background error checking check box, 99
encryption to workbooks, adding, 355
entering cell contents, 148
error bars, 285
Error checker
circular references, locating, 101
formula errors, correcting, 98-99
errors
add-in user interface errors, showing, 436
calculation errors, correcting, 97
formulas, correcting errors in, 98-99
Esc key, 155
Euro Currency Tools, 447
Euro Conversion Wizard, 327
European languages, support for, 444
Evaluate Formula dialog box, 106-107
event procedures in VBA (Visual Basic for Applications), 451
loading/unloading, 448
Excel Options dialog box, 434-435
Web updates from, 33
Excel window, viewing, 3
exclusion dictionaries, 64-65
existing workbooks. See workbooks
Expand dialog button, 85
Expansion Pack button, 391
exploding pie slice, 282
exporting. See importing and exporting
expressions, defined, 72
extended AutoSum, 93
external content security options, 374-375
external data
changing connection properties, 405
connections, managing, 403-404
existing connection, connecting with, 404
from Microsoft Access, 408-409
new data source, defining, 406
refreshing data from, 405
security for, 403
sources, 403
F
faxing workbooks, 387
feedback options, 436
file encryption, adding, 355
file extensions, displaying, 14
file formats
list of, 25
workbook file formats, 24-25
file sharing. See sharing
recently opened files in, 13
FileZ, 363
fill handles
with AutoFill, 44
enabling, 430
fills. See also gradient fills; shape fills
for cell styles, 167
in charts, 274-275
customizing, 45
gradient fills, 245
for data series, 283
picture fills, 242
shapes, adding to, 240-241
for SmartArt graphics, 224
textures, 243
WordArt text, applying to, 212-213
filtering, slicer, 316-317
filtering cell names, 88
Find & Select button, 143
finding and replacing, 35. See also searching
formatting, finding and replacing, 170
Flash movies with Active controls, 469
flat surfaces for shapes, 248-249
flipping
in Picture Manager, 443
shapes, 257
flow of text, controlling, 148
folders
DocumentThemes folder, 163-164
Font Color button, 149
fonts. See also cell styles; themes
changing font size, 134-135
for chart text, 284
for comments, 383
deleting attributes, 133
footers. See headers and footers
foreign languages
adding to Office programs, 444
thesauruses, 67
translating text to, 69
Format Cells dialog box, 43
alignment options, 147
borders, applying, 153
numbers, formatting, 133
text, formatting, 134-135
Format Painter
applying styles with, 155
with tables, 298
Format Shape dialog box, 246
for picture effects, 201
shadow options, 251
WordArt text with, 215
formatting. See also charts; conditional formatting; SmartArt graphics; tables
average, formatting cell contents based on, 136-137
comparison, formatting cell contents for, 136
copying cell formats, 155
finding and replacing, 170
hyperlinks, 416
numeric formats, 132-133
PivotTable reports, 313
ranking, formatting cell contents based on, 136-137
sparkline, 144-145
text, 134-135
in Text pane, 219
values, 43
WordArt text, 212-213
forms. See Groove
Formula AutoComplete, 76-77
keyboard navigation, 77
named cell or range, entering, 86
setting, 437
turning on/off, 77
formula bar, 433
cell contents, editing, 46
editing formulas with, 78
formulas, 36, 37, 71. See also calculations; cell references; nested functions
array formulas, creating, 105
AutoFill, copying with, 78
Clipboard, copying with, 78-79
columns and rows, rechecking on deleting, 119
definitions, 72
deleting values, keeping formulas, 96
displaying in cells, 74-75
editing, 78-79
error displays, avoiding, 97
errors, correcting, 98-99
with Formula AutoComplete, 76-77
Goal Seek, 329
links in, 402
multiple results, calculating, 105
one time calculations with, 94-95
options, setting, 437
ranges, using, 90
showing/hiding, 354
simple formula, creating, 74-75
table names in, 437
value, converting formula to, 85
Watch window, using, 98
Formulas tab, 4
fraction format, 43
freeforms, 229
drawing, 236
editing, 236
vertices of, 236
freezing/unfreezing columns and rows, 122
FTP (File Transfer Protocol)
accessing sites, 426
adding or modifying locations, 426
Full Screen view, 29
Function Library, 104
function procedures in VBA (Visual Basic for Applications), 451
functions, 71. See also nested functions
commonly used functions, 102
creating, 103
entering, 102
with Function Library, 104
Insert Function button, entering with, 103
in names, 108
ScreenTips for, 433
summarize data functions, 338-339
text functions, 336
Function Wizard button, 103
G
general options, setting, 428
GetPivotData functions, 437
GIF format, 207
animated GIFs, inserting, 189
glow, applying
to charts, 275
to organization charts, 228
to pictures, 201
to shapes, 247
to SmartArt graphics, 225
to WordArt text, 214
Goal Seek, 329
Go To Special dialog box
cells, locating, 38
formulas to values, converting, 96
gradient fills
custom gradient fills, 245
shapes, applying to, 244-245
WordArt, applying to, 212-213
grand totals, 337
graphics, 187. See also clip art; pictures; SmartArt graphics; WordArt
formats, adding, 195
for hyperlinks, 416
grayscale mode for pictures, 206
Greek, keyboard configuration for, 444
gridlines
chart gridlines, changing, 276
in charts, 262
display options, 435
printing, 181
Groove, 494-497
alerts, 496
creating workspace, 494-495
deleting
workspace, 494
inviting users to, 497
launching, 494
Microsoft SharePoint Workspaces,494-497
permissions, changing, 497
roles in, 497
sharing files in, 496
starting, 494
tools, Documents, managing, 496
grouping/ungrouping
creating groups, 320
outline data, 320
shapes, 258-259
with SmartArt graphics, 220
tables, 258
worksheets, 112
Guest role in Groove, 497
H
hackers. See security
handles. See also fill handles
for charts, 262
on shapes, 230
hardware graphics acceleration, 433
harmful attacks, avoiding, 362-363
headers and footers, 171
elements in header or footer, inserting, 177
inspecting information in, 351
options for, 177
predefined header or footer, adding, 176
in tables, 300
watermarks in, 177
Hebrew, support for, 444
height of rows, adjusting, 120-121
exclusion dictionaries, finding, 65
Microsoft, contacting, 33
searching for, 21
Help Viewer, 20
Connection Status, checking, 21
hiding. See showing/hiding
high-low lines, 285
HLOOKUP function, using, 334-335
Home tab, 4
homographs, 362
horizontal alignment, 146-147
of WordArt text, 215
horizontal page breaks, 178
HSL color mode, 151
in themes, 159
HTML (Hypertext Markup Language), 25, 411. See also Web pages
hue of color, defined, 159
colors, 156
creating, 414
editing, 414
formatting, 416
jumping to, 415
removing, 415
I
icon sets, formatting with, 138-139
Image button for Active controls, 466
image options, 432
importing and exporting, 394
Access database into workbook, exporting, 408
copy and past for exporting, 396
other program format, exporting files to, 397
in Picture Manager, 442
SharePoint lists, exporting tables to, 324
Web queries, data from, 423-424
XML data, 391-392
ImportTextWizard, 410
Increase Decimal button, 132
indenting text in Text pane, 219
InfoPath Form Template files, 470
Information functions, 346
Insert Function dialog box, 72, 103
Insert tab, 4
Inside borders, 153
inspecting documents, 350-351
IntelliMouse, 16
InterFAX, 387
Internet. See also queries; Web pages
clip art, accessing, 190-191
fax, sending workbooks by, 387
FTP (File Transfer Protocol) documents, 426
Web updates, 33
Internet Assistant VBA, 447
Internet Options dialog box, 412
invisible content, inspecting, 351
italicizing
comments, 383
text, 134-135
J
Japanese
support for, 444
JPG/JPEG format
pictures, modifying, 207
K
keyboard
for foreign languages, 444
Formula AutoComplete, navigation of, 77
formulas, commands for recalculating, 79
workbooks, navigating in, 17
KeyTips, 4
keywords for clip art, 193
L
labels
for Active controls, 466
with AutoComplete, 40-41
chart labels, changing, 280-281
consolidating data including, 400
data labels, 280-281
long labels, truncating, 41
number as label, entering, 40
relative cell names, 89
text labels, entering, 40
landscape orientation, 172
LANs (local area networks), 470
Lavasoft’s Ad-aware, 362
layouts. See also page layout
chart layouts, applying, 272
for organization charts, 228
PivotTable reports, 313
for SmartArt graphics, 223
left-alignment of objects, 253
legends, 262
changing, 280
editing, 267
LEN function, 336
light and dark variations for pictures, 206
lighting effects for shapes, 248-249
line charts, formatting, 285
lines, 229
changing, 234
drawing, 234
Quick Styles, adding, 234
links and linking, 394-395. See also hyperlinks
charts to worksheets, 278
creating links, 398
existing object, linking to, 398-399
security settings for workbooks, 374-375
worksheets or workbooks, creating links between, 402
List AutoFill, 305
list boxes, 8
for Active controls, 466
Lists and Galleries, 3
live preview, 4
enabling, 428
for picture effects, 201
for recolored pictures, 206
for SmartArt graphics, 216
WordArt text effects, 214
Lock Drawing Mode, 232
locking/unlocking cells, 354
logical operators/conditions
data validation and, 322
tables, searching, 309
long date format, 43
Lookup function, 104
HLOOKUP function, using, 334-335
VLOOKUP function, using, 334
LOWER function, 336
luminosity of color, defined, 159
M
macros. See also workbooks
controlling, 458-459
copying macro modules, 459
creating, 456
debugging, 458
deleting, 457
digital signatures, adding, 460
editing, 459
opening, 13
Personal Macro Workbook option, 454, 455
recording, 455
running, 457
self-signing certificates for, 460
Status bar, checking with, 9
stopping macros, 457
toolbar, assigning to, 461
Trust Center
opening workbooks with, 463
security options, setting, 370
understanding, 454
viruses, avoiding, 362-363
writing, 370
maintenance, performing, 32
Manager role in Groove, 497
Manage Templates button, 294
many to many pasting of data, 48
many to one pasting of data, 48
margins
customizing settings, 175
mouse, changing with, 174-175
settings, changing, 174-175
Mark as Final command, 378
marquees for copying cell contents, 50
Math & Trig functions, 104, 344
matte surfaces to shapes, 248-249
automatic calculations, 91
AutoSum calculations, 92-93
3-D cell references with, 83
maximizing/minimizing
windows, 18
McAfee antivirus software, 363
media clips, 189
menus. See also specific types
colors, adding, 151
merging cells, 148
styles, 168-169
Message Bar
macro problems, notification of, 460
security options, changing, 371
metadata, 350-351
metal surfaces to shapes, 248-249
MHT/MHTML formats, 25
Microsoft Access, 403
exporting table into workbook, 408
opening database files, 13
PivotTable from Access database, creating, 408-409
Microsoft DirectShow, 189
Microsoft FoxPro, 403
Microsoft Office. See also Groove
harmful attacks, avoiding, 362-363
for help, 33
maintenance, performing, 32
safe modes, 376-377
Microsoft Office Picture, 427
Microsoft Organization Chart, 227
Microsoft Paint, 207
Microsoft Picture Manager. See Picture Manager
Microsoft PowerPoint
organization charts with, 227
SmartArt graphics in, 187
Microsoft SharePoint Workspaces. See SharePoint
Microsoft SQL Server OLAP services, 403
MicrosoftTemplates. See templates
Microsoft Windows
harmful attacks, avoiding, 363
Media Player, 189
middle-alignment of objects, 253
MIN functions
automatic calculations, 91
AutoSum calculations, 92-93
3-D cell references with, 83
minimizing. See maximizing/minimizing
Mini-Toolbar, 5
options, setting, 428
text formatting buttons, 134
mixed cell references, 80
creating, 82
modules in VBA (Visual Basic for Applications), 451
Modules window, 451-452
More controls for Active, 466
motion clips, 189
mouse
columns and rows, adjusting width and height of, 120-121
resizing windows with, 18
workbooks, navigating in, 16
Move command, 112
movies, Active controls for, 469
moving
cell contents, 52-53
charts, 268
clip art, 193
dialog box, files in, 13
direction, 430
hyperlinks, 416
objects, 237
page breaks, 179
Quick Access Toolbar, 6-7
Text pane, 218
worksheets, 112
multiple columns and rows. See columns and rows
MyFAX, 387
My Templates, 292
N
Name box, 86
changing, 89
deleting, 89
entering, 86-87
formulas, using in, 90
managing, 88-89
organizing names, 88-89
Name Manager, 88-89
filter options, 88
names. See also cells; named ranges; renaming
constants in names, using, 108
functions in names, using, 108
workbooks, default names for, 10
for worksheets, 110
navigating. See also Groove
dialog boxes, 8
Formula AutoComplete, 77
in workbooks, 16-17
nested functions
conditional formulas with, 106
defined, 72
evaluating nested formulas, 107
New Formatting Rule dialog box, 140
New Name dialog box
cells, naming, 84-85
ranges, naming, 84-85
Refers to box contents, editing, 108
New Workbook dialog box, 11
new workbooks, creating, 10-11
1924 date system, 436
Normal view, 29
notebook (OneNote)
Office Web App, creating, 485
Number Format list arrow, 43
numeric formats, 43, 132-133. See also cell styles
charts, changing formats on, 274
deleting attributes, 133
separators, using, 430
numeric keypad, 42
Num Lock, 42
Status bar, checking with, 9
O
Object Browser, 451-452
objects, 229. See also shapes
alignment of, 252-253
connecting, 254
copying, 237
distributing, 252
flipping, 257
hidden objects, viewing, 256
moving, 237
rotating, 257
Selection pane, selecting with, 255
stacking order, changing, 256
ODBC (Open Database Connectivity), 403
clip art, inserting, 188
options, 436
SmartArt graphics, 216
Office Clipboard. See Clipboard
Office Data Connection (ODC) files, 404
Office Groove. See Groove
Office Mobile, 500
Office Web Apps
accessing documents, 480-481
browser support, 472-473
collaborating, documents with, 492-493
compare to desktop app, 478-479
creating documents, 485
downloading files, 490
file compatibility, 472
introduction, 471-472
Microsoft Silverlight, 472, 474-475
Office Mobile, 500
opening documents, 481
preparing to use, 472
saving documents, 476-477
saving files, new name, 490-491
using, 472
Windows Live ID, 471-472, 474-475
sign-in, 475
OLE (object linking and embedding), 394-395. See also Active controls
one-input data table, creating, 328
one time calculations, performing, 94-95
one to many pasting of data, 48
one to one pasting of data, 48
Open and Repair workbooks, 12
Open as Copy workbooks, 12
Open dialog box
default file location, changing, 13
file formats, opening, 13
Open in Browser workbooks, 12
opening. See also workbooks
Autoimport dialog box, 260
file formats, 13
Picture Manager, 442
templates, 129
Web page, workbook as, 412
workspace files, 127
Open or Save As dialog box, 28
operators
defined, 72
types of, 73
Option button, 8
for Active controls, 466
Oracle, 403
organization charts
colors in, 228
creating, 227
dotted lines in, 228
layouts, changing, 228
Quick Styles, applying, 228
shapes, adding, 227
orientation
of cell contents, 146-147
page orientation, changing, 172
SmartArt graphics, changing for, 222
outlines. See also shape outlines
on borders, 153
clearing, 320
creating, 320
SmartArt graphics, shape outlines for, 224
ungrouping data, 320
to WordArt text, 212-213
Oval button, 230
P
Page Break Preview view, 29
page breaks, 178-179
deleting, 179
moving, 179
previewing, 179
page layout, 171. See also themes customizing
margin settings, 175
size of page, 173
headers and footers, adding, 176-177
margins, adjusting, 174-175
orientation of page, changing, 172
page breaks, 178-179
print scaling, changing, 172-173
size of page, changing, 173
Page Layout tab, 4
Page Layout view, 171
switching to, 29
Page Number Status bar, 9
Page Setup dialog box. See also page lay-out
print scaling, setting up, 172
painting cell formatting, 155
Paint Shop Pro, 207
panes. See also window panes
task panes, 15
paper sizes, scaling for, 436
Paradox, 403
parental controls, setting, 373, 439
Participant role in Groove, 497
passwords, 349
changing, 357
deleting, 357
for FTP sites, 426
opening workbooks with, 357
range passwords, 353
strong passwords, using, 356
for workbooks, 356-357
for worksheets, 352-353
Paste Options button, 422
Paste Special
calculations, performing, 94-95
formulas, copying, 79
pasting, 48. See also copying
columns to rows, cells from, 53
formula data, 79
rows to columns, cells from, 53
with special results, 50-51
Web table data, 421
Past Special button, 50-51
patterns for cells, 150
PDF format, 1, 25. See also workbooks
percentage format, 43
enabling, 430
Percent Style button, 132
permissions
Groove workspace permissions, changing, 497
Status bar, checking with, 9
Personal Macro Workbook option, 454, 455
phishing, 362
Picture Border button, 200
Picture Corrections options, 205
Picture Effects gallery, 201
Picture Manager
discarding changes, 443
editing in, 443
locating pictures in, 442
opening, 442
Picture Quick Style gallery, 198
pictures. See also brightness; contrast; Picture Manager; shapes
artistic style, 197
backgrounds for, 207
removing, 210
borders, applying, 200
brightness, 205
changing, 195
in charts, 287
color modes for, 206
contrast, 205
correcting, 205
cropping, 208-209
with aspect ratio, 208
deleting cropped areas of pictures, 204
with fill or fit, 209
Picture Manager, editing in, 443
precisely cropping, 208-209
deleting
borders, 200
cropped areas of pictures, 204
effects, applying, 201
files, inserting pictures from, 195
fills to shapes, applying, 242
original state, resetting to, 207
precisely resizing, 202
Quick Styles, adding, 198
recoloring, 206-207
redisplaying cropped pictures, 208
removing background, 210
resizing, 202-203
rotating, 208-209
with Picture Manager, 443
3-D rotation, 201
soften, 205
scaling pictures, 202-203
screen shot, inserting, 196
sharpen, 205
transparent background, setting, 207
WordArt, applying to, 212-213
Picture Shape gallery, 199
Picture SmartArt, 226
Picture Tools tab, 4
pie charts, 263
exploding pie slice, 282
Pin icon for workbooks, 12
PivotChart, 264
Access database, creating from, 408-409
analyzing data with, 310
modifying reports, 319
refreshing data in, 319
renaming, 318
summarizing data in, 318
updating, 311
PivotChart Filter Pane, 318
PivotTable
Access database, creating from, 408-409
analyzing data with, 310
AutoFilter, 314-315
calculation, 314-315
conditional formatting rules, clearing, 141
creating reports, 310
data source for, 319
field settings, changing, 312
formatting reports, 313
GetPivotData functions, 437
modifying reports, 312
Quick Styles, 313
refreshing data in, 319
renaming, 318
repeating labels, 314-315
scenarios, reports from, 331
searching, 315
security for reports, 352
showing values, 314-315
slicer, 316-317
sorting, 315
updating, 311
placeholders
arguments, inserting, 103
SmartArt graphic, text in, 217
plastic surfaces for shapes, 248-249
Playback Status bar, 9
plot area, 269
background for, 286
PMT function, 102
PNG format
pictures, modifying, 207
shapes as pictures, saving, 198
Web pages allowing, 417
polygons. See freeforms; shapes
Pop-up Blocker, 362
portrait orientation, 172
presentation (PowerPoint)
Office Web App, creating, 485
presets. See also 3-D effects
to charts, 275
gradient fills as, 244
to SmartArt graphics, 225
preview buttons, 8
previewing. See also live preview
page breaks, 179
Web pages, 413
Print dialog box, 183
printer fonts, 135
printing, 171. See also columns and rows
all of worksheet, 184
comments, 172
formulas, 75
gridlines, 181
letters of columns, 181
numbers of rows, 181
print area, setting, 182
scaling, changing, 172-173
specific number of pages, fitting work-sheet on, 181
tables, 298
titles for columns and rows, 180
Privacy Options. See Trust Center
procedures in VBA (Visual Basic for Applications), 451
Product function, 83
program tabs, 4
Project Explorer, 451-452
projects in VBA (Visual Basic for Applications), 451
proofing options, changing, 63
PROPER function, 336
proportional shapes, drawing, 230
proprietary company information, accessing, 68
protection. See security
Protect Workbook button, 352
Publish as PDF or XPS add-in, 185, 186
publishing, 411. See also Web pages
Publish as PDF or XPS add-in, 185, 186
trusted publishers, 365
Q
queries
creating database query, 407
data from Web queries, importing, 423-424
importing, 423-424
new data source, defining, 406
opening query files, 13
refreshable Web queries, creating, 422
saving Web queries, 424
question mark (?) character, 336
commands, adding, 441
customizing, 7
macros, assigning, 461
Quick Print button, 184
resetting, 441
Quick Print button, 184
Quick Styles. See also Shape Quick Style
to lines, 234
for organization charts, 228
for pictures, 198
to shapes, 238
for SmartArt graphics, 222
to tables, 298
quitting Excel, 34
R
range references, 37
ranges, 37. See also calculations; named ranges
applying names to, 86-87
extending formats, 430
formulas, using in, 90
managing names, 88-89
multisheet ranges, selecting, 39
passwords for, 353
special ranges, selecting, 39
tables, converting, 302-303
viewing names, 88-89
zooming on name range, 88
ranking, formatting cell contents based on, 136-137
read-only workbooks, 12
marking as, 378
prompt to, 380
recent documents, 433
file extensions, displaying, 14
recoloring pictures, 206-207
Recolor Picture Quick Style gallery, 206
recording macros, 455
recovering workbooks, 30-31
Rectangle button, 230
red eye removal, 443
redoing actions, 70
Reference function, 104, 334-335
reference operators, 73
reflections
to charts, 275
to pictures, 201
to shapes, 247
to SmartArt graphics, 225
to WordArt text, 214
refreshing
external data source, data from, 405
PivotChart/PivotTable data, 319
Web queries, 422
relative cell references, 80
absolute references, changing, 81
labeling names, 89
removing. See adding/removing; deleting
renaming
dialog box, files in, 13
PivotChart or PivotChart, 318
worksheets, 110
repairing problems
macros, debugging, 458
Microsoft Office problems, 32
workbook problems, 32
repeating data, entering, 44
REPLACE function, 336
replacing. See finding and replacing
research
changing options, 439
parental controls, setting, 373, 439
topics, researching, 68
Research task pane
researching topics with, 68
translating text with, 69
Reset Picture button, 199
resizing
cell names, accommodating, 88
charts, 268
page size, 173
in Picture Manager, 443
shapes, 230-231
SmartArt graphics, 221
tables, 302-303
task panes, 15
Text pane, 218
vector images, 188
watermark picture, 177
window panes, 15
windows, 18
Review tab, 4
RGB color mode, 151
defined, 159
in themes, 159
add-in security options, 368
alignment, changing, 146
borders, applying or deleting, 152
Chart Elements list arrow, 269
Developer tab
setting options, 453
showing/hiding, 389
using, 4
right-alignment of objects, 253
R1C1 reference style, 437
roundtripping, 472
rotating. See also pictures; 3-D rotation
gradients, 245
objects, 257
Picture Manager, editing in, 443
shapes, 257
with SmartArt graphics, 220
WordArt text, 215
Row Differences option, 38
rows. See columns and rows
ruler units, setting, 433
Run dialog box in safe mode, 377
S
Safe for Initialization (SFI) controls, 369
safe modes
automated safe mode, 376-377
enabling, 376
user-initiated safe mode, 376-377
saturation of color, defined, 159
Save as type
list, 396
macros, saving, 462
Save button, 22
Save dialog box, 22-23
Save Workspace command, 127
saving. See also workbooks; XML (Extensible Markup Language)
Document Management Servers (SharePoint server), workbooks to, 498
in HTML (Hypertext Markup Language), 411
older version workbooks, 23
PDF documents, workbooks as, 185
setting options, 23
shapes as pictures, 198
Web queries, 424
workspace layout, 127
Scale to Fit Dialog Box Launcher, 181
scaling
paper sizes, 436
pictures, 202-203
print scaling, changing, 172-173
scanning
pictures, 195
scenarios
creating, 330
showing, 331
summaries, creating, 331
scientific format, 43
scope, names for, 84
Screen shot, inserting, 196
ScreenTips
for add-in security options, 368
for dialog box options, 8
for functions, 433
for hyperlinks, 415
for macros, 461
style, selecting, 428
for themes, 157
turning on/off, 6
scroll bars
for Active controls, 466
showing, 434
synchronizing scrolling of documents, 19
searching. See also clip art; finding and replacing
for cell values, 123
conditional formatting, cells with, 143
for help, 21
tables, custom searches of, 309
for themes, 156
security, 349. See also digital signatures; passwords;Trust Center
Active controls security settings, 369
add-in security options, 368, 449
for cell styles, 167
data connections, security settings for, 374
external content security options, 374-375
for external data, 403
file encryption, adding, 355
formulas, showing/hiding, 354
harmful attacks, avoiding, 362-363
locking/unlocking cells, 354
Message Bar security options, changing, 371
safe modes, 376-377
for shared workbooks, 385
XML data maps, 390
Select All button, 153
selecting
borders, 153
cells, 37
contiguous cells, 37
hyperlinks, 415
multisheet ranges, 39
noncontiguous cells, 37
ranges, 39
special ranges, 39
tables, columns and rows in, 302
worksheets, 110
Selection Mode Status bar, 9
Selection pane for objects, 255
Send to Back
objects, 255-256
with SmartArt graphics, 220
Send2Fax, 387
sepia mode for pictures, 206
series lines, 285
shadows
for data series, 283
to pictures, 201
to SmartArt graphics, 225
for text, 134-135
to WordArt text, 214
Shape Effects gallery, 224, 246
shape fills
to charts, 275
color fills, 241
gradient fills, applying, 244-245
picture fills, 242
Shape Outline button, 224
for lines or arrows, 234-235
shape outlines, 224, 234-235, 241
to charts, 275
for connecting shapes, 254
to text, 239
shapes, 229, 246-247. See also SmartArt graphics
adjusting, 231
applying shapes to pictures, 199
Clip Organizer, adding to, 260
colors
fills, adding, 240-241
outlines, applying, 241
connecting, 254
customizing creation of, 440
deleting, 230
color fills, 240
drawing, 230
flipping, 257
freeforms, converting to, 236
gradient fills, applying, 244-245
grouping/ungrouping, 258-259
individual effects, adding, 247
multiple shapes, inserting, 232
organization chart, adding to, 227
other shapes, changing to, 232
picture fills, applying, 242
presets, adding, 246
proportional shapes, drawing, 230
replacing, 231
resizing, 230-231
rotating, 257
saving as pictures, 198
textures, 243
3-D effects, adding, 248-249
3-D rotation, adding, 250
transparency, applying color fill with, 241
shape text, 233
Quick Styles, adding, 239
shared workbooks, 379, 380-381. See also comments
e-mailing shared workbooks, 386
enabling sharing, 380
faxing workbooks, 387
options for sharing, changing, 381
security for, 385
tracking changes in, 384-385
SharePoint, 1, 295. See also Groove
Groove
alerts, 496
creating workspaces, 494-495
invite others to use, 497
manage Document tools, 496
sharing files with, 496
Office Mobile, 500
Microsoft SharePoint Workspaces, 494-497
Server
co-authoring documents, 499
opening from, 499
saving to, 498
shared folder, 494
tables, exporting, 324
workspaces, 494-495
sharing. See also shared workbooks
Groove, files in, 494-496
OLE (object linking and embedding) and, 394-395
SharePoint, files in 494-495
Shockwave Flash Object Active control, 469
Shortcut menu
commands from, 5
group-related commands, 258
shortcuts, 4
from Start menu, 2
short date format, 43
showing/hiding. See also tables; workbooks
chart data tables, 280-281
columns and rows, 117
Document Inspector and, 350-351
formulas, 354
objects, viewing, 256
Text pane, 218
worksheets, 114
shrinking text to fit, 148
signatures. See also digital signatures
Status bar, checking with, 9
visible signature line, adding, 360-361
Signatures task pane, 358
Single File Web Pages, 417, 418-419
size. See resizing
Size and Position dialog box
for pictures, 202-203
Size Dialog Box Launcher, 202-203
shapes, resizing, 230
SkyDrive. See Windows Live
Slicer
creating, 316
deleting, 316
local slicer, 316
PivotChart, 318
PivotTable, 316-317
shared slicer, 316-317
Stand-alone slicer, 317
sharing, 317
using, 316-317
SLK format, 25
SmartArt graphics. See also organization charts
adding shapes to, 220
arranging shapes in, 221
changing shapes in, 220
colors, changing, 223
creating, 216
deleting shapes from, 219
editing shapes in, 221
effects, applying, 225
fills, applying, 224
formatting, 222-223
shapes, 224-225
layouts, changing, 223
orientation, changing, 222
outlines for, 224
picture graphic layouts, 226
placeholder text in, 217
purposes, 217
Quick Styles, applying, 222
resizing, 221
text, converting, 217
Text pane with, 218-219
soft edges
to charts, 275
to organization charts, 228
to pictures, 201
precedence of, 246
to SmartArt graphics, 225
using, 332-333
sorting
cell names, 88
data, 295
fields, 306-307
tables, data in, 306
sounds
feedback with, 436
inserting, 189
source files, 394
spam, 362
sparkline formatting, 144-145
spell-checking, 62
dictionaries, customizing, 64-65
foreign languages, 444
proofing options, changing, 63
Spelling dialog box, 62
Spin button for Active controls, 466
splitters, 15
splitting worksheets into panes, 123
spoofed Web sites, 362-363
Spotlight workbook templates, 11
spyware, 362
squares, drawing, 230
stacking order, changing, 256
standard modules in VBA (Visual Basic for Applications), 451
standard tabs, 4
starting
Excel, 2
Groove, 494-495
SharePoint Workspaces, 494-495
Start menu, 2
Groove, launching, 494
Statistical functions, 345
Status bar
calculations, displaying, 91
controls, 3
using, 9
STDEV or STDEV.S functions, 83
step mode, debugging macros with, 458
storing
cell contents, 49
data in Excel, 16
straight connectors, 254
straight lines, drawing, 234
strikethrough text, 134-135
styles. See also cell styles; charts; Quick Styles
ScreenTip styles, selecting, 428
sub procedures in VBA (Visual Basic for Applications), 451
subscript text, 135
SUBSTITUTE function, 336
subtotals
calculating, 92-93
list, data in, 337
in PivotTable reports, 313
SUM function, 71, 102. See also Conditional Sum Wizard
automatic calculations, 91
subtotals, calculation of, 337
SUMIF function, 338-339
summarize data functions, 338-339
superscript text, 135
surfaces
for data series, 283
shapes, adding to, 248-249
switching
between views, 29
between windows, 19
SYLK files, opening, 13
symbols, inserting, 66
synchronizing. See also Groove
scrolling of documents, 19
synonyms, 67
syntax for VBA, 450
T
Tab key for selecting objects, 258
tables, 295. See also PivotTable
Access table into workbook, exporting, 408
AutoFilter, displaying parts with, 308
banded columns and rows in, 300
calculations in, 301
chart data tables, showing/hiding, 280-281
clearing, 297
copying cell formats, 298
creating, 297
definitions, 296
deleting, 297
columns and rows, 304
duplicate rows, 304
drop-down list, entering data from, 305
duplicate rows, deleting, 304
elements, formatting, 300
first column, formatting, 300
formatting, 298
elements of table, 300
formulas, table names in, 437
inserting columns and rows in, 302
last column, formatting, 300
multiple fields, sorting data using, 307
names, 84
one-input data table, creating, 328
printing, 298
Quick Styles, applying, 298
range, converting to, 302-303
resizing, 302-303
searches, creating, 309
selecting columns and rows in, 302
SharePoint lists, exporting tables to, 324
showing/hiding
chart data tables, 280-281
elements, 300
sorting data in, 306-307
styles
creating or modifying, 299
Quick Styles, applying, 298
ungrouping, 258
Table tab, 4
colors, adding or removing, 154
dialog box options, 8
displaying, 434
task panes, 15. See also specific types
templates. See also charts
changing, 130
creating, 128
customizing, 130
default templates, 129
Document Information Panel template, selecting, 470
locating, 129
macro-enabled templates, 462
opening, 129
for workbooks, 11
text. See also charts; shape text; WordArt
columns, converting to, 321
concatenation, 73
customizing creation of, 440
flow of text, controlling, 148
formatting, 134-135
for hyperlinks, 416
labels, entering, 40
SmartArt graphics, converting to, 217
themes, text colors in, 156
translating text, 69
wrapping text in cells, 74, 148
text boxes, 8
for Active controls, 466
in charts, 287
creating, 233
Text Effects gallery, 214
text files
opening, 13
retrieving data from, 403
Text pane
for organization charts, 227
showing/hiding, 218
with SmartArt graphics, 218-219
tasks with text in, 219
texture fills, 243
textures in WordArt, 212-213
accent colors, 156
applying, 157
background colors, 156
custom themes
choosing and applying, 164
creating, 163
files, selecting and applying from, 165
selecting, 159
deleting
custom color themes, 159
DocumentThemes folder, custom themes from, 164
effects, 162
fonts, 162
DocumentThemes folder, 163-164
effects, choosing, 162-163
existing color theme, changing, 158
files, selecting and applying themes from, 165
fonts, 160-161
deleting, 162
hyperlink colors, 156
other programs, use in, 157
text colors, 156
viewing, 157
effects, 162
thesauruses, 67
Research task pane for accessing, 68
3-D cell references, 80
creating, 83
3-D effects. See also 3-D rotation
for data series, 283
precedence of, 246
to shapes, 248-249
3-D rotation
to charts, 275
to organization charts, 228
to pictures, 201
precedence of, 246
to SmartArt graphics, 225
to WordArt text, 214
tick marks
changing, 276
editing, 267
tilde (~), 336
times. See dates and times
titles. See charts
toggle buttons for Active controls, 466
toolbars. See also Mini-Toolbar; Quick AccessToolbar
working with, 6-7
tooltips for functions, 72
top-alignment of objects, 253
tracer arrows, 100
tracing worksheet relationships, 100
Track Changes feature, 384-385
accepting/rejected tracked changes, 384-385
transform effect for WordArt text, 214
translating text, 69
translucent surfaces for shapes, 248-249
transparency
for picture backgrounds, 207
shape color fill with, 241
trendlines, 285
Trojan Horse, 362
TrueType fonts, 135
truncating long labels, 41
Trust Center, 349. See also macros
Active controls security settings, 369
add-in security options, setting, 368
documents, 366-367
locations, selecting, 365
parental controls, setting, 373
Privacy Options, 363
parental controls, setting, 373
setting, 372-373
publishers, selecting, 365
safe modes, enabling, 376-377
viewing, 364
Trusted Documents, 366-367
TuCows, 363
turning on/off
AutoComplete option, 41
Formula AutoComplete, 77
ScreenTips, 6
Track Changes feature, 384
two-input data table, creating, 328
U
underlining comments, 383
in Picture manager, 443
pie chart explosion, 282
for shape fills, 240
ungrouping. See grouping/ungrouping
unhiding. See showing/hiding
Uniform Resource Name (URN), 470
Universal Data Connection (UDC) files, 404
Universal Naming Convention (UNC), 470
up/down arrows, 8
UPPER function, 336
URLs (Uniform Resource Locators), 413, 470
Use in Formula command, 86-87
user-initiated safe mode, 376-377
Use row and column names option, 87
V
validation, adding, 322
values, 36. See also scenarios
AutoFill, entering with, 44-45
deleting values, keeping formulas, 96
entering, 42-43
formatting, 43
formulas, converting, 85
searching for, 123
VAR functions, 83
VBA (Visual Basic for Applications), 445. See also macros
Active controls, adding code to, 468
definition of terms, 450
enhancing workbooks, 450
modules, 451
procedures, 451
projects, building, 451
structure of, 450
viruses, avoiding, 362-363
writing code, 450
vector images, 188
vertical alignment, 146-147
of WordArt text, 215
vertical page breaks, 178
view buttons, 3
viewing. See also Groove; themes; work-books; zooming
add-ins, 446
clips on Web, 190
digital signatures, 359
display view options, setting, 433
Excel window, 3
hidden objects, 256
safe mode, disabled items in, 377
switching between views, 29
Trust Center, 364
Visual Basic Editor, 452
workbook document properties, 28
XML documents, 388
View tab, 4
Viewer, Excel software, 12
viruses, avoiding, 362-363
Visual Basic Editor, 451-452
viewing, 452
Visual Basic for Applications. See VBA (Visual Basic for Applications)
VLOOKUP function, using, 334
VML, Web pages allowing, 417
W
washout mode for pictures, 206
Watch window
deleting cells from, 98
formula errors, correcting, 98
watermarks, inserting, 177
Web cameras, inserting pictures from, 195
Web pages
appearance, changing, 412
Check Office Documents that are from or link to suspicious Web sites option, 363, 372
copying Web table to worksheets, 421
hyperlinks to, 414
interactive Web page, publishing work-sheet as, 420
opening workbook as, 412
options, changing, 417
previewing, 413
refreshable Web queries, creating, 422
saving worksheets/workbooks as, 418-419
as Single File Web Pages, 417, 418-419
spoofed Web sites, 362-363
Web servers, 420
What-If Analysis button, 328
Goal Seek with, 329
width of columns, adjusting, 120-121
wildcard characters, 336
window panes, 15
worksheets, splitting, 123
windows
arranging, 18-19
copying contents of, 198
side by side comparisons, 19
switching between, 19
Windows Explorer
exclusion dictionaries, finding, 65
starting Excel from, 2
Windows Live
adding files to, 486
copying documents, 487
creating documents, 485
deleting documents, 487
downloading files, 488-489
embedding links, 492-493
moving documents, 487
opening documents, 476-477
renaming documents, 487
saving documents, 476-477
sending links, 492
SkyDrive
browsing folders, 480-481
creating folders, 482
deleting folders, 483
renaming folders, 483
setting folder permissions, 484
Windows Live ID, 471-472, 474-475
sign-in, 475
Windows Mobile, 500
Windows Rights Management XPS compatibility, 186
wire frame surfaces for shapes, 248-249
WMF format, 188
WordArt, 187
deleting text, 211
direction of text, changing, 215
effects, applying, 214
formatting text, 212-213
inserting, 211
rotating text, 215
styles, 212-213
WordArt Quick Style gallery, 211
for shape text, 239
Workbook Connections dialog box, 404-405
workbooks, 1. See also links and linking; read-only workbooks; security; shared workbooks; VBA (Visual Basic for Applications); Web pages; worksheets
AutoRecover for, 31
blank workbooks, 10
closing, 34
Compatibility Checker for, 26
consolidating data, 400-401
converting existing workbooks, 14
custom views, creating, 126
deleting custom views, 126
diagnosing problems, 32
display options, setting, 434
Document Management Servers (SharePoint server), workbooks to, 498
elements, showing/hiding, 124
e-mailing shared workbooks, 386
existing workbooks
converting, 14
opening, 12-13
faxing workbooks, 387
file encryption, adding, 355
final, marking as, 378
HTML (Hypertext Markup Language), saving in, 411
hyperlinks within, 414
inspecting documents, 350-351
keyboard, navigating with, 17
macros
opening workbook with, 463
saving workbook with, 462
modifying, 109
names for, 10
navigating in, 16-17
new options, changing, 429
Office Web App, creating, 485
opening, 12-13
with macros, 463
passwords for, 356-357
PDF format
saving workbooks in, 185
sending workbooks in, 386
properties, viewing and editing, 28
recently opened workbooks, opening, 13
recovering, 30-31
repairing problems, 32
saving, 22-23
different formats, workbooks
with, 24-25
with macros, 462
older version workbooks, 23
showing/hiding, 114-115
custom views, 126
elements of workbook, 124
as Single File Web Page, 418-419
specific location, navigating to, 17
templates for, 11
tracking changes, 384-385
viewing
custom views, creating, 126
in full screen, 124
window, 3
Windows Explorer, opening from, 2
XPS documents, saving as, 186
zooming on, 125
worksheets, 1. See also formatting; page layout; printing; tables; themes; Web pages
all worksheets, selecting, 110
auditing, 100
backgrounds, changing, 112
charts, linking, 278
colors of tab names, changing, 115
consolidating data, 400-401
copying, 112-113
data validation, adding, 322
deleting, 111
designing, 131
display options, setting, 434-435
entire worksheet, selecting, 116
groups, forming, 112
hiding/unhiding, 114
inserting, 111
inspecting information in, 351
links, creating, 402
modifying, 109
moving, 112
naming, 110
numeric formats, 132-133
panes, splitting into, 123
passwords for, 352-353
renaming, 110
selecting, 110
as Single File Web Page, 418-419
specific number of pages, fitting on, 181
splitting into panes, 123
tabs, controls on, 4
tracing relationships in, 100
Web tables, copying, 421
workspace. See also Groove
layout, saving, 127
opening workspace files, 127
worms, avoiding, 362-363
wrapping text in cells, 74, 148
X
X-axis. See chart axes
XLA format, 25
XLAM format, 25
XLSB format, 25
XLS format, 24-25
XLSM format, 25
XLSX format, 25
XLT format, 25
XLTM format, 25
XML (Extensible Markup Language), 1, 25, 379
data maps
creating, 390
properties, changing, 389
expansion packs, adding, 391
exporting data in, 392
importing XML data, 391
inspecting documents, 350-351
saving
data in XML format, 392-393
workbooks as XML file, 22
working with, 388-389
XML Schema, 379
attaching, 388
XML Source task pane, 388, 390
saving workbooks as, 186
workbooks sent as, 386
Y
Y-axis. See chart axes
Z
zero value cells, 435
zip file, creating, 488-489
zooming
on chart objects, 269
controls, 3
with IntelliMouse, 16
on name range, 88
in workbooks, 125