Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun

558 Pages·2011·17.26 MB·English

by  Mike Girvin

Table of Contents
Foreword
Introduction
How to Use This Book
Working Along With This Book
How Excel Is Set Up
Keyboard Shortcuts
Data in Excel
Style Formatting and Page Setup
Formulas and Functions
Data Analysis Features
Charts
Conditional Formatting
Find and Replace and Go To Features
Excel Efficiency-Robust Rules
Index
A Beginners Guide to Conquering Excel’s
Frustrations and Making Excel Fun
Mike “excelisfun” Girvin
Holy Macro! Books
PO Box 82, Uniontown, OH 44685
Slaying Excel Dragons
© 2011 by Mike Girvin and 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 storage retrieval system without written permission from
the publisher.
All terms known in this book 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.
ISBN: 978-1-61547-302-1 (ePub); 978-1-61547-000-6 (Print)
Library of Congress Control Number: 2010904992
First Printing: February, 2011
Author: Mike Girvin
Technical Editor: Bob Umlas
Copy Editor: Keith Cline
Cover Design: Shannon Mattiza, 6Ft4 Productions
Illustrations: Timm Joy
More Graphics: Scott “Scottie P” Pierson
Image Processing: Fine Grains, India
Layout: Mary Ellen Jelen
Additional Production: Schar Oswald, Zeke Jelen
InDesign Consultant: Anne Marie Concepcion
Publisher: Bill Jelen
Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685
Distributed by: Independent Publishers Group, Chicago, IL
Dedication
Dedicated to:
Dennis Big D Ho (14 year old son) who likes to read a lot
Isaac Viet Girvin (4 year old son) who likes to go on adventures a lot
About the Author
My name is Michael Gel Girvin. From 1984 to 1997 I was a world class
boomerang thrower who won multiple world titles. From 1988 to 1998 I ran a
boomerang manufacturing company called Gel Boomerangs and the boomerangs
earned numerous awards. During the early years of running the company I was
computer illiterate and did not know how to use a database or spreadsheet. After
I hired a consultant to teach me how to build a database and how to use a
spreadsheet, my business life was transformed. My ability to create financial
reports, do taxes and perform cost accounting was dramatically improved
because I was starting to become computer literate. In particular, it was my
growing ability to use Excel that allowed me to make better business decisions.
As I learned more about what Excel could do it dawned on me that knowing how
to use Excel to efficiently build robust solutions was one of the most important
skills that any working human should possess. Why isn’t this taught in the
schools, I thought?
This led me into teaching. From 2002 to current I taught (and still teach)
accounting, finance, statistics and math classes at Highline Community College.
My primary goal as a teacher is to teach all my classes in a computer lab using
only Excel - no paper and handheld calculators are allowed. In about 2004 I
found Bill MrExcel Jelen’s web site: www.mrexcel.com. This site had three
amazing things: a message board where anyone could ask questions, a daily
Excel PodCast (Excel TV) and easy to understand books. As a business person
and an educator who was like a dry Excel sponge, I soaked up all I could from
the MrExcel site and brought it to my classes. In addition, I appropriated
MrExcel’s idea of Excel TV by posting over 1000 Excel how to videos at
YouTube (www.youtube.com/user/excelisfun). About 15,000 videos are watched
every day at the excelisfun channel at YouTube site.
It is from my insights as a once-computer illiterate business person, an Excel-
Business teacher and an avid fan of the MrExcel web site that I write this book.
Acknowledgments
Thanks to Bill MrExcel Jelen for doing more than any other person on the earth
to bring Excel to the world with his Message Board, Excel TV Podcasts and his
amazing books! If it were not for him, I would not be writing this book. Thanks
to Bob Umlas for an amazing technical editing job! Thanks to Keith Cline for
the stellar “paying attention in English class” editing. Thanks to my two bosses
at Highline Jeff Ward and Joy Smucker for bending over backwards to allow me
time for writing this book.
Foreword
I remember the day like it was yesterday. I was on the phone with a fruit-and-nut
guy, a potential client who was interested in hiring me to write some VBA
macros to automate the recipe calculations for the various trail mixes that he
sold. He started off with a line that I had heard a few times before:
“I love your Learn Excel podcasts on YouTube...”
I was about to thank him for the compliment, when I realized that it wasn’t a
compliment at all. He finished his sentence:
“...you are almost as good as that ExcelisFun guy.”
Huh? What was he talking about??? I, MrExcel, was the Excel podcast guy on
YouTube. Thanks to my former gig on TechTV, I had monthly access to
podcasting pioneers such as Leo Laporte and Amber MacArthur. They were both
a few months into their podcasting career when I jumped aboard and was
producing video podcasts every weekday back in 2006. I had been pretty sure
that I had the corner on the Excel podcast market.
Soon thereafter, I met Mike Girvin. Our “Dueling Excel” podcasts each Friday
show that there are many different ways to solve a problem in Excel.
I love the fact that Mike builds his college classes around Excel. Whether he is
teaching Excel, Statistics, Accounting, or Math, his students do 100% of their
school work in Excel. This is, quite frankly, how the world should work. You
should do everything in Excel.
I’ve written quite a few intermediate to advanced books about Excel. In my
world, the accountants in my seminars use Excel 40 hours a week. The questions
that I get all the time are the fairly advanced. I am too far removed from the
people who are brand new to Excel. Mike, through his students, has contact
every school day with dozens of people who might be struggling through their
first experiences with worksheets. Because of this, Mike has a unique
perspective into the “dragons” that make Excel seem so intimidating. I wanted to
add a beginning Excel book to my publishing line-up and I am thrilled that Mike
offered to write the book that you are holding.
—Bill “MrExcel” Jelen
Introduction
Have you ever used Excel and been frustrated that you couldn’t get it to do what
you want? Like trying to enter 2% into a cell, but it shows up as 200%, or trying
to create a month report from daily transactional data, but you don’t know how
to add sales for each month, or adding a column of numbers, but the total is a
few pennies off. If you have encountered problems such as these, this book can
help you to overcome these problems and become an Excel Master!
Being an Excel Master is important because Excel is the default program on the
planet, and we humans must know how to use it. Because most people aren’t
“fluent” in Excel, knowing how to use Excel efficiently is an easy way to
impress the boss, look good in a job interview, or transform your ability to run
your own business.
This book is intended for anyone who wants to master Excel: beginners,
intermediate users, and even advanced users. The fact is that most of us
intermediate and advanced users learned Excel in bits and pieces and have
“holes in what we know”. This book tells the story of how to master Excel
without holes. In this way, this book can be useful for anyone who reads it.
This book will teach you two things:

How to avoid the everyday frustration that most people encounter
How to efficiently build robust Excel solutions
Efficient, in this scenario, means that you build your solutions quickly. Robust
means that the solutions will not break easily and your Excel solutions are
adaptable to change. This book will not teach you the rudimentary basics of
Excel, such as opening a file, saving, printing, minimizing, and so on. Instead,
this book assumes that you have opened Excel before and used it a little bit.
Before learning what you can do with Excel, you need to understand what Excel
can actually do. Generally speaking, Excel does three things:

Data storage: Stores raw data (like a database)
Calculations: Makes calculations (math, retrieves data, text manipulation,
or more)
Data analysis: Turns raw data into useful information
This book covers the following broad topics:

How Excel is set up
Keyboard shortcuts
Data storage in Excel
Style formatting and page setup
Calculations via formulas, functions, pivot tables, and other features
Excel’s powerful data analysis features
Charts to visualize quantitative data
Conditional formatting to visualize data