ABOUT THE AUTHOR |
xv |
|
|
ABOUT THE AUTHOR
In 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 the reports that allegedly came out of the $50K 4th GL reporting tool
from 1989 through 1994 really were actually 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 questions, but secretly researched the answer before replying to their e-mails.
Today, MrExcel Consulting provides custom VBA solutions to hundreds of clients around the English speaking world. The MrExcel.com Web site continues to provide answers to 30,000 questions a year. In fact, with over 900,000 answers archived, it is likely that the answer to nearly any Excel question has already been posted on the Web site’s message board.
Jelen is a regular IMA/IIA speaker circuit. He holds a regular Excel Q&A via his daily Learn Excel from MrExcel podcast. He writes the monthly Excel column for Strategic Finance magazine. There are so many features in Excel, that Jelen has never taught a seminar without learning something new from someone in the audience who reveals some new technique or shortcut. Mostly, though, Jelen learns what Excel annoyances are driving people crazy. The questions in this book are the types of questions Jelen hears over and over.
Jelen is the author of 50 books on Excel. He has produced over 2,000 episodes of the Learn Excel from MrExcel video podcast. He was a regular guest on Call for Help and That Lab with Leo Laporte on TechTV. He is a 10-year Microsoft MVP in Excel. When he isn’t writing, you will find him on a kayaking from his back yard in Merritt Island, Florida.
Jelen lives in Merritt Island, Florida with his wife Mary Ellen and two dogs.
DEDICATION
Dedicated to everyone in one of my Power Excel seminars.
ACKNOWLEDGMENTS
This book and its predecessor have been honed by hundreds of people. Through e-mail, podcasts, and seminars, people have added comments, suggestions, and new tips to make the book better.
Shannon Mattiza at 6’4 Media provided a great cover.
New ideas for this book came from: Vincent Adin, James Afflitto, Paul Allen, Andres Alvear, Patrick Amos, Loren Anderson, Rod Apbelbeck, Neil Appleton, Ilia Asafiev, Ed Ascoli, Chris Ayotte, Doug Bailey, David
Baker, Brad Barker, Cliff Barnett, Denis Barry, Wolfgang Bartel, Marc Barth, Khader Basha, Tim Bene,
Bill Bentley, Joel Berg, Paul van den Berg, A. Besis, Apostolos H. Besis, Matthew Bigelow, Ron Binder, Ram Bista, Ron Black, Jan Boord, Graham Booth, Marilou Borries, Sarah Bourne, Lindsay Boyce, David
Braddy, Eddie Bradley, Alan Brady, Tom Bricheri-Colombi, Tom Brichieri-Colombi, Craig Brody, Thor
Bronsvig, Lisa Brooks, Alan Brown, Derek Brown, James Brown, Patrick Bruer, Michael Bryson, Shawn
Bumgarner, Daniel Burke, Andres Cabello, Travis Carney, Jason Carroll, Price Chadwick, Phil Chamberlain, Mark Chambers, Elden Chandler, Natalie Chapman, Jim Cheap, Ronnie Chio, Jack Chopper, Gopal
Chouhan, Richard Clapp, Mike Clark, Todd Cleveland, Nancy Cody, Morne Combrinck, Steve Comer,
Dave Connors, Adrien Cooper, Melania Covey, Jordan Crawford, Laura Criste, and David Cuenta.
Also from Dion Daniel, Mark A. Davis, Patrick Delange, Rod Dempsey, Daniel Dion, Tim Dolan, Rob
Donaldson, Shannon Duffy, Dawn Duhon, Roy A. Dunn, Diane Durham, Richard E Todd, Adrian Early,
Jack Elgin, Micah Emmerson, Bryan Enos, Pablo Esperon, Roger Evangelista, Nora Fazio, Michael Fleet,
xvi |
POWER EXCEL WITH MR EXCEL |
|
|
Mike Fliss, Michael Fockler, Linda Foster, Michael Franchino, Florian Frankl, Bill Fuhrmann, Robert Gabriel, Terry Gamble, Kerry Gao, Mario Garcia, Margarita George, Marc Gershberg, Eric Gibson, Dietmar
Gieringer, Mike Gel Girvin, Devin Goldberg, Alex Gordon, Cheri Grady, Mark Grint, Ausdell Hadaway, Markus Hahner, Jeff Hale, Odd Inge Halvorsen, Riham Hammoda, Lorin Hanson, Sue Hartman, Peter Harvest, G. Russell Hauf, Karen Havens, Dermot Hayes, Don Heckerman, Bill Hemlick, Graeme Hemp- hrey, Rich Herbert, Med venlig Hilsen, Rob Hincks, Andrew Hinton, Steve Hocking, Andy Hoffmann, Mike Howlett, John Hulls, Paul Humphris, Chuck Irby, Bill Jackman, Jerry Jacobson, Neil Jimack, Kasper de Jonge, Rick Johnson, Stefan Johnsson, Al Johnston, Andrew Jones, Jackilyn Jones, Terry Jones in Spring- field, Szilvia Juhasz in Southern California, Howard Kaplan, Brad Kennedy, Kambiz Keshvari, Kathe
Killian, Paul Kimmel, and Jerry Kohl of Brighton.
More ideas from David Komisar, Ari Kornhauser, Howard Krams, Tanja Kuhn, Ann LaSasso, Jeffrey Latsko, Stacey Lawrence, Rob Leblanc, Johann Manjarrez Ledesma, Paul Leonard, Mark Leskowitz, Lau- ra Lewis, Rene Lie, Bei Lin, Crystal Long, Sérgio Nuno Pedro Lopes, Rick Lubinski, Stuart Luxmore, Patrick C Lynch, Carl MacKinder, Sarker Ashek Mahmud, Roseanne Maish, Romas Malevicius, Micahel Maramzin, Dan Marks, Al Marsella, Joe Marten, Giles Martin, Real Mayer, Dan Mayoh, Sally McBride,
Wendy McCann, Bethany McCrea, Bill McDiarmid, Thomas McGough, James McKay, Wyatt McNabb,
Sergio Melendez, Isabel Mendoza, David Merkel, Dakshesh Mewada, John Meyer, L. Michael, Henning Mikkelsen, Dan Miller, Mark Miller, Richard Miller, Susan Miller-Wells, Greg Montgomery, Mikal Moore, Terry Moorehouse, Ali Mozaffari, Isabel Mrndoza, Kyle Munson, Lucy Myers, Shawn Nelson, Matthew Netzley, Susan Nicholls, John Nichols, Susan Nichols, Dara Nolan, Bill Northrup, Dolores Oddo, Richard
Oldcorn, Jeremy Oosthuizen, and Brent Oswald.
Also from Milind Padhye, Andre Pearson, Michael Pennington, Mario Perez, Dominik Petri, Matthew
Pfluger, E. Phillips, Pete Pierron, Stephen Pike, David Plante, Bill Polen, Dave Poling, Sergiy Polovy,
Nadar Ponnuturai, Brenton Prior, John Pyskaty, Blaine Raddon, Bob Ragland, Jerry Ransom, Fabien
Raynaud, Sandra Renker, Greg Richmond, Russell Richter, Gary Ritter, Bill Robertson, Jamie Rogers, Chris Rohde, Julie Rohmann, Margaret De La Rosa, Vlad De Rosa, Jim S. Rose, Dave Rosenberger, David Rosenthal, Chuck Ross, Hamilton Rozario, Fabian Ruales, Peter Rutter, Marty Ryerson, Tom Saladin , Abdul Salam, Dion Sanchez, Ricardo Santiago, Jack Santos, Lorna A. Saunders, Steve Scaysbrook, Julie Scheels, Lori Schleuter, Randal L. Schwartz, Diane Seals, Robert D Seals, Mark Secord, Ashokan Selliah, Denison Seminar, Bryony Seume, Ewan Shannon, Uma Sharma, Wayne Shelton, Ute Simon, Brett Simp- son, Manfred Simrodt, Loh Seok Siong, Don Smith, Chris Sours, Mark Spratt, Daan Sprunken, Harold Starr, Shlomo Stern, Clay Sullivan, Kevin Sullivan, Keith Sumrall, Seiichi Suzuki, Bill Swearer, Mike Syracuse at the Globetrotters, Brian Taylor, James Tays, David Teague, Martin Thelfer, Sarah Thomas,
Denise Thomson, Bob Tiller, Mark Tittley, Richard Todd, Michael Tucker, Mr. Andrew Tucker, Breck
Tuttle, Bob Umlas, Vaibhav Vaidya, Claude Van Horn, Geoff Vautier, Dinesh Vijaywargiay, Thomas Vo- gel, J. B. Voss, Wiebe van der Waals, Grant Wang, Tim Wang, Kim Wasmundt, Pam Waymack, Rebecca
Weing, Susan Wells, John Wendell, Douglas A. Wesney, Justin White, Neville White, Gary Whiteford, Scott Whyte, Mack Wilk, Shaun Wilkinson, Bill Wood, Chris Wright, Yvonne at the AEAP meeting, Dick Yalmokas, Pat Yong, Kathy Zdarstek, and Deb Zurawski. Many others made a suggestion during a semi- nar, but all I can remember is something like “Derek in Row 6 in Springfield”, “Dan in Philly”, “that nice lady on the right side in Kent, Ohio”, and others. If you own an Excel Master pin and I didn’t list your name, please e-mail me so I can correct the omission.
At Microsoft, the Excel, Power Pivot, Power Map, Power Query, Power BI and Excel Web App teams keeps adding new features to Excel. At the IMA, Kathy Williams and Christopher Dowsett keep my Strategic
Finance articles in shape
My sister Barb Jelen likely packed and shipped the book if you ordered it directly from MrExcel.com.
My family were incredibly accommodating. Thanks to Mary Ellen, Josh & Zeke.
FOREWORD |
|
xvii |
|
|
|
|
FOREWORD |
|
Iam a comic book superhero.
At least, I play one at work. As the mighty man of macro, I have the coolest job in town: playing MrExcel, the smartest guy in the world of spreadsheets.
Well, yes, that is a lot of hype. I am not really MrExcel. In fact, there are so many different ways to do the same thing in Excel that I am frequently shown up by one of my own students. Of course, I then appropriate that tip and use it as my own!
I have incorporated some of these discoveries in a pretty cool 3.5-hour seminar titled Power Excel Tips.
This is amazing stuff—like pivot tables, filters, and automatic subtotals. I love to be in front of a room full of accountants who use Excel 40+ hours a week and get oohs and ahhhs within the first few minutes.
I have to tell you, if you can make a room full of CPAs ooh and ahh, you know that you’ve got some good karma going. At that point, I know it will be a laugh-filled session and a great morning.
One of these classes, which I was presenting at the Greater Akron Chamber, provided the Genesis moment for this book. One of the questions from the audience was about something fairly basic. As I went through the explanation, the room was silent as everyone sat in rapt attention. People were interested in this basic tip because it was something that affected their lives every day. It didn’t involve anything cool. It was just basic Excel stuff. But it was basic Excel stuff that a room full of pretty bright people had never figured out.
Think about how most of us learned Excel. We started a new job where they wanted us to use Excel. They showed us the basics of moving around a spreadsheet and sent us on our way. We were lucky to get 5 minutes of training on the world’s most complex piece of software!
Here is the surprising part of this deal. With only 5 minutes of training, you can use Excel 40 hours a week and be productive. Isn’t that cool? A tiny 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 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. This stuff is still lurking in there, but you would never know to even look for it. My experience tells me that the average Exceller is still doing things the slow way. If you learn a just couple of these new tips, you could save 2 hours per week.
This book talks about 617 of the most common and irritating problems in Excel. You will find each of these 617 items (which you have been stumbling over ever since your “5 minutes of training”) followed by the solution or solutions you need to solve that problem. A lot of these topics stem from questions sent my way in seminars I’ve taught. They may not be the coolest tips in the whole world, but if you master even half of these concepts, you will be smarter than 95% of the other Excellers in the world and will certainly save yourself several hours per week.
Most of the 617 topics in this book presents a problem and its solution. There are plenty of books that go through all of Excel’s menus in a serial fashion. (I’ve written a few). The trouble with those books is that you have no clue what to look up when you are having a problem. No one at my dinner table has ever used the word concatenation, so why would anyone ever think of looking up that word when they want to join a first name in column A with the last name in column B? (see"Join Two Text Columns" on page 86)
Despite its size, this book is a quick read. You can probably skim all 617 topics in a couple of hours to get a basic idea of what is in here. When you face a similar situation, you can find the appropriate topic, apply it to your own problem, and you should be all set.
This book takes a different approach than others I have tried to use. I am MrExcel, but I am hopelessly clueless with Photoshop. Wow! This is an intimidating program. I own a ton of books on Photoshop. There must be a bazillion toolbars in there. Most books I pick up tell me to press the XYZ button on the ABC toolbar. I can’t even begin to figure out where that toolbar is. I hate those books. So, my philosophy here is to explain the heck out of things. If you find a topic in this book in which I tell you to do something without explaining how to do it, please send me an e-mail to yell at me for not being clear.
xviii |
POWER EXCEL WITH MR EXCEL |
|
|
How to Use This Book
Each topic starts with a problem and then provides a strategy for solving the problem. Some topics may offer additional details, alternate strategies, results, gotchas, and other elements, as appropriate to the topic. Each chapter wraps up with a summary and a list of any Excel commands or functions used in the chapter. The screenshots are a mix of 2010, 2013, and 2016, depending on the age of the tip.
Starting with Excel 2007, Microsoft has organized the ribbon into a series of tabs: the Home tab, the Insert tab, the Page Layout tab, and so on. Within each tab, Microsoft has organized icons into various groups.
On the Home tab, for example, there are groups for Clipboard, Font, Alignment, Number, Styles, Cells, and Editing. In this book, if I want you to choose the Delete icon from the Cells group on the Home tab of the ribbon, I say, “Choose Home, Delete.” The other option is to say “Choose Home, Cells, Delete,” but you never actually choose Cells; it is merely a label, so I generally do not mention the group when I write about a command.
Gotcha: When you are working on a chart, Excel adds three new tabs under the Chart Tools heading, as shown in Figure
1. (These tabs do not appear when you are not working with charts.) You might see Excel Help referring to the “Chart Tools | Design tab”. I won’t don’t do this. There can only be only one Design, Layout, or Format tab available at any given time. If the topic is talking about charts, I am going to assume that you are actually working on a chart, and I will refer to the Layout tab instead of the Chart Tools | Layout tab.
Figure 1 This book refers to the Design, Layout, and Format tabs.
Gotcha: Some of the icons on the ribbon tabs have two parts: the main icon and a dropdown. You can see the dividing line between the two parts only when you hover the mouse over the icon. When you need to click the icon itself, this book uses the name of the icon. For example, when you need to select the Paste icon from the Home tab, the text says to choose Home, Paste. When you need to select something from a dropdown under an icon, the text specifies dropdown; for example, when you need to select Paste Values from the Paste dropdown, this book tells you to choose Home, Paste dropdown, Paste Values.
In addition to the tabs across the ribbon, many dialog boxes contain a number of tabs. For example, if you click the Print Titles icon on the Page Layout tab, Excel displays the Page Setup dialog, which has four tabs as shown in Figure 2. If I want you to choose the Header/Footer tab of the dialog, I might write, “Select Page Layout, Print Titles, Header/Footer, Custom Header.” Or, I might say, “From the Page Layout tab of the ribbon, select Print Titles. In the Page Setup dialog, choose the Header/Footer tab and then click Custom Header.”
Figure 2 Select Page Layout, Print Titles, Header/Footer, Custom Header.
ACKNOWLEDGMENTS |
xix |
|
|
Gotcha: In newer dialog boxes, Excel has abandoned tabs across the top and used a left navigation instead. This is particularly true in the Formatting dialog, in the Excel Options dialog, and in the Trust Center dialog. For such dialogs, I sometimes write to “choose Fill from the left navigation of the Format Data Series dialog,” but I also sometimes write “Choose Layout, Format Selection, Fill, No Fill.” In this case, Layout is the ribbon tab, Format Selection is the icon, Fill is the name of the cat- egory along the left navigation panel, and No Fill is the option to choose.
This book uses the term press to refer to keyboard keys (for example, “press Enter,” “press F2”). It uses the term click to refer to buttons and other items you click onscreen (for example, “click OK,” “click the Paste icon”). It uses the term select or choose to refer to selections from the ribbon and option buttons and check boxes within dialogs (for example, “select the Home tab,” “select the No Fill option”).
Figure 3 In this dialog, the tabs move from the top to the left.
Additional Resources
The files used in the production of this book are available for download at www.mrexcel.com/powerex- celfiles17.html. Most topics in the book are covered on the free MrExcel podcast. Visit www.mrexcel.com for details on how to get the podcasts for free.
Quick Start - If You are New to Excel
If you consider yourself new to Excel and don’t know where to start, here are some great topics for you. ●● Keep favorites in the Recent Documents List - page 18
●● Get finished worksheets from Office.com - page 22 ●● See Headings as You Scroll - page 28
●● Zoom with the mouse - page 33
●● Mix formatting within a cell - page 40
●● Use the Fill Handle to enter months - page 40 ●● Fit a report to one page wide - page 52
●● Add a watermark - page 53
●● Excel can read to you - page 60 ●● Entering Formulas - page 71
●● Why dollar signs in formulas - page 76 ●● Total without formulas - page 84
●● Join Two Text Columns - page 86 ●● Clean data with Flash Fill - page 89 ●● Discover new functions - page 108 ●● Excel as a calculator - page 111
●● Loan payments - page 124
●● Calculate a Percentage of Total - page 136 ●● Making decisions with IF - page 148
●● Match records with VLOOKUP - page 159 ●● Dice in Excel - page 185
●● How to avoid blank columns - page 234
●● Add hundreds of subtotals at once - page 249
●● Summarize a data set in 6 clicks - page 294
●● Help your manager visualize numbers - page 466 ●● Show Checkmarks in Excel - page 473
xx |
|
POWER EXCEL WITH MR EXCEL |
|
|
|
●● |
Tame your hyperlinks - page 505 |
|
●● Circle a cell - page 520 |
||
●● |
Plot your Excel data on a map - page 412 |
|
●● |
Use Document Themes - page 501 |
|
●● |
Draw Business Diagrams - page 527 |
|
●● |
Add a dropdown to a cell - page 537 |
|
Quick Start - For Power Excellers
If you think you know Excel really well, I bet you will find some gems in these topics: ●● Open Excel with Ctrl+Alt+X - page 23
●● Never change your margins again - page 25
●● Ctrl+Backspace brings current cell in to view - page 32 ●● Amazing way to paste values - page 34
●● Quickly rearrange columns - page 36 ●● Fill 1 to 100000 - page 42
●● F4 Repeats last command - page 63 ●● See all named ranges - page 83
●● Concatenate several cells - page 88
●● Formula to put worksheet name in a cell - page 98 ●● Intersection for 2-way lookup - page 115
●● Back into an answer - page 125
●● Replace IF with Boolean logic - page 149
●● Find the second largest value - page 186 ●● The real benefit of tables - page 198
●● Trace formulas - page 199
●● See key cells from many worksheets in one place - page 201 ●● Analyze every date between 2 cells - page 218
●● Track negative time - page 223 ●● Filter by selection - page 243
●● Total just the filtered rows - page 245 ●● Copy just the subtotals - page 253
●● Sort the subtotals - page 254 ●● Remove duplicates - page 277
●● Show Yes/No in a pivot table - page 310 ●● AutoFilter a pivot table - page 329
●● Generate reports for every customer without a macro - page 333 ●● Pivot table template - page 317
●● Compare two lists faster - page 362
●● Clean data with Power Query - page 368
●● No more VLOOKUPs with Power Pivot - page 376
●● Asymmetric pivot tables for past actuals and future plan - page 384 ●● 100 million rows with Power Pivot - page 375
●● Add new data to a chart - page 426 ●● Easy combo charts - page 440
●● Hundreds of tiny charts in seconds - page 462 ●● Sorting Left to Right - page 488
●● Pop-up Picture - page 513
●● Paste a live picture of cells - page 514
●● Get SmartArt content from cells - page 534
Quick Start - What’s New
Here are a few amazing newer features in Excel: ●● Forecast Sheets in Excel - page 188
●● Pivot Table auto date rollups - page 322
●● Power Query - page 368 ●● Power BI - page 386
●● Waterfall & Funnel charts - page 446
P A R T 1
THE EXCEL
ENVIRONMENT
1