Microsoft Excel 2013

I: Mastering the New User Interface - Page 52
1. Staying Connected Using Excel 2013 - Page 53
Understanding the Dark Side of SDI - Page 54
Understanding the Dark Side of SDI - Page 55
Understanding the Dark Side of SDI - Page 56
Understanding the Dark Side of SDI - Page 57
Forcing Excel to Open in a New Instance - Page 58
Signing In to Excel 2013 - Page 59
Signing In to Excel 2013 - Page 60
Revealing the Fatal Flaw of the Start Screen - Page 61
Dismissing the Start Screen with the Escape Key - Page 62
Dismissing the Start Screen Permanently - Page 63
Using the Cloud for Storage and More - Page 64
Using the Cloud for Storage and More - Page 65
Using the Cloud for Storage and More - Page 66
Using the Cloud for Storage and More - Page 67
Using the Open and Save As Panels - Page 68
Using the Open and Save As Panels - Page 69
Using the Open and Save As Panels - Page 70
Using the Open and Save As Panels - Page 71
Using the Open and Save As Panels - Page 72
Using the Open and Save As Panels - Page 73
Using the Open and Save As Panels - Page 74
Using the Open and Save As Panels - Page 75
Using the Open and Save As Panels - Page 76
Using the Open and Save As Panels - Page 77
2. Introducing Flash Fill and Quick Analysis - Page 78
2. Introducing Flash Fill and Quick Analysis - Page 79
2. Introducing Flash Fill and Quick Analysis - Page 80
Coaching Flash Fill with a Second Example - Page 81
Coaching Flash Fill with a Second Example - Page 82
Coaching Flash Fill with a Second Example - Page 83
Inserting New Characters in a Telephone Number - Page 84
Using Commas Helps Flash Fill - Page 85
Using Commas Helps Flash Fill - Page 86
Using Commas Helps Flash Fill - Page 87
Flash Fill Will Not Automatically Fill in Numbers - Page 88
Using Formatting with Dates - Page 89
Using Formatting with Dates - Page 90
Using Filter to Flash Fill a Subset of Records - Page 91
Using Filter to Flash Fill a Subset of Records - Page 92
Using Filter to Flash Fill a Subset of Records - Page 93
Using Filter to Flash Fill a Subset of Records - Page 94
Troubleshooting Flash Fill - Page 95
Troubleshooting Flash Fill - Page 96
Discovering Interesting Things in Your Data Using the Quick Analysis - Page 97
Discovering Interesting Things in Your Data Using the Quick Analysis - Page 98
Discovering Interesting Things in Your Data Using the Quick Analysis - Page 99
Color Coding Cells in the Data - Page 100
Color Coding Cells in the Data - Page 101
Color Coding Cells in the Data - Page 102
Color Coding Cells in the Data - Page 103
Color Coding Cells in the Data - Page 104
Color Coding Cells in the Data - Page 105
Charting Your Data - Page 106
Charting Your Data - Page 107
Adding Statistics to the Bottom or Right Edge of Your Data - Page 108
Adding Statistics to the Bottom or Right Edge of Your Data - Page 109
Adding Statistics to the Bottom or Right Edge of Your Data - Page 110
Adding Statistics to the Bottom or Right Edge of Your Data - Page 111
Adding Statistics to the Bottom or Right Edge of Your Data - Page 112
Creating a Summary Report from Your Data - Page 113
Creating a Summary Report from Your Data - Page 114
Creating a Summary Report from Your Data - Page 115
Adding Tiny Charts to Each Row - Page 116
Adding Tiny Charts to Each Row - Page 117
Adding Tiny Charts to Each Row - Page 118
I’ve Used the Quick Analysis! Now What? - Page 119
I’ve Used the Quick Analysis! Now What? - Page 120
3. Using the Excel Interface - Page 121
Using Fly-out Menus and Galleries - Page 122
Using Fly-out Menus and Galleries - Page 123
Using Fly-out Menus and Galleries - Page 124
Rolling Through the Ribbon Tabs - Page 125
Rolling Through the Ribbon Tabs - Page 126
Rolling Through the Ribbon Tabs - Page 127
Rolling Through the Ribbon Tabs - Page 128
Rolling Through the Ribbon Tabs - Page 129
Rolling Through the Ribbon Tabs - Page 130
Rolling Through the Ribbon Tabs - Page 131
Rolling Through the Ribbon Tabs - Page 132
Rolling Through the Ribbon Tabs - Page 133
Resizing Excel Changes the Ribbon - Page 134
Resizing Excel Changes the Ribbon - Page 135
Activating the Developer Tab - Page 136
Activating the Developer Tab - Page 137
Finding Lost Commands on the Ribbon - Page 138
Finding Lost Commands on the Ribbon - Page 139
Shrinking the Ribbon - Page 140
Adding Icons to the QAT - Page 141
Removing Commands from the QAT - Page 142
Removing Commands from the QAT - Page 143
Assigning VBA Macros to Quick Access Toolbar Buttons - Page 144
Using the Full-Screen File Menu - Page 145
Using the Full-Screen File Menu - Page 146
Pressing the Esc Key to Close Backstage View - Page 147
Pressing the Esc Key to Close Backstage View - Page 148
Clearing the Recent Workbooks List - Page 149
Clearing the Recent Workbooks List - Page 150
Marking a Workbook as Final to Prevent Editing - Page 151
Marking a Workbook as Final to Prevent Editing - Page 152
Finding Hidden Content Using the Document Inspector - Page 153
Adding White Space Around Icons Using Touch Mode - Page 154
Adding White Space Around Icons Using Touch Mode - Page 155
Previewing Paste Using the Paste Options Gallery - Page 156
Accessing the Gallery After Performing a Paste Operation - Page 157
Accessing the Paste Options Gallery from the Right-Click Menu - Page 158
Accessing the Paste Options Gallery from the Paste Drop-Down - Page 159
Using the New Sheet Icon to Add Worksheets - Page 160
Using the Mini Toolbar to Format Selected Text - Page 161
Getting the Mini Toolbar Back - Page 162
Getting the Mini Toolbar Back - Page 163
Zooming In and Out on a Worksheet - Page 164
Switching Between Normal View, Page Break Preview, and Page Layout View Modes - Page 165
Switching Between Normal View, Page Break Preview, and Page Layout View Modes - Page 166
4. Customizing Excel - Page 167
4. Customizing Excel - Page 168
Adding a New Ribbon Tab - Page 169
Sharing Customizations with Others - Page 170
Questions About Ribbon Customization - Page 171
Introducing the Excel Options Dialog - Page 172
New Options in Excel 2013 - Page 173
New Options in Excel 2013 - Page 174
Using AutoRecover Options - Page 175
Controlling Image Sizes - Page 176
Ten Options to Consider - Page 177
Ten Options to Consider - Page 178
Five Excel Oddities - Page 179
Five Excel Oddities - Page 180
5. Extending Excel with Excel Apps and Add-Ins - Page 181
5. Extending Excel with Excel Apps and Add-Ins - Page 182
5. Extending Excel with Excel Apps and Add-Ins - Page 183
5. Extending Excel with Excel Apps and Add-Ins - Page 184
5. Extending Excel with Excel Apps and Add-Ins - Page 185
5. Extending Excel with Excel Apps and Add-Ins - Page 186
5. Extending Excel with Excel Apps and Add-Ins - Page 187
5. Extending Excel with Excel Apps and Add-Ins - Page 188
5. Extending Excel with Excel Apps and Add-Ins - Page 189
5. Extending Excel with Excel Apps and Add-Ins - Page 190
5. Extending Excel with Excel Apps and Add-Ins - Page 191
5. Extending Excel with Excel Apps and Add-Ins - Page 192
Using Traditional Add-Ins - Page 193
Loading PDF Data to Excel by Using Able2Extract - Page 194
Accessing More Functions by Using MoreFunc.dll - Page 195
Utilities for Data Analysis Tasks - Page 196
6. Keyboard Shortcuts - Page 197
Selecting Icons on the Ribbon - Page 198
Selecting Options from a Gallery - Page 199
Navigating Within Drop-Down Lists - Page 200
Backing Up One Level Through a Menu - Page 201
Using the Shortcut Keys - Page 202
Using the Shortcut Keys - Page 203
Using the Shortcut Keys - Page 204
Using the Shortcut Keys - Page 205
Using the Shortcut Keys - Page 206
Using the Shortcut Keys - Page 207
Using the Shortcut Keys - Page 208
Using the Shortcut Keys - Page 209
Using the Shortcut Keys - Page 210
Using the Shortcut Keys - Page 211
Using the Shortcut Keys - Page 212
Using the Shortcut Keys - Page 213
Using My Favorite Shortcut Keys - Page 214
Jumping to the Bottom of Data with Ctrl+Arrow - Page 215
Date-Stamp or Time-Stamp Using Ctrl+; or Ctrl+: - Page 216
Using Excel 2003 Keyboard Accelerators - Page 217
Invoking an Excel 2003 Alt Shortcut - Page 218
Determining Which Commands Work in Legacy Mode - Page 219
Determining Which Commands Work in Legacy Mode - Page 220
Determining Which Commands Work in Legacy Mode - Page 221
Determining Which Commands Work in Legacy Mode - Page 222
Determining Which Commands Work in Legacy Mode - Page 223
Determining Which Commands Work in Legacy Mode - Page 224
Determining Which Commands Work in Legacy Mode - Page 225
Determining Which Commands Work in Legacy Mode - Page 226
Determining Which Commands Work in Legacy Mode - Page 227
Determining Which Commands Work in Legacy Mode - Page 228
Determining Which Commands Work in Legacy Mode - Page 229
Determining Which Commands Work in Legacy Mode - Page 230
Determining Which Commands Work in Legacy Mode - Page 231
7. The Big Grid and File Formats - Page 232
Why Are There Only 65,536 Rows in My Excel 2013 Spreadsheet? - Page 233
Other Limits in Excel 2013 - Page 234
Other Limits in Excel 2013 - Page 235
Other Limits in Excel 2013 - Page 236
Tips for Navigating the Big Grid - Page 237
Using the Current Range to Navigate - Page 238
Using Go To for Navigation - Page 239
Using Go To for Navigation - Page 240
Using the New Binary File Format: BIFF12 - Page 241
Using the New Binary File Format: BIFF12 - Page 242
Using the New Binary File Format: BIFF12 - Page 243
Version Compatibility - Page 244
Version Compatibility - Page 245
Version Compatibility - Page 246
Opening Excel 2013 Files in Excel 2002 or 2003 - Page 247
Opening Excel 2013 Files in Excel 2002 or 2003 - Page 248
Creating Excel 2013 File Formats in Excel 2003 - Page 249
Opening Excel 2013 Files in Excel 2007 - Page 250
Opening Excel 2013 Files in Excel 2007 - Page 251
II: Calculating with Excel - Page 252
8. Understanding Formulas - Page 253
Introduction to Formulas - Page 254
Formulas Versus Values - Page 255
Building a Formula - Page 256
The Relative Nature of Formulas - Page 257
Overriding Relative Behavior: Absolute Cell References - Page 258
Overriding Relative Behavior: Absolute Cell References - Page 259
Using Mixed References to Combine Features of Relative and Absolute References - Page 260
Using the F4 Key to Simplify Dollar Sign Entry - Page 261
Using the F4 Key to Simplify Dollar Sign Entry - Page 262
Using the F4 Key to Simplify Dollar Sign Entry - Page 263
Using the F4 Key to Simplify Dollar Sign Entry - Page 264
Using F4 After a Formula Is Entered - Page 265
Using F4 on a Rectangular Range - Page 266
Using F4 on a Rectangular Range - Page 267
Three Methods of Entering Formulas - Page 268
Enter Formulas Using the Mouse Method - Page 269
Enter Formulas Using the Mouse Method - Page 270
Enter Formulas Using the Mouse Method - Page 271
Entering the Same Formula in Many Cells - Page 272
Entering the Same Formula in Many Cells - Page 273
Entering the Same Formula in Many Cells - Page 274
Copying a Formula by Dragging the Fill Handle - Page 275
Double-Click the Fill Handle to Copy a Formula - Page 276
Double-Click the Fill Handle to Copy a Formula - Page 277
Use the Table Tool to Copy a Formula - Page 278
Use the Table Tool to Copy a Formula - Page 279
Use the Table Tool to Copy a Formula - Page 280
9. Controlling Formulas - Page 281
Order of Operations - Page 282
Addition and Multiplication Example - Page 283
Addition and Multiplication Example - Page 284
Stacking Multiple Parentheses - Page 285
Understanding Error Messages in Formulas - Page 286
Understanding Error Messages in Formulas - Page 287
Understanding Error Messages in Formulas - Page 288
Using Formulas to Join Text - Page 289
Using Formulas to Join Text - Page 290
Joining Text and a Number - Page 291
Joining Text and a Number - Page 292
Joining Text and a Number - Page 293
Copying Versus Cutting a Formula - Page 294
Copying Versus Cutting a Formula - Page 295
Copying Versus Cutting a Formula - Page 296
Copying Versus Cutting a Formula - Page 297
Copying Versus Cutting a Formula - Page 298
Automatically Formatting Formula Cells - Page 299
Automatically Formatting Formula Cells - Page 300
Using Date Math - Page 301
Using Date Math - Page 302
Using Date Math - Page 303
Using Date Math - Page 304
Using Date Math - Page 305
Troubleshooting Formulas - Page 306
Troubleshooting Formulas - Page 307
Seeing All Formulas - Page 308
Editing a Single Formula to Show Direct Precedents - Page 309
Using Formula Auditing Arrows - Page 310
Using Formula Auditing Arrows - Page 311
Tracing Dependents - Page 312
Evaluate a Formula in Slow Motion - Page 313
Evaluating Part of a Formula - Page 314
Evaluating Part of a Formula - Page 315
Evaluating Part of a Formula - Page 316
Evaluating Part of a Formula - Page 317
10. Understanding Functions - Page 318
10. Understanding Functions - Page 319
Working with Functions - Page 320
The Formulas Tab in Excel 2013 - Page 321
Finding the Function You Need - Page 322
Using AutoComplete to Find Functions - Page 323
Using the Insert Function Dialog to Find Functions - Page 324
Getting Help with Excel Functions - Page 325
Using On-Grid Tooltips - Page 326
Using On-Grid Tooltips - Page 327
Using Excel Help - Page 328
Using AutoSum - Page 329
Using AutoSum - Page 330
Potential Problems with AutoSum - Page 331
Potential Problems with AutoSum - Page 332
Special Tricks with AutoSum - Page 333
Special Tricks with AutoSum - Page 334
Using AutoAverage or AutoCount - Page 335
Function Reference Chapters - Page 336
Function Reference Chapters - Page 337
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 338
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 339
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 340
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 341
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 342
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 343
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 344
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 345
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 346
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 347
11. Using Everyday Functions: Math, Date and Time, and Text Functions - Page 348
Examples of Math Functions - Page 349
Examples of Math Functions - Page 350
Examples of Math Functions - Page 351
Examples of Math Functions - Page 352
Examples of Math Functions - Page 353
Using AGGREGATE to Ignore Error Cells or Filtered Rows - Page 354
Using AGGREGATE to Ignore Error Cells or Filtered Rows - Page 355
Using AGGREGATE to Ignore Error Cells or Filtered Rows - Page 356
Using AGGREGATE to Ignore Error Cells or Filtered Rows - Page 357
Using AGGREGATE to Ignore Error Cells or Filtered Rows - Page 358
Using COUNT or COUNTA to Count Numbers or Nonblank Cells - Page 359
Syntax - Page 360
Syntax - Page 361
Using ROUND, ROUNDDOWN, ROUNDUP, INT, TRUNC, FLOOR, FLOOR.MATH, CEILING, CEILING.MATH, EVEN, ODD, or MROUND to Remove Decimals or Round Numbers - Page 362
Syntax: - Page 363
Syntax: - Page 364
Syntax: - Page 365
Syntax: - Page 366
Syntax: - Page 367
Syntax: - Page 368
Syntax: - Page 369
Syntax: - Page 370
Syntax: - Page 371
Syntax: - Page 372
Using SUBTOTAL Instead of SUM with Multiple Levels of Totals - Page 373
Using SUBTOTAL Instead of SUM with Multiple Levels of Totals - Page 374
Using SUBTOTAL Instead of SUM with Multiple Levels of Totals - Page 375
Syntax: - Page 376
Syntax: - Page 377
Using SUBTOTAL Instead of SUM to Ignore Rows Hidden by a Filter - Page 378
Using RAND and RANDBETWEEN to Generate Random Numbers and Data - Page 379
Using RAND and RANDBETWEEN to Generate Random Numbers and Data - Page 380
Syntax - Page 381
Choosing a Random Item from a List - Page 382
Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits - Page 383
Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits - Page 384
Syntax - Page 385
Using ABS() to Figure Out the Magnitude of ERROR - Page 386
Syntax - Page 387
Using PI to Calculate Cake or Pizza Pricing - Page 388
Using =COMBIN to Figure Out Lottery Probability - Page 389
Using COMBINA to Calculate Triple-Dip Ice Cream Bowls - Page 390
Syntax - Page 391
Syntax - Page 392
Using GCD and LCM to Perform Seventh-Grade Math - Page 393
Using MULTINOMIAL to Solve a Coin Problem - Page 394
Using MULTINOMIAL to Solve a Coin Problem - Page 395
Using MOD to Find the Remainder Portion of a Division Problem - Page 396
Using QUOTIENT to Isolate the Integer Portion in a Division Problem - Page 397
Syntax - Page 398
Using PRODUCT to Multiply Numbers - Page 399
Using PRODUCT to Multiply Numbers - Page 400
Using SQRT and POWER to Calculate Square Roots and Exponents - Page 401
Figuring Out Other Roots and Powers - Page 402
Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data - Page 403
Syntax - Page 404
Syntax - Page 405
Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS() - Page 406
Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS() - Page 407
Syntax: - Page 408
Dates and Times in Excel - Page 409
Dates and Times in Excel - Page 410
Dates and Times in Excel - Page 411
Dates and Times in Excel - Page 412
Understanding Excel Date and Time Formats - Page 413
Understanding Excel Date and Time Formats - Page 414
Understanding Excel Date and Time Formats - Page 415
Understanding Excel Date and Time Formats - Page 416
Understanding Excel Date and Time Formats - Page 417
Examples of Date and Time Functions - Page 418
Using NOW and TODAY to Calculate the Current Date and Time or Current Date - Page 419
Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart - Page 420
Using DATE to Calculate a Date from Year, Month, and Day - Page 421
Syntax - Page 422
Using TIME to Calculate a Time - Page 423
Using DATEVALUE to Convert Text Dates to Real Dates - Page 424
Using DATEVALUE to Convert Text Dates to Real Dates - Page 425
Using TIMEVALUE to Convert Text Times to Real Times - Page 426
Using TIMEVALUE to Convert Text Times to Real Times - Page 427
Using WEEKDAY to Group Dates by Day of the Week - Page 428
Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks - Page 429
Syntax - Page 430
Alternative Calendar Systems and DAYS360 - Page 431
Syntax: - Page 432
Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time - Page 433
Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time - Page 434
Syntax - Page 435
Syntax - Page 436
Syntax - Page 437
Using EDATE to Calculate Loan or Investment Maturity Dates - Page 438
Using EOMONTH to Calculate the End of the Month - Page 439
Syntax - Page 440
Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays - Page 441
Syntax - Page 442
Syntax - Page 443
Syntax - Page 444
Using International Versions of WORKDAY or NETWORKDAYS - Page 445
Examples of Text Functions - Page 446
Syntax - Page 447
Using LOWER, UPPER, or PROPER to Convert Text Case - Page 448
Using LOWER, UPPER, or PROPER to Convert Text Case - Page 449
Syntax - Page 450
Syntax - Page 451
Syntax - Page 452
Syntax: - Page 453
Using CLEAN to Remove Nonprintable Characters from Text - Page 454
Syntax: - Page 455
Using the CHAR or UNICHAR Function to Generate Any Character - Page 456
Syntax - Page 457
Using the CODE or UNICODE Function to Learn the Character Number for Any Character - Page 458
Syntax - Page 459
Syntax - Page 460
Using LEFT, MID, or RIGHT to Split Text - Page 461
Syntax - Page 462
Syntax - Page 463
Using LEN to Find the Number of Characters in a Text Cell - Page 464
Using SEARCH or FIND to Locate Characters in a Particular Cell - Page 465
Using SEARCH or FIND to Locate Characters in a Particular Cell - Page 466
Using SEARCH or FIND to Locate Characters in a Particular Cell - Page 467
Using SEARCH or FIND to Locate Characters in a Particular Cell - Page 468
Syntax - Page 469
Using SUBSTITUTE and REPLACE to Replace Characters - Page 470
Syntax - Page 471
Syntax - Page 472
Using EXACT to Test Case - Page 473
Using TEXT, DOLLAR, and FIXED to Format a Number as Text - Page 474
Syntax - Page 475
Syntax - Page 476
Syntax - Page 477
Using the T and VALUE Functions - Page 478
Using Functions for Non-English Character Sets - Page 479
Using Functions for Non-English Character Sets - Page 480
Using Functions for Non-English Character Sets - Page 481
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 482
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 483
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 484
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 485
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 486
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 487
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 488
12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions - Page 489
Examples of Logical Functions - Page 490
Using the AND Function to Check for Two or More Conditions - Page 491
Syntax: - Page 492
Syntax: - Page 493
Using OR or XOR to Check Whether One or More Conditions Are Met - Page 494
Syntax - Page 495
Syntax - Page 496
Nesting IF Functions - Page 497
Nesting IF Functions - Page 498
Using the TRUE and FALSE Functions - Page 499
Using the IFERROR or IFNA Function to Simplify Error Checking - Page 500
Using the IFERROR or IFNA Function to Simplify Error Checking - Page 501
Syntax - Page 502
Examples of Information Functions - Page 503
Examples of Information Functions - Page 504
Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells - Page 505
Using IS Functions to Test for Types of Values - Page 506
Using IS Functions to Test for Types of Values - Page 507
Using the SHEETS and SHEET Functions to Dynamically Build a 3-D Reference - Page 508
Syntax - Page 509
Syntax - Page 510
Using the ISREF Function to Check a Reference - Page 511
Using the N Function to Add a Comment to a Formula - Page 512
Using the NA Function to Force Charts to Not Plot Missing Data - Page 513
Using the NA Function to Force Charts to Not Plot Missing Data - Page 514
Using the INFO Function to Print Information About a Computer - Page 515
Using the INFO Function to Print Information About a Computer - Page 516
Using the CELL Function - Page 517
Using the CELL Function - Page 518
Using the CELL Function - Page 519
Using the CELL Function - Page 520
Using CELL to Track the Last Cell Changed - Page 521
Using TYPE to Determine the Type of Cell Value - Page 522
Examples of Lookup and Reference Functions - Page 523
Using the CHOOSE Function for Simple Lookups - Page 524
Using VLOOKUP with TRUE to Find a Value Based on a Range - Page 525
Using VLOOKUP with TRUE to Find a Value Based on a Range - Page 526
Using VLOOKUP with FALSE to Find an Exact Value - Page 527
Using VLOOKUP with FALSE to Find an Exact Value - Page 528
Syntax - Page 529
Using VLOOKUP to Match Two Lists - Page 530
Using VLOOKUP to Match Two Lists - Page 531
Using COLUMN to Assist with VLOOKUP When Filling a Wide Table - Page 532
Using COLUMN to Assist with VLOOKUP When Filling a Wide Table - Page 533
Using COLUMN to Assist with VLOOKUP When Filling a Wide Table - Page 534
Syntax - Page 535
Syntax - Page 536
Syntax - Page 537
Using the MATCH Function to Locate the Position of a Matching Value - Page 538
Syntax - Page 539
Using MATCH to Compare Two Lists - Page 540
Using INDEX and MATCH for a Left Lookup - Page 541
Using INDEX and MATCH for a Left Lookup - Page 542
Using INDEX and MATCH for a Left Lookup - Page 543
Syntax - Page 544
Using MATCH and INDEX to Fill a Wide Table - Page 545
Performing Many Lookups with LOOKUP - Page 546
Syntax - Page 547
Syntax - Page 548
Syntax - Page 549
Using FORMULATEXT to Document a Worksheet - Page 550
Using Functions to Describe the Shape of a Contiguous Reference - Page 551
Using AREAS and INDEX to Describe a Range with More Than One Area - Page 552
Syntax - Page 553
Syntax - Page 554
Using Numbers with OFFSET to Describe a Range - Page 555
Using Numbers with OFFSET to Describe a Range - Page 556
Using Numbers with OFFSET to Describe a Range - Page 557
Using Numbers with OFFSET to Describe a Range - Page 558
Using Numbers with OFFSET to Describe a Range - Page 559
Using Numbers with OFFSET to Describe a Range - Page 560
Using ADDRESS to Find the Address for Any Cell - Page 561
Using ADDRESS to Find the Address for Any Cell - Page 562
Using INDIRECT to Build and Evaluate Cell References on the Fly - Page 563
Using INDIRECT to Build and Evaluate Cell References on the Fly - Page 564
Using INDIRECT to Build and Evaluate Cell References on the Fly - Page 565
Using the HYPERLINK Function to Quickly Add Hyperlinks - Page 566
Using the TRANSPOSE Function to Formulaically Turn Data - Page 567
Using the TRANSPOSE Function to Formulaically Turn Data - Page 568
Syntax - Page 569
Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table - Page 570
Syntax - Page 571
Cube Functions Introduced in Excel 2007 - Page 572
Syntax: - Page 573
Examples of Web Functions - Page 574
Syntax - Page 575
Syntax - Page 576
Syntax - Page 577
Syntax - Page 578
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function - Page 579
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function - Page 580
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function - Page 581
Examples of Database Functions - Page 582
Examples of Database Functions - Page 583
Using DSUM to Conditionally Sum Records from a Database - Page 584
Creating a Simple Criteria Range for Database Functions - Page 585
Using AND to Join Criteria - Page 586
Using Dates or Numbers as Criteria - Page 587
Using the Miracle Version of a Criteria Range - Page 588
Using the DGET Function - Page 589
Using the DGET Function - Page 590
Using the DGET Function - Page 591
Using the DGET Function - Page 592
Using the DGET Function - Page 593
13. Using Financial Functions - Page 594
13. Using Financial Functions - Page 595
13. Using Financial Functions - Page 596
13. Using Financial Functions - Page 597
13. Using Financial Functions - Page 598
13. Using Financial Functions - Page 599
Examples of Common Household Loan and Investment Functions - Page 600
Using RRI to Calculate the Investment Return After Many Years - Page 601
Using PDURATION to Calculate How Long It Will Take Before You Are a Millionaire - Page 602
Using PMT to Calculate the Monthly Payment on an Automobile Loan - Page 603
Using RATE to Determine an Interest Rate - Page 604
Syntax: - Page 605
Using PV to Figure Out How Much House You Can Afford - Page 606
Using PV to Figure Out How Much House You Can Afford - Page 607
Using NPER to Estimate How Long a Nest Egg Will Last - Page 608
Using FV to Estimate the Future Value of a Regular Savings Plan - Page 609
Syntax - Page 610
Examples of Functions for Financial Professionals - Page 611
Examples of Functions for Financial Professionals - Page 612
Using IPMT to Calculate the Interest Portion of a Loan Payment for Any Month - Page 613
Syntax - Page 614
Using CUMIPMT to Calculate Total Interest Payments During a Time Frame - Page 615
Using CUMIPMT to Calculate Total Interest Payments During a Time Frame - Page 616
Using CUMPRINC to Calculate Total Principal Paid in Any Range of Periods - Page 617
Using EFFECT to Calculate the Effect of Compounding Period on Interest Rates - Page 618
Using NOMINAL to Convert the Effective Interest Rate to a Nominal Rate - Page 619
Examples of Depreciation Functions - Page 620
Using SLN to Calculate Straight-Line Depreciation - Page 621
Syntax - Page 622
Using DDB to Calculate Double-Declining-Balance Depreciation - Page 623
Syntax - Page 624
Syntax - Page 625
Using SYD to Calculate Sum-of-Years’-Digits Depreciation - Page 626
Using VDB to Calculate Depreciation for Any Period - Page 627
Syntax - Page 628
Functions for Investment Analysis - Page 629
Using the NPV Function to Determine Net Present Value - Page 630
Using the NPV Function to Determine Net Present Value - Page 631
Using IRR to Calculate the Return of a Series of Cash Flows - Page 632
Syntax - Page 633
Using MIRR to Calculate Internal Rate of Return, Including Interest Rates - Page 634
Using MIRR to Calculate Internal Rate of Return, Including Interest Rates - Page 635
Using XNPV to Calculate the Net Present Value When the Payments Are Not Periodic - Page 636
Using XNPV to Calculate the Net Present Value When the Payments Are Not Periodic - Page 637
Using XIRR to Calculate a Return Rate When Cash Flow Dates Are Not Periodic - Page 638
Examples of Functions for Bond Investors - Page 639
Examples of Functions for Bond Investors - Page 640
Using YIELD to Calculate a Bond’s Yield - Page 641
Using YIELD to Calculate a Bond’s Yield - Page 642
Using PRICE to Back into a Bond Price - Page 643
Using PRICE to Back into a Bond Price - Page 644
Using RECEIVED to Calculate Total Cash Generated from a Bond Investment - Page 645
Using RECEIVED to Calculate Total Cash Generated from a Bond Investment - Page 646
Using INTRATE to Back into the Coupon Interest Rate - Page 647
Using DISC to Back into the Discount Rate - Page 648
Using DISC to Back into the Discount Rate - Page 649
Handling Bonds with an Odd Number of Days in the First or Last Period - Page 650
Syntax: - Page 651
Syntax - Page 652
Using PRICEDISC and YIELDDISC to Calculate Discount Bonds - Page 653
Syntax - Page 654
Syntax: - Page 655
Syntax - Page 656
Syntax - Page 657
Using DURATION to Understand Price Volatility - Page 658
Syntax - Page 659
Examples of Miscellaneous Financial Functions - Page 660
Using FVSCHEDULE to Calculate the Future Value for a Variable Scheduled Interest Rate - Page 661
Using FVSCHEDULE to Calculate the Future Value for a Variable Scheduled Interest Rate - Page 662
14. Using Statistical Functions - Page 663
14. Using Statistical Functions - Page 664
14. Using Statistical Functions - Page 665
14. Using Statistical Functions - Page 666
14. Using Statistical Functions - Page 667
14. Using Statistical Functions - Page 668
14. Using Statistical Functions - Page 669
14. Using Statistical Functions - Page 670
14. Using Statistical Functions - Page 671
14. Using Statistical Functions - Page 672
14. Using Statistical Functions - Page 673
14. Using Statistical Functions - Page 674
14. Using Statistical Functions - Page 675
14. Using Statistical Functions - Page 676
Functions That Have Been Renamed - Page 677
Using Worksheets with Legacy Function Names - Page 678
Using Worksheets with Legacy Function Names - Page 679
Using Worksheets with Legacy Function Names - Page 680
Examples of Functions for Descriptive Statistics - Page 681
Syntax - Page 682
Syntax - Page 683
Syntax - Page 684
Syntax - Page 685
Syntax - Page 686
Using SMALL to Sequence a List in Date Sequence - Page 687
Using SMALL to Sequence a List in Date Sequence - Page 688
Using MEDIAN, MODE.SNGL, MODE.MULT, and AVERAGE to Find the Central Tendency of a Data Set - Page 689
Syntax - Page 690
Syntax - Page 691
Syntax - Page 692
Syntax - Page 693
Using TRIMMEAN to Exclude Outliers from the Mean - Page 694
Syntax - Page 695
Using AVERAGEIF or AVERAGEIFS - Page 696
Using RANK to Calculate the Position Within a List - Page 697
Using RANK to Calculate the Position Within a List - Page 698
Using RANK to Calculate the Position Within a List - Page 699
Using RANK to Calculate the Position Within a List - Page 700
Using RANK to Calculate the Position Within a List - Page 701
Using QUARTILE.INC to Break a Data Set into Quarters - Page 702
Using PERCENTILE.INC to Calculate Percentile - Page 703
Syntax - Page 704
Using PERCENTRANK.INC to Assign a Percentile to Every Record - Page 705
Using PERCENTRANK.INC to Assign a Percentile to Every Record - Page 706
Using AVEDEV, DEVSQ, VAR.S, and STDEV.S to Calculate Dispersion - Page 707
Theories About Standard Deviation - Page 708
Theories About Standard Deviation - Page 709
Syntax: - Page 710
Syntax: - Page 711
Considerations When Using Regression Analysis - Page 712
Considerations When Using Regression Analysis - Page 713
Considerations When Using Regression Analysis - Page 714
Regression Function Arguments - Page 715
Syntax: - Page 716
Using LINEST to Calculate Straight-Line Regression with Complete Statistics - Page 717
Syntax - Page 718
Syntax - Page 719
Syntax - Page 720
Syntax - Page 721
Syntax - Page 722
Syntax - Page 723
Troubleshooting LINEST - Page 724
Using FORECAST to Calculate Prediction for Any One Data Point - Page 725
Using FORECAST to Calculate Prediction for Any One Data Point - Page 726
Using TREND to Calculate Many Future Data Points at Once - Page 727
Syntax - Page 728
Syntax - Page 729
Syntax - Page 730
Syntax - Page 731
Using LOGEST to Perform Exponential Regression - Page 732
Performing an Exponential Regression - Page 733
Performing an Exponential Regression - Page 734
Using GROWTH to Predict Many Data Points from an Exponential Regression - Page 735
Using GROWTH to Predict Many Data Points from an Exponential Regression - Page 736
Exponential Regression Used to Predict Future Generations - Page 737
Syntax - Page 738
Syntax: - Page 739
Syntax: - Page 740
Using STEYX to Calculate Standard Regression Error - Page 741
Using COVARIANCE.P to Determine Whether Two Variables Vary Together - Page 742
Using COVARIANCE.P to Determine Whether Two Variables Vary Together - Page 743
Using CORREL to Calculate Positive or Negative Correlation - Page 744
Using FISHER to Perform Hypothesis Testing on Correlations - Page 745
Syntax: - Page 746
Syntax: - Page 747
Syntax - Page 748
Examples of Functions for Inferential Statistics - Page 749
Examples of Functions for Inferential Statistics - Page 750
Examples of Functions for Inferential Statistics - Page 751
Examples of Functions for Inferential Statistics - Page 752
Examples of Functions for Inferential Statistics - Page 753
Using BINOM.DIST to Determine Probability - Page 754
Using BINOM.DIST to Determine Probability - Page 755
Using BINOM.DIST to Determine Probability - Page 756
Using BINOM.DIST.RANGE to Calculate the Probability of N to N Binomial Events - Page 757
Using BINOM.INV to Cover Most of the Possible Binomial Events - Page 758
Using NEGBINOM.DIST to Calculate Probability - Page 759
Using POISSON.DIST to Predict a Number of Discrete Events Over Time - Page 760
Syntax - Page 761
Using FREQUENCY to Categorize Continuous Data - Page 762
Using FREQUENCY to Categorize Continuous Data - Page 763
Syntax - Page 764
Using NORM.DIST to Calculate the Probability in a Normal Distribution - Page 765
Syntax - Page 766
Using NORM.INV to Calculate the Value for a Certain Probability - Page 767
Functions for the Standard Normal Distribution - Page 768
Using NORM.S.DIST to Return Cumulative Probabilities - Page 769
Using NORM.S.DIST to Return Cumulative Probabilities - Page 770
Syntax - Page 771
Syntax - Page 772
Syntax: - Page 773
Using STANDARDIZE to Calculate the Distance from the Mean - Page 774
Using Student’s t-Distribution for Small Sample Sizes - Page 775
Syntax - Page 776
Syntax - Page 777
Syntax - Page 778
Using CHISQ.TEST to Perform Goodness-of-Fit Testing - Page 779
Syntax - Page 780
The Sum of Squares Functions - Page 781
The Sum of Squares Functions - Page 782
Syntax - Page 783
Syntax - Page 784
Testing Probability on Logarithmic Distributions - Page 785
Testing Probability on Logarithmic Distributions - Page 786
Syntax - Page 787
Using GAMMA.DIST and GAMMA.INV to Analyze Queuing Times - Page 788
Syntax - Page 789
Syntax - Page 790
Syntax - Page 791
Using F.TEST to Measure Differences in Variability - Page 792
Syntax - Page 793
Syntax - Page 794
Other Distributions: Exponential, Hypergeometric, and Weibull - Page 795
Syntax - Page 796
Using PROB to Calculate Probability for a Population That Fits No Distribution Curve - Page 797
Using Z.TEST, CONFIDENCE.NORM, and CONFIDENCE.T to Calculate Confidence Intervals - Page 798
Using Z.TEST, CONFIDENCE.NORM, and CONFIDENCE.T to Calculate Confidence Intervals - Page 799
Syntax: - Page 800
Using Z.TEST to Accept or Reject an Hypothesis - Page 801
Using PERMUT to Calculate the Number of Possible Arrangements - Page 802
Using PERMUTATIONA to Calculate the Number of Possible Arrangements When Repeats Are Allowed - Page 803
Installing the Analysis ToolPak in Excel 2013 - Page 804
Generating Random Numbers Based on Various Distributions - Page 805
Generating Random Numbers Based on Various Distributions - Page 806
Generating Random Numbers Based on Various Distributions - Page 807
Generating a Histogram - Page 808
Generating a Histogram - Page 809
Generating a Histogram - Page 810
Generating Descriptive Statistics of a Population - Page 811
Generating Descriptive Statistics of a Population - Page 812
Ranking Results - Page 813
Using Regression to Predict Future Results - Page 814
Using Regression to Predict Future Results - Page 815
Using Regression to Predict Future Results - Page 816
Using a Moving Average to Forecast Sales - Page 817
Using Exponential Smoothing to Forecast Sales - Page 818
Using Exponential Smoothing to Forecast Sales - Page 819
Using Exponential Smoothing to Forecast Sales - Page 820
Using Correlation or Covariance to Calculate the Relationship Between Many Variables - Page 821
Using Correlation or Covariance to Calculate the Relationship Between Many Variables - Page 822
Using Sampling to Create Random Samples - Page 823
Using Sampling to Create Random Samples - Page 824
Using Sampling to Create Random Samples - Page 825
Using ANOVA to Perform Analysis of Variance Testing - Page 826
Using ANOVA to Perform Analysis of Variance Testing - Page 827
Using ANOVA to Perform Analysis of Variance Testing - Page 828
Evaluating the Results - Page 829
Evaluating the Results - Page 830
Using the F-Test to Measure Variability Between Methods - Page 831
Using the F-Test to Measure Variability Between Methods - Page 832
Performing a z-Test to Determine Whether Two Samples Have Equal Means - Page 833
Performing a z-Test to Determine Whether Two Samples Have Equal Means - Page 834
Performing a z-Test to Determine Whether Two Samples Have Equal Means - Page 835
Performing Student’s t-Testing to Test Population Means - Page 836
Performing Student’s t-Testing to Test Population Means - Page 837
Using Functions Versus the Analysis ToolPak Tools - Page 838
Using Functions Versus the Analysis ToolPak Tools - Page 839
15. Using Trig, Matrix, and Engineering Functions - Page 840
15. Using Trig, Matrix, and Engineering Functions - Page 841
15. Using Trig, Matrix, and Engineering Functions - Page 842
15. Using Trig, Matrix, and Engineering Functions - Page 843
15. Using Trig, Matrix, and Engineering Functions - Page 844
15. Using Trig, Matrix, and Engineering Functions - Page 845
A Brief Review of Trigonometry Basics - Page 846
A Brief Review of Trigonometry Basics - Page 847
Syntax - Page 848
One Side + One Angle = Trigonometry - Page 849
One Side + One Angle = Trigonometry - Page 850
Using TAN to Find the Height of a Tall Building from the Ground - Page 851
Syntax: - Page 852
Using SIN to Find the Height of a Kite in a Tree - Page 853
Syntax - Page 854
Syntax - Page 855
Syntax - Page 856
Syntax - Page 857
Syntax - Page 858
Using the “Arc” Functions to Find the Measure of an Angle - Page 859
Using the “Arc” Functions to Find the Measure of an Angle - Page 860
Syntax: - Page 861
Using ATAN2 to Calculate Angles in a Circle - Page 862
Syntax - Page 863
Syntax - Page 864
Syntax - Page 865
Syntax - Page 866
Examples of Logarithm Functions - Page 867
Common Logarithms on a Base-10 Scale - Page 868
Syntax - Page 869
Using LOG to Calculate Logarithms for Any Base - Page 870
Using LN and EXP to Calculate Natural Logarithms - Page 871
Using LN and EXP to Calculate Natural Logarithms - Page 872
Syntax - Page 873
Syntax - Page 874
Syntax - Page 875
Working with Imaginary Numbers - Page 876
Using COMPLEX to Convert a and b into a Complex Number - Page 877
Syntax - Page 878
Using IMREAL and IMAGINARY to Break Apart Complex Numbers - Page 879
Syntax - Page 880
Syntax: - Page 881
Syntax: - Page 882
Syntax - Page 883
Syntax - Page 884
Syntax - Page 885
Syntax - Page 886
Using IMARGUMENT to Calculate the Angle to a Complex Number - Page 887
Calculating Powers, Logarithms, and Trigonometry Functions with Complex Numbers - Page 888
Calculating Powers, Logarithms, and Trigonometry Functions with Complex Numbers - Page 889
Solving Simultaneous Linear Equations with Matrix Functions - Page 890
Solving Simultaneous Linear Equations with Matrix Functions - Page 891
Solving Simultaneous Linear Equations with Matrix Functions - Page 892
Syntax - Page 893
Syntax - Page 894
Syntax: - Page 895
Syntax - Page 896
Using MDETERM to Determine Whether a Simultaneous Equation Has a Solution - Page 897
Syntax: - Page 898
Syntax: - Page 899
Using SQRTPI to Find the Square Root of a Number Multiplied by π - Page 900
Syntax - Page 901
Using SUMPRODUCT to Sum Based on Multiple Conditions - Page 902
Using SUMPRODUCT to Sum Based on Multiple Conditions - Page 903
Using SUMPRODUCT to Sum Based on Multiple Conditions - Page 904
Syntax - Page 905
Using CONVERT to Convert English to Metric - Page 906
Using CONVERT to Convert English to Metric - Page 907
Using CONVERT to Convert English to Metric - Page 908
Using CONVERT to Convert English to Metric - Page 909
Using CONVERT to Convert English to Metric - Page 910
Using CONVERT to Convert English to Metric - Page 911
Using CONVERT to Convert English to Metric - Page 912
Using CONVERT to Convert English to Metric - Page 913
Using CONVERT to Convert English to Metric - Page 914
Using CONVERT to Convert English to Metric - Page 915
Using CONVERT to Convert English to Metric - Page 916
Using CONVERT to Convert English to Metric - Page 917
Using CONVERT to Convert English to Metric - Page 918
Using CONVERT to Convert English to Metric - Page 919
Using CONVERT to Convert English to Metric - Page 920
Using CONVERT to Convert English to Metric - Page 921
Using CONVERT to Convert English to Metric - Page 922
Using CONVERT to Convert English to Metric - Page 923
Using CONVERT to Convert English to Metric - Page 924
Using CONVERT to Convert English to Metric - Page 925
Performing Bitwise Operations for Electrical Engineering - Page 926
Performing Bitwise Operations for Electrical Engineering - Page 927
Syntax - Page 928
Syntax - Page 929
Converting to Other Number Systems - Page 930
Converting to Other Number Systems - Page 931
Syntax - Page 932
Converting from Other Number Systems to Decimal - Page 933
Syntax - Page 934
Converting Using the Legacy Functions - Page 935
Syntax: - Page 936
Syntax - Page 937
Syntax - Page 938
Syntax - Page 939
Using DELTA or GESTEP to Filter a Set of Values - Page 940
Syntax - Page 941
Using ERF and ERFC to Calculate the Error Function and Its Complement - Page 942
Syntax: - Page 943
Syntax - Page 944
Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs) - Page 945
Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs) - Page 946
16. Connecting Worksheets, Workbooks, and External Data - Page 947
16. Connecting Worksheets, Workbooks, and External Data - Page 948
16. Connecting Worksheets, Workbooks, and External Data - Page 949
16. Connecting Worksheets, Workbooks, and External Data - Page 950
Creating Links Using Paste Options Menu - Page 951
Creating Links Using Paste Options Menu - Page 952
Creating Links Using the Right-Drag Menu - Page 953
Creating Links Using the Right-Drag Menu - Page 954
Building a Link by Using the Mouse - Page 955
Links to External Workbooks Default to Absolute References - Page 956
Building a Formula by Typing - Page 957
Creating Links to Unsaved Workbooks - Page 958
Opening Workbooks with Links to Closed Workbooks - Page 959
Preventing the Update Links Dialog from Appearing - Page 960
Connecting to Data on a Web Page - Page 961
Connecting to Data on a Web Page - Page 962
Connecting to Data on a Web Page - Page 963
Connecting to Data on a Web Page - Page 964
Connecting to Data on a Web Page - Page 965
Connecting to Data on a Web Page - Page 966
Connecting to Data on a Web Page - Page 967
Managing Properties for Web Queries - Page 968
Managing Properties for Web Queries - Page 969
Managing Properties for Web Queries - Page 970
Managing Properties for Web Queries - Page 971
Managing Properties for Web Queries - Page 972
Managing Properties for Web Queries - Page 973
Managing Properties for Web Queries - Page 974
Setting Up a Connection to an Access Database - Page 975
Setting Up a Connection to an Access Database - Page 976
Setting Up a Connection to an Access Database - Page 977
Setting Up SQL Server, XML, OLE DB, and ODBC Connections - Page 978
Connecting Using Microsoft Query - Page 979
Connecting Using Microsoft Query - Page 980
Connecting Using Microsoft Query - Page 981
Connecting Using Microsoft Query - Page 982
Managing Connections - Page 983
Managing Connections - Page 984
17. Using Super Formulas in Excel - Page 985
17. Using Super Formulas in Excel - Page 986
17. Using Super Formulas in Excel - Page 987
Referring to the Previous Worksheet - Page 988
Referring to the Previous Worksheet - Page 989
Referring to the Previous Worksheet - Page 990
Combining Multiple Formulas into One Formula - Page 991
Combining Multiple Formulas into One Formula - Page 992
Combining Multiple Formulas into One Formula - Page 993
Combining Multiple Formulas into One Formula - Page 994
Calculating a Cell Reference in the Formula by Using the INDIRECT Function - Page 995
Calculating a Cell Reference in the Formula by Using the INDIRECT Function - Page 996
Calculating a Cell Reference in the Formula by Using the INDIRECT Function - Page 997
Calculating a Cell Reference in the Formula by Using the INDIRECT Function - Page 998
Using OFFSET to Refer to a Range That Dynamically Resizes - Page 999
Using OFFSET to Refer to a Range That Dynamically Resizes - Page 1000
Assigning a Formula to a Name - Page 1001
Assigning a Formula to a Name - Page 1002
Assigning a Formula to a Name - Page 1003
Turning a Range of Formulas on Its Side - Page 1004
Turning a Range of Formulas on Its Side - Page 1005
Turning a Range of Formulas on Its Side - Page 1006
Turning a Range of Formulas on Its Side - Page 1007
Replacing Multiple Formulas with One Array Formula - Page 1008
Replacing Multiple Formulas with One Array Formula - Page 1009
Replacing Multiple Formulas with One Array Formula - Page 1010
Setting Up an Array Formula - Page 1011
Understanding an Array Formula - Page 1012
Understanding an Array Formula - Page 1013
Coercing a Range of Dates Using an Array Formula - Page 1014
Coercing a Range of Dates Using an Array Formula - Page 1015
18. Using Names in Excel - Page 1016
18. Using Names in Excel - Page 1017
Naming a Cell by Using the Name Dialog - Page 1018
Naming a Cell by Using the Name Dialog - Page 1019
Using the Name Box for Quick Navigation - Page 1020
Avoiding Problems by Using Worksheet-Level Scope - Page 1021
Defining a Worksheet-Level Name - Page 1022
Referring to Worksheet-Level Names - Page 1023
Using Named Ranges to Simplify Formulas - Page 1024
Using Named Ranges to Simplify Formulas - Page 1025
Using Named Ranges to Simplify Formulas - Page 1026
Retroactively Applying Names to Formulas - Page 1027
Retroactively Applying Names to Formulas - Page 1028
Using Names to Refer to Ranges - Page 1029
Dealing with Invalid Legacy Naming - Page 1030
Dealing with Invalid Legacy Naming - Page 1031
Adding Many Names at Once from Existing Labels and Headings - Page 1032
Adding Many Names at Once from Existing Labels and Headings - Page 1033
Adding Many Names at Once from Existing Labels and Headings - Page 1034
Using Intersection to Do a Two-Way Lookup - Page 1035
Using Implicit Intersection - Page 1036
Managing Names - Page 1037
Managing Names - Page 1038
Managing Names - Page 1039
Filtering the Name Manager Dialog - Page 1040
Using a Name to Simplify an Absolute Reference - Page 1041
Using a Name to Simplify an Absolute Reference - Page 1042
Using a Name to Simplify an Absolute Reference - Page 1043
Assigning a Formula to a Name - Page 1044
Using Dynamic Named Formulas - Page 1045
Using Dynamic Named Formulas - Page 1046
Using Dynamic Named Formulas - Page 1047
Using a Named Formula to Point to the Cell Above - Page 1048
Using a Named Formula to Point to the Cell Above - Page 1049
Using a Named Formula to Point to the Cell Above - Page 1050
Using a Named Formula to Point to the Cell Above - Page 1051
19. Fabulous Table Intelligence - Page 1052
Defining a Table - Page 1053
Keeping Headers in View - Page 1054
Freezing Worksheet Panes - Page 1055
Freezing Worksheet Panes - Page 1056
Clearing Freeze Panes - Page 1057
Clearing Freeze Panes - Page 1058
Adding a Total Row to a Table - Page 1059
Adding a Total Row to a Table - Page 1060
Toggling Totals - Page 1061
Manually Resizing a Table - Page 1062
Adding New Columns to a Table - Page 1063
Adding New Columns to a Table - Page 1064
Stopping the Automatic Copying of Formulas - Page 1065
Selecting Only the Data in the Column - Page 1066
Selecting by Right-Clicking - Page 1067
Selecting by Using Shortcuts - Page 1068
Selecting by Using the Arrow Mouse Pointers - Page 1069
Selecting by Using the Arrow Mouse Pointers - Page 1070
Using Table Data for Charts to Ensure Stickiness - Page 1071
Using Table Data for Charts to Ensure Stickiness - Page 1072
Replacing Named Ranges with Table References - Page 1073
Referencing an Entire Table from Outside the Table - Page 1074
Referencing an Entire Table from Outside the Table - Page 1075
Referencing Table Columns from Outside a Table - Page 1076
Referencing Table Columns from Outside a Table - Page 1077
Using Structured References to Refer to Tables in Formulas - Page 1078
Creating Banded Rows and Columns with Table Styles - Page 1079
Customizing a Table Style: Creating Double-Height Banded Rows - Page 1080
Customizing a Table Style: Creating Double-Height Banded Rows - Page 1081
Customizing a Table Style: Creating Double-Height Banded Rows - Page 1082
Creating Banded Rows Outside a Table - Page 1083
Creating Banded Rows Outside a Table - Page 1084
Creating Banded Rows Outside a Table - Page 1085
III: Business Intelligence - Page 1086
20. Sorting Data - Page 1087
20. Sorting Data - Page 1088
Using Specialized Sorting - Page 1089
Using Specialized Sorting - Page 1090
Factoring Case into a Sort - Page 1091
Reordering Columns with a Left-to-Right Sort - Page 1092
Sorting into a Unique Sequence by Using Custom Lists - Page 1093
Sorting into a Unique Sequence by Using Custom Lists - Page 1094
One-Click Sorting - Page 1095
One-Click Sorting - Page 1096
Sorting by Several Columns Using One-Click Sorting - Page 1097
Sorting by Several Columns Using One-Click Sorting - Page 1098
Sorting by Several Columns Using One-Click Sorting - Page 1099
Sorting by Several Columns Using One-Click Sorting - Page 1100
Sorting by Several Columns Using One-Click Sorting - Page 1101
21. Removing Duplicates and Filtering - Page 1102
Using a Filter - Page 1103
Selecting One or Multiple Items from the Filter Drop-Down - Page 1104
Selecting One or Multiple Items from the Filter Drop-Down - Page 1105
Identifying Which Columns Have Filters Applied - Page 1106
Combining Filters - Page 1107
Resizing the Filter Drop-Down - Page 1108
Resizing the Filter Drop-Down - Page 1109
Filtering by Selection—Easy Way - Page 1110
Filtering by Selection—Easy Way - Page 1111
Filtering by Selection—Easy Way - Page 1112
Filtering by Color or Icon - Page 1113
Handling Date Filters - Page 1114
Handling Date Filters - Page 1115
Using Special Filters for Dates, Text, and Numbers - Page 1116
Using Special Filters for Dates, Text, and Numbers - Page 1117
Using Special Filters for Dates, Text, and Numbers - Page 1118
Sorting Filtered Results - Page 1119
Totaling Filtered Results - Page 1120
Formatting and Copying Filtered Results - Page 1121
Using the Advanced Filter Command - Page 1122
Using the Advanced Filter Command - Page 1123
Using the Advanced Filter Command - Page 1124
Using the Advanced Filter Command - Page 1125
Using Remove Duplicates to Find Unique Values - Page 1126
Using Remove Duplicates to Find Unique Values - Page 1127
Removing Duplicates Based on Several Columns - Page 1128
Handling Duplicates Other Ways - Page 1129
Handling Duplicates Other Ways - Page 1130
22. Using Automatic Subtotals - Page 1131
22. Using Automatic Subtotals - Page 1132
22. Using Automatic Subtotals - Page 1133
Working with the Subtotals - Page 1134
Sorting the Collapsed Subtotal View So the Largest Customers Are on Top - Page 1135
Sorting the Collapsed Subtotal View So the Largest Customers Are on Top - Page 1136
Copying Only the Subtotal Rows - Page 1137
Copying Only the Subtotal Rows - Page 1138
Formatting the Subtotal Rows - Page 1139
Formatting the Subtotal Rows - Page 1140
Removing Subtotals - Page 1141
Removing Subtotals - Page 1142
Add Subtotals by Two Fields - Page 1143
Add Subtotals by Two Fields - Page 1144
23. Using Pivot Tables to Analyze Data - Page 1145
Creating Your First Pivot Table - Page 1146
Browsing Four “Recommended” Pivot Tables Using the Quick Analysis Icon - Page 1147
Browsing Ten “Recommended” Pivot Tables - Page 1148
Browsing Ten “Recommended” Pivot Tables - Page 1149
Starting with a Blank Pivot Table - Page 1150
Adding Fields to Your Pivot Table Using the Field List - Page 1151
Adding Fields to Your Pivot Table Using the Field List - Page 1152
Adding Fields to Your Pivot Table Using the Field List - Page 1153
Adding Fields to Your Pivot Table Using the Field List - Page 1154
Changing the Pivot Table Report by Using the Field List - Page 1155
Changing the Pivot Table Report by Using the Field List - Page 1156
Changing the Pivot Table Report by Using the Field List - Page 1157
Dealing with the Compact Layout - Page 1158
Dealing with the Compact Layout - Page 1159
Rearranging a Pivot Table - Page 1160
Finishing Touches: Numeric Formatting and Removing Blanks - Page 1161
Finishing Touches: Numeric Formatting and Removing Blanks - Page 1162
Finishing Touches: Numeric Formatting and Removing Blanks - Page 1163
Four Things You Have to Know When Using Pivot Tables - Page 1164
One Blank Cell in a Value Column Causes Excel to Count Instead of Sum - Page 1165
Calculating and Roll-ups with Pivot Tables - Page 1166
Calculating and Roll-ups with Pivot Tables - Page 1167
Calculating and Roll-ups with Pivot Tables - Page 1168
Adding Calculations Outside the Pivot Table - Page 1169
Adding Calculations Outside the Pivot Table - Page 1170
Changing the Calculation of a Field - Page 1171
Changing the Calculation of a Field - Page 1172
Changing the Calculation of a Field - Page 1173
Showing Percentage of Total Using Show Value As Settings - Page 1174
Showing Running Totals and Rank - Page 1175
Showing Running Totals and Rank - Page 1176
Using a Formula to Add a Field to a Pivot Table - Page 1177
Using a Formula to Add a Field to a Pivot Table - Page 1178
Formatting a Pivot Table - Page 1179
Finding More Information on Pivot Tables - Page 1180
Finding More Information on Pivot Tables - Page 1181
24. Using Slicers and Filtering a Pivot Table - Page 1182
24. Using Slicers and Filtering a Pivot Table - Page 1183
24. Using Slicers and Filtering a Pivot Table - Page 1184
Filtering Using the Search Box - Page 1185
Filtering Using the Search Box - Page 1186
Filtering Using the Search Box - Page 1187
Clearing a Filter - Page 1188
Filtering Using the Label Filter Fly-Out - Page 1189
Filtering Using the Date Filters - Page 1190
Filtering Using Value Filters - Page 1191
Filtering Using Value Filters - Page 1192
Filtering Using Value Filters - Page 1193
Filtering to the Top 10 - Page 1194
Filtering to the Top 10 - Page 1195
Filtering to the Top 10 - Page 1196
Filtering Using Filter Fields - Page 1197
Filtering Using Filter Fields - Page 1198
Arranging the Filters - Page 1199
Arranging the Filters - Page 1200
Arranging the Filters - Page 1201
Filtering Using Slicers - Page 1202
Adding Slicers - Page 1203
Arranging the Slicers - Page 1204
Arranging the Slicers - Page 1205
Formatting the Slicers - Page 1206
Using the Slicers - Page 1207
Filtering Using Timelines - Page 1208
Filtering Using Timelines - Page 1209
Filtering Using Timelines - Page 1210
Adding a Timeline - Page 1211
Adding a Timeline - Page 1212
Choosing Between Timelines or Grouped Slicers - Page 1213
Filtering Oddities - Page 1214
AutoFiltering a Pivot Table - Page 1215
Applying Row Label Filters to Fields Not in the Pivot Table Report - Page 1216
Applying Row Label Filters to Fields Not in the Pivot Table Report - Page 1217
Replicating a Pivot Table for Every Customer - Page 1218
Sorting a Pivot Table - Page 1219
Sorting a Pivot Table - Page 1220
Sorting a Pivot Table - Page 1221
Sorting a Pivot Table - Page 1222
25. Mashing Up Data with PowerPivot - Page 1223
Preparing Data for Use in the Data Model - Page 1224
Preparing Data for Use in the Data Model - Page 1225
Preparing Data for Use in the Data Model - Page 1226
Adding the First Table to the Data Model - Page 1227
Adding the First Table to the Data Model - Page 1228
Adding the First Table to the Data Model - Page 1229
Adding the Second Table and Defining a Relationship - Page 1230
Adding the Second Table and Defining a Relationship - Page 1231
Adding the Second Table and Defining a Relationship - Page 1232
Adding the Second Table and Defining a Relationship - Page 1233
Adding the Second Table and Defining a Relationship - Page 1234
Understanding the Limitations of the Data Model - Page 1235
Understanding the Limitations of the Data Model - Page 1236
Benefits of Moving to PowerPivot - Page 1237
Enabling PowerPivot - Page 1238
Case Study: Building a PowerPivot Report - Page 1239
Import a Text File - Page 1240
Import a Text File - Page 1241
Import a Text File - Page 1242
Add Excel Data by Copying and Pasting - Page 1243
Add Excel Data by Linking - Page 1244
Add Calculated Columns Using DAX - Page 1245
Build a Pivot Table - Page 1246
Build a Pivot Table - Page 1247
Build a Pivot Table - Page 1248
Some Things Are Different - Page 1249
DAX Calculations for Calculated Columns - Page 1250
Using DAX to Create a Calculated Field in the Pivot Table - Page 1251
Using DAX to Create a Calculated Field in the Pivot Table - Page 1252
Count Distinct Using DAX - Page 1253
Count Distinct Using DAX - Page 1254
Defining KPIs with PowerPivot - Page 1255
Defining KPIs with PowerPivot - Page 1256
Defining KPIs with PowerPivot - Page 1257
Using QuickExplore - Page 1258
Combination Layouts - Page 1259
Combination Layouts - Page 1260
Combination Layouts - Page 1261
Refreshing the Pivot Table Refreshes PowerPivot - Page 1262
26. Creating Interactive Dashboards with Power View or GeoFlow - Page 1263
26. Creating Interactive Dashboards with Power View or GeoFlow - Page 1264
Creating a Power View Worksheet - Page 1265
Creating a Power View Worksheet - Page 1266
Every New Dashboard Element Starts as a Table - Page 1267
Every New Dashboard Element Starts as a Table - Page 1268
Convert the Table to a Chart - Page 1269
Convert the Table to a Chart - Page 1270
Convert the Table to a Chart - Page 1271
Creating a New Element by Dragging - Page 1272
Adding a Real Slicer - Page 1273
The Filter Pane Can Be Confusing - Page 1274
Use Tile Boxes to Filter One or a Group of Charts - Page 1275
Use Tile Boxes to Filter One or a Group of Charts - Page 1276
Replicating Charts Using Multiples - Page 1277
Showing Data on a Map - Page 1278
Showing Data on a Map - Page 1279
Using Table or Card View with Images - Page 1280
Using Table or Card View with Images - Page 1281
Animating a Scatter Chart Over Time - Page 1282
Animating a Scatter Chart Over Time - Page 1283
Animating a Scatter Chart Over Time - Page 1284
Using Drill-Down - Page 1285
Using Drill-Down - Page 1286
Using Drill-Down - Page 1287
Using Drill-Down - Page 1288
Using Drill-Down - Page 1289
27. Using What-If, Scenario Manager, Goal Seek, and Solver - Page 1290
27. Using What-If, Scenario Manager, Goal Seek, and Solver - Page 1291
Creating a Two-Variable What-If Table - Page 1292
Creating a Two-Variable What-If Table - Page 1293
Creating a Two-Variable What-If Table - Page 1294
Creating a Two-Variable What-If Table - Page 1295
Using Scenario Manager - Page 1296
Using Scenario Manager - Page 1297
Using Scenario Manager - Page 1298
Using Scenario Manager - Page 1299
Using Scenario Manager - Page 1300
Creating a Scenario Summary Report - Page 1301
Creating a Scenario Summary Report - Page 1302
Creating a Scenario Summary Report - Page 1303
Creating a Scenario Summary Report - Page 1304
Adding Multiple Scenarios - Page 1305
Adding Multiple Scenarios - Page 1306
Adding Multiple Scenarios - Page 1307
Using Goal Seek - Page 1308
Using Goal Seek - Page 1309
Using Goal Seek - Page 1310
Using Goal Seek - Page 1311
Using Goal Seek - Page 1312
Using Goal Seek - Page 1313
Using Goal Seek - Page 1314
Using Goal Seek - Page 1315
Using Goal Seek - Page 1316
Using Solver - Page 1317
Installing Solver - Page 1318
Installing Solver - Page 1319
Installing Solver - Page 1320
Installing Solver - Page 1321
Installing Solver - Page 1322
Installing Solver - Page 1323
Installing Solver - Page 1324
Installing Solver - Page 1325
28. Automating Repetitive Functions Using VBA Macros - Page 1326
Enabling VBA Security - Page 1327
Recording a Macro - Page 1328
Case Study: Macro for Formatting for a Mail Merge - Page 1329
Case Study: Macro for Formatting for a Mail Merge - Page 1330
Case Study: Macro for Formatting for a Mail Merge - Page 1331
Case Study: Macro for Formatting for a Mail Merge - Page 1332
How Not to Record a Macro: The Default State of the Macro Recorder - Page 1333
How Not to Record a Macro: The Default State of the Macro Recorder - Page 1334
Relative References in Macro Recording - Page 1335
Starting the Macro Recorder - Page 1336
Starting the Macro Recorder - Page 1337
Starting the Macro Recorder - Page 1338
Running a Macro - Page 1339
Running a Macro - Page 1340
Everyday-Use Macro Example: Formatting an Invoice Register - Page 1341
Using the Ctrl+Down Arrow Key to Handle a Variable Number of Rows - Page 1342
Making Sure You Find the Last Record - Page 1343
Making Sure You Find the Last Record - Page 1344
Editing a Macro - Page 1345
Editing a Macro - Page 1346
Understanding VBA Code—An Analogy - Page 1347
Comparing Collections to Plural Nouns - Page 1348
Comparing Collections to Plural Nouns - Page 1349
Comparing Collections to Plural Nouns - Page 1350
Comparing Collections to Plural Nouns - Page 1351
Comparing Collections to Plural Nouns - Page 1352
Comparing Adjectives to Properties - Page 1353
Using the Analogy While Examining Recorded Code - Page 1354
Using the Analogy While Examining Recorded Code - Page 1355
Using R1C1-Style Formulas - Page 1356
Using R1C1-Style Formulas - Page 1357
Using R1C1-Style Formulas - Page 1358
Using R1C1-Style Formulas - Page 1359
Using R1C1-Style Formulas - Page 1360
Fixing Calculation Errors in Macros - Page 1361
Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName - Page 1362
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges - Page 1363
Finding the Last Row with Data - Page 1364
Looping Through All Rows - Page 1365
Referring to Ranges - Page 1366
Combining a Loop with FinalRow - Page 1367
Using the Select Case Construct - Page 1368
Putting Together the From-Scratch Example: Testing Each Record in a Loop - Page 1369
Putting Together the From-Scratch Example: Testing Each Record in a Loop - Page 1370
Putting Together the From-Scratch Example: Testing Each Record in a Loop - Page 1371
A Special Case: Deleting Some Records - Page 1372
A Special Case: Deleting Some Records - Page 1373
Combination Macro Example: Creating a Report for Each Customer - Page 1374
Combination Macro Example: Creating a Report for Each Customer - Page 1375
Combination Macro Example: Creating a Report for Each Customer - Page 1376
Using the Advanced Filter for Unique Records - Page 1377
Using the Advanced Filter for Unique Records - Page 1378
Using the Advanced Filter for Unique Records - Page 1379
Using AutoFilter - Page 1380
Selecting Visible Cells Only - Page 1381
Selecting Visible Cells Only - Page 1382
Combination Macro Example: Putting It All Together - Page 1383
Combination Macro Example: Putting It All Together - Page 1384
29. More Tips and Tricks for Excel 2013 - Page 1385
29. More Tips and Tricks for Excel 2013 - Page 1386
Watching the Results of a Distant Cell - Page 1387
Opening the Same Files Every Day - Page 1388
Calculating a Formula in Slow Motion - Page 1389
Calculating a Formula in Slow Motion - Page 1390
Inserting a Symbol in a Cell - Page 1391
Edit an Equation - Page 1392
Edit an Equation - Page 1393
Protecting a Worksheet - Page 1394
Protecting a Worksheet - Page 1395
Protecting a Worksheet - Page 1396
Sharing a Workbook - Page 1397
Translating Text - Page 1398
Auditing Worksheets Using Inquire - Page 1399
Auditing Worksheets Using Inquire - Page 1400
Highlighting Potential Problems Using Interactive Diagnostics - Page 1401
Building Relationship Diagrams - Page 1402
Comparing Two Versions of a Workbook - Page 1403
Comparing Two Versions of a Workbook - Page 1404
Comparing Two Versions of a Workbook - Page 1405
IV: Visual Presentation - Page 1406
30. Formatting Worksheets - Page 1407
30. Formatting Worksheets - Page 1408
Using Traditional Formatting - Page 1409
Using Traditional Formatting - Page 1410
Changing Numeric Formats by Using the Home Tab - Page 1411
Changing Numeric Formats by Using the Home Tab - Page 1412
Changing Numeric Formats by Using the Home Tab - Page 1413
Changing Numeric Formats by Using the Home Tab - Page 1414
Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog - Page 1415
Displaying Currency - Page 1416
Displaying Fractions - Page 1417
Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers - Page 1418
Changing Numeric Formats Using Custom Formats - Page 1419
Using the Four Zones of a Custom Number Format - Page 1420
Controlling Text and Spacing in a Custom Number Format - Page 1421
Controlling Decimal Places in a Custom Number Format - Page 1422
Using Dates and Times in a Custom Number Format - Page 1423
Using Dates and Times in a Custom Number Format - Page 1424
Displaying Scientific Notation in Custom Number Formats - Page 1425
Changing Font Size - Page 1426
Changing Font Typeface - Page 1427
Applying Bold, Italic, and Underline - Page 1428
Applying Bold, Italic, and Underline - Page 1429
Using Borders - Page 1430
Using Borders - Page 1431
Using Borders - Page 1432
Drawing a Border Around a Range - Page 1433
Coloring Cells - Page 1434
Adjusting Column Widths and Row Heights - Page 1435
Adjusting Column Widths and Row Heights - Page 1436
Using Merge and Center - Page 1437
Using Merge and Center - Page 1438
Rotating Text - Page 1439
Rotating Text - Page 1440
Rotating Text - Page 1441
Formatting with Styles - Page 1442
Formatting with Styles - Page 1443
Formatting with Styles - Page 1444
Formatting with Styles - Page 1445
Understanding Themes - Page 1446
Choosing a New Theme - Page 1447
Choosing a New Theme - Page 1448
Choosing a New Theme - Page 1449
Creating a New Theme - Page 1450
Converting from Hex to Decimal - Page 1451
Finding New Colors - Page 1452
Finding New Colors - Page 1453
Specifying a Theme’s Colors - Page 1454
Specifying a Theme’s Colors - Page 1455
Specifying a Theme’s Fonts - Page 1456
Reusing Another Theme’s Effects - Page 1457
Reusing Another Theme’s Effects - Page 1458
Reusing Another Theme’s Effects - Page 1459
Saving a Custom Theme - Page 1460
Using a Theme on a New Document - Page 1461
Changing the Default Font - Page 1462
Wrapping Text in a Cell - Page 1463
Wrapping Text in a Cell - Page 1464
Justifying Text in a Range - Page 1465
Justifying Text in a Range - Page 1466
Adding Cell Comments - Page 1467
Adding Cell Comments - Page 1468
Copying Formats - Page 1469
Copying Formats - Page 1470
Copying Formats - Page 1471
Pasting Conditional Formats - Page 1472
Using the Format Painter - Page 1473
Using the Format Painter - Page 1474
Using the Format Painter - Page 1475
Using the Format Painter - Page 1476
Using the Format Painter - Page 1477
31. Using Data Visualizations and Conditional Formatting - Page 1478
Using Data Bars to Create In-Cell Bar Charts - Page 1479
Using Data Bars to Create In-Cell Bar Charts - Page 1480
Using Data Bars to Create In-Cell Bar Charts - Page 1481
Creating Data Bars - Page 1482
Customizing Data Bars - Page 1483
Customizing Data Bars - Page 1484
Showing Data Bars for a Subset of Cells - Page 1485
Using Color Scales to Highlight Extremes - Page 1486
Customizing Color Scales - Page 1487
Using Icon Sets to Segregate Data - Page 1488
Setting Up an Icon Set - Page 1489
Moving Numbers Closer to Icons - Page 1490
Moving Numbers Closer to Icons - Page 1491
Mixing Icons or Hiding Icons - Page 1492
Using the Top/Bottom Rules - Page 1493
Setting Up Conditional Formatting Rules - Page 1494
Setting Up Conditional Formatting Rules - Page 1495
Using the Highlight Cells Rules - Page 1496
Highlighting Cells by Using Greater Than and Similar Rules - Page 1497
Highlighting Cells by Using Greater Than and Similar Rules - Page 1498
Highlighting Cells by Using Greater Than and Similar Rules - Page 1499
Comparing Dates by Using Conditional Formatting - Page 1500
Comparing Dates by Using Conditional Formatting - Page 1501
Comparing Dates by Using Conditional Formatting - Page 1502
Identifying Duplicate or Unique Values by Using Conditional Formatting - Page 1503
Using Conditional Formatting for Text Containing a Value - Page 1504
Tweaking Rules with Advanced Formatting - Page 1505
Tweaking Rules with Advanced Formatting - Page 1506
Using a Formula for Rules - Page 1507
Working with the Formula Box - Page 1508
Finding Cells Within Three Days of Today - Page 1509
Highlighting Data from Specific Days of the Week - Page 1510
Highlighting Every Other Row Without Using a Table - Page 1511
Highlighting Every Other Row Without Using a Table - Page 1512
Combining Rules - Page 1513
Clearing Conditional Formats - Page 1514
Extending the Reach of Conditional Formats - Page 1515
Special Considerations for Pivot Tables - Page 1516
Special Considerations for Pivot Tables - Page 1517
Special Considerations for Pivot Tables - Page 1518
32. Graphing Data Using Excel Charts - Page 1519
Choosing from Recommended Charts - Page 1520
Choosing from Recommended Charts - Page 1521
Using Paintbrush Icon for Styles - Page 1522
Using Paintbrush Icon for Styles - Page 1523
Using Paintbrush Icon for Styles - Page 1524
Deleting Extraneous Data Using the Funnel - Page 1525
Deleting Extraneous Data Using the Funnel - Page 1526
Changing Chart Options Using the Plus Icon - Page 1527
Changing Chart Options Using the Plus Icon - Page 1528
Showing Two Orders of Magnitude Using a Combo Chart - Page 1529
Showing Two Orders of Magnitude Using a Combo Chart - Page 1530
Showing Two Orders of Magnitude Using a Combo Chart - Page 1531
Showing Two Orders of Magnitude Using a Combo Chart - Page 1532
Showing Two Orders of Magnitude Using a Combo Chart - Page 1533
Showing Two Orders of Magnitude Using a Combo Chart - Page 1534
Using Ctrl+1 to Format Any Chart Element - Page 1535
Using Ctrl+1 to Format Any Chart Element - Page 1536
Labeling Charts - Page 1537
Labeling Charts - Page 1538
Labeling Charts - Page 1539
Controlling the Axis Range - Page 1540
Controlling the Axis Range - Page 1541
Controlling the Axis Range - Page 1542
Seeing the Relationship Between Two Variables in a Scatter Chart - Page 1543
Seeing the Relationship Between Two Variables in a Scatter Chart - Page 1544
Seeing the Relationship Between Two Variables in a Scatter Chart - Page 1545
Seeing the Relationship Between Two Variables in a Scatter Chart - Page 1546
Adding a Third Variable with a Bubble Chart - Page 1547
Adding a Third Variable with a Bubble Chart - Page 1548
Plotting Two Populations on One Scatter Chart - Page 1549
Plotting Two Populations on One Scatter Chart - Page 1550
Plotting Two Populations on One Scatter Chart - Page 1551
Summarizing Data Using the Quick Analysis Icon - Page 1552
Summarizing Data Using the Quick Analysis Icon - Page 1553
Summarizing Data Using the Quick Analysis Icon - Page 1554
Summarizing Data Using the Quick Analysis Icon - Page 1555
Saving Time with Charting Tricks - Page 1556
Saving Time with Charting Tricks - Page 1557
Adding Drop Lines to a Surface Chart - Page 1558
Predicting the Future by Using a Trendline - Page 1559
Predicting the Future by Using a Trendline - Page 1560
Creating Stock Charts - Page 1561
Creating Stock Charts - Page 1562
Dealing with Small Pie Slices - Page 1563
Dealing with Small Pie Slices - Page 1564
Dealing with Small Pie Slices - Page 1565
Saving a Favorite Chart Style As a Template - Page 1566
Saving a Favorite Chart Style As a Template - Page 1567
33. Using Sparklines - Page 1568
33. Using Sparklines - Page 1569
Understanding How Excel Maps Data to Sparklines - Page 1570
Understanding How Excel Maps Data to Sparklines - Page 1571
Creating a Group of Sparklines - Page 1572
Creating a Group of Sparklines - Page 1573
Creating a Group of Sparklines - Page 1574
Built-in Choices for Customizing Sparklines - Page 1575
Built-in Choices for Customizing Sparklines - Page 1576
Built-in Choices for Customizing Sparklines - Page 1577
Built-in Choices for Customizing Sparklines - Page 1578
Controlling Axis Values for Sparklines - Page 1579
Controlling Axis Values for Sparklines - Page 1580
Setting Up Win/Loss Sparklines - Page 1581
Setting Up Win/Loss Sparklines - Page 1582
Showing Detail by Enlarging the Sparkline and Adding Labels - Page 1583
Showing Detail by Enlarging the Sparkline and Adding Labels - Page 1584
Showing Detail by Enlarging the Sparkline and Adding Labels - Page 1585
Showing Detail by Enlarging the Sparkline and Adding Labels - Page 1586
Other Sparkline Options - Page 1587
Other Sparkline Options - Page 1588
34. Using SmartArt, Shapes, WordArt, and Text Boxes - Page 1589
34. Using SmartArt, Shapes, WordArt, and Text Boxes - Page 1590
Elements Common in Most SmartArt - Page 1591
Elements Common in Most SmartArt - Page 1592
Inserting SmartArt - Page 1593
Inserting SmartArt - Page 1594
Inserting SmartArt - Page 1595
Changing Existing SmartArt to a New Style - Page 1596
Micromanaging SmartArt Elements - Page 1597
Changing Text Formatting in One Element - Page 1598
Changing One Shape - Page 1599
Changing One Shape - Page 1600
Controlling SmartArt Shapes from the Text Pane - Page 1601
Controlling SmartArt Shapes from the Text Pane - Page 1602
Adding Images to SmartArt - Page 1603
Adding Images to SmartArt - Page 1604
Special Considerations for Organizational Charts and Hierarchical SmartArt - Page 1605
Special Considerations for Organizational Charts and Hierarchical SmartArt - Page 1606
Special Considerations for Organizational Charts and Hierarchical SmartArt - Page 1607
Using Limited SmartArt - Page 1608
Using Limited SmartArt - Page 1609
Deciphering the Labeled Hierarchy Layouts - Page 1610
Deciphering the Labeled Hierarchy Layouts - Page 1611
Deciphering the Labeled Hierarchy Layouts - Page 1612
Deciphering the Labeled Hierarchy Layouts - Page 1613
Working with Shapes - Page 1614
Working with Shapes - Page 1615
Using WordArt for Interesting Titles and Headlines - Page 1616
Using Text Boxes to Flow Long Text Passages - Page 1617
Using Text Boxes to Flow Long Text Passages - Page 1618
Using Text Boxes to Flow Long Text Passages - Page 1619
Using Text Boxes to Flow Long Text Passages - Page 1620
Using Text Boxes to Flow Long Text Passages - Page 1621
35. Using Pictures and Clip Art - Page 1622
Inserting Multiple Pictures at Once - Page 1623
Inserting a Picture or Clip Art from Online - Page 1624
Inserting a Picture or Clip Art from Online - Page 1625
Inserting a Picture or Clip Art from Online - Page 1626
Adjusting the Picture Using the Ribbon Tab - Page 1627
Resizing the Picture to Fit - Page 1628
Resizing the Picture to Fit - Page 1629
Adjusting the Brightness and Contrast - Page 1630
Adjusting the Brightness and Contrast - Page 1631
Adding Interesting Effects Using the Picture Styles Gallery - Page 1632
Applying Artistic Effects - Page 1633
Removing the Background - Page 1634
Removing the Background - Page 1635
Removing the Background - Page 1636
Reducing a Picture’s File Size - Page 1637
Adding Captions to Images - Page 1638
Adding Captions to Images - Page 1639
Using the Format Picture Task Pane - Page 1640
Using the Format Picture Task Pane - Page 1641
Rotating a Shape over a Picture - Page 1642
Inserting Screen Clippings - Page 1643
Selecting and Arranging Pictures - Page 1644
Selecting and Arranging Pictures - Page 1645
Selecting and Arranging Pictures - Page 1646
V: Sharing Information - Page 1647
36. Printing - Page 1648
Finding Print Settings - Page 1649
Finding Print Settings - Page 1650
Finding Print Settings - Page 1651
Finding Print Settings - Page 1652
Previewing the Printed Report - Page 1653
Using the Print Preview on the Print Panel - Page 1654
Using Full Screen Print Preview - Page 1655
Making the Report Fit On the Page - Page 1656
Adjusting Worksheet Orientation - Page 1657
Adjusting Worksheet Margins - Page 1658
Adjusting Worksheet Margins - Page 1659
Adding Print Titles - Page 1660
Adding Print Titles - Page 1661
Excluding Part of Your Worksheet from the Print Range - Page 1662
Working with Page Breaks - Page 1663
Manual Versus Automatic Page Breaks - Page 1664
Adding Headers or Footers to the Printed Report - Page 1665
Adding Headers or Footers to the Printed Report - Page 1666
Adding Headers or Footers to the Printed Report - Page 1667
Adding a Custom Header - Page 1668
Using Different Headers and Footers in the Same Document - Page 1669
Using Different Headers and Footers in the Same Document - Page 1670
Scaling Headers and Footers - Page 1671
Scaling Headers and Footers - Page 1672
Choosing a Printer - Page 1673
Choosing What to Print - Page 1674
Choosing What to Print - Page 1675
Changing Printer Properties - Page 1676
Exploring Other Page Setup Options - Page 1677
Centering a Small Report on a Page - Page 1678
Printing Comments - Page 1679
Controlling the First Page Number - Page 1680
37. Excel Web App and Other Ways to Share Workbooks - Page 1681
37. Excel Web App and Other Ways to Share Workbooks - Page 1682
Viewing Your SkyDrive Workbooks from Anywhere - Page 1683
Viewing Your SkyDrive Workbooks from Anywhere - Page 1684
Viewing Your SkyDrive Workbooks from Anywhere - Page 1685
Viewing Your SkyDrive Workbooks from Anywhere - Page 1686
Viewing Your SkyDrive Workbooks from Anywhere - Page 1687
Viewing Your SkyDrive Workbooks from Anywhere - Page 1688
Editing Excel on the iPad - Page 1689
Editing Excel on the iPad - Page 1690
Editing Excel on the Surface RT Tablet - Page 1691
Selecting a Range with Touch - Page 1692
Using Ink Tools - Page 1693
Using the Excel Web App Instead of Excel Starter - Page 1694
Using the Excel Web App Instead of Excel Starter - Page 1695
Using the Excel Web App Instead of Excel Starter - Page 1696
Designing a Workbook as an Interactive Web Page - Page 1697
Designing a Workbook as an Interactive Web Page - Page 1698
Designing a Workbook as an Interactive Web Page - Page 1699
Designing a Workbook as an Interactive Web Page - Page 1700
Sharing a Link to Your Web Workbook - Page 1701
Sharing a Link to Your Web Workbook - Page 1702
Sharing a Link to Your Web Workbook - Page 1703
Sharing a Link to Your Web Workbook - Page 1704
Embedding Your Workbook in a Blog Post or Your Web Page - Page 1705
Embedding Your Workbook in a Blog Post or Your Web Page - Page 1706
Embedding Your Workbook in a Blog Post or Your Web Page - Page 1707
Collecting Survey Data in the Excel Web App - Page 1708
Collecting Survey Data in the Excel Web App - Page 1709
Collecting Survey Data in the Excel Web App - Page 1710
Collecting Survey Data in the Excel Web App - Page 1711
Collecting Survey Data in the Excel Web App - Page 1712
Collecting Survey Data in the Excel Web App - Page 1713
Collecting Survey Data in the Excel Web App - Page 1714
Make Any Web Table Interactive with Excel Everywhere - Page 1715
Make Any Web Table Interactive with Excel Everywhere - Page 1716
Make Any Web Table Interactive with Excel Everywhere - Page 1717
Make Any Web Table Interactive with Excel Everywhere - Page 1718
Make Any Web Table Interactive with Excel Everywhere - Page 1719
Creating a PDF from a Worksheet - Page 1720
Creating a PDF from a Worksheet - Page 1721
Creating a PDF from a Worksheet - Page 1722
Interacting with Other Office Applications - Page 1723
Pasting Excel Data to Microsoft OneNote - Page 1724
Pasting Excel Data to Microsoft OneNote - Page 1725
Using Excel Charts in PowerPoint - Page 1726
Creating Tables in Excel and Pasting to Word - Page 1727
Creating Tables in Excel and Pasting to Word - Page 1728
Creating Labels in Word from Excel Data - Page 1729
Creating Labels in Word from Excel Data - Page 1730
Creating Labels in Word from Excel Data - Page 1731
Creating Labels in Word from Excel Data - Page 1732
Creating Labels in Word from Excel Data - Page 1733
Creating Labels in Word from Excel Data - Page 1734
Creating Labels in Word from Excel Data - Page 1735
Creating Labels in Word from Excel Data - Page 1736
38. Saving Time Using the Easy-XL Program - Page 1737
Easy-XL Works Best with Tabular Data - Page 1738
Easy-XL Works Best with Tabular Data - Page 1739
Using a Fuzzy Match - Page 1740
Using a Fuzzy Match - Page 1741
Using a Fuzzy Match - Page 1742
Using a Fuzzy Match - Page 1743
Using a Fuzzy Match - Page 1744
Using a Fuzzy Match - Page 1745
Text to Columns on Steroids - Page 1746
Text to Columns on Steroids - Page 1747
Text to Columns on Steroids - Page 1748
Sorting Columns Left to Right - Page 1749
Summarizing Data - Page 1750
Adding Text to Cells - Page 1751
Adding Text to Cells - Page 1752
Filling in the Annoying Outline View - Page 1753
Deal with Fiscal Years - Page 1754
Record Easy-XL Commands into VBA Macros - Page 1755