Power Excel with MrExcel E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 884

Veröffentlichungsjahr: 2024

Beschreibung

Excel 2019 is more than just a spreadsheet tool; it's a powerful platform for solving complex data challenges. In this guide, MrExcel takes you through the essentials of Excel 2019, incorporating the latest features from Office 365. You'll start by mastering the Excel interface before moving on to advanced calculation techniques that streamline your workflow. The guide emphasizes data wrangling, teaching you how to transform raw data into meaningful insights with ease.
Beyond calculations, you'll learn to create visually appealing spreadsheets that communicate your data effectively. MrExcel's approach combines problem-solving strategies with real-world scenarios, ensuring you gain practical skills that apply directly to your work. Reader-sourced tips and solutions to common user pitfalls are integrated throughout the guide, offering a personalized learning experience.
By the end of this book, you'll have a solid understanding of Excel 2019's capabilities, enabling you to work faster, make informed decisions, and present your data professionally. Whether you're looking to enhance your existing skills or learn new techniques, MrExcel's expert guidance will help you master Excel and elevate your data analysis game.

Leseprobe

Inside Front Cover - this page intentionally blank

654 Excel Mysteries Solved

Bill Jelen

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Power Excel with MrExcel - 2019 Edition

© 2019 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 Hess Print Solutions

First Printing: April 2019

Author: Bill Jelen

Cover Design: Shannon Mattiza, 6Ft4 Productions

Interior Illustrations: Ingeborg Hawighorst

Cover Poster: © 2007 Hatch Show Print in Nashville. Used with Permission.

Cover Photo: Dallas Wallace, Paramount Photo

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-060-0 Print, 978-1-61547-245-1 PDF, 978-1-61547-367-0 ePub, ­978-1-61547-145-4 Mobi

Library of Congress Control Number: 2019904027

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 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.

The MrExcel.com Web site continues to provide answers to 40,000 questions a year. In fact, with over 1,000,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 60 books on Excel. He has produced over 2,200 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 my sister Barb Jelen. Thanks for moving to Florida to assist!

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, 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 Hemphrey, 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 Springfield, 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, Laura 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 Simpson, 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 Vogel, 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 seminar, 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

I am 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 with MrExcel. 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 667 of the most common and irritating problems in Excel. You will find each of these 667 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 667 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 92)

Despite its size, this book is a quick read. You can probably skim all 667 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.

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, 2016, and 2019, 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 two new tabs for charts, 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 | Format tab”. I won’t don’t do this. 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 Format tab instead of the Chart Tools | Format tab.

Figure 1 This book refers to the Chart Design or 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.

Gotcha: In some dialog boxes, Excel has abandoned tabs across the top and used a left navigation instead. See the Excel Options dialog shown in Figure 3. I might write File, Options, Data, Edit Default Layout to refer to the Data category in the Excel Options dialog.

Figure 3 In this dialog, the tabs move from the top to the left.

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”).

Additional Resources

The files used in the production of this book are available for download at mrx.cl/pe19files. 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 18Get finished worksheets from Office.com - page 22See Headings as You Scroll - page 28Zoom with the mouse - page 33Mix formatting within a cell - page 42Use the Fill Handle to enter months - page 42Fit a report to one page wide - page 57Add a watermark - page 58Excel can read to you - page 66Entering Formulas - page 75Why dollar signs in formulas - page 80Total without formulas - page 89Join Two Text Columns - page 92Clean data with Flash Fill - page 95Discover new functions - page 115Excel as a calculator - page 119Loan payments - page 132Calculate a Percentage of Total - page 146Making decisions with IF - page 158Match records with VLOOKUP - page 170Dice in Excel - page 199How to avoid blank columns - page 270Add hundreds of subtotals at once - page 287Summarize a data set in 6 clicks - page 335Help your manager visualize numbers - page 518Show Checkmarks in Excel - page 525Tame your hyperlinks - page 561Circle a cell - page 579Plot your Excel data on a map - page 463Use Document Themes - page 556Draw Business Diagrams - page 587Add a dropdown to a cell - page 599

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 23Never change your margins again - page 26Ctrl+Backspace brings current cell in to view - page 33Amazing way to paste values - page 34Quickly rearrange columns - page 37Fill 1 to 100000 - page 44F4 Repeats last command - page 68See all named ranges - page 88Concatenate several cells - page 93Formula to put worksheet name in a cell - page 105Intersection for 2-way lookup - page 123Back into an answer - page 133Replace IF with Boolean logic - page 159Find the second largest value - page 199The real benefit of tables - page 212Trace formulas - page 213See key cells from many worksheets in one place - page 216Analyze every date between 2 cells - page 234Track negative time - page 240Filter by selection - page 280Total just the filtered rows - page 282Copy just the subtotals - page 291Sort the subtotals - page 293Remove duplicates - page 317Show Yes/No in a pivot table - page 351AutoFilter a pivot table - page 370Generate reports for every customer without a macro - page 374Pivot table template - page 358Compare two lists faster - page 404Clean data with Power Query - page 410No more VLOOKUPs with Power Pivot - page 427Asymmetric pivot tables for past actuals and future plan - page 435100 million rows with Power Pivot - page 425Add new data to a chart - page 475Easy combo charts - page 489Hundreds of tiny charts in seconds - page 512Sorting Left to Right - page 541Pop-up Picture - page 571Paste a live picture of cells - page 571Get SmartArt content from cells - page 595

Quick Start - What’s New

Here are a few amazing newer features in Excel:

Geography, Stock, and Exchange Rate Data Types - page 286Dynamic Arrays: Formulas Can Now Spill - page 289Use Column From Examples to Learn Power Query Formulas - page 440Get Ideas from Artificial Intelligence - page 538Use Filled Map Charts - page 550

Part 1 The Excel Environment

Why Does Office 365 Have Better Features?

Problem: I have Geography and Stock Data Types at home, but not at work, What is going on?

Strategy: You have Office 365 at home. Buy agreeing to pay a monthly or annual fee for Office, you are getting frequent updates and new features. If someone buys Office 2019, they get a few new features, but they will never get the new monthly features. The days of the annual Service Pack are gone.

In the good old days (2003, 2007, 2010, 2013), Microsoft would spend three years putting new features into Office and the customers would invest $400 every other release. Now, Microsoft wants you to rent your copy of Office. Pay $10, $12, or $15 a month or $99 a year and you will get monthly updates.

I originally said that I would never rent Office. But then Microsoft started putting must-have features in Office 365 and not in the regular release of Office, so now I can see that renting Office 365 is the only logical choice. With Office 365, you will get to use mobile versions of Excel on an iPad, iPhone or Android device.

Should I Buy Office 2019 or Subscribe to Office 365?

Problem: The Office 365 website is super-confusing. I don’t want to buy the wrong version.

Strategy: Office 365 is the only way to get you the full version of Excel. The $99 a year plan allows you to install Excel on six computers including a Mac. You might have to use Surveys instead of Forms and will be sent to the consumer OneDrive instead of OneDrive for Business. But it will get you all of the Excel that you need.

On the other hand, if you buy Office 2019, you will be missing many features: Geography & Stock data types, Artificial Intelligence with Ideas, Dynamic Arrays such as SORT, FILTER, UNIQUE and SEQUENCE. .

How Can I Use Excel on Dual Monitors?

Problem: Why is it so hard to use Excel on two monitors?

Strategy: This problem is fixed in Excel 2013. Every Excel workbook gets its own window, complete with a ribbon and formula bar. Open two workbooks, drag on to the other monitor and you will have 36 linear inches of Excel.

In Excel 2010, you have to use this hack: You can hold down the Shift key while opening Excel to create a second instance of Excel. Downside: you can not copy formulas from one instance to the other.

How Can I Open The Same Workbook Twice?

Problem: I used to open two copies of the same workbook. I could select cells in copy B, see the total in the status bar, and then type that information in to a different place in copy A. Now that Excel opens every workbook in a new window, I can not open the same workbook twice.

Strategy: Open the first instance of the workbook. Then, force Excel to open in a new instance by holding down the Shift key while opening Excel. In the second instance of Excel, use File, Open to open the workbook again.

Find Icons on the Ribbon

Problem: I know a certain feature exists in Excel, but I can not find it in the Ribbon.

Strategy: Use the new Tell Me feature in Excel 2019. Located to the right of the last tab in the Ribbon, a box with a lightbulb and “Tell Me What You Want To Do” appears. Click in the box and type the name of the feature. A selectable list of commands appears.

Figure 4 These commands are usually hidden in Commands Not in the Ribbon, but are now available.

Gotcha: If you are in Excel 2013 and don’t have Tell Me, open an Excel workbook at Office.Live.Com and use the Tell Me command in Excel Online.

Where is File, Exit?

Problem: What happened to the old Exit command?

Strategy: Although Exit is missing from the File menu in 2013-2019, you can use Alt+F, X to invoke the Exit command. Or, add Exit to the Quick Access Toolbar.

1. The top-left corner of Excel contains a tiny strip with icons for AutoSave, Save, Undo, and Redo. Right-click that strip and choose Customize Quick Access Toolbar.

2. The top left dropdown starts with Popular Commands. Open that dropdown and choose All Commands. You now have an alphabetical list of 2400+ commands.

3. Scroll through the list to find Exit. When you find your command, click the command. Click the Add>> button in the center of the screen to add the command to the Quick Access Toolbar.

Where Are My Macros?

Problem: Did Microsoft abandon the macro facility? Where are the buttons to record a new macro, run a macro, and so on? How do I get to the Visual Basic Editor?

Figure 5 A subset of macro commands are available on the View tab.

Strategy: Most of the macro icons are hidden. Three macro options appear on the extreme right end of the View tab. You use the Macros dropdown to view macros, record a macro, or use relative references while recording a macro.

To access the rest of the macro functionality, you need to enable a hidden Developer ribbon tab. Choose File, Options, Customize Ribbon. Add a checkmark next to Developer. The Developer tab offers macro commands, buttons from the former Forms toolbar and Control Toolbox, and XML settings.

Figure 6 Microsoft disables the Developer tab by default.

Figure 7 If you use macros, enable the Developer tab.

Additional Details:When you are recording a macro, instead of seeing the Stop Recording icon floating above the Excel window, you now see it in the Status Bar, next to Ready.

Figure 8 Once you’ve recorded a macro, the Stop and Record buttons will appear next to Ready.

The same area of the status bar includes a Record Macro button when you are not recording a macro. However, because there is not a Relative References button, you cannot effectively record macros without using either the View tab or the Developer tab of the ribbon.

Collapse the Search Box

Problem: Why is the Search box so large? I never use this.

Strategy: Starting in the spring of 2019, you can collapse it to a small icon. Use File, Options, General

.

Figure 9 Collapse the Search Box to a tiny icon.

Customizing the Ribbon

Problem: I want to customize the ribbon.

Strategy: Ribbon customizations in Excel are weak compared with the customization capabilities in Excel 2003. You might feel like the Pivot Table command belongs on the Data tab rather than on the Insert tab. You can add a new group to the Data tab to hold the pivot table icons. First, look at the ribbon and decide where you want the new group to appear. Perhaps a good location would be between the Sort & Filter group and the Data Tools group.

Figure 10 Decide where you want the new group to appear.

Right-click anywhere on the ribbon and choose Customize the Ribbon.

Figure 11 Right-click the ribbon to access this menu.

The Customize dialog contains two large list boxes. You will first be working with the list box on the right side of the screen. Expand the plus sign next to the Data entry to see the groups on the Data tab. If you want a new group to appear after the Sort & Filter group, click Sort & Filter, and then click the New Group button below the list box.

Figure 12 Choose where the new group should go.

Excel adds a new group with the name of New Group (Custom). Click the Rename button below the list box.

Figure 13 Choose to rename the group

Type a new name in the Rename dialog. Also, choose an icon. This icon will appear only when the Excel window gets small enough to force the group into a dropdown, as shown later in Figure 18.

Figure 14 Type a new name and choose an icon to represent the group.

Note: The 180 icons available are a far cry from the 4096 icons available in Excel 2003. As I pointed out at the beginning of this chapter, toolbar customization took a giant step backward after Excel 2003.

After renaming the new group in the list box on the right side, it is time to turn your attention to the list box on the left side. It starts out showing Popular Commands. Use the dropdown above the left list box to change from Popular Commands to All Commands.

Scroll down to the commands starting with Pivot. You will see a confusing array of commands. Click the first PivotTable icon, and click the Add button in the center of the screen. Click the second PivotChart icon, and then click the Add button. Click PivotTable and PivotChart Wizard, and then click the Add button.

Figure 15 Choose icons to add to the new group.

It is sometimes difficult to figure out which icons you want. There are two icons that say PivotTable. The first icon is simply an icon. The second icon is an icon with a rightward-facing triangle on the right side of the list box. That triangle indicates that the second icon is actually a dropdown that leads to more choices. That second PivotTable dropdown icon is the icon at the bottom half of the Insert tab’s Pivot Table group. It opens to enable you to choose between PivotTable and PivotChart. You might prefer to use that icon instead.

Two PivotChart icons are available. Hover over each icon to see that the first one is the PivotChart icon available on the PivotTable Tools Options tab. You will also see that the second icon is the one on the Insert tab. The first PivotChart icon will be grayed out unless you are in a pivot table. The second PivotChart icon is the one that is used to create a new pivot chart from a data set.

This figure shows the resulting group on the Data tab.

Figure 16 The custom group is added to the ribbon.

If you are wondering why you had to choose an icon back in Figure 14, it is for people who have the Excel window resized to a narrower width. If you make your Excel window narrower, the custom group will eventually get squished down to a single dropdown. Your icon will appear on that dropdown, as shown here.

Figure 17 The icon from Figure 14 shows with a smaller window size.

Note back in Figure 10 that the Sort icon appears as a large icon with a caption and that the AZ and ZA icons appear as small icons without a caption. How can you specify that the pivot table icon should be large and the pivot chart and wizard icons should be small? You can’t. At least not with the Excel interface.

If you want to start writing some XML and VBA, you can gain control over the size and images used in the ribbon. For an excellent book on this daunting task, look for RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls and Teresa Hennig. Or, check out the Ribbon Commander utility described at http://mrx.cl/2dbS4Js.

I find that I spend most of my time on either the Home or the Data tab. If I could combine the left side of the Home tab with the right side of the Data tab, plus pivot tables, I would probably be able to spend all my time on one tab.

This figure shows a new MrExcel tab that reuses groups from other ribbon tabs to build a new tab.

Figure 18 The MrExcel tab is a custom tab with my favorite groups.

The general steps for creating a new ribbon tab are as follows:

1. Right-click the Ribbon and choose Customize the Ribbon.

2. Click New Tab at the bottom right of the dialog.

3. Click Rename and give the tab a name.

4. Use the Up and Down buttons at the right side of the dialog to move the new tab into the proper location.

5. From the left dropdown, choose Main Tabs.

6. In the left dropdown, expand an existing tab and find an existing group that you want to add to your new tab. Click that group and click Add.

7. Repeat step 6 to add additional groups.

8. You can reuse a custom group that you created previously. In the left dropdown, choose Custom Tabs and Groups. You can move the Pivot Table (Custom) tab created earlier in this chapter onto your new ribbon tab.

9. Click OK to finish customizing the ribbon tab.

Go Wide

Problem:My ribbon looks different than my co-workers.

Strategy: Invest in a wide-screen monitor. The Excel experience dramatically improves at a 1440x900 or 1920x1080 resolution.