Learn Excel 2007 from Mr Excel: 377 Excel mysteries solved

Dedication......Page 17
About the Author......Page 19
Acknowledgments......Page 21
Foreword......Page 23
The Excel Environment......Page 31
Find Icons on the Ribbon......Page 33
Go Wide......Page 34
Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003......Page 36
The Office Development Team Likes the Artist Formerly Known as Prince......Page 37
The Paste Icon Is Really Two Icons......Page 38
Use Dialog Launchers to Access the Excel 2003 Dialog......Page 41
Make Your Most-Used Icons Always Visible......Page 43
The Alt Keystrokes Still Work in 2007 (If You Type Them Slowly Enough)......Page 46
Use New Keyboard Shortcuts to Access the Ribbon......Page 48
The Blue Question Mark Is Help......Page 51
All Commands Start at the Top (Except for 2 Controls at the Bottom)......Page 53
What Happened to Tools – Customize?......Page 55
What Happened to Tools – Options?......Page 56
Where Are My Macros?......Page 58
Why Do I Have Only 65,536 Rows?......Page 60
Which File Format Should I Use?......Page 62
Share Files with People Who Are Still Using Excel 97 Through Excel 2003......Page 64
Use Live Preview......Page 66
Get Quick Access to Formatting Options Using the Mini Toolbar......Page 68
MIX FORMATTING IN a Single CELL......Page 69
Copy the Characters from a Cell ­Instead of Copying an Entire Cell......Page 70
I am a Lobbyist Writing Policy Papers for the White House......Page 72
My Manager Wants Me to Create a New Expense Report from Scratch......Page 73
Increase the Number of Documents in the Recent Documents List......Page 74
Keep Favorites in the Recent Documents List......Page 76
I’ve Searched Everywhere. Where Is the Save Workspace Command?......Page 77
Use a Workspace to Remember What Workbooks to Open......Page 79
Close All Open Workbooks......Page 80
Automatically Move the Cell Pointer in a Direction After Entering a Number......Page 82
Return to the First Column After Typing the Last Column......Page 83
Enter Data in a Circle (Or Any Other Pattern)......Page 85
How to See Headings as You Scroll Around a Report......Page 87
How to See Headings and Row Labels as You Scroll Around a Report......Page 89
How to Print Titles at the Top of Each Page......Page 91
Print a Letter at the Top of Page 1 and Repeat Headings at the Top of Each Subsequent Page......Page 94
How to Print Page Numbers at the Bottom of Each Page......Page 96
How to Make a Wide Report Fit to One Page Wide by Many Pages Tall......Page 98
Arrange Windows to See Two or More Open Workbooks......Page 99
Why Is There a “:2” After My Workbook Name in the Title Bar?......Page 103
Have Excel Always Open Certain Workbook(s)......Page 105
Set up Excel Icons to Open a Specific File on Startup......Page 106
Use a Macro to Further Customize Startup......Page 109
Control Settings for Every New Workbook and Worksheet......Page 112
Open a Copy of a Workbook......Page 114
Open a Saved File Whose Name You Cannot Recall......Page 115
Excel 2007’S Obsession with Security Has Destroyed Linked Workbooks......Page 116
I Navigate by Sliding the Scrollbar and Now the Slider Has Become Tiny......Page 118
Send an Excel File as an Attachment......Page 120
Save Excel Data as a Text File......Page 122
Use Excel as a Word Processor......Page 126
Add Word to Excel......Page 129
Spell check a Region......Page 132
Translate with Excel......Page 133
Use Hyperlinks to Create an Opening Menu for a Workbook......Page 135
Get Quick Access to Paste Values......Page 138
Quickly Copy a Formula to All Rows of Data......Page 140
Enter a Series of Months, Days, or More by Using the Fill Handle......Page 142
Have the Fill Handle Fill Your List of Part Numbers......Page 145
Quickly Turn a Range on Its Side......Page 148
Stop Excel from AutoCorrecting Certain Words......Page 150
Use AutoCorrect to Enable a Shortcut......Page 151
Why Won’t the Track Changes Feature Work in Excel?......Page 153
Copy Cells from One Worksheet to Many Other Worksheets......Page 154
Have Excel Talk to You......Page 156
Enter Special Symbols......Page 158
Find Text Entries......Page 159
What Do All the Triangles Mean?......Page 163
Why Can’t Excel Find a Number?......Page 165
Get Free Excel Help......Page 170
CALCULATING WITH EXCEL......Page 173
Copy a Formula That Contains Relative References......Page 175
Copy a Formula While Keeping One Reference Fixed......Page 178
Create a Multiplication Table......Page 180
Calculate a Sales Commission......Page 182
Simplify the Entry of Dollar Signs in Formulas......Page 184
Learn R1C1 Referencing to Understand Formula Copying......Page 189
Create Easier-to-Understand Formulas with Named Ranges......Page 194
Use Named Constants to Store Numbers......Page 197
Assign a Name to a Formula......Page 199
Total Without Using a Formula......Page 207
Add Two Columns Without Using Formulas......Page 209
How to Calculate Sales in Excess of a Quota......Page 212
How to Join Two Text Columns......Page 214
Join Text with a Date or Currency......Page 217
How to Sort on One Portion of an Account ID......Page 219
How to Isolate the Center Portion of an Account ID......Page 221
How to Isolate Everything Before a Dash in a Column by Using Functions......Page 223
How to Use Functions to Isolate Everything After a Dash in a Column......Page 224
How to Use Functions to Isolate Everything After the Second Dash in a Column......Page 227
How to Separate a Part Number into Three Columns......Page 229
Combine Intermediate Formulas into a Mega-Formula......Page 234
Change Smith, Jane to Jane Smith......Page 238
Add the Worksheet Name as a Title......Page 240
Avoid #REF! Errors When Deleting Columns......Page 242
Create Random Numbers......Page 244
Create Random Numbers to Sequence a Class of Students......Page 246
Play Dice Games with Excel......Page 247
Create Random Letters......Page 250
Convert Numbers to Text......Page 252
Calculate a Loan Payment......Page 254
Calculate Many Scenarios for Loan Payments......Page 255
Back into an Answer Using Goal Seek......Page 256
Create an Amortization Table......Page 258
Get Help on Any Function While Entering a Formula......Page 260
Discover New Functions Using the fx Button......Page 262
Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses......Page 264
Three Methods of Entering Formulas......Page 266
Start a Formula with = or +......Page 272
Use AutoSum to Quickly Enter a Total Formula......Page 273
AutoSum Doesn’t Always Predict My Data Correctly......Page 275
Use the AutoSum Button to Enter Averages, Min, Max, and Count......Page 279
The Count Option of the AutoSum Dropdown Doesn’t Appear to Work......Page 281
Use AutoSum After Filtering......Page 284
Use Table Functionality to Simplify Copying of Formulas......Page 286
Rename Your Tables......Page 288
Use Simple References in a Table......Page 289
Automatically Number a List of Employees......Page 292
Rank Scores......Page 294
Sorting with a Formula......Page 295
Rank a List Without Ties......Page 299
Add Comments to a Formula......Page 301
Calculate a Moving Average......Page 302
Calculate a Trendline Forecast......Page 304
Build a Model to Predict Sales Based on Multiple Regression......Page 308
Use F9 in the Formula Bar to Test a Formula......Page 312
Quick Calculator......Page 314
When Entering a Formula, You Get the Formula Instead of the Result......Page 315
Why Don’t Dates Show as Dates?......Page 320
Handle Long Formulas in the New Excel 2007 Formula Bar......Page 322
Calculate a Percentage of Total......Page 324
Calculate a Running Percentage of Total......Page 326
Raise a Number to a Fraction to Find the Square or Third Root......Page 329
Calculate a Growth Rate......Page 332
Find the Area of a Circle......Page 333
Figure Out Lottery Probability......Page 336
Help Your Kids with Their Math......Page 338
Measure the Accuracy of a Sales Forecast......Page 341
Round Prices to the Next Highest $5......Page 344
Round to the Nearest Nickel with MROUND......Page 345
Why Is This Price Showing $27.85000001 Cents?......Page 347
You Change a Cell in Excel but the Formulas Do Not Calculate......Page 349
Use Parentheses to Control the  Order of Calculations......Page 351
Before Deleting a Cell, Find out if Other Cells Rely on It......Page 353
Navigate to Each Precedent......Page 356
Calculate a Formula in Slow Motion......Page 359
Which Cells Flow into This Cell?......Page 361
Total Minutes That Exceed an Hour......Page 364
Convert Text to Minutes and Seconds......Page 369
Convert Text to Hours, Minutes, and Seconds......Page 371
Convert Times from H:MM to M:SS......Page 373
Display Monthly Dates......Page 378
Group Dates by Month......Page 380
Calculate the Last Day of the Month......Page 382
Create a Timesheet That Can Total over 24 Hours......Page 386
Can Excel Track Negative Time?......Page 389
What Is the Difference Between Now and Today?......Page 391
Calculate Work Days......Page 393
Convert Units......Page 394
Use Match to Find Which Customers Are in an Existing List......Page 395
Use VLOOKUP to Find Which Customers Are in an Existing List......Page 397
Match Customers Using VLOOKUP......Page 399
Watch for Duplicates  When Using VLOOKUP......Page 402
Remove Leading and Trailing Spaces......Page 404
I Don’t Want to Use a Lookup Table to Choose One of Five Choices......Page 406
Match Web Colors with HEX2DEC......Page 408
Switching Columns into Rows Using a Formula......Page 410
Count Records That Match a Criterion......Page 412
Build a Table That Will Count by Criteria......Page 414
Build a Summary Table to Place Employees in Age Brackets......Page 416
Count Records Based on Multiple Conditions......Page 418
Total Revenue from Rows that Match a Criterion......Page 421
Use the Conditional Sum Wizard to Build Conditional Formulas......Page 422
Create a CSE Formula to Build a Super-Formula......Page 429
Learn to Use Boolean Logic Facts to Simplify Logic......Page 431
Replace IF Function with Boolean Logic......Page 434
Test for Two Conditions in a Sum......Page 435
Can the Results of a Formula Be Used in COUNTIF?......Page 439
WRANGLING DATA......Page 441
How to Set up Your Data for Easy Sorting and Subtotals......Page 443
How to Fit a Multiline Heading into One Cell......Page 444
How to Sort Data......Page 448
Sort Days of the Week......Page 451
How to Sort a Report into a Custom Sequence......Page 452
Sort All Red Cells to the Top of a Report......Page 456
Quickly Filter a List to Certain Records......Page 458
Find the Unique Values in a Column......Page 463
Copy Matching Records to a New Worksheet......Page 466
Replace Multiple Filter Criteria with a Single Row of Formulas......Page 468
Add Subtotals to a Data set......Page 471
Use Group & Outline Buttons to Collapse Subtotaled Data......Page 475
Copy Just Totals from Subtotaled Data......Page 477
Enter a Grand Total of Data Manually Subtotaled......Page 480
Why Do Subtotals Come out as Counts?......Page 482
Subtotal Many Columns at Once......Page 485
Add Subtotals Above the Data......Page 489
Add Other Text to the Subtotal Lines......Page 490
Create Subtotals by Product Within Region......Page 494
My Manager Wants the Subtotal Lines in Bold Pink Cambria Font......Page 498
My Manager Wants a Blank Line After Each Subtotal......Page 500
Subtotal One Column and Subaverage Another Column......Page 507
Send Error Reports......Page 513
Help Make Excel 2009 Better......Page 515
How to Do 40 Different What-if Analyses Quickly......Page 516
Remove Blank Rows from a Range......Page 520
Remove Blanks from a Range While Keeping the Original Sequence......Page 522
Add a Blank Row Between Every Row of Your Data Set......Page 525
Excel Is Randomly Parsing Pasted Data......Page 527
Increase a Range by Two Percent......Page 529
Use Find to Find an Asterisk......Page 531
Use an Ampersand in a Header......Page 533
Hide Zeros & Other Custom Number Formatting  Tricks......Page 538
Use Consolidation to Combine Two Lists......Page 542
Find Total Sales by Customer by Combining Duplicates......Page 546
Create a Summary of Four Lists......Page 549
Number Each Record for a Customer, Starting at 1 for a New Customer......Page 552
Add a Group Number to Each Set of Records That Has a Unique Customer Number......Page 554
Deal with Data in Which Each Record Takes Five Physical Rows......Page 556
Add a Customer Number to Each Detail Record......Page 563
Use a Pivot Table to Summarize Detailed Data......Page 566
Your Manager Wants Your Report Changed......Page 571
Why Does This Look Different from Excel 2003?......Page 573
Move or Change Part of a Pivot Table......Page 576
See Detail Behind One Number in a Pivot Table......Page 577
Update Data Behind a Pivot Table......Page 578
Replace Blanks in a Pivot Table with Zeros......Page 579
Add or Remove Fields from an Existing Pivot Table......Page 581
Summarize Pivot Table Data by Three Measures......Page 582
Collapse and Expand Pivot Fields......Page 584
Manually Re-sequence the Order of Data in a Pivot Table......Page 587
Present a Pivot Table in High-to-Low Order by Revenue......Page 589
Limit a Pivot Report to Show Just the Top 12 Customers......Page 592
Explore the New Filters Available in Excel 2007 Pivot Tables......Page 595
Why Aren’t the Cool New Filters Available in My Pivot Table?......Page 597
Why Can’t Co-Workers with Excel 2003 Use My Pivot Table?......Page 598
Limit a Report to Just One Region......Page 599
Create a Report for Every Customer......Page 601
Create a Unique List of Customers with a Pivot Table......Page 603
Create a Report That Shows Count, Min, Max, Average, Etc.......Page 604
Use Multiple Value Fields as a Column Field......Page 606
Compare Four Ways to Show Two Values Fields in a Pivot Table......Page 607
Specify a Number Format for a Pivot Table Field......Page 609
Group Daily Dates by Month in a Pivot Table......Page 612
Group by Week in a Pivot Table......Page 616
Produce an Order Lead-Time Report......Page 618
Report Revenue Many Ways in a Pivot Table......Page 622
Format Pivot Tables with the Gallery......Page 626
None of the 23,233 Built-In Styles Do What My Manager Asks For......Page 629
Select Parts of a Pivot Table......Page 632
Apply Conditional Formatting to a Pivot Table......Page 634
Suppress Totals in a Pivot Table......Page 637
Eliminate Blanks in the Outline Format of a Pivot Table......Page 639
Use a Pivot Table to Compare Two Lists......Page 642
Calculated Fields in a Pivot Table......Page 647
Add a Calculated Item to Group Items in a Pivot Table......Page 650
Instead of Using Calculated Items Group Text Fields......Page 653
Build a Better Top 10 by Using Group Selection......Page 656
Group Ages into Age Ranges......Page 660
Use a Pivot Table When There Is No Numeric Data......Page 662
Why Does the Pivot Table Field List Dialog Keep Disappearing?......Page 664
Control the Shape of Report Filter Fields......Page 667
Create a Pivot Table from Access Data......Page 669
Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?......Page 671
Quickly Create Charts for Any Customer......Page 674
Use Microsoft Query to Get a Unique Set of Records......Page 676
Use a Trusted Location to Prevent Excel’s Constant Warnings......Page 684
Import a Table from a Web Page into Excel......Page 685
Have Web Data Update Automatically When You Open Workbook......Page 689
Have Web Data Update Automatically Every Two Minutes......Page 691
The Spaces in This Web Data Won’t Go Away......Page 692
Use a Built-in Data Entry Form......Page 696
How Do I Clean Up This Data?......Page 698
Transform Black-and-White Spreadsheets to Color by Using a Table......Page 702
Remove Duplicates......Page 704
Protect Cells That Contain Formulas......Page 706
MAKING THINGS LOOK GOOD......Page 709
Change the Look of Your Workbook with Document Themes......Page 711
Add Formatting to Pictures in Excel......Page 714
Create a Chart with One Click......Page 716
Move a Chart from an Embedded Chart to a Chart Sheet......Page 718
Excel Creates a Chart at the Bottom Of My Data; How Can I Move It to the Top?......Page 719
How Can I Nudge a Chart Within the Visible Excel Window?......Page 720
Why Does Excel Add a Legend to a One-Series Chart?......Page 722
Why Do None of the Built-in Chart Layouts Look Good?......Page 723
The Chart Styles Are Cool, But Why So Few Colors?......Page 726
Display an Axis in Millions Using the Layout Tab’s Built-in Menus......Page 728
Display an Axis in Trillions Using the More Options Choice......Page 730
Customize Anything on a Chart by Right-Clicking......Page 732
The Format Dialog Box Offers a New Trick......Page 734
Charts Acting Flaky? It’s Not Just You......Page 736
Minimize Overlap of Pie Labels by Rotating the Chart......Page 737
Add New Data to a Chart......Page 740
Add a Trendline to a Chart......Page 741
Chart Two Series with Differing Orders of Magnitude......Page 744
Use Meaningful Chart Titles......Page 748
Move the Legend to the Left or Top......Page 750
Avoid 3-D Chart Types......Page 752
Save Your Chart Settings as a Template......Page 754
Other Charting Notes......Page 756
For Each Cell in Column A, Have Three Rows in Column B......Page 758
Copy Formatting to a New Range......Page 761
Copy Without Changing Borders......Page 763
Group Columns Instead of Hiding Them......Page 765
Move Columns by Sorting Left to Right......Page 766
Move Columns Using Insert Cut Cells......Page 769
Move Rows or Columns with Shift Drag......Page 770
Change All Red Font Cells to Blue Font......Page 772
Use Cell Styles to Change Formats......Page 774
Leave Helpful Notes with Cell Comments......Page 779
Change the Appearance of Cell Comments......Page 782
Force Certain Comments to Be Always Visible to Provide a Help System to Users of Your Spreadsheet......Page 786
Control How Your Name  Appears in Comments......Page 788
Change the Comment Shape to a Star......Page 790
Add a Pop-up Picture of an Item in a Cell......Page 792
Add a Pop-up Picture to Multiple Cells......Page 794
Draw an Arrow to Visually Illustrate That Two Cells Are Connected......Page 795
Circle a Cell on Your Worksheet......Page 798
Draw Perfect Circles......Page 802
Create Dozens of Lightning Bolts......Page 803
Rotate a Shape......Page 805
Alter the Key Inflection Point in a Shape......Page 806
Make a Logo into a Shape......Page 807
Use the Scribble Tool......Page 811
Add Text to Any Closed Shape......Page 812
Place Cell Contents in a Shape......Page 814
Add Connectors to Join Shapes......Page 817
Draw Business Diagrams with Excel......Page 818
Choose the Right Type of SmartArt......Page 821
Use the Text Pane to Build SmartArt......Page 824
Change a SmartArt Layout......Page 826
Finalize a SmartArt Layout Before Adding Pictures......Page 829
Format SmartArt......Page 830
Switch to the Format Tab to Format Individual Shapes......Page 833
Don’t Convert Another Layout to Create an Organization Chart......Page 835
How Do the Labeled Hierarchy SmartArt Graphics Work?......Page 838
How Does Excel Decide How Many Shapes Per Row?......Page 839
Add New SmartArt Layouts......Page 841
Use Cell Values as the Source for SmartArt Content......Page 842
Change the Background of a Worksheet......Page 844
Add a Printable Background to a Worksheet......Page 847
Remove Hyperlinks Automatically Inserted by Excel......Page 849
Change the Width of All Columns with One Command......Page 851
Control Page Numbering in a Multisheet Workbook......Page 852
Use White Text to Hide Data......Page 854
Hide and Unhide Data......Page 856
Temporarily See a Hidden Column Without Unhiding......Page 858
Build Complex Reports Where Columns in Section 1 Don’t Line Up with Section 2......Page 860
Paste a Live Picture of a Cell......Page 864
Monitor Far-off Cells in Excel 2002 and Later Versions......Page 865
Add a Page Break at Each Change in Customer......Page 868
Hide Error Cells When Printing......Page 872
Organize Your Worksheet Tabs with Color......Page 873
Copy Cell Formatting, Including Column Widths......Page 874
Debug Using a Printed Spreadsheet......Page 878
Copied Formula Has Strange Borders......Page 879
Double Underline a Grand Total......Page 881
Use the Border Tab in Format Cells......Page 882
Fit a Slightly Too-Large Value in a Cell......Page 884
Show Results as Fractions......Page 886
Convert a Table of Numbers to a Visualization......Page 887
Prevent Outliers from Skewing the Visualizations......Page 892
Add Icons to Only the Good Cells......Page 894
Select Every Kid in Lake Wobegon......Page 898
Color All Sales Green for a Day if Total Sales Exceed $1,000......Page 899
Turn Off Wrap Text in Pasted Data......Page 904
Delete All Pictures in Pasted Data......Page 905
Add WordArt to a Worksheet......Page 906
Chart and SmartArt Text Is Automatically WordArt......Page 914
Use MapPoint to Plot Data on a Map......Page 915
Why Does Excel Mark Cells with a Purple Indicator?......Page 918
Add a Dropdown to a Cell......Page 920
Store Lists for Dropdowns on a Hidden Sheet......Page 923
Allow Validation Lists to Automatically Redefine as They Grow......Page 925
Configure Validation to “Ease up”......Page 928
Use Validation to Create Dependent Lists......Page 930
Add a ToolTip to a Cell to Guide the Person Using the Workbook......Page 933
Afterword......Page 935
Index......Page 936