Slaying Excel Dragons E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 578

Veröffentlichungsjahr: 2024

Beschreibung

This comprehensive guide is designed to elevate your Excel skills from beginner to advanced. Starting with the fundamentals, you'll learn how to navigate Excel's interface, use essential keyboard shortcuts, and manage data efficiently. As you progress, you'll dive into complex features like PivotTables, dynamic ranges, and advanced formatting, gaining the ability to handle intricate data tasks with ease.
The guide also covers powerful formulas and functions, including VLOOKUP, INDEX/MATCH, and logical tests. These tools will empower you to automate calculations, perform detailed analyses, and streamline your workflow. Additionally, you'll explore Excel’s data analysis features, such as sorting, filtering, and creating dynamic charts, enabling you to present your data clearly and effectively.
By the end of this book, you'll have a deep understanding of Excel's capabilities, equipped with the skills to tackle any spreadsheet challenge. Whether you're preparing for advanced data analysis or seeking to optimize your day-to-day tasks, this guide provides the knowledge and practical experience to make Excel work for you.

Leseprobe

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

Distributedby: Independent Publishers Group, Chicago, IL

Table of Contents

Chapter 1, “How Excel is Set Up”

Chapter 2, “Keyboard Shortcuts”

Chapter 3, “Data in Excel”

Chapter 4, “Style Formatting and Page Setup”

Chapter 5, “Formulas and Functions”

Chapter 6, “Data Analysis Features”

Chapter 7, “Charts”

Chapter 8, “Conditional Formatting”

Chapter 9, “Find and Replace and Go To Features”

Appendix, “Excel Efficiency Rules”

Index

Dedication

Dedicated to:

Dennis Big D Ho (14 year old son) who like 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 encounterHow 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 upKeyboard shortcutsData storage in ExcelStyle formatting and page setupCalculations via formulas, functions, pivot tables, and other featuresExcel’s powerful data analysis featuresCharts to visualize quantitative dataConditional formatting to visualize data

How to Use This Book

Every book is unique and this one is no exception.

The first time that a term is defined, it will appear in bold. Words or formulas that you are supposed to type will also appear in bold.

There are 43 Excel Efficiency Rules that appear in this book. You should cut these out and hang them up by your computer. We’ve even reprinted all of the Excel Efficiency Rules in the appendix to make this easier. The Excel Efficiency Rules appear in italics like this paragraph.

Notes about the “why” you are doing something will appear in blue

Sidebars Appear in Green

Sometimes, in the middle of a set of steps, there will be an opportunity to summarize some points. This summary is not part of the steps, but it seems like a good time to offer the summary. This discussion will appear with a green title.

A Note from the Author About Stepped Procedures

I must mention an unusual convention that I am using in this book. Most books that have step-by-step instructions limit the steps to 7 to 10 steps. I did not do that in this book. Some of the step–by-step instructions have more than 20 steps, and in some of the sequencing of steps I have injected large blocks of text in between the steps. What I am trying to do with this unusual approach is to simulate how I teach. When I teach, I do a project in class, and the students follow along. I do step 1 and step 2, then I stop and explain the significance of what we just did, then I do step 3 and step 4, then I stop and explain the significance of what we just did, and so on. The idea is that if I explain the ‘whys” as close to the “hows” as possible, what you’re learning sticks in the brain more firmly because the glue is stronger.

A Note from the Publisher About Figures in the ePub Edition

The publishing world is in a state of transition. For the most part, books are designed for the print edition and then transferred to the eBook edition. Someday soon, that paradigm will change.

Already, eBooks offer many benefits over print books:

Images are in color in this editionYou can do a full-text search in this editionWhen the text tells you to “see chapter X for more”, you can click on the words “Chapter X” to jump to that location.

The one drawback to eBooks is that we can not control that a figure and the text that refers to that figure will be on the same page. In general in this book, a figure will appear first, followed by the text that refers to the figure.

Working Along With This Book

This book comes with two main Excel workbook files, as follows:

excelisfun-Start.xlsmexcelisfun-Finished.xlsm

Download these files from either of these sites:

http://www.mrexcel.com/slayingfiles.html.https://people.highline.edu/mgirvin/ExcelIsFun.htm

The Start file has the blank templates and worksheets so that you can try everything that you see in the book. As you read this book, prompts will tell you what worksheet in the workbook you must use to follow along with the examples in the book. The Finished file has all the finished examples from the book. There is also one zipped folder named Products that contains some files for Chapter 3, “Data in Excel,” and a third workbook titled “May NetIncome.xlsm” for the “Workbook References” section in Chapter 5, “Formulas and Functions.”

In addition, because this book is set up to enable people to learn Excel from beginning to end, teachers may want to use this book in the classroom. To this end, there is a zipped folder named Homework available that has homework files and solutions for each chapter in the book.

You can download these files from the URLs above.

Alright, now it’s time to get started learning about the power of Excel!

1

How Excel Is Set Up

As we get started, we must look at how Excel is set up. The essence of Excel is that it has a rectangular shape that has two directions.

The left-to-right direction is represented by letters that indicate columns. The letters are called column headers. In Figure 1, you can see the vertical column C. As we move to the left from column C, the letters go backward, and as we move to the right, the columns advance through the alphabet. (When you get to Z, the next columns are AA, AB, AC, and continue to the last column, which is XFD, which is the 16,384th column.)

The up-and-down direction is represented by numbers that indicate rows. The numbers are called row headers. In Figure 1, you can see the horizontal row 5. As we move down from row 5, the row numbers increase (the last row is 1,048,576), and as we move up, the row numbers decrease.

Figure 1

The intersection of a row and column is called a cell or a cell reference. In Figure 1, you can see that the cell C5 is the intersection of column C and row 5.

All the cells together are called the worksheet or spreadsheet or simply sheet. There can be many sheets in an Excel file. The name of the sheet is shown in the sheet tab. In Figure 1, the sheets tabs are a dark color, and the active sheet tab (sheet showing) is a light color. The default names for the sheets are sheet1, sheet2, and so on.

Figure 1 (continued)

Because the default names hinder efficient and robust formula creation and navigation through a workbook, you should always give the sheet a logical name. For example, if the sheet has sales data, name it something like SalesData. This way, when you look at the sheet or make a formula with a sheet reference (more later), you have a good idea about what the sheet contains. To name the sheets, just double-click the sheet tab, type a name, and press Enter. To select a sheet, simply click the sheet tab with your cursor. All the worksheets in an Excel file are together called the workbook. You can see the workbook name in the title bar at the very top of the window (excelisfun-Start.xlsm).

Because there are more than 150 sheets in the Excel workbook file named excelisfun-Start.xlsm that came with this book, we need to be sure that you know how to navigate through this large workbook to any particular sheet that we may be working with. In Figure 2, the active sheet is named Setup, and it is colored white to indicate that the cells from this sheet can be seen and worked with. To select a sheet, simply use your cursor (white diagonal arrow) to click the sheet tab. In Figure 2, the last sheet that we can see is named Decimals. But there are many more sheets beyond (to the right). There are three ways to access sheets that cannot be seen:

Use the keyboard shortcuts Ctrl + Page Down to move to the next sheet in a workbook (thus making it the active sheet) or Ctrl + Page Up to move to the previous sheet in a workbook (again, making it the active sheet).Use the sheet navigation arrows. The arrows without vertical lines move the view of the sheets without changing the active sheet, and the arrows with the vertical lines jump all the way to the end or beginning of the sheets.Right-click any of the sheet navigation arrows, click More Sheets, and then navigate to whichever sheet you would like.

Figure 2

If you are not familiar with navigating in workbooks with a large number of sheets, try all three methods before reading further in this book. Doing so will help you to follow along with the more than 150 sheets in the Excel examples in this book.

Now we can state our first two Excel Efficiency-Robust Rules:

Rule 1: Excel sheets are rectangles with columns (letters) that move left to right and rows (numbers) that move up and down. A firm understanding of this will help us later to build formulas that are efficient and robust.

Rule 2: Always name sheets (double-click the sheet tab, type the name, press Enter) with an easy-to-understand name so that navigation through the workbook and formulas with sheet references are easy to understand.

Figure 3 shows a few more Excel elements that this book assumes you are familiar with, or at least have seen before.

Note: Your ribbon might look slightly different. This is because the groups in the ribbon will expand and collapse depending on two things:

Whether your window is maximized or restored downThe display resolution for your computer (Control Panel settings)

Figure 3

Note: There is one ribbon that has many tabs. The standard seven tabs are Home, Insert, Page Layout, Formulas, Data, Review, and View. There are many context-sensitive ribbon tabs that will show up when we use certain features. For example, when we make charts or pivot tables, specific ribbon tabs will appear when we work with the charts or pivot tables. In this book, when we want to get to the Insert or Page Layout part of the ribbon, I write, “Click the Insert tab or Page Layout tab.”

Note: Because the ribbons take up a lot of space, you can hide and unhide them with the keyboard shortcut Ctrl + F1.

Note: You can add buttons that you see in the ribbons to the Quick Access Toolbar (QAT) by right-clicking a button in the ribbon and pointing to Add to Quick Access Toolbar. The advantage to this is that the QAT is always visible no matter what ribbon tab you have selected.

Now it’s time to take a look at keyboard shortcuts.

3

Data in Excel

Jelen To make calculations (such as adding) or perform data analysis (converting data to information), you first need raw data. So, you need to learn about how Excel deals with raw data. In order that you understand how Excel sees data, this chapter covers the following six important points:

The difference between raw data and informationThe table format structure and the Excel Table featureEntering data into ExcelNumber formatting as façade (i.e., number formatting sits on top of data)How data is aligned

Raw Data Versus Information

What is raw data? Raw data is data in small bits. For example, the number of units a sales representative sold in one day is a piece of raw data. Information is made from raw data and is used to make decisions. For example, if you need to decide whether a sales representative met the sales quota for the week, you must first look at the raw data, add all the sales for just that one sales representative, and then decide “yes, the sales quota was met” or “no, the sales quota was not met.” The process is as follows:

Figure 58

1. Take raw data.

2. Create information.

3. Make decision.

Figure 58 shows a raw data set. We can see that on 8/9/2010, the number of units Sioux sold was 10. The number 10 is an example of a piece of raw data. To make information from all the bits of raw data, we need to organize it in a way that will allow us to make decisions based on that information.

The information we need is “Sioux’s total weekly units sold.” The decision we need to make is “did Sioux make the weekly quota of 20 units?” We can create this information by using the SUMIFS function. Let’s take a look at how to do this.

SUMIFS Function

The goal is to add with the single condition (criteria) Sioux. The SUMIFS function is perfect for this because it will sum units sold “if the units sold belong to Sioux.” The name of the SUMIFS function means this:

The SUM part means to add.The IF part means there is a condition or criteria.The S part means that you can have more than one condition or criteria. (We have only one condition here: Sioux).

In this first example, we will do both calculating and data analysis in Excel. The calculating part is the adding of the sales numbers, and the data analysis part is the taking raw data and converting it into information.

To follow along, open the file named excelisfun-Start.xlsm and navigate to the sheet named SUMIFS.

1. Click the sheet tab (sheet) named SUMIFS.

2. Notice that the raw data we have is Units Sold and the information we need is Sioux’s total units sold for the week.

The SUMIFS functions will be part of a formula that we will put into cell C10. A formula is simply a calculation that we make in a cell, and a function is a built-in formula element that will do the “heavy lifting” for us (do the math or analysis for us). Formulas and functions always start with an equal sign as the first character in the cell. You’ll learn more about these in Chapter 5, “Formulas and Functions.”

Figure 59

3. Click in cell C10. And type an equal sign () and then type the letter s.

In Figure 59, a drop-down list of built-in functions appears with a list of functions that start with the letter s.

This drop-down is Excel’s way of trying to be polite. Excel is reminding us of functions we might want to use. Notice that an fx icon appears next to each function name. (Later we will see other formula elements show up on this list.) We don’t see the SUMIFS functions, so we have to type the next letter, u.

Figure 60

Figure 61

4. Type the letter u and you should see the SUMIFS function as the fifth function in the list.

5. Using your Down Arrow key, arrow down until the SUMIFS function is highlighted, and then press the Tab key to insert the SUMIFS function into cell C10.

6. After you insert the SUMIFS function, a screen tip prompts you to enter the sum_range, criteria_range1, and criteria1.

In Figure 61, we can see our formula take shape. The equal sign tells Excel that this is a formula that requires some sort of calculation. The SUMIFS function has built-in code that will do the hard work for us. The open parenthesis tells us that we need to provide some details for the SUMIFS function. The text box below is called a screen tip or a ToolTip and tells us what arguments the SUMIFS function requires to do the adding.

Argument? What is an argument? An argument is a word from math that means “element that is needed for the function to make the calculation.” The sum_range argument is just asking for the range of cells with the numbers to add. For us, it will be the UnitsSold range. The criteria_range1 argument is the range of cells with all the criteria. For us, it will be the SalesRep range. The criteria1 argument is the criteria for adding. For us, it will be cell B10 because that cell holds the criteria, Sioux. Also, the word SUMIFS in the screen tip is a hot-link to Excel Help for the SUMIFS function. If you click the hot-link for this or any other function, a window opens with help for the function. In addition, after you inserted your SUMIFS function with the Tab key, the cursor became a flashing vertical bar. The flashing vertical bar tells you that this part of the formula can be edited. Finally, notice that the sum_range argument in the screen tip is bold. The fact that it is bold means that the SUMIFS function is waiting for that argument. Let’s not keep it waiting any longer.

Figure 62

7. With the cursor flashing in the sum_range argument, select the range C2:C7 by clicking in cell C2 and dragging the cursor to cell C7. You can tell that you have selected the cells correctly by the dancing ants that surround the range of cells.

In Figure 62, you can see that the dancing ants surround the cell range C2:C7 and the range C2:C7 is inserted into the sum_range argument of the SUMIFS function. Notice that the cell range C2:C7 is the formula notation to indicate that the cells C2, C3, C4, C5, C6, and C7 are being referred to. For this book, cell ranges like C2:C7 can be referred to as a cell range, or a range of cells, or simply as a range.

Note: Don’t worry if you make a mistake when selecting the range. As long as the ants are still dancing, just reselect the correct range and the formula will automatically update to the correct range!

Figure 63

8. Notice the word Point in the lower-left corner (location in the status bar). Point mode means that you are selecting cells for a formula or function.

Figure 64

9. After the range C2:C7 is inserted into the formula, type a comma (,) to get to the next argument.

When you type a comma, two things happen:

The dancing ants stop dancing, which means the range is no longer editable by simply dragging your selection cursor.The next argument in the screen tip becomes bold. When Excel bolds the criteria_range1 argument, this is Excel’s way of politely saying, “Hey, what I need next is the cell range with the criteria.”

Figure 65

10. To put the range into the criteria_range1 argument, select the range B2:B7.

The criteria_range1 is the range that the SUMIFS function will look to determine which numbers should be added. From the SalesRep column, whenever it sees the word Sioux, it then knows to go over to the UnitsSold column and use the corresponding number of units sold for adding.

Figure 66

11. Type a comma and you will see that the screen tip is asking for criteria1.

Figure 67

12. Because our criteria is Sioux, we click cell B10.

Figure 68

13. Type a close parenthesis to tell the SUMIFS function that we have entered all the arguments. Notice that the screen tip goes away.

Figure 69

14. Press Enter to put the formula in the cell and tell Excel to create our information from our SUMIFS calculation.

With this information we can now determine whether Sioux met the sales quota of 20. Sioux didn’t; 15 is less than 20. Notice that we used Excel to do two things:

Calculate a total by adding with the criteria SiouxPerform data analysis (that is, convert raw data into useful information)

Note: Suppose you want to share your workbook with someone who does not have Excel 2007 or 2010. Because the SUMIFS function does not exist in earlier versions, this formula would show a #NAME! error and would not work. In this scenario, however, you can use the following alternative formula:

=SUMIF(B2:B7,B10,C2:C7)

Even so, if you can avoid using the SUMIF function and instead use the SUMIFS function, you’ll realize two big advantages:

The SUMIFS function allows more than one criterion. In our case, we had just one criterion, but in many cases you will have more than one.

SUMIFS argument names are much easier to understand, and therefore inexperienced users of the functions will make fewer mistakes.

We discuss the SUMIFS function in more detail in Chapter 5.Verifying Formula Results (and tracking down bad data)

So, what did we just do with the SUMIFS function? We converted raw data to information and made a decision. But, is 15 units sold for the week correct? If we are the ones designing the spreadsheet, we always want a way to double-check to determine whether our calculation or data analysis is correct. In this case, the data set is small enough that we can do it manually, as follows:

Figure 70

1. Click in cell C2.

2. Then, holding the Ctrl key, click cell C4 and then C6.

3. When you do this, notice that the three cells are highlighted even though they are not next to each other. (This is called a noncontiguous range.)

Figure 70 shows what is called the noncontiguous range selection trick.

Figure 71

After highlighting C2, C4, and C6, look at the right side of the status bar. You will see that Excel has given us a preview of the sum. Because it says 15, we can assume that our calculation is correct.

And that leads to our next Excel Efficiency-Robust Rule:

Rule 4: If we are the ones designing the spreadsheet, we always want a way to double-check to determine whether our calculation or data analysis is correct.

Figure 72

Now, what about Tina? Did Tina meet the sales quota of 20 units sold? To determine the answer, do we have to create the whole formula over again? No way! Because of Excel’s updateability, we can just change the word Sioux in cell B10 to Tina and the formula will calculate the total units sold for Tina. This is called changing a formula input. A formula input is simply the raw data in a cell.

1. Click in cell B10.

2. Type Tina.

3. Press Enter.

That is amazing! Our formula updated. But wait… Is 11 units sold for Tina correct? Using our noncontiguous range selection trick, we want to determine whether we got the right answer for Tina. Figure 73 shows the sum of 20 in the status bar. So, we have a problem.

Figure 73

The 20 in the status bar is correct because we have the correct cells selected and Excel does not make math errors. This means that we must investigate further. When investigating a problem with a formula, it is efficient to check the formula first to determine whether we created it correctly, and then to work backward through the formula’s inputs to determine whether any of the raw data is creating the problem. To check the cells with formulas or raw data, you can use a keyboard shortcut, the F2 key, to put the cell into Edit mode. (The status bar will indicate when you are in Edit mode.)

Figure 74

1. Click in the cell with the formula, cell C10.

2. Press the F2 key.

3. Look at the color-coded ranges to determine whether they are correct.

They are correct

Next we must look in the actual cells to determine whether we can find a problem with the formula inputs.

We will work backward from B10 and look at the content of each cell.

Figure 75

1. To get out of Edit mode in cell C10, press the Esc key.

2. Click in cell B10.

3. Press F2.

This looks okay because there are only four characters: T, I, N, A.

Figure 76

1. Click in cell B7.

2. Press F2.

This looks okay because there are only four characters: T, I, N, A.

Figure 77

1. Click in cell B5.

2. Press F2.

This looks okay because there are only four characters: T, I, N, A.

Figure 78

1. Click in cell B3.

2. Press F2.

We found the problem! There are five characters: T, I, N, A, space.

Figure 79

To fix this problem, press the Backspace key and then press Enter.

After you remove the space, the formula updates.

Excel sees “Tina” and “Tina ” as different things. This is a very common problem with data in Excel. Later when we look at examples with larger data sets, we will see a nonmanual way to remedy this problem.

Our next two Excel Efficiency-Robust Rule are as follows:

Rule 5: Excel is literal about how it sees data. Spaces matter in Excel. “Tina” is different from “Tina ”.

Rule 6: When investigating a problem with a formula, it is efficient to check the formula first to determine whether the formula is correct, and then to work backward through the formula’s inputs to determine whether any of the raw data is creating the problem. The keyboard shortcut F2 puts a cell into Edit mode and places the cursor at the end of the formula.

In the preceding example, we used the SUMIFS function to perform a calculation that converted raw data into information. Next we want to use a PivotTable to perform data analysis to convert raw data into information.

PivotTables

Figure 80

The PivotTable feature is one of Excel’s most powerful data analysis features. (You’ll learn more about Excel’s data analysis features in Chapter 6, “Data Analysis Features.”) PivotTables can quickly summarize data and create reports that can be used for decision making. Our goal in the next example is to summarize the raw data shown in Figure 80 into the report shown Figure 81 in just seven clicks. Before we make our report, we have to look at how the raw data is set up and how we want our resultant report to look.

Figure 80 shows us a raw data set. The raw data set has the following column headers or field names: Date, SalesRep, and UnitsSold. These field names tell the user of the raw data what sort of data will be in the column. Dates are in the Date column, names are in the SalesRep column, and numbers of units sold are in the UnitsSold column.

Figure 81

Each row in the data set represents a transactional record that contains one date, one sales representative name, and the number of units sold for that date. Each transactional record is an individual recording of a sales event.

Before you create a PivotTable, it is helpful to visualize how you want your report to look. In Figure 81, we can visualize how we want the report to look. You can create such a visualization by sketching with a pen and paper or by typing out a “sketch” in Excel. This is important because if you sketch it out, creating the PivotTable will be easier than if you do not. In the working world, you often hear people say that PivotTables are hard. They are not hard if you make a sketch first, or at least visualize the end result in your head. In Figure 81, the sales representatives’ names (each name listed one time, creating a unique list) are called row labels