Bill Jelen - Power Excel with MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis in Excel 2010–2013

567 Excel Mysteries Solved

Bill Jelen

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Power Excel with MrExcel

2015 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: September 2014

Author: Bill Jelen

Cover Design: Shannon Mattiza, 6Ft4 Productions

Interior Illustrations: Bob DAmico, Millennium Design Group

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

Cover Photo: Dallas Wallace, Paramount Photo

Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-038-9 Print, 978-1-61547-225-3 PDF, 978-1-61547-348-9 ePub, 978-1-61547-125-6 Mobi

Library of Congress Control Number: 2014915530

Table of Contents

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.

Today, MrExcel Consulting provides custom VBA solutions to hundreds of clients around the English speaking world. The MrExcel.com Web site continues to provide answers to 30,000 questions a year. In fact, with over 750,000 answers archived, it is likely that the answer to nearly any Excel question has already been posted on the Web sites 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 44 books on Excel. He has produced over 1,800 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 isnt writing, you will find him on a kayak.

Jelen lives outside Akron, Ohio with his wife Mary Ellen and two dogs.

Dedication

D edicated to Robert F. Jelen.

Acknowledgments

T his 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 64 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, Dawh 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, Srgio 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.