Learn Excel from Mr Excel 277 Excel Mysteries Solved

TEAM LinG

TEAM LinG

Bill Jelen

Holy Macro! Books

13386 Judy Ave NW, Uniontown OH 44685

277 Excel Mysteries Solved

TEAM LinG

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

TEAM LinG

v

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

TEAM LinG

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

TEAM LinG

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

TEAM LinG

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

TEAM LinG

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

TEAM LinG

x of xii LEARN EXCEL FROM MR EXCEL

Manually Resequence the Order of Data in a Pivot Table .............. 506

Present a Pivot Table in High-to-Low Order by Revenue ............... 509

Limit a Pivot Report to Show Just the Top 12 Customers .............. 511

Quickly Produce Reports for Each Region ....................................... 514

Create an Ad-Hoc Reporting Tool ..................................................... 516

Create a Unique List of Customers with a Pivot Table ................... 517

Create a Pivot Table with Fewer Clicks ........................................... 519

Create a Report Showing Count, Min, Max, Average, etc. .............. 521

Use Multiple Data Fields as a Column Field ................................... 522

Compare Four Ways to Show Two Data Fields in a Pivot Table .... 523

Group Daily Dates up by Month in a Pivot Table ........................... 526

Group by Week in a Pivot Table ....................................................... 528

Produce an Order Lead-time Report ................................................ 530

Use AutoFormat with Pivot Tables .................................................. 536

Specify a Number Format for a PivotTable Field ............................ 542

Suppress Totals in a Pivot Table ...................................................... 546

Eliminate Blanks in the Outline Format of a Pivot Table .............. 549

Use a Pivot Table to Compare Two Lists ......................................... 554

Calculated Fields in a Pivot Table .................................................... 559

Add a Calculated Item to Group Items in a Pivot Table ................. 562

Quickly Create Charts for Any Region ............................................. 566

Use Query to Get a Unique Set of Records ...................................... 570

Import a Table from a Web Page into Excel .................................... 580

Have Web Data Update Automatically When You

Open Workbook ......................................................................... 584

Have Web Data Update Automatically Every Two Minutes .......... 586

The Spaces in This Web Data Won’t Go Away ................................ 588

Use a Built-in Data Entry Form ....................................................... 592

Transform Black and White Spreadsheets into Color ..................... 594

Your Manager Is Obsessed with Formatting and Cannot Make up

Her Mind ................................................................................... 596

PART 4: MAKING THINGS LOOK GOOD .............................605-836

Create a Chart with One Click ......................................................... 607

Change a Chart from a Chart Sheet to an Embedded Chart .......... 609

Customize Anything on a Chart with Right-click ............................ 610

How to Minimize Overlap of Pie Chart Labels ................................ 618

Add New Data to a Chart .................................................................. 620

Add a Trendline to a Chart ............................................................... 622

Display Profi tability in a Profi t Waterfall Chart ............................. 628

For Each Cell in Column A, Have Three Rows in Column B .......... 641

Copy Formatting to a New Range .................................................... 643

TEAM LinG

Table of Contents xi of xii

Copy Without Changing Borders ...................................................... 646

Leave Helpful Notes with Cell Comments ....................................... 648

Change Appearance of Cell Comments ............................................ 651

Force Certain Comments to be Always Visible to

Provide a Help System to Users of Your Spreadsheet ............ 656

Control Name That Appears in Comments ...................................... 658

Change Shape of Comment to a Star ............................................... 660

Add a Pop-up Picture of an Item in a Cell ....................................... 664

Add a Pop-up Picture to Multiple Cells ............................................ 670

Change the Background of the Worksheet ...................................... 672

Add a Printable Background to Your Spreadsheet .......................... 676

Remove Hyperlinks Automatically Inserted by Excel ..................... 680

Change Width of All Columns in One Command ............................ 681

Control Page Numbering in a Multisheet Workbook ...................... 684

Use White Color for Fonts to Hide Data .......................................... 685

Hide and Unhide Data ...................................................................... 687

Temporarily See a Hidden Column Without Unhiding ................... 689

Build Complex Reports Where Columns in Section 1

Don’t Line up with Section 2 .................................................... 691

Paste a Live Picture of a Cell ............................................................ 697

Monitor Far-off Cells in Excel 2002 and Later Versions ................. 699

Add a Page Break at Each Change in Customer ............................. 701

Use Horizontal Page Breaks Even When You Use

Fit to N Pages Wide .................................................................. 707

Hide Error Cells When Printing ....................................................... 709

Organize Your Worksheet Tabs with Color ..................................... 710

Copy Cell Formatting, Including Column Widths ........................... 711

Why Does Excel Mark All My TRUE Cells with an Indicator? ...... 713

Debug from a Printed Spreadsheet .................................................. 715

Copied Formula Has Strange Borders ............................................. 716

Double Underline a Grand Total ...................................................... 718

Use the Border Tab in the Format Cells Dialog .............................. 719

Fit a Slightly Too-Large Value in a Cell .......................................... 722

Show Results as Fractions ................................................................ 724

Color All Sales Green for a Day if Total Sales > $999 ..................... 727

Color Sales for a Day That Exceeds $999 ........................................ 733

Turn off Wrap Text in Pasted Data .................................................. 737

Delete All Pictures in Pasted Data ................................................... 739

Draw an Arrow to Visually Illustrate That Two

Cells Are Connected .................................................................. 741

Add an AutoShape to Your Worksheet ............................................. 743

Draw Perfect Circles .......................................................................... 746

TEAM LinG

xii of xii LEARN EXCEL FROM MR EXCEL

Draw Perfect Squares ........................................................................ 748

Draw More Than the Four Basic Shapes ......................................... 750

Change an Existing AutoShape ........................................................ 753

Add Text to an AutoShape ................................................................ 756

Use the Toolbar to Change Three Colors of an AutoShape ............. 758

Rotate an AutoShape ......................................................................... 763

Alter the Key Infl ection Point in an AutoShape .............................. 764

Add a Shadow to an AutoShape ....................................................... 767

Add a 3-D Effect to an AutoShape .................................................... 770

Add Connectors to Join Shapes ........................................................ 774

Join Two AutoShapes ........................................................................ 777

Change Properties of Only One AutoShape in a Group .................. 779

When Two AutoShapes Overlap, Control Which Is on Top ............ 782

Make Any Logo into an AutoShape .................................................. 784

Use the Scribble Tool ......................................................................... 786

Place Cell Contents in an AutoShape ............................................... 789

Draw Business Diagrams with Excel ............................................... 793

Draw Org Charts with Excel ............................................................ 801

Add WordArt to a Chart or Worksheet ............................................ 808

Use MapPoint to Plot Data on a Map ............................................... 818

Add a Dropdown to a Cell ................................................................. 821

Store Lists for Dropdowns on a Hidden Sheet ................................. 825

Add a ToolTip to a Cell to Guide the Person Using the

Workbook ................................................................................... 828

Confi gure Validation to “Ease up” .................................................... 830

Use Validation to Create Dependent Lists ...................................... 833

Afterword ........................................................................................... 837

Index ................................................................................................... 839

TEAM LinG

xiii

ABOUT THE AUTHOR

I n 1989, Bill Jelen took a job in a Finance department to maintain a

very expensive reporting tool. When he discovered on day one that

this new tool did not work, he began to learn how to use a $299

spreadsheet program in ways no sane person would ever think to use it.

To the manager who hired him, he now wants to admit that all of the

reports that allegedly came out of the $50K 4th GL reporting tool from

1989 through 1994 really were produced with Lotus 1-2-3 and, later,

Excel.

Thinking he was the smartest spreadsheet guy he knew, Jelen

launched MrExcel.com in 1998 and quickly learned that while he knew

everything about taking 50,000 rows of mainframe data and turning

them into a summary report, there were many people using Excel in

many different ways. To all of the people who mailed in questions back

in 1998 and 1999, Jelen thanks them for honing his spreadsheet skills.

He now admits that he initially knew the answers to none of their ques￾tions, but secretly researched the answer before hitting Reply to their

e-mails.

Today, MrExcel Consulting provides custom VBA solutions to hun￾dreds of clients around the English speaking world. The MrExcel.com

website continues to provide answers to 30,000 questions a year. In fact,

with 135,000 answers archived, it is likely that the answer to nearly

any Excel question has already been posted on the website’s message

board.

Jelen enjoys getting out to teach a Power Excel seminar. There are

so many features in Excel, that Jelen has never taught a seminar with￾out learning something new from someone in the audience who reveals

some new technique or shortcut. Mostly, though, Jelen learns what is

driving Excel users crazy. The questions in this book are the types of

questions that Jelen hears over and over.

TEAM LinG

xiv of xiv LEARN EXCEL FROM MR EXCEL

Jelen is the author of seven books on Excel and OneNote. You can see

him regularly on TechTV Canada. In his spare time, you will fi nd him

promoting literacy causes as president of his local Rotary Club. He is the

founder of the Fresh Writers Books program, encouraging high school

students to pursue literary careers.

He lives outside of Akron, Ohio with his wife Mary Ellen, sons Josh

and Zeke, and two dogs.

TEAM LinG

xv

ACKNOWLEDGMENTS

This book was edited by a lot of people. Linda DeLonais provided the

fi nal technical editing. Along the way, Kat Chamberlin provided

a great deal of feedback that ultimately led to this becoming a

better book. During the spring of 2005, over 6,000 people took part

in a free preview of the book and many readers sent in suggestions.

Thanks to James Affl itto, Andres Alvear, Ron Binder, Alan Brady, Alan

Brown, Phil Chamberlain, Richard Clapp, Dave Connors, Bryan Enos,

Linda Foster, Margarita George, Odd Inge Halvorsen, Sue Hartman,

G. Russell Hauf, Rich Herbert, Steve Hocking, Mike Howlett, David

Komisar, Howard Krams, Ann Lasasso, Carl MacKinder, Al Marsella,

Real Mayer, Wendy McCann, Henning Mikkelsen, Mark Miller, Mark

Miller, E. Phillips, Dave Poling, Bill Robertson, Marty Ryerson, Ashokan

Selliah, Don Smith, Bill Swearer, and Tim Wang for their suggestions.

Suat Ozgur provided countless macros that helped me number or

renumber or caption images. Freeda Roberts typed early sections of the

book. Lisa Davis typed and proofed everything, including the illegible

handwriting written on bumpy airplane fl ights. Shailander Malhotra

and his team did a great job assembling the 277 topics into a meaningful

book. Shannon Mattiza provided a great cover and publicity materials.

I always thank Dan Bricklin and Bob Frankston for inventing the

spreadsheet in the fi rst place. Without them, the computer industry

would not be where it is today.

A good deal of this book was written on airplane fl ights from Cleve￾land to Toronto for appearances on TechTV. Thanks to the entire crew at

Call For Help, including Leo LaPorte, Andy Walker, Amber MacArthur,

Claudia Abate, Katya Diakow, Matt Harris, Steve Antal, Claudia Abate,

Mike Lazazzera, Jenny Celly, Doug Robertson, Gregory Pilsworth, Basil

Coward, Aaren Perrier, Lorraine Quirk, Hayden Mindell, Kelly Colas￾anti, and Malcolm Dunlop.

TEAM LinG