Head First Excel

Chapter 4. Change your Point of View: Sort, zoom, and filter

The details of your data are tantalizing.

But only if you know how to look at them. In this chapter, you’ll forget about formatting and functions and just focus on how to change your perspective on your data. When you are exploring your data, looking for issues to investigate, the sort, zoom, and filter tools offer surprising versatility to help you get a grip on what your data contains.

Political consultants need help decoding their fundraising database

The Main Campaign is working for the Dataville mayor and wants to solicit his supporters for money.

Your client is a super-intense, super-demanding politico. But the good news is that the data is pretty clean (that’s always a relief!), and if you can help this group organize their contributor list, you’ll have scored a huge account.

The data is a list of their contributors’ donations from the past year. And while the data is of a high quality, at over 4,000 rows, there’s quite a lot of it!

Find the names of the big contributors

Getting in touch with their most passionate (that is, most generous!) contributors is a big deal. The small fries are important, but before anything else, the Main Campaign needs to get in touch with the big contributors.

Sharpen your pencil

Take a look at your data. How could you change your perspective on it to show you the top donors?

____________________________________________________

____________________________________________________

____________________________________________________

____________________________________________________

Load this!

www.headfirstlabs.com/books/hfexcel/hfe_ch04_data.xlsx

Sharpen your pencil Solution

How did you decide you might change perspective on this data to show you the top donors?

If you had the data ordered by donation amount, you could see the big donors at the top.

____________________________________________________

Sort changes the order of rows in your data

The Sort buttons are a useful tool that enables you to reshuffle the order of the rows in your data. The Sort buttons can be found under the Data tab of the Ribbon.

To sort ascending means to order your data from first to last or smallest to largest, and to sort descending means to do the opposite.

There are many occasions where you’ll want to use Sort to change the order of your data, but Sort is especially useful when you’re looking at data for the first time and trying to get a feel for what’s in it.

Exercise

Let’s sort your data to group all the big contributors together.

1. Select any cell in the column you want to sort by. Since you want to sort by donation here, you’d pick the Donation column.

   

2. Click one of the Sort buttons to sort your data. Which button should you press to get big donors up top: Ascending or Descending?

Exercise Solution

Were you able to bring all the big contributors to the top of your list through sorting?

1. Select a cell in the column you want to sort by.

2. Sort the data. Which button should you press to get big donors up top?

   

Sorting shows you different perspectives on a large data set

When you look at data for the first time, it’s a good idea to sort by different columns to look for visible patterns.

Watch it!

Excel can figure out which columns are in your table...usually.

If Excel doesn’t sort all your columns together, it can wreck your database. Always save your data first and check it after sorting to make sure you and Excel got it right.

Let’s see what the Main Campaign has to say about this newly organized data....

Exercise

Let’s see if we can fulfill the client’s request: first to sort the data by zip code, and then to sort it by donation. This sort will enable us to look at the biggest givers by grouping them by geography.

1. To execute this new and more complex sort, start by clicking the big Sort button. (Be sure your cursor is inside your data table first.)

   

2. In the dialog that pops up, start by telling Excel to sort your data by zip.

   

3. Now click Add Level and add a new level to sort by donation. You may receive a warning dialog box...just do what you think is best.

   

Exercise Solution

Were you able to sort the data by zip and then donation?

1. To execute this new and more complex sort, start by clicking the big Sort button. (Be sure your cursor is inside your data table first.)

2. In the dialog that pops up, start by telling Excel to sort your data by zip.

3. Now click Add Level and add a new level to sort by donation. You may receive a warning dialog box...just do what you think is best.

   

There are no Dumb Questions

Q:	Q: What do some of those other options mean inside of the Sort dialog box?
A:	A: There are lots of different ways you can sort besides alphabetizing text and sorting numbers from smallest to largest or vice versa. For example, you can sort by color.
Q:	Q: Why would I want to sort by color?
A:	A: Very often people will highlight cells in their spreadsheet to be different colors, and you’ll see long spreadsheets that have various elements highlighted. If you’d like to group those elements together, you can sort by color.
Q:	Q: Is it good formatting practice to highlight cells by color? It seems like that would be an alternative to sorting in terms of drawing your eye to various parts of the spreadsheet.
A:	A: Generally there are better ways to tag data than highlighting cells. You can sort by color, but most formulas can’t read your cells’ formatting. So if you want to tag interesting cells, it’s better to add a column and insert your own text or Boolean functions (which you’ll learn about later on).
Q:	Q: So we can sort by color. What else?
A:	A: Under the Order drop box, you can set up a Custom List. Custom lists enable you to create any arbitrary sorting you want. So if Excel doesn’t automatically sort your data in exactly the way you need it to, you can create a custom list that shows exactly how you need that data sorted.
Q:	Q: How big of a problem is it when Excel sorts one column but not another?
A:	A: It can be terrible. Think about what would happen with this data: each donation amount is tied to a specific person, so if you changed the order of the donations but kept the order of people the same, you wouldn’t know who gave what anymore.
Q:	Q: That sounds awful.
A:	A: It happens. And it’s indeed awful.
Q:	Q: How do I avoid it?
A:	A: This is a reminder of a very important principle of dealing with data: always keep copies of your original data. Once you’ve done an analysis of the data, it’s always a good idea to check your data against the original to make sure that nothing weird happened.
Q:	Q: That sounds true, but it’s not very reassuring. How do I avoid a screwed-up sort?
A:	A: Sorting is another place where the Tables feature you learned about in Chapter 2 comes in handy. If you define your data set as a table, then you are being really explicit with Excel about the dimensions of your data.
Q:	Q: So then Excel always knows what data is in my table, and it won’t accidentally just sort a single column.
A:	A: Exactly. You don’t have to define your data as a table in order to sort it correctly, but for the uber-paranoid the Table feature is the way to go.

Nice work on sorting that data.

Now you can see how many large donations fall into each zip. Let’s see what the client thinks....

Looking at the data is a good thing. It’s a nonobvious but important part of data analysis, and your client is right to want to be able to see the data better, not just group it correctly. What should you do?

Sharpen your pencil

What do you think of each of these solutions? Is it a good idea? Is one of them the best?

1. Use formulas to create summaries of the data.

   _______________________________________________________________

   _______________________________________________________________

   _______________________________________________________________

2. Get a really big monitor.

   _______________________________________________________________

   _______________________________________________________________

   _______________________________________________________________

3. Delete records you don’t need.

   _______________________________________________________________

   _______________________________________________________________

   _______________________________________________________________

4. Zoom out.

   _______________________________________________________________

   _______________________________________________________________

   _______________________________________________________________

Sharpen your pencil Solution

What can you do to help your client get a better perspective on the big picture of the data?

1. Use formulas to create summaries of the data.

   I can do this, but it isn’t really what the client is asking for. Formulas and their results might be illuminating, but they take you away from actually looking at the data.

   ________________________________________________________

2. Get a really big monitor.

   This is a great idea! It would be great to be able to see a few thousand legible spreadsheet cells on the screen at once. Problem is, big monitors can get expensive, and that’s probably not a reasonable response to the client.

3. Delete records you don’t need.

   No way. This is just asking for trouble. If I really can’t find a way to get the right visualization of your data, I can use summaries based on formulas. Deleting records is the fastest way to lose all perspective on the data.

4. Zoom out.

   Note

   Zooming in and out on data is something that spreadsheet ninjas do all the time.

   Zooming out is a great idea. Sometimes you need to look at the forest, and sometimes you need to look at the trees. Zooming will let us do it.

   ________________________________________________________

See a lot more of your data with Zoom

Sometimes you need to focus on a small part of your data. Why not zoom way in?

Sometimes you need to focus on the big picture. In that case, zoom way out.

Getting the big picture through zooming out is not about straining your eyes (if you feel your eyes strain, you should be zooming in!). It’s about looking at as much of the whole picture of your data as you can see at once. To zoom, click the View tab to get to the Zoom button.

Exercise

1. Zoom out to see all your data.

2. Poke around a little. What zip has the most big donors?

Exercise Solution

Were you able to get a better perspective on the data after zooming way out?

Your client is impressed!

Looks like Mr. Demanding is impressed! Without running any sort of function or doing any sort of formatting, you were able to sort and zoom your way to a greater clarity about his data.

But now he wants to focus on just one subset of the data. You know you can’t just delete the data he doesn’t want to focus on. How can you look at just the data he wants without changing the rest of the data?

Filters hide data you don’t want to see

Sorting and zooming have given you a rich big-picture perspective, but sometimes you want to look at just a slice of data. Try clicking on the Filter button.

Filters are convenient because they give you a way to hide the data you don’t want to see. It’s still very much there; it’s just conveniently out of the way. And just as with sorting, when you’re exploring a new data set for the first time, it’s a great idea to run filters to look at various subsets of the data.

Do this!

Select a cell in the column you want to filter and click Filter. What happens?

Use Filter drop boxes to tell Excel how to filter your data

When you click on the Filter button, Excel puts a drop box on every column in your data table.

When you click on one of the drop boxes, Excel gives you a bunch of options for filtering the data based on the data in that column.

Each of the seven zip codes represented in your data set are listed in the Filter drop box for ZIP. Because they’re all checked, Excel is showing all of them. But you only want to see 78723....

An unexpected note from the Main Campaign...

Exercise

Your client wants to see a list that contains only people who live in the 78723 zip code and who gave $1,000 or more. Use filters to create that list.

1. Tell Excel to filter by zip 78723. With the Filter drop box activated for the ZIP field, uncheck the Select All box so that none of the zips are selected. Then select the 78723 zip and press OK.

   Note

   This one is easy.

2. Apply another filter that shows only people with donations of $1,000 or more. Select the Donation filter drop box and then the options that you believe will show you only the individuals you want to see.

   Note

   This one’s a little trickier.

Exercise Solution

Were you able to apply the filters that show only people from zip 78723 who gave $1,000 or more?

1. Tell Excel to filter by zip 78723. With the Filter drop box activated for the ZIP field, uncheck the Select All box so that none of the zips is selected. Then select the 78723 zip and press OK.

   
   

2. Apply another filter that shows only people with donations of $1,000 or more. Select the Donation filter drop box and then the options that you believe will show you only the individual you want to see.

   
   

The Main Campaign is delighted with your work

This is kind of ambiguous.

A guy named Alex who’s a big giver? There could be tons of people in the database named Alex! This could be like finding a needle in a haystack. Except that we have a potent tool: filters.

There are no Dumb Questions

Q:	Q: What ever happened to writing formulas? It seems like all this sorting, zooming, and filtering is really just a prelude to writing formulas, which is the real meat of data analysis.
A:	A: It’d be better to say that good thinking about data is the substance of data analysis, not writing formulas or any other feature of Excel or any other software.
Q:	Q: So where do sort/zoom/filter and formulas fit into data analysis?
A:	A: Sorting, zooming, and filtering are great tools to use to get a sense of what is inside data that you are looking at for the first time. Sometimes you just need a better perspective on your data, and the way to get at that perspective is literally to look at the data in a bunch of different ways.
Q:	Q: So once I want to start drawing conclusions about data, I’m probably not going to need sort/zoom/filter so much, right?
A:	A: Your mileage may vary. It may be that your specific problem really needs nothing besides the perspective that these visualization tools give you. Or it may be that you need to create a model that summarizes and manipulates the data once you’ve gotten the perspective you need.
Q:	Q: So that is where formulas come in?
A:	A: Yes. Formulas, in their most general sense, take data as arguments and return new data. If your analytic goals aren’t met by simply changing your point of view on the data, chances are you’ll need to hit the data with some formulas to achieve the manipulation or summary that you need.
Q:	Q: Still, it’s kind of cool just how much you can do with these visualization tools.
A:	A: Definitely. Don’t just accept without question the default zoom amount of Excel or the ordering of the data for analysis you receive. You can use sorting, zooming, and filtering to change up your perspective in a big way, enabling you to understand your data better.

Exercise

This client request is a toughie. Your client found a big donor named Alex, or so he thinks. Can you find this mysterious “Alex”?

1. Clear your old filter. To do this, click the Filter button twice: first to eliminate the filter, and second to start a new filter.

2. Use your filter to find Alex! What do you find?

   ____________________________________________________

   ____________________________________________________

   ____________________________________________________

   Note

   Hint: There’s no one named Alex in the data. Do what you need to do to help your client!

Exercise Solution

Were you able to dig up this mysterious donor?

1. Clear your old filter. To do this, click the Filter button twice: first to eliminate the filter, and second to start a new filter.

2. Use your filter to find Alex!

   There’s no Alex, but there’s Alec, Alejandro, and Alessandro. All of them are small givers, except for one of them. There’s an Alec who’s a $10,000 donor. He must be the one!

   

Donations are pouring in!

The Main Campaign was able to get in touch with Alec, and the lists you isolated have proven to be really valuable for the Dataville mayor’s fundraising efforts!