Bill Jelen - MrExcel LX the Holy Grail of Excel Tips

This is the inside of the front cover. It is blank in the physical book.

But, since you are reading this on an e-reader, how about a joke:

What do you call an eReader virus?

A bookworm!

Holy Macro! Books
PO Box 541731, Merritt Island FL 32954

MrExcel LX The Holy Grail of Excel Tips

2019 by Tickling Keys, Inc.

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 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: March 2019

Author: Bill Jelen

Copy Editor: Kitty Wilson

Tech Editor: Roger Govier

Indexer: Nellie Jay

Screen Reader Captions: Deb Govern

Compositor: Jill Cabot

Cover Design: Alexander Philip

Illustrations: Cartoon Bob DAmico, George Berlin, Walter Moore, Bobby Rosenstock, Chad Thomas

Photography: Sean Carruthers, Mary Ellen Jelen

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

Subtitles: Dianna Deatherage

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-063-1 Print, 978-1-61547-249-9 PDF, 978-1-61547-148-5 Mobi, 978-1-61547-370-0 ePub

Library of Congress Control Number: 2019930608

Table of Contents

Dedication

For Suat & Muge Ozgur

About the Author

Bill Jelen is the host of MrExcel.com and the author of 60 books about Microsoft Excel including Excel Gurus Gone Wild and Excel 2019 Inside Out for Microsoft Press. He has made over 80 guest appearances on TVs 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,300 episodes of his daily video podcast Learn Excel from MrExcel.

About the Contributors

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, hes done the best job of bringing the sloths, LOL cats, and cast of Twilight deeper into everyones lives.

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

About the Illustrators

Cartoonist Bob DAmico 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.

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 print maker who specializes in woodcut and letterpress printing. He is owner of the letterpress and design studio in Marietta, Ohio, Just a Jar Design Press - www.justAjar.com.

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 that 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 week50 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 presidents office before he figures out what is going on. It brings laughter every time.

I keep a spreadsheet that lists the books Ive 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. As new features were introduced in Excel, the book was updated, first as MrExcel LIVe and now as MrExcel LX (which is sort of a mirror image of XL. Thanks to Dianna Deatherage from Pensacola who suggested the Excel Forward and Backwards subtitle.

The spreadsheet in 2019 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, Data Types, Dynamic Array Functions. These come on the heels of Power BI, 3D Maps, and awesome new functions like TEXTJOIN, MAXIFS, and others. It is another golden age for spreadsheet development.

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

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.

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

You have thousands of rows of data. Youve added a new formula in the top row of your data set, something like =PROPER(A2&" "&B2), as shown here. You 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. 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, youve dragged the Fill Handle way too far.