Contents......Page 4
Introduction......Page 30
The Massive Grid......Page 31
Fantastic Data Visualizations......Page 32
Where Are My Menus and Toolbars?......Page 35
Why Couldnโt They Leave Well Enough Alone?......Page 36
The Most Requested New Features......Page 37
A Scientific Approach to Popular Features......Page 38
Introducing the Ribbon......Page 40
Ribbon Limitations......Page 41
Ribbon Tricks......Page 42
Live Preview......Page 43
The Journey......Page 44
Special Elements......Page 45
I: Mastering the New User Interface......Page 48
1 Introducing the Ribbon User Interface......Page 50
The Excel 2007 Interface......Page 51
Using the Office Icon Menu......Page 52
Understanding the Ribbon User Interface......Page 54
Finding Old Menu Items in the New Interface......Page 60
2 The Quick Access Toolbar......Page 70
Changing the Location of the Quick Access Toolbar......Page 71
Adding Your Favorite Commands to the Quick Access Toolbar......Page 72
Customizing the Quick Access Toolbar......Page 74
3 The Mini Toolbar and Other U.I. Improvements......Page 82
Using the Mini Toolbar to Format Selected Text......Page 83
Expanding the Formula Bar......Page 87
Zooming In and Out on a Worksheet......Page 88
Using the Status Bar to Add Numbers......Page 89
Switching Between Normal View, Page Break Preview, and Page Layout View Modes......Page 90
Using the New Sheet Icon to Add Worksheets......Page 92
4 Keyboard Shortcuts......Page 94
Using the New Keyboard Accelerators......Page 95
Using the Shortcut Keys......Page 99
Using Excel 2003 Keyboard Accelerators......Page 105
5 Galleries, Live Preview, and Themes......Page 116
Using Galleries......Page 117
Using Live Preview......Page 119
Understanding Themes......Page 120
6 The Excel Options Dialog......Page 132
Introducing the Excel Options Dialog......Page 133
Toggling the New Excel 2007 Features......Page 136
Controlling the Look and Feel of the Worksheet Window......Page 137
Guide to Excel Options......Page 141
II: A Tour of Whatโs New......Page 148
7 The Big Grid......Page 150
Excel 2007 Grid Limits......Page 151
Other Limits in Excel 2007......Page 154
Tips for Navigating the Big Grid......Page 156
8 Fabulous Table Intelligence......Page 160
Defining Suitable Data for Excel Tables......Page 161
Keeping Headers in View......Page 162
Adding a Total Row to a Table......Page 166
Expanding a Table......Page 167
Adding New Formulas to Tables......Page 168
Selecting Only the Data in the Column......Page 171
Using Table Data for Charts to Ensure Stickiness......Page 174
Replacing Named Ranges with Table References......Page 175
Creating Banded Rows and Columns with Table Styles......Page 178
Dealing with the AutoFilter Drop-Downs......Page 181
9 Visualizing Data in Excel......Page 182
Creating In-Cell Bar Charts with Data Bars......Page 184
Creating Heat Maps with Color Scales......Page 187
Using Stop if True to Improve All Conditional Formatting......Page 193
Using the Top/Bottom Rules......Page 195
Using the Highlight Cells Rules......Page 197
Tweaking Rules with Advanced Formatting......Page 203
Combining Rules......Page 214
Clearing Conditional Formats......Page 215
Special Considerations for Tables......Page 216
10 Using Pivot Tables to Analyze Data......Page 217
Preparing Your Data for Pivot Table Analysis......Page 221
Creating Your First Pivot Table......Page 229
Changing the Default Layout of a Pivot Table......Page 231
Adding New Fields to a Pivot Table......Page 237
Eliminating Blank Cells from a Pivot Table......Page 238
Using Pivot Table Legacy Mode......Page 239
Pivot Table Limitations......Page 240
11 Formatting Pivot Tables......Page 244
The New Compact View......Page 245
Report Layout Options......Page 249
Special Considerations When Adding Data Fields to the Value Section......Page 253
Formatting a Pivot Table......Page 256
12 Pivot Table Data Crunching for Excel 2007......Page 266
Grouping Daily Dates to Months and Years......Page 267
Sorting and Filtering......Page 268
Exploring Even More Filtering Options......Page 275
Adding New Items Along a Dimension......Page 284
Showing Percentages Instead of Values......Page 287
Exploring Interesting Uses for Pivot Tables......Page 292
Finding More Information on Pivot Tables......Page 298
13 Removing Duplicates and Filtering......Page 300
Using Remove Duplicates to Find Unique Values......Page 301
Filtering Records......Page 304
Using the Advanced Filter Command......Page 313
14 Sorting Data......Page 316
Introducing the New Sort Dialog......Page 317
Using Specialized Sorting......Page 318
One-Click Sorting......Page 323
Sorting Randomly......Page 324
15 Using Excel Charts......Page 328
Understanding the Components of a Chart......Page 329
Setting Up Your Data for Charting......Page 330
Inserting a Chart by Choosing a Chart Type......Page 331
Choosing a Chart Layout to Further Customize the Chart Type......Page 336
Customizing a Chart by Using a Chart Style or Theme......Page 337
Charting Tricks and Techniques......Page 341
Using Pivot Charts......Page 352
16 Using SmartArt, Shapes, WordArt, and Text Boxes......Page 356
Using SmartArt......Page 357
Using Shapes to Display Cell Contents......Page 371
Using WordArt for Interesting Titles and Headlines......Page 374
Using Text Boxes to Flow Long Text Passages......Page 376
17 Using Pictures and Clip Art......Page 380
Using Pictures on Worksheets......Page 381
Using Clip Art......Page 389
III: Working in a Legacy Environment......Page 392
18 File Format Differences......Page 394
A Brief History of File Formats......Page 395
Using the New XML File Formats: XLSX and XLSM......Page 397
The Future of File Formats......Page 399
19 Working with Prior Versions of Excel......Page 400
Opening Excel 2007 Files in Previous Versions of Excel......Page 401
Creating Excel 2007 File Formats in Excel 2003......Page 405
Opening Excel 2003 Files in Excel 2007......Page 406
Preparing to Save Excel 2007 Files for Excel 97 Through Excel 2003......Page 407
The Digital Divide......Page 410
IV: Calculating with Excel......Page 412
20 Understanding Formulas......Page 414
Getting the Most from This Chapter......Page 415
Introduction to Formulas......Page 416
Entering Your First Formula......Page 417
Three Methods of Entering Formulas......Page 424
Entering the Same Formula in Many Cells......Page 427
Copying a Formula by Using the Table Tool......Page 431
21 Controlling Formulas......Page 438
Formula Operators......Page 439
Understanding Error Messages in Formulas......Page 443
Using Formulas to Join Text......Page 444
Copying Versus Cutting a Formula......Page 447
Automatically Formatting Formula Cells......Page 448
Using Date Math......Page 450
Troubleshooting Formulas......Page 452
22 Understanding Functions......Page 460
Working with Functions......Page 461
Getting Help with Excel Functions......Page 465
Using AutoSum......Page 467
Using the New General-Purpose Functions in Excel 2007......Page 472
Using the New CUBE Functions in Excel 2007......Page 476
Using the Former ATP Functions......Page 477
The Function Reference Chapters......Page 478
23 Using Everyday Functions: Math, Date and Time, and Text Functions......Page 480
Examples of Math Functions......Page 487
Dates and Times in Excel......Page 513
Examples of Date and Time Functions......Page 519
Examples of Text Functions......Page 534
24 Using Powerful Functions: Logical, Lookup, and Database Functions......Page 554
Examples of Logical Functions......Page 560
Examples of Information Functions......Page 568
Examples of Lookup and Reference Functions......Page 579
Examples of Database Functions......Page 608
25 Using Financial Functions......Page 616
Examples of Common Household Loan and Investment Functions......Page 621
Examples of Functions for Financial Professionals......Page 627
Examples of Depreciation Functions......Page 632
Functions for Investment Analysis......Page 638
Examples of Functions for Bond Investors......Page 644
Examples of Miscellaneous Financial Functions......Page 656
26 Using Statistical Functions......Page 660
Examples of Functions for Descriptive Statistics......Page 671
Examples of Functions for Regression and Forecasting......Page 687
Examples of Functions for Inferential Statistics......Page 711
Using the Analysis Toolpak to Perform Statistical Analysis......Page 740
27 Using Trig, Matrix, and Engineering Functions......Page 764
A Brief Review of Trigonometry Basics......Page 769
Examples of Logarithm Functions......Page 779
Working with Imaginary Numbers......Page 785
Solving Simultaneous Linear Equations with Matrix Functions......Page 792
Examples of Engineering Functions......Page 799
Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)......Page 823
28 Connecting Worksheets, Workbooks, and External Data......Page 826
Connecting Two Worksheets......Page 827
Connecting to a Worksheet in Another Workbook......Page 829
Connecting to Data on a Web Page......Page 834
Setting Up a Connection to a Text File......Page 838
Setting Up a Connection to an Access Database......Page 841
Setting Up SQL Server, XML, OLE DB, and ODBC Connections......Page 842
Managing Connections......Page 847
29 Using Super Formulas in Excel......Page 852
Using 3D Formulas to Spear Through Many Worksheets......Page 853
Combining Multiple Formulas into One Formula......Page 857
Calculating a Cell Reference in the Formula by Using the INDIRECT Function......Page 859
Assigning a Formula to a Name......Page 862
Replacing Multiple Formulas with One Array Formula......Page 867
30 Using Names in Excel......Page 870
Defining a Name for a Cell by Using the Name Box......Page 871
Naming a Cell by Using the New Name Dialog......Page 872
Using the Name Box for Quick Navigation......Page 873
Using Scope to Allow Duplicate Names in a Workbook......Page 874
Using Named Ranges to Simplify Formulas......Page 875
Managing Names......Page 883
Using a Name to Hold a Value......Page 885
Assigning a Formula to a Name......Page 886
31 Using What If, Scenario Manager, Goal Seek, and Solver......Page 892
Using What-If......Page 893
Using Scenario Manager......Page 896
Using Goal Seek......Page 903
Using Solver......Page 908
V: Formatting and Sharing Information......Page 918
32 Formatting Worksheets......Page 920
Why Format Worksheets?......Page 921
Using Traditional Formatting......Page 922
Formatting with Styles......Page 943
Other Formatting Techniques......Page 944
Copying Formats......Page 950
33 Printing......Page 954
Using Page Layout View......Page 955
Using the Improved Headers and Footers......Page 956
Using the Page Setup and Sheet Options......Page 960
Working with Page Breaks......Page 966
Printing......Page 968
34 Sharing Workbooks with Others......Page 970
Sending a Workbook via Email......Page 971
Creating a PDF from a Worksheet......Page 972
Publishing a Worksheet to a Webpage......Page 973
Publishing to a Server......Page 975
35 More Tips and Tricks for Excel 2007......Page 978
Speeding Up Calculation by Using Multithreaded Calculation......Page 979
Watching the Results of a Far-Off Cell......Page 980
Looking for Hidden Data in a Workbook......Page 981
Opening the Same Files Everyday......Page 982
Downloading Cool Spreadsheets from Office Online......Page 983
Adding Visual Interest by Using a Background Picture......Page 984
Comparing Documents Side-by-Side with Synchronous Scrolling......Page 985
Calculating a Formula in Slow Motion......Page 986
Adding a Digital Signature Line to a Workbook......Page 987
Protecting a Worksheet......Page 988
Sharing a Workbook......Page 990
Adding Automatic Subtotals......Page 991
Translating Text......Page 994
VI: More Power......Page 996
36 Automating Repetitive Functions Using VBA Macros......Page 998
Checking Security Settings Before Using Macros......Page 999
Recording a Macro......Page 1000
Case Study: Macro for Formatting for a Mail Merge......Page 1001
Everyday-Use Macro Example: Formatting an Invoice Register......Page 1008
Understanding VBA CodeโAn Analogy......Page 1011
Using Simple Variables and Object Variables......Page 1015
Using R1C1-Style Formulas......Page 1016
Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName......Page 1019
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges......Page 1020
Combination Macro Example: Creating a Report for Each Customer......Page 1027
Conclusion......Page 1033
37 Interacting with Other Office Applications......Page 1034
Pasting Excel Data to Microsoft OneNote......Page 1035
Pasting Excel Data to PowerPoint......Page 1038
Creating Tables in Excel and Pasting to Word......Page 1040
Pasting Word Data to an Excel Text Box......Page 1041
Using Excel Data in a Word Mail Merge......Page 1042
Building a Pivot Table from Access Queries......Page 1045
38 A Tour of the Best Add-Ins for Excel......Page 1048
Customizing the Ribbon by Using CustomizeRibbon......Page 1049
Doing Access with Excel by Using DigDB......Page 1050
Accessing More Functions by Using MoreFunc.dll......Page 1051
Using Add-ins from AddIns.com......Page 1052
A......Page 1054
C......Page 1055
D......Page 1059
E......Page 1060
F......Page 1061
G......Page 1063
I......Page 1064
K......Page 1065
M......Page 1066
N......Page 1068
P......Page 1069
R......Page 1071
S......Page 1072
T......Page 1074
V......Page 1076
Z......Page 1077