Walsh - YOUR EXCEL SURVIVAL KIT: your guide to surviving and thriving in an excel world

Inside front cover

Intentionally blank

YOUR EXCEL SURVIVAL KIT

A Guide to Surviving and Thriving in an Excel World

by Anne Walsh, MCT

Holy Macro! Books

PO Box 541731

Merritt Island Florida 32954

USA

Your Excel Survival Kit

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

Author: Anne Walsh, MCT

Copy Editor: Kitty Wilson

Layout: Jill Bee

Cover Illustrtation: Peter Strain

Cover Design: Shannon Mattiza, 6'4 Productions

Indexing: Nellie J. Liwam

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

Distributed by: Independent Publishers Group, Chicago, IL

Printed in USA by Hess Print Solutions

ISBN (Print): 978-1-61547-045-7

ISBN (PDF): 978-1-61547-229-1

ISBN (ePub): 978-1-61547-352-6

ISBN (MOBI): 978-1-61547-129-4

LCCN: 2016938257

Table of Contents

About the Author

Anne Walsh has been training users in Excel since 1997. She saw her first spreadsheet in the early 1990s and has been curious and intrigued ever since. She has been an MCT (Microsoft Certified Trainer) and MOS Master Instructor since Office 97. She has delivered thousands of hours of training to individuals and businesses, helping users save thousands of hours with the tips, techniques, and shortcuts she shares in class. She likes to bring wit and humour to her classes; one user evaluation said, I never thought Id put Excel and fun in the same sentence. She lives in the west of Ireland, with her very non-techie husband and very techie son.

Dedication

For Bill MrExcel Jelen. This is my first Excel book, and I am thrilled and delighted to be the first Irish person on his books.

Acknowledgements

Thanks, of course, to Bill MrExcel Jelen, for giving me this opportunity. I also want to thank the believing mirrors who have been with me on this journey: Dolores Cummins, Sharon Gaskin (and all the Success Shapers groups I have been part of), Claire Commins, Brna Clifford, and Karen Gorey.

I also want to thank Kitty Wilson for her work on the book. In the words of my beloved Kate Bush, Its nearly killing me, but what a lovely feeling (from Them Heavy People on The Kick Inside ).

Big thanks to Deborah Taylor of www.booklaunchyourbusiness.com . She was the person who advised and helped in the early stages of this book and helped keep me steady when self-doubt was shrieking in my ears.

Also a big thank you to Brna Clifford, who read the book toward the end and proofread it when I couldnt see it anymore.

I also want to give a mention to my earliest English teachers: Sr. Pius, who lent me books; Mrs. Fennell, who encouraged my imagination and creativity; and Sr. Mary, who taught me accounting and typingboth skills that have been extremely useful to me.

Finally, I offer my deepest thanks to the organisations that have employed me to work with them and for all the great questions I have received from learners over the years.

Introduction

I remember the first time I saw a spreadsheet. It was orange. All of it. (At that time, computer screens showed text in orange or green.) It was a Lotus 1-2-3 sheet, and the various tabs were activated by pressing the / key. I remember feeling both fascinated and tentative as I experimented a bit with it. It was owned by one of the senior managers in the organisation where I was working, so access was rather restricted. That particular spreadsheet added up numbers and did a few other things. Compared to a manual calculator, it was pretty impressive. It was a few more years before I really got to grips with spreadsheets, but the memory of my first stayed with me. (Dare I say the first row is the deepest?)

Excel is now the spreadsheet of choice for most users, and that is what this book is about. When I look at Excel, I feel the same way I feel when I am in the presence of great architecture or a beautiful piece of art or music. Yes, of course it has flaws, but I feel inspired by the fierce love and intelligence that have gone into its creation. It is like a great work of art in that every time I return to it, I find something new, I see something new, I learn something new, and Im not ashamed to say I love it.

I have been teaching Excel for well over 20 years (Ive got my 10,000 hours done!), and I still feel joy when I see peoples faces light up as they grasp what a pivot table can do for them or when they realise that printing is much easier than they thought. After you have read this book, my hope is that you start to see Excels power and begin to really leverage it. I feel so privileged when I help people realize that Excel can be their best office friend.

The reality is that in most workplaceseven those with very sophisticated systemsdata usually ends up in Excel. If you can get to grips with the concepts in this book, you will soon feel comfortable working with Excel.

Who This Book Is For

This book is for you if you are being asked to use Excel more than you ever expected and you are looking for a guide to take you to what is relevant and most frequently used. Its also for you if you have seen that in your organization, its the Excel power users who get promoted, and you want to join them.

This book is also for you if you have done any of these things:

• Spent hours or even days trying to produce an analysis that can be done in minutes with a pivot table
• Cleaned up data by clicking on each individual cell (perhaps thousands of times) and pressing Backspace rather than using Trim() to do them all in one go
• Set up repeating data in separate sheets instead of all together in one and then gone through Hell trying to put it all together at the end of the year
• Called on tape, glue, scissors, and a stapler to get an Excel sheet printed on one page instead of on multiple pages
• Manually pulled in matching entries from separate workbooks instead of using the Vlookup() function
• Found yourself in a job where your boss is talking about pivot tables and Vlookup()s, and you have no idea what he/she is talking aboutbut you know you have to learn fast, and you are terrified

Let me give you a virtual hug if youve been through any of this. And let me reassure you: The madness stops here! It doesnt have to be like this! Excel is on your side. This book is going to get you through. It is, after all, your Excel Survival Kit . One of the key things you need to understand about Excel is that it is susceptible to the garbage in, garbage out idea, so a good part of this book is about making sure the data you will be using is clean, consistent, and Excel friendly. Thats a part of using Excel that I rarely see addressed in Excel books.

What This Book Covers

This book is split into seven chapters:

• Chapter 1, Back to Basics: What Do You Know Already?: This chapter provides a quick intro to the absolute essentials you need to know to grapple with Excel. It shows you how to tackle common tasks like printing, sorting and filtering, and fixing cells. It is set up as a series of numbered steps that you can quickly work through to learn or get reacquainted with Excel basics.