MrExcel XL: The 40 Greatest Excel Tips of All Time

Resources

Dedication

Bill:

For Anne Troy. Thank you for dragging me through Book #1. I never would have gotten to Book #40 if you hadn’t forced me to finish Book #1.

Szilvia:

To my dad, John Juhasz. It was your tireless devotion to my early education in science and math that ultimately shaped my appreciation of the art of good spreadsheet design. Additionally, your many predictions that my being a smart aleck would only result in my own peril have all proven completely accurate.

Acknowledgments

This book was funded through an IndieGogo campaign. I wanted to produce a color book, and I am grateful for the response and preorders.

My sincere thanks to everyone who participated: Access Analytic (Jeff Robson), Christopher Akina, Alumni Ryan & Matthew Albern, Peter Albert data2impact, Areef Ali, Belinda L Allen, Addie Allison, Matt Allington - ExceleratorBI.com.au, Scott St. Amant, Brian Høg Andersen, Amy Andrae, Rod Apfelbeck, Frank Arendt-Theilen, Ron Armstrong - XL GURU Inc., Myles Arnott Clarity Consultancy Services Ltd, Association for Computers & Taxation, ateebit

Julie Babcock-Hyde, Jo Ann Babin, Alnis Bajars, Freddy Barahona, Salvador M Barreiros, Zack Barresse, Jeremy Bartz, Andrew Basey, Suvojit Basu, Christopher Battiston, Dave Baylis, Prem Beejan, Vladimir Belikov, Tarquin Bell, Aaron Bembry, Simon Z Benninga, Oliver Berghaus, Michael V. Bernot, Carsten Bieker, Blockhead Data Consultants (Julie Quick), Max Martin Blum, Marcus Bolton, Caroline Bonner, Lyne Borbe-Navarra, Lan Boughton, Sid Bowman, Eric Boyer, Andrew Brad, Jeff Bradbury , Melanie Breden, Robert Breedlove, Walt Breuninger, Sheri Brewer, Quentin Brooke, Steve A Brooks UK, Derek Brown from Basingstoke (UK), Sam Bruce, Sean R. Buck, Lisa Burkett, Daniel Burleigh, Michael Byrne

Siva C, Tami Calleia, Anita Campbell CEO of Small Business Trends, Gregory R Canda, Brian D Canes, Dave Carlson DynoTech Software, Jose Castaneda, Guy-Francois Castella, Mike Cawley, Tony Chaouch, Petros Chatzipantazis (RibbonCommander.com), John Chis, SJ Choi, Patty Cisneros, Khalif John Clark, Luann Clark, Conn Clissmann, Marion Coblentz, Cynthia Cockburn, John Cockerill, Christopher C. Cole, Arend Collen, Rob Collie, Randy Collier, David Colman, Steve Comer, Jessica Commins @renewabelle, Lee Conner, Ed Corell, Nick Corrie, Jeffrey P. Coulson, CPASelfstudy.com, Crystal (strive4peace), CTROY, Aaron D Culbertson, David Cullipher, Nuno Cunha, Ricky Curioso

Amey Dabholkar (Exploring Excel), Brad Dancer, Kwame Davis, Karen Davison, Bryan J Delfs, Will DeLoach, Vicki Denny, Edwin Deo, Dethmer, Mike Dietterick , Karlheinz Dölger, Mark Domeyer, Heather Drake, Larry Duko, John Durran

Eileen Eckes, Brad Edgar, Snorre Eikeland, Ian Elsum, Elizabeth Elswick, Peter Ennis, Ron Esposito, Melissa Esquibel, Excel Strategies LLC (Blog.ExcelStrategiesLLC.com), The Excel4apps Team, ExcelSmart.com (Dennis Plum), www.exceltricks.de, EZBOE

John Fairlie, Tammy Farmer, Edward Feder, Roger Fisher, Mike Dolan Fliss, D Floyd, Michael Foran, Mark Ford, Jerid C. Fortney, Brian Fox, Derek Fraley (Row 6 in Springfield), Nick Freeman, Thomas Fries, Fuchsi, Freddy, Nancy and Elena Fuentes, Dan Fylstra

David Gainer, Mario Garcia, Yesenia Garcia, Alexa Gardner, Tim and Debbie Garlak, Lianna Gerrish, Tony Giannotti, Wim Gielis, Dawn Gilbert, Stephen Gilmer, Mike excelisfun Girvin, Judy A. Glaser, Anand Goel, Jordan Goldmeier, Hernán González, Susan Goodreau, Alex Gordon, Ryan Gottesman, Roger Govier, Rick Grantham, Joni Graves AICP, Bob Greenblatt, Olen L. Greer, Donovan Grimett, Frédéric Le Guen, James Gunther

David Haggarty, Patty Hahn, Paul Hannelly, Sabine Hanschitz, Arly Hansen, David Hansen, Jason Hanson, Matt Hard, Roy Harrill, Matt Harris, Be’eri Gurtler Har-Tuv, Hartville MarketPlace and Flea Market, Peter Harvest Harvest Consulting (Melbourne Australia), Mark Hauser, Steven Havelock, Alex Havermans (Belgium), Bill Hazlett, Michael D Hecht, Heiko Heimrath, Jean-Yves Hemlin, John Henning, Jonathan Hepplewhite, George Hepworth, Jon von der Heyden, Jon Higbed, Greg Hill, Grace P. Hinrichs, Don and Patty Hitt, Carl Hjortsjö, Chad Hobson, Helen Hoefele, Mr D C Hoey, Brad Hoffer www.xlyourfinances.com, Robert Holleran, Carl R Hooker, Jeneta Hot, Timb Hours, Simon Hughes (Morpeth UK), John Hughes, Paul Humphris, Jacqui Hunter, Gary Hutson

Nazeerul H M Ihthisham, INDZARA, Christopher Ivester

Bruce J, J.Ty., Kathy Jacobs, Dennis P Jancsy, Johanna Jaramillo, Jaumier , Robert F Jelen, Robert Karl Jelen, Zeke Jelen, JEVS Human Services, JH Training Services, Excel by Joe, Torstein S. Johnsen (Norway), James N Johnson, Al Johnston, Barbara Johnston (How To Excel At Excel.Com), Jackilyn Jones

Masaru Kaji, Gary Kane, Ryan Kane, Wayne Kao, Greg Karl, Michael Karpfen, Ryan Awesome McAwesomepants Kauffman, Gordon A. Kendall, Dr Frank Kendralla, Kathy Key, Jeannette Kight, Don Knowles, Sergey Kochergan, Dawn Kosmakos, Arne Kotowski, Martin ‘Ulf’ Kreitmair, Martin Kreitmair, Stephen J Krisel, Olga Kryuchkova

LaFrenier Sons Septic, Lake Local School District, Esko Lakso, Dan Langer, Richard B Lanza, David F. Lawson, S Leavitt, DeLisa Lee, G Lee, Kevin Lehrbass www.youtube.com/user/MySpreadsheetLab, The Leitz/Stutzman Families, Todd A Lesko, Thomas Leung, Mindy Lewis, Geoff Lilley, Daryl Lim, Ria Lee Shue Ling, Craig Little, Anthony J. LoBello Jr., Jeff Long, Celine Loos, Ronald J. Lopez Sr., Mourad Louha, Martin Lucas, Michelle Lucchese, Ruth Ludeman, Lugh Information Services Consulting, John A Luff, Mark Luhdorff, John T Lutz, Mike Lygas, Audrey Lynn

Jack Madden, Karen Madigan, Muhammed Ashfaq Ashraf Makda, Ana V Maldonado-Molina, Dawnita Malevris, Christin Malmquist, Jean-Philippe Maltais, Mike Mann, Peter Mantell, Alex De Marco, Thiago Cerqueira Marcos, Keith Marshall, Bob Martinez, Isaac Matarasso, Emily Mathews, James Mathews, Robert Matthews, Shannon Mattiza, Stacey Matula, Allen Matz, Lynda Maynard, Dan Mayoh, Stephanie (Sam) McAtee, Patricia McCarthy, Bob McClellan, Steve McCready, Diana McGunigale, McGunigale/Ringling/Liles, The McGunigales, Wyatt McNabb, Dan Means, Claire Medland, Micah Melnyk, Carlo Melone, Beth Melton, Leo Menard, Mary Middleton, Robert P Mika, MikeAsHimself, Jade Miller, Saw Kyaw Htike Moe, James E. Moede CPA, Bob Moffatt, Jamil Mohammad, Chris moomoo Moore, Ryan Moore, Jeffrey Morgan, Steve and Linda Morosko, Bradford Myers, myexcelonline.com

Hiroshi Nakanishi, German Nande, Rafael I. Farfán Navarro, Kevin Nee, Justin Newsom, nidzela, Kim Nir, Robert Nix, Anthony Nixon, Augustin Nizery, Martin Nolan SurtenExcel.com, Kevin Norris, Simon Nuss

Wendall F Oakes, Mark O’Brien, officetrain.co.uk, Richard Oldcorn, Sam O’Leary, Tim O’Mara, Aizhan Omarbekova, Nate Ondricek, Jacqueline L Oneil, Victor Ooi, Michael Ortenberg

Donald Parish, Catherine Parkinson, Keyur Rahul Patel, JoAnn Paules, Susan Payton Egg Marketing & Communications, Michele M. Pearce, Lynette Pebernat, Avidan Pell, Michael Pennington, Raul Perez, Ian Perry, Rob Phillips, Keith Pieper, Mr Joseph Pierre, Alex Pilar, Kimberlie Pilar, Tony Pitakpaivan, Peter Polakovic, Shlomi Postelnik, Sherry Prindle, ProfitSpreadsheet.com, Profology.com, Ken Puls, Julie Quick (Blockhead Data Consultants)

Sam Radakovitz, John Raffin, Mohammed Altaf Ur Rahman India, Peter Raiff, Shishir Ranjan, Jake C. Rau. Mary Raya, Chris Raymundo, Palakodeti Bangaru Rayudu, Ahsan Raza S., Nigel Reardon, Bruce Reynolds, Micheal Reynolds, Rhodri., Cecelia Rieb, Will Riley, Darwin & Darla Ringling, David Ringstrom CPA, Mark Risner, Julie Rohmann, Mark Rosenkrantz, Jürgen Rösing, Richard Rost, Mike Rottenborn, Tony Rozwadowski, Rub5ter, Rylewski

Aiman Sadeq, The Salem Historical Society Salem Ohio, Edward Salinas, Ion Saliu, John W Salmons, Jon Sanderson, Sandy Sandmeyer, Victor E. Scelba II, Gail Scheuer, Morten Schjoldager, Tom Scullion, Robert D. Seals, Michael Seeley, Francesca Seidita, Serving Brevard Realty, Stephen Shay, Thomas Sherrouse, Joseph Shivers, Madeline Patton Shivers, David N Short, Linda Shrewsbury, Joanne Siegla, Sergio.Silva77, Letty Silva, Ian Silver, Ute Simon, Jim Simons, Denise Simpson, David Sisson, Susan Slinkman, Sean Smith, Chris Smitty Smith, Ambjörn Snickars, Jerry Solares, Oz du Soleil, Humberto Soto, Andrew Spain, Jon Spain, Mark Spencer, Stan, Cindy Stark-Jones, Graham Stent, Edward Stephen, Ken Stern, Tsuneaki Sugawa, Kathryn Sullivan , Kevin J Sullivan, Sam Suppe, SurtenExcel.com, Peter Susen, Ben Sutton (Brookson LTD), Erik Svensen, Rick Symons, Tracy Syrstad

Eros Tagliabue, John Takacs, Ryo Takagi, Joe Takher-Smith, James Tallman, David Tan, Michael Tarzia, Dean Taunton, Angelina Teneva, Andreas Thehos, Robert Thorne, thrivebookstore.com, Tom Thususka of Brampton ON, Raymond K Gota Toudji, Shannon Travise (Creative Correspondence), Mynda Treacy, Anne Troy, TWeegels

Mike Ulrich, Bob Umlas, Tom Urtis, Larry Vance, Jose Varas, Siva Prasad Vempali, Stephen Venables, Dr. Gerard M. Verschuuren, Khushnood Viccaji, Darrell Wade, Clay B. Wagner, Ian Wainwright, Anne Walsh MCT, Mark Walter CPA, Rickard Warnelid, Alex Waterton MCT, Trevor Weaver UK, Adam Weaver, Russ Webster, Stephan van Well, Martha K Wendel, Jonathan Wernick, Michele Whaley, Steven White, Roy Wilkinson, Jenna Williams, Jim Williams, Rich Williams, Kitty Wilson, Ryan Wilson, Jeff DrSynthetic Wilson, Alan J. Wind, Anneliese Wirth, Patrick Wirz, John C. Wisse, Jon Wittwer, Bob Wright, Linar Yafarov, Melody Yang, www.yourgoodk9.com, Steve Yun, Nathan Zelany, D. Zureski

About the Authors

Bill Jelen is the host of MrExcel.com and the author of 40 books about Microsoft Excel including Excel Gurus Gone Wild, Pivot Table Data Crunching, and Power Pivot Alchemy. 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 1900 episodes of his daily video podcast Learn Excel from MrExcel.

Los Angeles based, Ohio-born consultant Szilvia Juhasz, aka XSzil is a true story of spreadsheet-love at first sight. Szilvia first cut her teeth on Excel in Budapest, Hungary, as a staff auditor at a consulting firm where she got hooked on pivot tables. Now she runs her own consulting business, helping clients leverage and integrate Excel with other systems and designing customized Excel training programs and workshops for their employees. She originally stumbled upon her first big claim to Excel-fame in Ohio, where she met MrExcel at one of his seminars and stumped him on a pivot table trick. Off-spreadsheet, Szilvia enjoys wise cracking on Twitter and creating, singing, and recording Excel-inspired parody music.

About the Contributors

Peter Albert runs the Excel-for-Consultants blog and operates data2impact - a boutique specialized in building Excel, Access, SQL Server and data centric web solutions for consultants and professionals.

Mike Alexander is the host of DataPigTechnologies.com and the author of more than a dozen books on Excel and Access.

Zack Barresse is a Microsoft MVP in Excel and Excel ninja. He is the coauthor of Excel Tables. He provides Excel consulting and training.

Rob Collie is the host of PowerPivotPro.com and the author of DAX Formulas for Power Pivot and Power Pivot Alchemy. He is a former software engineer on Excel at Microsoft.

Debra Dalgleish is an Excel MVP and runs the popular Contextures web site. Her Pivot Power add-in and Excel Theatre blog are two of our favorite things about Excel.

Jordan Goldmeier is a producer and co-host of Excel.TV, author of Advanced Excel Essentials, blogger at OptionExplicitVBA.com, and owner of CambiaFactor.com. He has been an Excel MVP since 2013.

Andrew Spain is the owner of Spain Enterprise located in Huntsville, Alabama. He provides consulting services to companies and trains individuals and groups in Excel and the Office suite.

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 the cast of twilight deeper into everyone’s lives.

Chris Smith is an Excel MVP, author of Excel 2010 Business Basics and Beyond, Excel Trainer and Consultant.

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

Mynda Treacy is an Excel MVP and creator of the amazing Excel Dashboarding course. Find her at MyOnlineTrainingHub.com

Excel4apps is a leading provider of real-time, Excel-based reporting solutions for 20,000+ Oracle and SAP users worldwide.

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

Chelsea Besse has been drawing all her life. She graduated with her bachelors in art and loves all things creative. She can be found at www.chelseabesse.com

Chank Diesel is an alphabetician & builder of fonts. He designed the MrExcel.com font years ago. Find him at www.Chank.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.

Em DeMarco is seamstress-carpenter-baker turned journalist. She broke a national story about shale gas wastewater and has investigated amusement park safety. These days she is doing comics journalism, which merges nonfiction storytelling with illustration. View her work at www.emdemarco.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.

By day, Libby Norcross is a space science educator; by night, she is the quirky artist & founder of Libbydoodle. She loves to spread inspiration and encouragement with her hand-doodled artwork. You can find more at www.libbydoodle.com.

Bobby Rosenstock is a printmaker who specializes in woodcut & letterpress printing. He is owner of the letterpress & 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 at www.whiterabbitart.com. His colorful and detailed artwork ranges anywhere from pet and people portraits to illustrating children's books.

Foreword

Microsoft Excel debuted 30 years ago in 1985. It wasn't the first spreadsheet program – VisiCalc had debuted in 1979. It certainly wasn't the most popular spreadsheet program – Lotus 1-2-3 had 89% market share by the late 1980s. But Microsoft had one thing in its favor – it was based on a graphical interface from the beginning. There was never a DOS-style text-based version of Excel.

If you had a long view in 1985, going with a GUI from the beginning was brilliant. But it had to be painful in 1985, originally on the Mac and then in early versions of Windows. The computing power was not fast enough to repaint the screen. In many cases, Lotus 1-2-3 would calculate ten times faster than Excel.

It wasn’t until the mid-1990s, when computing power caught up, that Excel became a viable contender. 1993 to 1997 were some golden years in spreadsheet development. Every new version of Lotus 1-2-3 and Excel introduced some killer features. Pivot tables, subtotals, new functions and VBA macros all debuted in Excel during this period.

As Excel battled for market share, Microsoft had to make sure that they were 100% compatible with Lotus 1-2-3. That meant making the same mistakes as Lotus (there was no February 29 in 1900) and replicating all of the less-than-useful functions used in Lotus.

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 or the Institute of Internal Auditors. 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: 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 listing the books I've authored. It was a few years ago that I realized I would soon be writing book #40, and that the =ROMAN(40) is XL.

Illustration: Chank Diesel

I figured this would be a milestone – and thus this book was born. My 40 favorite Excel tips. Some good Excel stories. Szilvia Juhasz added 30 more Excel tips to coincide with the 30th birthday of Excel. There are some Excel jokes from Jordan Goldmeier, some tweets collected by Debra Dalgleish, and an awesome collection of Excel-themed cocktails from Szilvia Juhasz.

The spreadsheet in 2015 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 Pivot, the feature formerly known as Power Query, and 3D Maps that have debuted since 2010. It is another golden age for spreadsheet development.

Introduction

This book is broken into these sections.

Part 1 – Bill’s Top 40 Tips - my favorite 40 tips that I cover in my Power Excel seminars.

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

Part 3 – 30 More Tips for the 30th Anniversary of Excel. Szilvia Juhasz adds her favorite tips and tips from readers.

Part 4 – Excel Fun - Excel cocktails, jokes, tweets, and stories.

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

You will see a number of shortlinks in this book in the format of 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.)

Bill will do videos to go with each of his 40 tips. Search at http://mrx.cl/billonyoutube.

Illustration: Em DeMarco

Part 1 - Bill’s Top 40 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 this =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 would select one cell in A1:B112 and press Ctrl+T, Excel will format the range as a table. Once you have a table, simply enter the formula in C2. When you press Enter, it will be 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. (Note that 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 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