This ebook licensed to H DH DH DH D. Unauthorized reproduction or distribution of this ebook is illegal. Bill Jelen Holy Macro! Books 13386 Judy Ave NW, Uniontown OH 44685 377 Excel Mysteries Solved 55514A47-344A-47BD-93C1-A748F3D04803 Learn Excel 97 through Excel 2007 from MrExcel © 2008 by Bill Jelen All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher. All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. Printed in USA by Malloy First Printing: November 2007 Author: Bill Jelen Editors: Kitty Jarrett Interior Design: Cass White Cover Design: Shannon Mattiza, 6Ft4 Productions Illustrations: Bob DAmico, Millenium Design Group Cover Photo: Dallas Wallace, Paramount Photo Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685 Distributed by Independent Publishers Group ISBN 978-1-932802-27-6 Library of Congress Control Number: 2007922378 55514A47-344A-47BD-93C1-A748F3D04803 v TABLE OF CONTENTS Dedication xvii About the Author xix Acknowledgments xxi Foreword xxiii THE EXCEL ENVIRONMENT 1 Find Icons on the Ribbon 3 Go Wide 4 Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003 6 The Ofce Development Team Likes the Artist Formerly Known as Prince 7 The Paste Icon Is Really Two Icons 8 Use Dialog Launchers to Access the Excel 2003 Dialog 11 Make Your Most-Used Icons Always Visible 13 The Alt Keystrokes Still Work in 2007 (If You Type Them Slowly Enough) 16 Use New Keyboard Shortcuts to Access the Ribbon 18 The Blue Question Mark Is Help 21 All Commands Start at the Top (Except for 2 Controls at the Bottom) 23 What Happened to Tools – Customize? 25 What Happened to Tools – Options? 26 Where Are My Macros? 28 Why Do I Have Only 65,536 Rows? 30 Which File Format Should I Use? 32 Share Files with People Who Are Still Using Excel 97 Through Excel 2003 34 Use Live Preview 36 Get Quick Access to Formatting Options Using the Mini Toolbar 38 MIX FORMATTING IN a Single CELL 39 Copy the Characters from a Cell Instead of Copying an Entire Cell 40 I am a Lobbyist Writing Policy Papers for the White House 42 55514A47-344A-47BD-93C1-A748F3D04803 vi of xxx LEARN EXCEL 97-2007 FROM MR EXCEL My Manager Wants Me to Create a New Expense Report from Scratch 43 Increase the Number of Documents in the Recent Documents List 44 Keep Favorites in the Recent Documents List 46 I’ve Searched Everywhere. Where Is the Save Workspace Command? 47 Use a Workspace to Remember What Workbooks to Open 49 Close All Open Workbooks 50 Automatically Move the Cell Pointer in a Direction After Entering a Number 52 Return to the First Column After Typing the Last Column 53 Enter Data in a Circle (Or Any Other Pattern) 55 How to See Headings as You Scroll Around a Report 57 How to See Headings and Row Labels as You Scroll Around a Report 59 How to Print Titles at the Top of Each Page 61 Print a Letter at the Top of Page 1 and Repeat Headings at the Top of Each Subse- quent Page 64 How to Print Page Numbers at the Bottom of Each Page 66 How to Make a Wide Report Fit to One Page Wide by Many Pages Tall 68 Arrange Windows to See Two or More Open Workbooks 69 Why Is There a “:2” After My Workbook Name in the Title Bar? 73 Have Excel Always Open Certain Workbook(s) 75 Set up Excel Icons to Open a Specic File on Startup 76 Use a Macro to Further Customize Startup 79 Control Settings for Every New Workbook and Worksheet 82 Open a Copy of a Workbook 84 Open a Saved File Whose Name You Cannot Recall 85 Excel 2007’S Obsession with Security Has Destroyed Linked Workbooks 86 I Navigate by Sliding the Scrollbar and Now the Slider Has Become T iny 88 Send an Excel File as an Attachment 90 Save Excel Data as a Text File 92 Use a Laser Printer to Have Excel Calculate Faster 96 Use Excel as a Word Processor 96 Add W ord to Excel 99 Spell check a Region 102 T ranslate with Excel 103 Use Hyperlinks to Create an Opening Menu for a Workbook 105 Get Quick Access to Paste Values 108 Quickly Copy a Formula to All Rows of Data 110 Enter a Series of Months, Days, or More by Using the Fill Handle 112 Have the Fill Handle Fill Your List of Part Numbers 115 Quickly Turn a Range on Its Side 118 Stop Excel from AutoCorrecting Certain Words 120 55514A47-344A-47BD-93C1-A748F3D04803 TAbLE OF CONTENTs vii of xxx Use AutoCorrect to Enable a Shortcut 121 Why Won’t the Track Changes Feature Work in Excel? 123 Copy Cells from One Worksheet to Many Other Worksheets 124 Have Excel Talk to You 126 Enter Special Symbols 128 F ind Text Entries 129 What Do All the Triangles Mean? 133 Why Can’t Excel Find a Number? 135 Get Free Excel Help 140 C A LCULATING WITH EXCEL 143 Copy a Formula That Contains Relative References 145 Copy a Formula While Keeping One Reference Fixed 148 Create a Multiplication Table 150 Calculate a Sales Commission 152 Simplify the Entry of Dollar Signs in Formulas 154 Learn R1C1 Referencing to Understand Formula Copying 159 Create Easier-to-Understand Formulas with Named Ranges 164 Use Named Constants to Store Numbers 167 Assign a Name to a Formula 169 Total Without Using a Formula 177 Add T wo Columns Without Using Formulas 179 How to Calculate Sales in Excess of a Quota 182 How to Join Two Text Columns 184 Join Text with a Date or Currency 187 How to Sort on One Portion of an Account ID 189 How to Isolate the Center Portion of an Account ID 191 How to Isolate Everything Before a Dash in a Column by Using Functions 193 How to Use Functions to Isolate Everything After a Dash in a Column 194 How to Use Functions to Isolate Everything After the Second Dash in a Column 197 How to Separate a Part Number into Three Columns 199 Combine Intermediate Formulas into a Mega-Formula 204 Change Smith, Jane to Jane Smith 208 Add the Worksheet Name as a Title 210 Avoid #REF! Errors When Deleting Columns 212 Create Random Numbers 214 Create Random Numbers to Sequence a Class of Students 216 Play Dice Games with Excel 217 Create Random Letters 220 55514A47-344A-47BD-93C1-A748F3D04803 viii of xxx LEARN EXCEL 97-2007 FROM MR EXCEL Convert Numbers to Text 222 Calculate a Loan Payment 224 Calculate Many Scenarios for Loan Payments 225 Back into an Answer Using Goal Seek 226 Create an Amortization Table 228 Get Help on Any Function While Entering a Formula 230 Discover New Functions Using the fx Button 232 Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Paren- theses 234 T hree Methods of Entering Formulas 236 Start a Formula with = or + 242 Use AutoSum to Quickly Enter a Total Formula 243 AutoSum Doesn’t Always Predict My Data Correctly 245 Use the AutoSum Button to Enter Averages, Min, Max, and Count 249 The Count Option of the AutoSum Dropdown Doesn’t Appear to Work 251 Use AutoSum After Filtering 254 Use Table Functionality to Simplify Copying of Formulas 256 Rename Y our Tables 258 Use Simple References in a Table 259 Automatically Number a List of Employees 262 Rank Scores 264 Sorting with a Formula 265 Rank a List Without Ties 269 Add Comments to a Formula 271 Calculate a Moving Average 272 Calculate a Trendline Forecast 274 Build a Model to Predict Sales Based on Multiple Regression 278 Use F9 in the Formula Bar to Test a Formula 282 Quick Calculator 284 When Entering a Formula, You Get the Formula Instead of the Result 285 Why Don’t Dates Show as Dates? 290 Handle Long Formulas in the New Excel 2007 Formula Bar 292 Calculate a Percentage of Total 294 Calculate a Running Percentage of Total 296 Use the ^ Sign for Exponents 299 Raise a Number to a Fraction to Find the Square or Third Root 299 Calculate a Growth Rate 302 Find the Area of a Circle 303 Figure Out Lottery Probability 306 Help Your Kids with Their Math 308 55514A47-344A-47BD-93C1-A748F3D04803 TAbLE OF CONTENTs ix of xxx Measure the Accuracy of a Sales Forecast 311 Round Prices to the Next Highest $5 314 Round to the Nearest Nickel with MROUND 315 Why Is This Price Showing $27.85000001 Cents? 317 Y ou Change a Cell in Excel but the Formulas Do Not Calculate 319 Use Parentheses to Control the Order of Calculations 321 Before Deleting a Cell, Find out if Other Cells Rely on It 323 Navigate to Each Precedent 326 Calculate a Formula in Slow Motion 329 Which Cells Flow into This Cell? 331 T otal Minutes That Exceed an Hour 334 Convert Text to Minutes and Seconds 339 Convert Text to Hours, Minutes, and Seconds 341 Convert Times from H:MM to M:SS 343 Display Monthly Dates 348 Group Dates by Month 350 Calculate the Last Day of the Month 352 Create a Timesheet That Can Total over 24 Hours 356 Can Excel Track Negative Time? 359 What Is the Difference Between Now and Today? 361 Calculate Work Days 363 Convert Units 364 Use Match to Find Which Customers Are in an Existing List 365 Use VLOOKUP to Find Which Customers Are in an Existing List 367 Match Customers Using VLOOKUP 369 Watch for Duplicates When Using VLOOKUP 372 Remove Leading and Trailing Spaces 374 I Don’t Want to Use a Lookup Table to Choose One of Five Choices 376 Fill a Cell with Repeating Characters 378 Match W eb Colors with HEX2DEC 378 Switching Columns into Rows Using a Formula 380 Count Records That Match a Criterion 382 Build a Table That Will Count by Criteria 384 Build a Summary Table to Place Employees in Age Brackets 386 Count Records Based on Multiple Conditions 388 T otal Revenue from Rows that Match a Criterion 391 Use the Conditional Sum Wizard to Build Conditional Formulas 392 Create a CSE Formula to Build a Super-Formula 399 Learn to Use Boolean Logic Facts to Simplify Logic 401 Replace IF Function with Boolean Logic 404 55514A47-344A-47BD-93C1-A748F3D04803 x of xxx LEARN EXCEL 97-2007 FROM MR EXCEL Test for Two Conditions in a Sum 405 Can the Results of a Formula Be Used in COUNTIF? 409 WRANGLING DATA 411 How to Set up Your Data for Easy Sorting and Subtotals 413 How to Fit a Multiline Heading into One Cell 414 How to Sort Data 418 Sort Days of the Week 421 How to Sort a Report into a Custom Sequence 422 Sort All Red Cells to the Top of a Report 426 Quickly Filter a List to Certain Records 428 Find the Unique Values in a Column 433 Copy Matching Records to a New Worksheet 436 Replace Multiple Filter Criteria with a Single Row of Formulas 438 Add Subtotals to a Data set 441 Use Group & Outline Buttons to Collapse Subtotaled Data 445 Copy Just Totals from Subtotaled Data 447 Enter a Grand Total of Data Manually Subtotaled 450 Why Do Subtotals Come out as Counts? 452 Subtotal Many Columns at Once 455 Add Subtotals Above the Data 459 Add Other Text to the Subtotal Lines 460 Create Subtotals by Product Within Region 464 My Manager Wants the Subtotal Lines in Bold Pink Cambria Font 468 My Manager Wants a Blank Line After Each Subtotal 470 Subtotal One Column and Subaverage Another Column 477 Be Wary 483 Send Error Reports 483 Help Make Excel 2009 Better 485 How to Do 40 Different What-if Analyses Quickly 486 Remove Blank Rows from a Range 490 Remove Blanks from a Range While Keeping the Original Sequence 492 Add a Blank Row Between Every Row of Your Data Set 495 Excel Is Randomly Parsing Pasted Data 497 Increase a Range by Two Percent 499 Use Find to Find an Asterisk 501 Use an Ampersand in a Header 503 Hide Zeros & Other Custom Number Formatting Tricks 508 Use Consolidation to Combine Two Lists 512 Find Total Sales by Customer by Combining Duplicates 516 55514A47-344A-47BD-93C1-A748F3D04803 TAbLE OF CONTENTs xi of xxx Create a Summary of Four Lists 519 Number Each Record for a Customer, Starting at 1 for a New Customer 522 Add a Group Number to Each Set of Records That Has a Unique Customer Num- ber 524 Deal with Data in Which Each Record Takes Five Physical Rows 526 Add a Customer Number to Each Detail Record 533 Use a Pivot Table to Summarize Detailed Data 536 Your Manager Wants Your Report Changed 541 Why Does This Look Different from Excel 2003? 543 Move or Change Part of a Pivot Table 546 See Detail Behind One Number in a Pivot Table 547 Update Data Behind a Pivot Table 548 Replace Blanks in a Pivot Table with Zeros 549 Add or Remove Fields from an Existing Pivot Table 551 Summarize Pivot Table Data by Three Measures 552 Collapse and Expand Pivot Fields 554 Manually Re-sequence the Order of Data in a Pivot Table 557 Present a Pivot Table in High-to-Low Order by Revenue 559 Limit a Pivot Report to Show Just the Top 12 Customers 562 Explore the New Filters Available in Excel 2007 Pivot Tables 565 Why Aren’t the Cool New Filters Available in My Pivot Table? 567 Why Can’t Co-Workers with Excel 2003 Use My Pivot Table? 568 Limit a Report to Just One Region 569 Create an Ad-Hoc Reporting Tool 571 Create a Report for Every Customer 571 Create a Unique List of Customers with a Pivot Table 573 Create a Report That Shows Count, Min, Max, Average, Etc. 574 Use Multiple Value Fields as a Column Field 576 Compare Four Ways to Show Two Values Fields in a Pivot Table 577 Specify a Number Format for a Pivot Table Field 579 Group Daily Dates by Month in a Pivot Table 582 Group by Week in a Pivot Table 586 Produce an Order Lead-Time Report 588 Report Revenue Many Ways in a Pivot Table 592 Format Pivot Tables with the Gallery 596 None of the 23,233 Built-In Styles Do What My Manager Asks For 599 Select Parts of a Pivot Table 602 Apply Conditional Formatting to a Pivot Table 604 Suppress Totals in a Pivot Table 607 Eliminate Blanks in the Outline Format of a Pivot Table 609 55514A47-344A-47BD-93C1-A748F3D04803 [...]... 907 55514A47-344A-47BD-93C1-A748F3D04803 xvi of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL DEDICATION xvii of xxx Dedication 55514A47-344A-47BD-93C1-A748F3D04803 Dedicated to every person who has ever asked a question at one of my Power Excel seminars 55514A47-344A-47BD-93C1-A748F3D04803 xviii of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL ABOUT THE AUTHOR xix of xxx About the Author I n 1989, Bill... Part I  of 926 LEARN EXCEL 97- 2007 FROM MR EXCEL fan of customizing toolbars in Excel 2003, you will love the functionality of this product With both of these add-ins, you can work in the Excel 2003 menu, and then switch over to the Excel 2007 menu when you need to access new features Find links to these utilities as well as any others that become available at www.mrexcel.com /excel2 007.html Summary:... list of any Excel commands or functions used in the chapter Finally, at the end of each topic, you’ll find a section labeled Excel 97- 2003.” If you are using Excel 97, Excel 2000, Excel 2002 from Office XP or Excel 2003, you can easily do the techniques in this book Most of the dialogs look the same, but getting to them may require different commands Each topic’s directions include the Excel 2007 commands... assist with the transition At MrExcel.com, I offer a free tip card that maps each item on the Excel 2003 menu to a tab on the Excel 2007 ribbon If you have a color printer, download the card and print it for free Lin Jie was the first to solve the Excel 2007 ribbon confusion His Classic Excel Menu add-in will add a new menu tab to the Excel 2007 ribbon This tab replicates the Excel 2003 menu The add-in... The Lab with Leo Laporte on TechTV Canada and Google Video He has produced over 500 episodes of the Learn Excel from MrExcel video podcast Jelen lives outside Akron, Ohio with his wife Mary Ellen, sons, Josh and Zeke, and two dogs 55514A47-344A-47BD-93C1-A748F3D04803 xx of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL Foreword T xxi of xxx Acknowledgments This book and its predecessor have been honed by hundreds... bit of training, and you can do 80% of what you need to do in Excel The problem, though, is that there are lots of cool things you never learned about Microsoft and Lotus were locked in a bitter battle for xxiv of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL market share in the mid-1990s In an effort to slay one another, each succeeding version of Excel or Lotus 1-2-3 offered incredibly powerful new features... can make your Excel window as wide as possible to see the full set of ribbon icons Part I LEARN EXCEL 97- 2007 FROM MR EXCEL  of 926 Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003 Problem: The ribbon is taking up a lot of real estate at the top of my screen It distracts me I spend 99% of my Excel time in the grid, so I don’t need to see the ribbon all the time Strategy: You can minimize... seminar xxii of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL Before the book went to print, I formed an advisory board of people to read and review the book This global cross section provided excellent feedback For example, around the world, the symbol that I call a pound sign (#) is also known as a hash sign or a number sign In England, a pound sign is a currency symbol (£) Feedback from advisors indicated... Shape 784 Add Connectors to Join Shapes 787 Draw Business Diagrams with Excel 788 Choose the Right Type of SmartArt 791 Use the Text Pane to Build SmartArt 794 Change a SmartArt Layout 796 55514A47-344A-47BD-93C1-A748F3D04803 xiv of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL Finalize a SmartArt Layout Before Adding Pictures 799 Format SmartArt ... available for Word 2007 and PowerPoint 2007 Figure 4 55514A47-344A-47BD-93C1-A748F3D04803 The Classic Excel Menu add-in brings the File, – Edit, – View, – and Insert menus back to Excel 2007 The second impressive utility is the Toolbar Toggle add-in This add-in offers a lite version similar to the Classic Excel Menu add-in and also offers a full-featured utility that brings back the Excel 2003 menu and . NW, Uniontown OH 44685 377 Excel Mysteries Solved 55514A47-344A-47BD-93C1-A748F3D04803 Learn Excel 97 through Excel 2007 from MrExcel © 2008 by Bill Jelen All. 55514A47-344A-47BD-93C1-A748F3D04803 vi of xxx LEARN EXCEL 97- 2007 FROM MR EXCEL My Manager Wants Me to Create a New Expense Report from Scratch 43 Increase the Number