Jelen - MrExcel LIVe

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

MrExcel LIVe The 54 Greatest Excel Tips of All Time

2018 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: August 2017

Authors: Bill Jelen

Copy Editor: Kitty Wilson

Tech Editor: Bob Umlas

Indexer: Nellie Jay

Layout Consulting: Anne Marie Concepcion with David Blattner and Dawn Kosmakos

Cover Design: Shannon Mattiza, 6Ft4 Productions

Illustrations: Scott Adams, Cartoon Bob DAmico, George Berlin, Chelsea Besse, Chank Diesel, Sarah Lucia Jones, Walter Moore, Libby Norcross, Chad Thomas, Michelle Routt

Photography: Sean Carruthers, Karen Eonta, Freddy Fuentes, Mary Ellen Jelen, Kenny Spain,

Photography Post Processing: Michael Seeley & Jared Haworth

Production Assistance: Mary Ellen Jelen

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

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-056-3 Print, 978-1-61547-239-0 PDF, 978-1-61547-127-0 Mobi, 978-1-61547-362-5 ePub

Library of Congress Control Number: 2017912904

Table of Contents

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

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 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. For this second edition, I am going with Excel 54 (which will be known as Excel LIVeLIV 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 Bills Top 54 Tipsmy favorite 54 tips that I cover in my live Power Excel seminars plus 49 bonus tips.