Chris Smitty Smith - Excel 2010 business basics and beyond

Excel 2010 Business Basics and Beyond

by

Chris Smitty Smith

Holy Macro! Books

PO Box 82

Uniontown, OH 44684

Excel 2010 Business Basics & Beyond

2013 by Holy Macro! Books

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: November 2012

Author: Chris Smitty Smith

Layout: Tyler Nash

Indexer: Nellie J Willam

Cover Design: Shannon Mattiza, 6Ft4 Productions

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

Distributed by Independent Publishers Group

ISBN: 9781615470129, print, 978-1-61547-210-9 PDF, 978-1-61547-330-4 ePub, 978-1-61547-110-2 mobi

Library of Congress Control Number: 2012949096

Dedication

To my loving wife and daughter, Cyndi and Campbell for being so patient with me while I worked on this, at the expense of spending time with you.

Acknowledgements

First and foremost Id like to thank Bill Jelen (aka MrExcel). Ive known Bill for close to ten years, and he has encouraged me to write for quite some time, but I never carried through on the threat until now. Bill also got me sparked and subsequently hooked on training, which has been a fantastic opportunity to learn and spread that knowledge around, although I certainly do fill some big shoes, literally! At a seminar in Florida where I filled in for Bill, an attendee bought drinks for my wife and me and said Youre good, but youre no Bill JelenI thought youd be taller. Bill is 6 3+ and Im 5 9 Its an easy distinction. Bill stands tall in a room in both stature and knowledge, whether its at a seminar or in a room full of Microsoft folks talking about what theyre going to build into Excel or fix next. In fact, watching Bills interactions with people over the years is one of the reasons that I decided to go out on my own, and I am much better for it, so thank you Bill.

I will forever be indebted to my mother, Judith, who is widely published in her own field of expertise and who inspired me to delve deeper in my knowledge and experience and share it with people who can benefit.

Next, the fantastic team of folks who support the MrExcel publishing operation, his wife Mary Ellen; Keeper of the Inventory, his sister Barb Jelen; Project Manager, Tracy Syrstad; ScottieP and Tyler Nash (amazing editor). This wouldnt be anywhere without you. There are also the fantastic forum contributors, administrators and moderators at www.mrexcel.com, my interaction with many of whom over the years gave me the inspiration to finally write this.

And I absolutely cant leave out the amazing folks at Microsoft who helped me with terrific feedback and general knowledge/direction, Anneliese Wirth, Dan Battigan, Keyur Patel, Melissa Travers, et. al.

I would also like to thank Paul Gagliardi and Bill Carmen, two of the finest men, bosses and rock climbing partners Ive had the honor of knowing. Thank you for giving me the opportunity to learn and explore the world of Excel in our day-to-day business operations, without that this book simply would never have been possible.

Finally, Id like to thank Christopher Fennell and Zack Barresse, two gentlemen who I have gotten to know very well and consider very fortunate to have as friends. Your collaboration on this was absolutely invaluable and I could not have done it without you.

On the technical side, I definitely need to thank the folks at TechSmith (www.techsmith.com), who make both Camtasia and Snagit software (Snagit was used to capture the images for this book), as well as VMWare (www.vmware.com), which allows you to run multiple versions of Windows/Office on the same PC. Im not a sales rep, but Id highly recommend both if youre interested in doing this kind of thing.

Thank you all!

Foreword

I have never had a formal Excel (or computer) training class*. Like many people, I learned by the seat of my pants as a part of my job requirements. I got my first computer when I was attending TCUs Ranch Management Program, and discovered the spreadsheet via Borlands QuattroPro (pre-Windows). While I could barely make heads or tail of it, my experience on ranches with owners/operators who used paper ledgers to balance the books foretold that computers would play a big role in their future (and I was only thinking about the farming/ranching industry at the time). Unfortunately, few entrenched ranchers felt the same way (I was once told that I needed to be out fixing fence instead of messing with that damned computer-thingy), although now farmers and ranchers are among some of the most adept spreadsheet users out there, which I think is fantastic.

When I first entered the corporate world I started working with Lotus 1-2-3 (DOS based), and remember the day that we moved to the first version of Windows and started using the first Windows version of Excel. What a difference! My first insight into how powerful the spreadsheet could be came when someone told me about the VLOOKUP formula, which literally saved me 10 hours per week! Over the years I came to rely upon Excel for work and learned to do things with it that allowed me do the work of several people in just a few keystrokes. When VBA (Visual Basic for Applications) was introduced I started developing end-user applications and have never looked back. While I embraced everything that Excel can do (and I dont think any one person can ever take advantage of Excels full capabilities just because it is so broad), I found that many people rarely get past the simple basics of using Excel as a glorified paper ledger. Case in point: I once redesigned a corporate expense report for a company, and once it past all of the various committees approval it finally went to the head of Accounts Payable for her sign off. She loved it and wanted to try it out for a few days, but the next day the 10 year Excel veteran called me in a panic and stated that we couldnt distribute it because the report date was protected and she couldnt change it. I explained that it was a formula (TODAY()), and that when she opened the expense report the next day, the date would automatically update itself. She was both amazed and stunned that you could do such a thing.

Especially with the introduction of the Ribbon in Office 2007 it can easily appear that Excel is incredibly complicated and scary. While Excel is an incredibly complicated application, by no means does it need to be scary, and the aim of this book is to expose some of Excels fundamental tools that will ultimately make you more productive. You dont need to be an application developer or computer programmer to be able to use Excel well, but you do need to be willing to explore a bit. If youre an advanced Excel user who understands its more complex functionality then this book isnt for you, but if youre a normal Excel user who wants to learn more about becoming more proficient and expose some of its hidden gems, then youll be pleasantly surprised at how much you can pick up in just a short period of time.