Jelen Bill. - Excel 2013 In Depth

Bill Jelen
MrExcel

800 East 96th Street,
Indianapolis, Indiana 46240 USA

EXCEL 2013 IN DEPTH

Copyright 2013 by Que Publishing

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

ISBN-13: 978-0-7897-4857-7

ISBN-10: 0-7897-4857-6

Library of Congress Cataloging-in-Publication data is on file.

Printed in the United States of America

First Printing: January 2013

Editor-in-Chief
Greg Wiegand

Executive Editor
Loretta Yates

Development Editor
Charlotte Kughen

Managing Editor
Sandra Schroeder

Senior Project Editor
Tonya Simpson

Copy Editor
Bart Reed

Indexer
Brad Herriman

Proofreader
Dan Knott

Technical Editor
Bob Umlas

Publishing Coordinator
Cindy Teeters

Book Designer
Anne Jones

Compositor
Bumpy Design

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Que Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.

Excel is a registered trademark of Microsoft Corporation.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an as is basis. The author 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.

Bulk Sales

Que Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact

U.S. Corporate and Government Sales
1-800-382-3419

For sales outside the United States, please contact

International Sales

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 36 books about Microsoft Excel, writes the monthly Excel column for Strategic Finance magazine, and his Excel tips appear regularly in the CFO Excel Pro Newsletter and CFO magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenchesworking as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen.

To Max Mahoney

Thanks to all the Excel project managers who were happy to take the time to discuss the how or why behind a feature. At various times, Keyur Patel, Chris Doan, Igor Peev, Nick Chiang, Michael Herman, Chad Rothschiller, Dan Battigan, Melissa MacBeth, Diego Oppenheimer, and Scott Ruble pitched in to help with a particular issue.

Kari Finn, Melissa Travers, Joe Camp, and Anneliese Wirth at the MVP team and Office Online team are true gems.

Other Excel MVPs often offered their take on a potential bug. I could send a group email over a weekend and someone like Kevin Jones, Zack Baresse, Ken Puls, Andy Pope, Mike Alexander, Tom Urtis, Debra Dalgleish, or Ingeborg Hawighorst would usually respond. I particularly loved launching a missive just after the Microsoft crew in Building 36 went home on Friday evening, knowing they would return on Monday morning with 40 or 50 responses to the conversation. Without any Excel project managers to temper the discussion, we would often have designed massive improvements that we would have liked to have implemented in Excel. Someone would show up on Monday and tell us why that could never be done.

Ive learned that when writing a 1,100-page book, there is not much time for anything else. Thanks to Tracy Syrstad, Barb Jelen, and Scott Pierson for keeping MrExcel running while I wrote. As always, thanks to the hundreds of people answering 30,000 Excel questions a year at the MrExcel message board. Thanks to Wei Jiang and Jake Hildebrand for their programming-expertise.

At Pearson, Loretta Yates is an awesome acquisitions editor. If you have ever written a book for any other publisher, you are missing out by not working with Loretta Yates. Bob Umlas is the smartest Excel guy that I know, and I am thrilled to have him as the technical editor for this book. Thanks to Charlotte Kughen, Tonya Simpson, and Bart Reed for getting this book through the editing process.

Zeke Jelen asked me to mention that Zeke is awesome. He kept joking that he was going to randomly type that sentence somewhere in the book. I hope he didntor I hope the editors catch it. Mary Ellen Jelen plays the important role of brandishing the whip. If lunch time came around and I was not well on my way to the 22.3 pages I needed to write that day, she would jokingly, but pointedly, remind me to get to work.

As the reader of this book, you are our most important critic and commentator. We value your opinion and want to know what were doing right, what we could do better, what areas youd like to see us publish in, and any other words of wisdom youre willing to pass our way.

We welcome your comments. You can email or write to let us know what you did or didnt like about this bookas well as what we can do to make our books better.

Please note that we cannot help you with technical problems related to the topic of this book.

When you write, please be sure to include this books title and author as well as your name and email address. We will carefully review your comments and share them with the author and editors who worked on the book.

Email:

Mail: Que Publishing
ATTN: Reader Feedback
800 East 96th Street
Indianapolis, IN 46240 USA

Visit our website and register this book at quepublishing.com/register for convenient access to any updates, downloads, or errata that might be available for this book.

The introduction is always written last. After updating 38 chapters, I reflect back on the things that simply knocked my socks off in this version of Excel.

For people who are new to Excel:

The Flash Fill and Quick Analysis features will be huge timesavers. Read about those features in .

The new JavaScript Excel App Store holds a lot of promise. A few good apps are there right now. Microsoft is currently training more people to write apps, so hopefully by the time you are reading this, there will be many more apps. Read about apps in .