MrExcel LIVe: The 54 Greatest Excel Tips of All Time

By Bill Jelen

The 54 essential tips that all Excel users need to know. MrExcel LIVe provides users with a concise book that can be absorbed in under an hour. Includes a section with keyboard shortcuts. Anyone who uses Excel will be able to turn to any page and pick up tips that will save them hours of work.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Apr 1, 2018
• ISBN: 9781615473625

Book preview

Stories

Dedication

For every IMA and IIA chapter volunteer. Thanks for booking me for your professional development seminars.

About the Author

Bill Jelen is the host of MrExcel.com and the author of 54 books about Microsoft Excel including Excel Gurus Gone Wild and Pivot Table Data Crunching. He has made over 80 guest appearances on TV’s The Lab with Leo / Call for Help with Leo Laporte and was voted guest of the year on the Computer America radio show. He writes the Excel column for Strategic Finance magazine. He has produced over 2,100 episodes of his daily video podcast Learn Excel from MrExcel.

About the Contributors

Rob Collie is the host of PowerPivotPro.com and the author of Power Pivot and Power BI and The Book of Data. He is a former Excel software engineer at Microsoft.

Jordan Goldmeier wrote the jokes on pages 95 & 97. Jordan is a Excel MVP and data scientist. Check out his video podcast at Excel.TV.

Sam Radakovitz is a program manager on the Excel team at Microsoft. He has designed many features while there, including sort and filter, sparklines, and the Ribbon interface. And, more than anyone else on the team, he’s done the best job of bringing the sloths, LOL cats, and cast of Twilight deeper into everyone’s lives.

Katie Sullivan is a program manager on the Word team at Microsoft. GO, WORD!!!!! WOOOOO!!!!!

About the Illustrators

Cartoonist Bob D’Amico creates custom cartoons for business and more. See www.cartoonbob.com for more about his work.

George Berlin is all about delight and wonder! He puts a smile on the world's faces with illustration, animation, and interactive projection art. See more at www.georgeberlin.com.

Emily Jones is a graphic designer and painter in Chicago with a passion for innovative and intuitive design.

Her work can be found at www.emilyjones.com.

Sarah Lucia Jones is a children's book author and illustrator living and working in Cincinnati. She creates her bright and cheery work in watercolor, cut paper, and digital. Her fine art and illustration can be seen at www.SarahLuciaJones.com.

Walter Moore is famous for his ape cartoons. If you need an illustration of the monkey business at your work, search Bing for Walter Moore Apes.

Bobby Rosenstock is a printmaker who specializes in woodcut and letterpress printing. He is owner of the letterpress and design studio in Marietta, Ohio, Just a Jar Design Press. Find his amazing letterpress posters at www.justAjar.com.

Michelle Routt is a freelance multimedia artist and has been drawing her whole life. Her portfolio is available at www.routtstanding.com, where she does everything from illustrations to animation and video game development.

Chad Thomas is an illustrator who showcases his artwork on his website, www.whiterabbitart.com. His colorful and detailed artwork ranges from pet and people portraits to illustrations for children's books.

Foreword

In the course of writing Special Edition Using Excel 2007, I had to research and document every single function in Excel. There were some that were hard to explain (FACTDOUBLE and SQRTPI) and some that were easy to explain but left you scratching your head. For example, who in real life could use the =ROMAN() function? I guess the Vatican could use =ROMAN() in naming future popes. Movie production companies could use =ROMAN() to put the copyright at the end of the movie credits. And the NFL folks could use =ROMAN() to figure out the names of the upcoming Super Bowls. That is not a big audience of Excellers who could find a use for =ROMAN().

I am frequently on the road, doing half-day or all-day Power Excel seminars for groups like the Institute of Managerial Accountants, the Institute of Internal Auditors, or the Hospitality Finance & Tech Professionals. I love these live seminars. The right tip will help someone save an hour a week—50 hours a year. I often interject some humor. I have a variety of spreadsheet quips and gags that get added to the seminar. One that I used many years ago: If you have to present bad financial news, Excel has a function for you: ROMAN! Convert your report to Roman numerals, hide column B, and you can escape the president’s office before he figures out what is going on. It brings laughter every time.

I keep a spreadsheet that lists the books I‘ve authored. A few years ago I realized I would soon be writing book #40 and that the =ROMAN(40) is XL. Because of this joke, the MrExcel XL book was born. It became a staple at my live Power Excel seminars. For this second edition, I am going with Excel 54 (which will be known as Excel LIVe—LIV because it is the ROMAN(54) and the final e to point out that this is the book I use in my LIVE seminars.

The spreadsheet in 2018 has a promising future. Yes, Excel is again facing competition from Google Docs and Tableau. But that competition brings innovation. Look at the amazing new features, like Power Query, Power BI, 3D Maps, and awesome new functions like TEXTJOIN, MAXIFS, and others. It is another golden age for spreadsheet development.

Introduction

This book is broken into two sections:

Part 1 – Bill’s Top 54 Tips—my favorite 54 tips that I cover in my live Power Excel seminars plus 49 bonus tips.

Part 2 – 54 Keyboard Shortcuts—so many of the votes from readers were for keyboard shortcuts that they are all provided together.

Part 3 - Excel Fun

The files used in this book are available for download from mrx.cl/54bookfiles.

You will see a number of shortlinks in this book in the format mrx.cl/short. The idea is that it will be easier for you to type mrx.cl than a long URL. (Thanks, Felix Jelen in Chile, for securing the .cl suffix for me.)

Each of the 54 tips has an accompanying video. Search at mrx.cl/billonyoutube.

Part 1: The Top 54 Tips

#1 Double-Click the Fill Handle to Copy a Formula

You have thousands of rows of data. You’ve added a new formula in the top row of your data set, something like =PROPER(A2& &B2), as shown below. You now need to copy the formula down to all of the rows of your data set.

Many people will grab the Fill Handle and start to drag down. But as you drag down, Excel starts going faster and faster. Starting in Excel 2010, there is a 200-microsecond pause at the last row of data. 200 microseconds is long enough for you to notice the pause but not long enough for you to react and let go of the mouse button. Before you know it, you’ve dragged the Fill Handle way too far.

The solution is to double-click the Fill Handle! Go to exactly the same spot where you start to drag the Fill Handle. The mouse pointer changes to a black plus sign. Double-click.

Excel looks at the surrounding data, finds the last row with data today, and copies the formula down to the last row of the data set.

In the past, empty cells in the column to the left would cause the double-click the Fill Handle trick to stop working just before the empty cell. But as you can see below, names like Madonna, Cher, or Pele will not cause problems. Provided that there is at least a diagonal path (for example, via B76-A77-B78), Excel will find the true bottom of the data set.

In my live Power Excel seminars, this trick always elicits a gasp from half the people in the room. It is my number-one time-saving trick.

Alternatives to Double-Clicking the Fill Handle

This trick is an awesome trick if all you've done to this point is drag the Fill Handle to the bottom of the data set. But there are even faster ways to solve this problem:

Use Tables. If you select one cell in A1:B112 and press Ctrl+T, Excel formats the range as a table. Once you have a table, simply enter the formula in C2. When you press Enter, it is copied to the bottom.

Use a complex but effective keyboard shortcut. This shortcut requires the adjacent column to have no empty cells. While it seems complicated to explain, the people who tell me about this shortcut can do the entire thing in the blink of an eye.

Here are the steps:

1. From your newly entered formula in C2, press the Left Arrow key to move to cell B2.

2. Press Ctrl+Down Arrow to move to the last row with data—in this case, B112.

3. Press the Right Arrow key to return to the bottom of the mostly empty column C.

4. From cell C112, press Ctrl+Shift+Up Arrow. This selects all of the blank cells next to your data, plus the formula in C2.

5. Press Ctrl+D to fill the formula in C2 to all of the blanks in the selection. Ctrl+D is fill Down.

Note: Ctrl+R fills right, which might be useful in other situations.

As an alternative, you can get the same results by pressing Ctrl+C before step 1 and replacing step 5 with pressing Ctrl+V.

Thanks to the following people who suggested this tip: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan and Greg Lambert Lane suggested Ctrl+D. Bill Hazlett, author of Excel for the Math Classroom, pointed out Ctrl+R.

#2 Break Apart Data

You have just seen how to join data, but people often ask about the opposite problem: how to parse data that is all in a single column. Say you wanted to sort the data in the figure below by zip code:

Select the data in A2:A99 and choose Data, Text to Columns. Because some city names, such as Sioux Falls, are two words, you cannot break the data at each occurrence of a space. Instead, you need to use a comma to get the city in column A and the state and zip code in column B, so choose Delimited in step 1 of the wizard and click Next.

In step 2 of the wizard, deselect Tab and select Comma. The preview at the bottom of the dialog shows what your data will look like. Click Next.

Caution: For the rest of the day after you use Text to Columns, Excel will remember the choices you've chosen in step 2 of the Convert Text to Columns Wizard. If you copy data from Notepad and paste to Excel, it will be split at the comma. This is often maddening because most days, the data is not parsed at the comma, but for the rest of today, it will be. To fix it, close and re-open Excel.

Step 3 of the wizard asks you to declare each column as General, Text, or Date. It is fine to leave the columns set as General.

After you‘ve split the state and zip code to column B, select B2:B99 and again choose Data, Text to Columns. This time, since each state is two characters, you can use Fixed Width in step 1 of the wizard. To preserve leading zeros in the zip code, select the second column and choose Text as the data type in step 3 of the wizard.

Tip: A lot of data will work well with Fixed Width, even it doesn‘t look like it lines up. In the next figure, the first three rows are in Calibri font and don‘t appear to be lined up. But if you change the font to Courier New, as in rows 4:7, you can see that the columns are perfectly lined up.

Sometimes, you will find a data set where someone used Alt+Enter to put data on a new line within a cell. You can break out each line to a new column by typing Ctrl+j in the Other box in step 2 of the wizard, as shown below. Why Ctrl+j? I am not sure. There is an IBM user manual from the 1980s that lists Ctrl+j as the shortcut for a linefeed. None of the other shortcuts on that page work in the dialog box, so I have no idea why this one works.

There are three special situations that Text to Columns handles easily:

Dates in YYYYMMDD format can be changed to real dates. In step 3 of the wizard, click the column heading in the dialog, choose Date, then choose YMD from the dropdown.

If you have negative numbers where the minus sign shows up after the number, go to step 3 of the wizard, click the Advanced Button, and choose Trailing Minus for Negative Numbers.

Data copied from a book table of contents will often have dot leaders that extend from the text to the page number as shown below. In step 2 of the wizard, choose Other, type a period, and then select the checkbox for Treat Consecutive Delimiters as One.

#3 Filter by Selection

The filter dropdowns have been in Excel for decades, but there is a much faster way to filter. Normally, you select a cell in your data, choose Data, Filter, open the dropdown menu on a column heading, uncheck Select All, and scroll through a long list of values, trying to find the desired item.

Microsoft Access invented a concept called Filter by Selection. It is simple: find a cell that contains the value you want and click Filter by Selection. The filter dropdowns are turned on, and the data is filtered to the selected value. Nothing could be simpler.

Guess what? The Filter by Selection trick is also built into Excel, but it is hidden and mislabeled.

Here is how you can add this feature to your Quick Access Toolbar: Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar.

There are two large listboxes in the dialog. Above the left listbox, open the dropdown and change from Popular Commands to Commands Not In The Ribbon.

In the left listbox, scroll