Learn Excel from Mr. Excel: 277 Excel Mysteries Solved

877 Pages·2006·63.729 MB·English

by  Jelen, Bill

277 Excel Mysteries Solved
Bill Jelen
Holy Macro! Books
13386 Judy Ave NW, Uniontown OH 44685
Learn Excel from Mr Excel
© 2005 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 affi liated with Holy Macro! Books
Every effort has been made to make this book as complete and accurate
as possible, but not warranty or fi tness 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 India
First Printing: September 2005
Author: Bill Jelen
Editors: Linda DeLonais and Paragon Prepress Editorial Team
Production: Lisa Davis
Cover Design: Shannon Mattiza, 6Ft4 Productions
Cover Photo: Dallas Wallace, Paramount Photo
Interior Design: Paragon Prepress, Inc.
Published by: Holy Macro! Books, 13386 Judy Ave,
Uniontown OH 44685
Distributed by Independent Publishers Group
ISBN 1-932802-19-3
Library of Congress Control Number: 2005929673
TABLE OF CONTENTS
About the Author ................................................................................xiii
Acknowledgments .................................................................................xv
Dedication ..........................................................................................xvii
Foreword .............................................................................................xix
PART 1: THE EXCEL ENVIRONMENT ........................................1-93
Show Full Menus All the Time .............................................................3
Show Full Toolbars All the Time ..........................................................6
Add a Close Button to the Standard Toolbar .......................................8
Close All Open Workbooks ..................................................................10
Double the Value of the Recently Used File List ...............................11
Remember Workbooks to Open Using a Workspace .........................12
Automatically Move the Cell Pointer in a Direction After
Entering a Number .....................................................................13
How to See Headings as You Scroll Around a Report .......................14
How to See Headings and Row Labels as You Scroll
Around a Report ..........................................................................17
How to Print Titles at the Top of Each Page .....................................19
Print a Letter at the Top of Page 1 and Repeat Headings
at the Top of Each Subsequent Page .........................................22
How to Print Page Numbers at the Bottom of Each Page ................25
How to Make a Wide Report Fit to One Page Wide by
Many Pages Tall ..........................................................................27
Arrange Windows to See Two or More Open Workbooks ..................30
Why Is There a “:2” After My Workbook Name in the Title Bar? .....34
Have Excel Always Open Certain Workbook(s) .................................36
Set up Excel Icons to Open a Specifi c File on Startup ......................38
Use a Macro to Further Customize Startup ......................................42
Control Settings for Every New Workbook and Worksheet ..............44
Open a Copy of a Workbook ................................................................46
Open a Saved File Whose Name You Cannot Recall .........................47
v
vi of xii LEARN EXCEL FROM MR EXCEL
Suppress the Update Links Message .................................................48
Send Excel File as an Attachment ......................................................50
Save Excel Data as a Text File ...........................................................52
Use a Laser Printer to Have Excel Calculate Faster ........................56
Use Excel as a Word Processor ...........................................................56
Spellcheck a Region .............................................................................60
Use Hyperlinks to Create an Opening Menu for Your Workbook ....61
Get Quick Access to Paste Special ......................................................63
Use Shift Key to Reverse Popular Toolbar Icons ...............................64
Create a Menu or a Toolbar of Your Favorite Icons ..........................67
Restore Your Menus After Customizing ............................................72
Quickly Copy a Formula to All Rows of Data ....................................76
Quickly Turn a Range on Its Side ......................................................78
Stop Excel from AutoCorrecting Certain Words ................................81
Use AutoCorrect to Enable a Shortcut ...............................................82
Why Won’t the Track Changes Feature Work in Excel? ...................83
Copy Cells from One Worksheet to Many Worksheets .....................84
Have Excel Talk to You .......................................................................86
Enter Special Symbols ........................................................................88
Find Text Entries ................................................................................90
PART 2: CALCULATING WITH EXCEL ...................................95-354
Copy a Formula That Contains Relative References ........................97
Copy a Formula While Keeping One Reference Fixed ....................100
Create a Multiplication Table ...........................................................102
Calculate a Sales Commission ..........................................................104
Simplify Entry of Dollar Signs in Formulas ....................................106
Learn R1C1 Referencing to Understand Formula Copying ............110
Create Easier-to-Understand Formulas with Named Ranges ........115
Use Named Constants to Store Numbers ........................................119
Build a Formula Using Labels Instead of Cell Addresses ...............120
Use Natural Language Formulas to Refer to the Current Row ......124
Assign a Formula to a Name ............................................................125
Total Without Using a Formula .......................................................133
Count, Average, etc. Without Using a Formula ...............................136
Add Two Columns Without Using Formulas ...................................138
How to Calculate Sales over Quota ..................................................141
How to Join Two Text Columns ........................................................143
How to Sort on One Segment of an Account ID ...............................147
How to Isolate the Center Portion of an Account ID .......................149
How to Isolate Everything Before a Dash in a Column by Using
Functions ...................................................................................150
Table of Contents vii of xii
How to Use Functions to Isolate Everything After a
Dash in a Column .....................................................................153
How to Use Functions to Isolate Everything After the
Second Dash in a Column .........................................................155
How to Separate a Part Number into Three Columns ....................158
Avoid #REF! Errors When Deleting Columns .................................162
Create Random Numbers ..................................................................165
Create Random Numbers to Sequence a Class of Students ............168
Play Dice Games with Excel .............................................................170
Play Bunco with Excel .......................................................................173
Play Craps with Excel .......................................................................178
Create Random Letters .....................................................................180
Convert Numbers to Text ..................................................................183
Calculate a Loan Payment ................................................................186
Calculate Many Scenarios for Loan Payments ................................187
Get Help on Any Function While Entering a Formula ...................189
Discover New Functions Using the ƒx Button .................................192
Three Methods of Entering Formulas ..............................................194
Use AutoSum to Quickly Enter a Total Formula ............................199
AutoSum Doesn’t Always Predict My Data Correctly .....................201
Use AutoSum Button to Enter Averages, Min, Max, and Count ....204
The Count Option of the AutoSum Doesn’t Appear to Work ..........207
Automatically Number a List of Employees ....................................212
Rank Scores .......................................................................................215
Sorting with a Formula .....................................................................217
Rank a List Without Ties ..................................................................221
Add Comments to a Formula ............................................................224
Calculate a Moving Average .............................................................225
Calculate a Trendline Forecast .........................................................226
Build a Model to Predict Sales Based on Multiple Regression .......231
Use F9 in Formula Bar to Test a Formula .......................................235
Quick Calculator ................................................................................236
When Entering a Formula, You Get the Formula Instead of
the Result ..................................................................................237
Calculate a Percentage of Total ........................................................241
Calculate a Running Percentage of Total ........................................244
Use ^ Sign for Exponent ...................................................................247
Raise a Number to a Fraction to Find the Square or Third Root ...247
Calculate a Growth Rate ...................................................................249
Find the Area of a Circle ...................................................................251
Figure out Lottery Probability ..........................................................253
Help Your Kids with Their Math ......................................................254
viii of xii LEARN EXCEL FROM MR EXCEL
Measure the Accuracy of a Sales Forecast .......................................256
Round Prices to Next Highest $5 ......................................................259
Why Is This Price Showing $27.85000001 Cents? ...........................261
You Change a Cell in Excel but the Formulas Do Not Calculate ...263
Use Parentheses to Control Order of Calculations ..........................264
Before Deleting a Cell, Find out if Other Cells Rely on It ..............266
Navigate to Each Precedent ..............................................................270
Formula Auditing ..............................................................................274
How Is This Cell Calculated? ............................................................277
Total Minutes That Exceed an Hour ................................................280
Convert Text to Minutes and Seconds ..............................................287
Convert Text to Hours, Minutes, and Seconds ................................289
Convert Times From H:MM to M:SS ................................................293
Display Dates as Months ..................................................................299
Group Dates by Month ......................................................................302
Calculate Last Day of Month ............................................................307
Create a Timesheet That Can Total over 24 Hours .........................310
Find Which Customers Are in an Existing List ...............................313
Use VLOOKUP to Find Which Customers Are in
an Existing List .........................................................................315
Match Customers Using VLOOKUP ................................................316
Watch for Duplicates When Using VLOOKUP ................................320
Count Records That Match a Criteria ..............................................322
Build a Table That Will Count by Criteria ......................................324
Build a Summary Table to Place Employees in Age Bands ............326
Total Revenue from Rows that Match a Criterion ..........................329
Use Conditional Sum Wizard to Help with SUMIF ........................330
Create a CSE Formula to Build a Super Formula ..........................336
Learn to Use Boolean Logic Facts to Simplify Logic .......................338
Replace IF Function with Boolean Logic ..........................................342
Test for Two Conditions in a Sum ....................................................343
Can the Results of a Formula Be Used in COUNTIF? ....................346
Back into an Answer Using Goal Seek .............................................347
Protect Cells with Formulas .............................................................350
PART 3: WRANGLING DATA ...................................................355-603
How to Set up Your Data for Easy Sorting and Subtotals ..............357
How to Fit a Multiline Heading into One Cell .................................358
How to Sort Data ...............................................................................362
How to Specify More Than Three Columns in a Sort ......................364
How to Sort a Report into a Custom Sequence ................................367
Quickly Filter a List to Certain Records ..........................................370
Table of Contents ix of xii
Find the Unique Values in a Column ...............................................374
Copy Matching Records to a New Worksheet ..................................376
Add Subtotals to a Dataset ...............................................................380
Use Group & Outline Buttons to Collapse Subtotaled Data ...........384
Copy Just Totals from Subtotaled Data ...........................................386
Enter a Grand Total of Data Manually Subtotaled .........................389
Why Do Subtotals Come out as Counts? ..........................................392
Subtotal Many Columns at Once ......................................................394
My Manager Wants Subtotals Above the Data ...............................396
Add Other Text Data to the Automatic Subtotal Lines ..................397
Be Wary ..............................................................................................402
General Protection Faults .................................................................403
Create Subtotals by Product Within Region ....................................405
My Manager Wants the Subtotal Lines in Bold Pink
Tahoma Font .............................................................................411
My Manager Wants a Blank Line After Every Subtotal .................413
Subtotal One Column and Subaverage Another Column ...............429
How to Do 40 Different What-if Analyses Quickly ..........................435
Remove Blanks from a Range ...........................................................439
Remove Blanks from a Range While Keeping the
Original Sequence .....................................................................440
Increase a Range by Two Percent .....................................................443
Use Find and Replace to Find an Asterisk ......................................445
Use a Custom Header of “Profi t & Loss” ..........................................447
Use Consolidation to Combine Two Lists .........................................450
Find Total Sales by Customer by Combining Duplicates ................455
Create a Summary of Four Lists ......................................................458
Number Each Record for a Customer, Starting at One
for a New Customer ..................................................................461
Add a Group Number to Each Set of Records with a
Unique Customer Number .......................................................463
Deal with Data Where Each Record Takes Five Physical Rows .....465
Add a Customer Number to Each Detail Record .............................476
Use a Pivot Table to Summarize Detailed Data ..............................480
Your Manager Wants Your Report Changed ...................................487
Move or Change Part of a Pivot Table ..............................................489
See Detail Behind One Number in a Pivot Table ............................491
Update Data Behind a Pivot Table ...................................................493
Replace Blanks in a Pivot Table with Zeroes ...................................495
Add or Remove Fields from an Existing Pivot Table .......................499
Summarize Pivot Table Data by Three Measures ...........................502
Make Pivot Tables Be Taller than Wide ..........................................505