C | MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

calculated fields

in PivotTables, 734–738

in Query, 777

calculated table columns, 455

Calculating message, 460, 463

calculations

columns in tables, 455

customizing in PivotTables, 732–734

displaying PivotTable details, 741–742

Excel Web Access and, 821

iterative, 466

keyboard shortcuts, 937

PivotTables, 734, 736–738

Query, 764, 774–777

recalculation process, 460–467

calling custom functions, 854–857

callouts, 355–356

Camera button, 395–397

canceling actions, 930

candlestick charts, 663

capacity of workbooks, 9–10

capitalization. See also case sensitivity

correcting use of Caps Lock key, 228

first letter of sentences, 228

names of days, 228

sorting and, 683–684

text in graphics, 372

two initial capitals, correcting, 228

VBA variables, 861

Caps Lock mode, 127

capturing images of worksheets, 395–398, 805, 945

caret (^), 142–143, 458

carriage returns, 458, 621

case sensitivity. See also capitalization

file names, 52

FIND function, 505

searching and replacing, 221

sorting text, 683–684

VBA variables, 861

categories

functions, 493

multilevel, in charts, 658–660

PivotTables, 712–713

category axes

changing scale of, 645–651

compared to value axes, 625

intersection, 647–648

labels on, 642, 647

moving labels, 647

multilevel, 658–660

nonadjacent data sources and, 656–657

overlapping labels, 647

switching between text and dates, 648

cautions. See also troubleshooting

cell styles, 282, 284

clearing series, 203

deleting, 200

Fill option, 318

formatting, data alignment, 318

hiding zero values, 100

merging cells, 337

passwords, 163

relative references in macros, 832

themes, 277

.xlk file extension, 58

CDs, calculating values, 536–537

CEILING function, 501, 951

CELL function, 951

cell ranges

as arguments, 491

array formulas, 468–473

automatically expanding, 498

case-sensitive text and, 683–684

columns in, 676–678

converting tables to, 670

creating PivotTables, 715

creating tables with, 669

current rows, 703

custom lists and, 682–683

dates in, 682–683

For Each statements and, 867

formulas and, 468–473, 680–682

hyperlinks to, 877, 878

linking to named ranges, 897

named, 441–452, 897

navigating, keyboard shortcuts, 943

partial list sorting, 678–679

pasting into Word, 891–893

rows in, 679–680, 703

sending by e-mail, 802

sorting, 675–684

cell references

ADDRESS function and, 516

adjusting in linked workbooks, 476–477

as arguments, 491

automatically expanding, 498

circular, 464–466

computed filter criteria and, 695–697

copying, 432–435

dynamic hyperlinks and, 880

entering, 429

external, 430–431

in formulas, 428–429

in functions, 495

INDEX function and, 517

inserting or deleting, 435

in linked workbooks, 476–477

macros and, 832

in other worksheets, 430–431

reference syntax, 435

relative, absolute, and mixed, 429–430

replacing with names, 451–452

Scenario Manager and, 590

sorting issues and, 680–682

structured references, 454–461

three-dimensional, 448–449

understanding rows and columns, 431–432

cell styles. See also formatting

attributes, specifying, 280

categories, 279

cautions, 282, 284

creating by example, 281–282

customized, 280–284

deleting, 284

duplicate names, 284

duplicating, 282–283

formatting with, 278–284

merging, 283–284

modifying, 282–283

style categories, 279–280

and themes, 278

cells (tables), 5–6, 9. See also tables

cells (worksheets)

active, 25, 128, 429

adding to selections, 127

alignment in, 318

array ranges and, 469

attributes, 280

background colors, 331

clearing, 202–204

comments in, 251–252

conditional formatting, 284, 287

counting, 510, 554

currently selected, 25

cutting, copying, and pasting, 207

data formats, 5–6, 9

deleting, 198–202, 203–204

direct editing, 209

disabling editing, 208

displaying text in text boxes, 353

distributing contents, 318

dynamic properties, 62

editing shortcuts, 936

entering data, 937–938

error flags in, 582

extending formatting, 209

filling with characters, 318

formatting contents, 209, 278–284

going to named cells, 452

hidden, charting, 658

hiding, 162–163

hyperlinks and, 874–879

identifying contents, 510, 511

implicit and explicit intersections, 452–453

indenting contents, 317–318

inserting, 197–198, 203–204

jumping to, 28

keyboard shortcuts, 931–932, 941

last cell in active area, 133

linking custom properties, 62

locking, 156–157

maximum number of, 25

maximum size of, 139

merging, 283–284, 337–339

named, 441–452, 604

new lines in, 936

overlapping, 141

placeholders, 584

range finders, 208

resizing, 334–339

returning contents, 519

selecting, 128–131, 133, 349, 944

selecting without activating hyperlinks, 879

selection rectangles, 203

single-cell array formulas, 471

special item specifiers, 458

themes and, 620

tracing references, 245–246

unlocking, 157–158

unmerging, 337–339

value changes and recalculation, 460

visible, selecting, 133

zero values in, 100

center handles on shapes, 347, 348

centering items, 393, 408, 723

change tracking, 794–797, 916

changing cells

displaying in reports, 595

Goal Seek command, 600

merged scenarios, 595

reports, 598, 610

scenarios, 591

Solver add-in, 603, 604

CHAR function, 504, 951

character heights, 372

character spacing, 372

characters

case of, 503, 505

cell names, 445

deleting from text, 503, 504

delimiters, 751–752

heights, 372

limits for functions, 491

#NAME? errors, 490

Chart menu commands and options, 921

chart objects, 617, 633–634

Chart Tools feature, 13–14, 911

charts

axes, 625–626

axes labels, 627–628, 642–643

axis line styles, 642

axis titles, 626–628

base units, 649–651

chart objects, 617, 633–634

combining two types, 618

data labels, 623–624, 652–653

data sources, 615–616, 654–658

data tables, 624

double-clicking elements, 634

empty and hidden cells and, 658

error bars, 661–662

fills, 637–640

flipping, 645

formatting areas in, 637–641

formatting text in, 641–642

gridlines, 628, 646

high-low lines, 662–663

Histogram tool, 572

hyperlinks in, 873–874, 879

intersection of axes, 644–645, 647–648

keyboard shortcuts, 935

layouts, 619–620

legends, 622–623

logarithmic scaling, 645

moving averages, 660–661

moving elements of, 635

multilevel categories in, 658–660

multiple series charting, 653

new Ribbon features, 615

numeric formats in labels, 643

overlapping labels, 647

overlays, 618

PivotCharts, 659, 742–744

placing on chart sheets, 620–621

recovering, 67

rotating text, 627–628

saving as templates, 631–632

scaling factors, 645–651

selecting elements of, 633–634

Shape Styles feature, 629–631

in shared workbooks, 792

styles, 620

switching rows and columns, 618–619

switching types, 618

text annotations, 628–629

textures or pictures in, 640–641

themes and, 620

tick marks, 642–643, 644

titles, 621–622, 627–628

trendlines, 660–661

types of, 616–618

up and down bars, 662–663

Word documents and, 899–901

WordArt styles, 629–631

checking documents

for correct data with smart tags, 229

in or out, 810–811

for spoofing or phishing, 110

CHIDIST function, 951

CHIINV function, 951

CHITEST function, 951

CHOOSE function, 516, 952

chronological sorting, 682–683

circles, 348

circular references, 464–466

Circular Reference.xlsx file, 465

classes of objects, 836–837

CLEAN function, 503, 504, 952

Clear menu, 202–203

Clear Series command, 203

clearing. See also deleting; removing

array assignments, 469

breakpoints, 863

caution, 203

cell contents, 203

cells, 202–204

comments, 203

conditional formatting rules, 293–294

formats, 203

highlighting, 795

print area definitions, 413–414

table formatting, 706

clicking

to create documents, 873, 877–878

double-clicking chart elements, 634

client/server database applications, 803

client-side IRM components, 803

clip art

examples of, 398–401

filling chart areas with, 640–641

formatting and adjusting, 369

inserting, 362–364

managing, 363–364

searching for, 364

Clip Organizer, 364–365

Clipboard

assembling lists, 183

copying and pasting into Word, 891–893

copying and pasting multiple copies from, 182–185

defined, 181

displaying, 182

emptying, 182

importing and parsing data on, 755

Paste Options smart tag, 185

CMA (Competitive Market Analysis), 563

code

adding to macros, 841–843

breakpoints, 863–864

built-in custom formats, 311–312

comments in, 852

custom functions, 848

debugging, 859–869

displaying for macros, 834

error handlers, 867–869

monitoring, 866

stepping through, 862–863

Watch Window, 245, 865

CODE function, 504, 952

coefficients of determination, 561–562

collaboration. See also SharePoint Services

advanced sharing options, 793–794

combining changes to multiple workbooks, 798–800

contacting workgroup members, 811–812

distributing files by e-mail, 800–802

Excel Services, 820–823

exporting tables, 818–820

group editing, 240–241

IRM (Information Rights Management), 803–806

new and changed features, 15, 19

publishing workbooks, 821–823

reviewing changes, 797

Send For Review command, 802

SharePoint Services sites, 807–820

sharing files on remote computers, 789–790

sharing files outside Excel, 70–72

sharing workbooks, 51

sharing workbooks on networks, 790–798

turning off sharing, 797, 798

collapsing

dialog box functionality, 447

field headings, 724

outline levels, 256, 257

collating printouts, 418

Collect and Copy feature, 182–183

collections (clip art)

adding art to, 364

copying art to, 363

creating new, 364

organizing, 363–364

collections (VBA), 838–839, 867

colon (:), 458, 840

color gradients. See gradient fills

color scales in conditional formatting, 6, 285

Color Scales palette, 6, 7

colors

accessibility features, 97, 100

adjusting in images, 369

applying, 275

automatic, 325, 636

breakpoints in code, 863

cell backgrounds, 331

chart axes, 642

conditional formatting, 285

custom formats, 313–314

embedded object borders, 384

fills, 376–377

filtered lists, 684, 692

fonts, 325

formatting, 313–314, 329–334

gridlines, 100

lines in charts, 635

new and changed features, 9

number of, 9

pasted tables in Word, 891–893

printing in black and white, 416

schemes, 707

Shape Styles feature, 631

sheet tabs, 100

switching, 707

syntax errors, 860

table styles, 706

themes and, 706, 709

Visual Basic Editor display, 835

worksheet tabs, 101

column differences, 135–136

COLUMN function, 519, 952

column specifiers

examples, 457

filling cells and, 460

reference operators, 458

in tables, 455

columns (array), 473

columns (chart), 618–619, 630, 637

columns (table)

adding to PivotTable data, 715

calculated, 455

headings, 10

hiding PivotTable labels, 724

inserting, 667, 671–672

new and changed features, 9, 10

number of, 9

organizing, 669

selecting, 673

columns (text), 383

columns (worksheet). See also fields

accessibility and, 96, 98

AutoFill feature, 216

AutoFit feature, 335

banding, 271, 706

cell references and, 431–432

centering text across, 318

column differences, 135–136

data types, 753

deleting, 198–202

design guidelines, 117–118

displaying or hiding, 944

filtering by, 684, 686, 690–693, 695

fixed-width text imports and, 752–753

headings, 96, 98, 414–415

hidden, 337, 658

inserting, 194–196

keyboard shortcuts, 940

lettering, 25

in mail merge data, 901

mapping XML elements to, 757–758

in names, 452

pasting, 185, 187

printing headings, 414–415

resizing in Page Layout view, 406

returning identifiers, 519

returning numbers of, 520

selecting, 944

selecting in Query, 761

sorting, 675–680

specifiers. See column specifiers

splitting imported data into, 751

in VLOOKUP and HLOOKUP functions, 513

width, 185, 187, 334–335

COLUMNS function, 520, 952

COMBIN function, 500, 952

combining

changes to multiple workbooks, 798–800

chart types, 618

consolidating worksheets, 258–264, 799

comma (,)

array constants, 471, 472

data entry, 138

formatting while typing, 297–298

in imported data, 755

separating arguments, 491, 840

structured references, 457, 458

styles of, 298

Comma Delimited (CSV) file format, 71, 72, 750

comma-separated-values files (CSV), 71, 72, 750

commands

choosing for Quick Access Toolbar, 86–87

command tabs, 86–87

finding in Excel 2007, 907–925

hyperlinks and, 879

keyboard command mode, 39–40

keyboard shortcuts, 39–40, 942

modifiers, 89

not in Ribbon, 88

Quick Access Toolbar, 86–87, 88, 89

comments. See notes and comments

Compact PivotTable format, 722

compacting clip art, 364

comparing workbooks, 18, 170–171, 798–800

comparison criteria in queries, 773–774

Compatibility Checker, 54–56

Compatibility mode, 54–56, 670, 893

compatibility with older versions

error messages, 55

exporting files, 72

File Format Compatibility Pack, 56

overview, 54–56

Competitive Market Analysis (CMA), 563

compiler, design-time errors, 859–861

completed tasks, 812

COMPLEX function, 952

compound lines, 636

compressing

objects, 385

pictures, 373–374

computed criteria, 695–697

CONCATENATE function, 507, 952

concatenating

CONCATENATE function, 507, 952

text strings, 507

text values, 436

conditional breakpoints, 863–864, 865

conditional formatting

clearing rules, 293–294

color scales, 285

copying rules, 293–294

creating rules, 290–291

data bars, 285, 288

date ranges, 287

duplicate views, 287

Excel Web Access and, 821

formulas, 294–295

highlight cells rules, 287

highlighting, 284

icon sets, 285

jumping to cells with, 133

managing rules, 291–294

new and changed features, 7–8, 9

number ranges, 287

relative values, 285

searching for rules, 293–294

in shared workbooks, 792

text strings, 287

threshold values, 284–285

top/bottom rules, 285, 287

unique views, 287

Conditional Formatting command, 131–132, 284, 294, 915

Conditional Formatting Rules Manager, 291–294

conditional functions, 507–510, 555

conditional operators, 314–315

Conditional Sum Wizard, 478–483

conditional test formulas

Conditional Sum Wizard, 478–483

Lookup Wizard, 478–479, 483–485

overview, 478

conditions. See also constraints

inserting in worksheets, 484

removing in conditional sums, 480

cones in charts, 637

confidence, 569, 661–662, 953

CONFIDENCE function, 953

conflicts in shared workbooks, 791, 794

connection files

ODC files, 745–746

properties, 747–748

connection points on shapes, 354–356

connections

to external sources, 760

ODC files, 745–746

properties, 747–748

to tables on SharePoint sites, 818–820

Web queries, 779

connectors, 354–356

consolidating worksheets

by category, 262–264

collaborating, 799

links to source worksheets, 264

overview, 258–259

by position, 259–261

consolidation ranges, 711

constants

array, 468

categories of, 136

finding, 131–132

jumping to, 133

in linear regression, 561

naming, 448

in scenarios, 592

constrained shapes, 347–348

constraints. See also conditions

integers and, 605–606

in reports, 610

Solver add-in, 602, 603, 604–606

contacts

instant messaging, 812

online, 807

workgroup members, 811–812

Contains filtering, 688

contextual tabs, Quick Access Toolbar, 86

contextual tool sets, 36–37. See also ScreenTips

contours of shapes, 380

contrast in images, 369, 381

Contributor privileges, 808

Control menu keyboard shortcuts, 942

convergence, 465

CONVERT function, 953

converting

Euro currencies, 549–551

external references to formulas, 476

menu and command locations in Excel 2007, 907–925

older files with automatic calculations, 461

older files with natural language formulas, 456

older files with tables, 670

tables to cell ranges, 274, 670

copying. See also cutting; pasting

assembling lists, 183

buttons for, 181

cell references, 432–435

cell styles, 282–283

cells or formulas in linked workbooks, 476–477

charts into Word, 900–901

clip art, 363

Clipboard features, 181–185

Collect and Copy feature, 182–183

conditional formatting rules, 293–294

conditional sum formulas, 480–482

data into e-mail, 802

data table results, 588

direct cell manipulation, 192–194

emptying Clipboard, 182

filtered rows to other locations, 697–698

history worksheet, 796

images of worksheets, 397–398

IRM-protected documents, 805

keyboard shortcuts, 182, 941

lookup formulas, 484

macro code, 842

moving with mouse, 192–194

multiple items, 182–185

Paste Options smart tag, 185

pasting, 182–185

pictures from other applications, 365–366

shapes, 348

structured references, 460–461

table formats, 273

tables into Word, 891–893

Web query data, 784

worksheet segments with graphics, 390

worksheets, 946

corner points on shapes, 352

CORREL function, 953

correlating tables, 575

corrupted files, 65, 66–67

COS function, 953

COSH function, 953

costs of assets, 542

COUNT function

overview, 554, 953

in PivotTables, 732

in Query, 775

total rows in tables, 675

COUNT NUMBERS function, 732

COUNTA function, 556, 704, 953

COUNTBLANK function, 510, 953

COUNTIF function, 555, 953

COUNTIFS function, 953

counting functions in Descriptive Statistics tool, 569

COUNTNUMBERS function, 675

COUPDAYBS function, 548, 954

COUPDAYS function, 548, 954

COUPDAYSNC function, 548, 954

COUPNCD function, 548, 954

COUPNUM function, 548, 954

coupon rates, 544, 548

COUPPCD function, 548, 954

COVAR function, 954

crash recovery. See recovering data

creating

blank workbooks, 873, 877–878

buttons, Quick Access Toolbar, 91

cell styles, 280–284

collections (clip art), 364

conditional formatting rules, 290–291

custom functions, 847–848

data sources, 760

documents and files, 873, 877–878

e-mail messages, 874, 878–879

formulas, 12

header row styles, 708

hyperlinks, 191, 874–879

macros, 95

mini Ribbon, 88

names automatically, 446–447

numeric formats, 307–312

objects, 886–887

PivotCharts, 742–744

PivotTables, 715–719

regression analyses, 553

scenarios, 593

SmartArt feature, 358–362

table styles, 707–709

tables, 669–673, 746–747, 897–899

template tabs, 49–50

text boxes, 352–353

WordArt, 356–358

workbooks, 46–48

workspaces, 60–61, 816–818

CRITBINOM function, 954

criteria (queries)

comparison, 773–774

components of, 761–762

exact matches, 770–772

removing, 762, 773

criteria ranges

Advanced filtering, 689–690

computed criteria, 695–697

filtering multiple criteria on same column, 695

naming, 693

cropping images, 370, 373

cross cursor, 879

CSV files (comma-separated-values), 71, 72, 750

Ctrl key, 927–946

CUBEKPIMEMBER function, 955

CUBEMEMBER function, 955

CUBEMEMBERPROPERTY function, 955

CUBERANKEDMEMBER function, 955

CUBESET function, 956

CUBESETCOUNT function, 956

CUBEVALUE function, 956

CUMIPMT function, 956

CUMPRINC function, 956

cumulative percentages, 571

currency

converting fractional pricing, 545

Currency format, 299–301

data entry, 138

default, 522

DOLLAR function, 503

Euro Currency Tools add-in, 549–551

formatting, 297–298, 299–301, 940

rounded values, 467

troubleshooting, 314

current arrays, 133

current date, 523, 531–532

current regions, 133

current time, 531–532

curves, 348–352

custom filters, 688–689

custom functions

adding to library, 856–857

adding to Visual Basic Editor, 856–857

arguments in, 849

availability of, 854–857

compared to macros, 847

creating, 847–848

defined, 847

documenting, 851–852