Microsoft Excel 2010 In Depth

Contents......Page 6
Introduction......Page 34
Understanding “In” Versus “Out” Commands......Page 40
Using Backstage View......Page 41
Opening Recent Files......Page 42
Getting Information About the Current Workbook......Page 47
Printing and Print Preview......Page 51
Sharing Your Workbook Using Save & Send......Page 53
Getting Updates and Help......Page 54
Using the Ribbon......Page 56
The Ribbon Is Constantly Changing......Page 60
Solving Common Ribbon Problems......Page 63
Using the Quick Access Toolbar......Page 65
Adding Favorite Commands to the Quick Access Toolbar......Page 67
Customizing the Quick Access Toolbar......Page 68
3 Using Other Excel Interface Improvements......Page 72
Using Live Preview......Page 73
Previewing Paste Using the Paste Options Gallery......Page 74
Using the Mini Toolbar to Format Selected Text......Page 80
Expanding the Formula Bar......Page 83
Zooming In and Out on a Worksheet......Page 84
Switching Between Normal View, Page Break Preview, and Page Layout View Modes......Page 85
Using the New Sheet Icon to Add Worksheets......Page 87
Performing a Simple Ribbon Modification......Page 90
Using a More Complex Ribbon Modification......Page 92
Adding a New Ribbon Tab......Page 95
Questions About Ribbon Customization......Page 96
Using New Keyboard Accelerators......Page 98
Using the Shortcut Keys......Page 102
Using Excel 2003 Keyboard Accelerators......Page 109
Introducing the Excel Options Dialog......Page 120
Ten Options to Consider......Page 130
Guide to Excel Options......Page 132
Excel Grid Limits......Page 142
Other Limits in Excel 2010......Page 144
Tips for Navigating the Big Grid......Page 146
Understanding the New File Formats......Page 147
Version Compatibility......Page 151
Opening Excel 2010 Files in Excel 2002 or 2003......Page 153
Creating Excel 2010 File Formats in Excel 2003......Page 154
Opening Excel 2010 Files in Excel 2007......Page 155
Getting the Most from This Chapter......Page 156
Introduction to Formulas......Page 157
Entering Your First Formula......Page 158
Three Methods of Entering Formulas......Page 165
Entering the Same Formula in Many Cells......Page 168
Use the Table Tool to Copy a Formula......Page 171
Formula Operators......Page 176
Understanding Error Messages in Formulas......Page 180
Using Formulas to Join Text......Page 182
Copying Versus Cutting a Formula......Page 184
Automatically Formatting Formula Cells......Page 187
Using Date Math......Page 188
Troubleshooting Formulas......Page 190
Excel in Practice: Moving the Formula ToolTip......Page 197
10 Understanding Functions......Page 198
Working with Functions......Page 200
Getting Help with Excel Functions......Page 203
Using AutoSum......Page 205
Using the New General-Purpose Functions in Excel......Page 209
Functions with New Variations in Excel 2010......Page 211
Functions That Have Been Renamed......Page 213
Cube Functions Introduced in Excel 2007......Page 216
11 Using Everyday Functions: Math, Date and Time, and Text Functions......Page 222
Examples of Math Functions......Page 230
Dates and Times in Excel......Page 266
Examples of Date and Time Functions......Page 272
Examples of Text Functions......Page 289
12 Using Powerful Functions: Logical, Lookup, and Database Functions......Page 310
Examples of Logical Functions......Page 316
Examples of Information Functions......Page 324
Examples of Lookup and Reference Functions......Page 335
Examples of Database Functions......Page 367
13 Using Financial Functions......Page 376
Examples of Common Household Loan and Investment Functions......Page 381
Examples of Functions for Financial Professionals......Page 387
Examples of Depreciation Functions......Page 392
Functions for Investment Analysis......Page 399
Examples of Functions for Bond Investors......Page 405
Examples of Miscellaneous Financial Functions......Page 419
14 Using Statistical Functions......Page 422
Examples of Functions for Descriptive Statistics......Page 434
Examples of Functions for Regression and Forecasting......Page 454
Examples of Functions for Inferential Statistics......Page 478
Using the Analysis ToolPak to Perform Statistical Analysis......Page 512
15 Using Trig, Matrix, and Engineering Functions......Page 536
A Brief Review of Trigonometry Basics......Page 540
Examples of Logarithm Functions......Page 552
Working with Imaginary Numbers......Page 559
Solving Simultaneous Linear Equations with Matrix Functions......Page 567
Examples of Engineering Functions......Page 576
Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)......Page 601
Connecting Two Worksheets......Page 604
Connecting to Data on a Web Page......Page 614
Setting Up a Connection to a Text File......Page 617
Setting Up a Connection to an Access Database......Page 621
Setting Up SQL Server, XML, OLE DB, and ODBC Connections......Page 622
Managing Connections......Page 628
Using 3D Formulas to Spear Through Many Worksheets......Page 632
Combining Multiple Formulas into One Formula......Page 636
Calculating a Cell Reference in the Formula by Using the INDIRECT Function......Page 639
Assigning a Formula to a Name......Page 642
Replacing Multiple Formulas with One Array Formula......Page 646
Use the Name Box to Define a Name for a Cell......Page 652
Naming a Cell by Using the Name Dialog......Page 654
Using Scope to Allow Duplicate Names in a Workbook......Page 655
Using Named Ranges to Simplify Formulas......Page 657
Managing Names......Page 664
Using a Name to Simplify an Absolute Reference......Page 666
Using a Name to Hold a Value......Page 667
Assigning a Formula to a Name......Page 668
19 Fabulous Table Intelligence......Page 676
Defining Suitable Data for Excel Tables......Page 677
Toggling Totals......Page 682
Expanding a Table......Page 683
Adding New Formulas to Tables......Page 685
Selecting Only the Data in the Column......Page 687
Using Table Data for Charts to Ensure Stickiness......Page 691
Replacing Named Ranges with Table References......Page 692
Creating Banded Rows and Columns with Table Styles......Page 696
Dealing with the AutoFilter Drop-Downs......Page 698
20 Sorting Data......Page 700
Introducing the Sort Dialog......Page 701
Using Specialized Sorting......Page 702
One-Click Sorting......Page 707
Sorting Randomly......Page 708
Filtering Records......Page 712
Sorting Filtered Results......Page 722
Using the Advanced Filter Command......Page 724
Using Remove Duplicates to Find Unique Values......Page 726
Adding Automatic Subtotals......Page 730
Working with the Subtotals......Page 732
Using Specialty Subtotal Techniques......Page 738
23 Using Pivot Tables to Analyze Data......Page 744
When you have your data in the correct format, creating and changing a pivot table is easy......Page 746
Dealing with the Compact Layout......Page 748
Rearranging a Pivot Table......Page 749
Finishing Touches: Numeric Formatting and Removing Blanks......Page 751
Four Things You Have to Know When Using Pivot Tables......Page 753
Calculating and Roll-Ups with Pivot Tables......Page 754
Formatting a Pivot Table......Page 762
Finding More Information on Pivot Tables......Page 763
Filtering Using the Row Label Filter......Page 764
Filtering Using Report Filter Fields......Page 775
Filtering Using Slicers......Page 778
Filtering Oddities......Page 782
Sorting a Pivot Table......Page 783
Benefits and Drawbacks to PowerPivot......Page 786
Installing PowerPivot......Page 789
Case Study: Building a PowerPivot Report......Page 790
Some Things Are Different......Page 803
Two Kinds of DAX Calculations......Page 804
Using DAX to Create New Measures......Page 810
Other Notes......Page 820
Using What-If......Page 824
Using Scenario Manager......Page 828
Using Goal Seek......Page 835
Using Solver......Page 840
Checking Security Settings Before Using Macros......Page 850
Recording a Macro......Page 851
Case Study: Macro for Formatting for a Mail Merge......Page 852
Everyday-Use Macro Example: Formatting an Invoice Register......Page 860
Understanding VBA Code—An Analogy......Page 864
Using Simple Variables and Object Variables......Page 870
Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME......Page 873
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges......Page 875
Combination Macro Example: Creating a Report for Each Customer......Page 882
Speeding Up Calculation by Using Multithreaded Calculation......Page 890
Watching the Results of a Distant Cell......Page 891
Opening the Same Files Every Day......Page 892
Comparing Documents Side by Side with Synchronous Scrolling......Page 893
Calculating a Formula in Slow Motion......Page 894
Inserting a Symbol in a Cell......Page 895
Edit an Equation......Page 896
Adding a Digital Signature Line to a Workbook......Page 897
Protecting a Worksheet......Page 898
Sharing a Workbook......Page 899
Separating Text Based on a Delimiter......Page 900
Translating Text......Page 901
Charting Utilities from Jon Peltier......Page 904
Add Labels to XY Charts......Page 905
Accessing More Functions by Using MOREFUNC.DLL......Page 906
General Purpose Utility Suites......Page 907
Why Format Worksheets?......Page 908
Using Traditional Formatting......Page 910
Formatting with Styles......Page 934
Understanding Themes......Page 936
Other Formatting Techniques......Page 946
Copying Formats......Page 952
31 Using Data Visualizations and Conditional Formatting......Page 958
Using Data Bars to Create In-Cell Bar Charts......Page 959
Using Color Scales to Highlight Extremes......Page 964
Using Icon Sets to Segregate Data......Page 965
Using the Top/Bottom Rules......Page 968
Using the Highlight Cells Rules......Page 970
Tweaking Rules with Advanced Formatting......Page 975
Combining Rules......Page 980
Clearing Conditional Formats......Page 982
Special Considerations for Pivot Tables......Page 983
32 Using Excel Charts......Page 986
Understanding the Components of a Chart......Page 987
Setting Up Data for Charting......Page 988
Inserting a Chart by Choosing a Chart Type......Page 989
Choosing a Chart Layout to Further Customize the Chart Type......Page 994
Customizing a Chart Using the Chart Tools Tabs......Page 995
Charting Tips and Tricks......Page 1000
Using Pivot Charts......Page 1012
Fitting a Chart into the Size of a Cell with Sparklines......Page 1016
Understanding How Excel Maps Data to Sparklines......Page 1017
34 Using SmartArt, Shapes, WordArt, and Text Boxes......Page 1030
Using SmartArt......Page 1031
Using Shapes to Display Cell Contents......Page 1048
Using WordArt for Interesting Titles and Headlines......Page 1051
Using Pictures on Worksheets......Page 1056
Inserting Screen Clippings......Page 1068
Using Clip Art......Page 1069
Printing from Backstage View......Page 1072
Using Page Layout View......Page 1077
Using the Improved Headers and Footers......Page 1079
Using the Page Setup and Sheet Options......Page 1083
Working with Page Breaks......Page 1088
Sharing Workbooks with Others......Page 1092
Sending a Workbook via Email......Page 1099
Publishing to Excel Services on SharePoint......Page 1102
Interacting with Other Office Applications......Page 1103
Downloading and Installing Easy-XL......Page 1112
Doing Away with VLOOKUP......Page 1113
Text to Columns on Steroids......Page 1119
Summarizing Data......Page 1121
Transforming Data Instead of TRIM(), PROPER(), CLEAN()......Page 1124
There’s More......Page 1126
A......Page 1130
C......Page 1131
D......Page 1133
E......Page 1134
F......Page 1135
H......Page 1136
I......Page 1137
M......Page 1138
O......Page 1139
P......Page 1140
Q......Page 1141
R......Page 1142
S......Page 1143
T......Page 1144
V......Page 1145
W......Page 1146
Z......Page 1147