Chapter 14. Segmentation: Slice and dice

Get creative with your tools.

You’ve developed a formidable knowledge of Excel in the past 13 chapters, and by now you know (or know how to find) most of the tools that fit your data problems. But what if your problems don’t fit those tools? What if you don’t even have the data you need all in one place, or your data is divided into categories that don’t fit your analytical objectives? In this final chapter, you’ll use lookup functions along with some of the tools you already know to slice new segments out of your data and get really creative with Excel’s tools.

You are with a watchdog that needs to tally budget money

Geopolitical Grunts is a swashbuckling group of policy geeks who advise businesses and governments on the most important macro trends of the day. They need your help doing some hardcore pivot table work.

The data set describes the spending of the U.S. federal government at the county level for the past couple years.

Here’s the graph they want

Here’s the federal spending data, broken out by county

The folks at Geopolitical Grunts sent you this database, which is a county-by-county summary of U.S. federal government spending over the past couple years. And because it’s almost 50,000 lines, you might want to close other programs before loading it!

Load this!

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

Sharpen your pencil

Look at the chart he wants. Can you create it from the data you have? Why or why not?

Sharpen your pencil Solution

You just compared the data to the chart you’ve been asked to generate. How well do the two match up?

Sometimes the data you get isn’t enough

Data can be close to what you want without ever quite getting there. But that doesn’t mean that you can’t do your analysis. You can just transform the data you have into the data you need to have.

Exercise

Go ahead and create the field you need: the spending per household for each agency in each county.

1. Convert your data to a table and name the table “SpendingData.”

2. Create a column on the end called Per_Household and populate it with a function that divides the fund_total field by the hh_count field.

   

Exercise Solution

You just created one of the fields you need for your chart: spending per household. How did it go?

1. Convert your data to a table and name the table “SpendingData.”

   

2. Create a column on the end called Per_Household and populate it with a function that divides the fund_total field by the hh_count field.

   

Your problems with region are bigger

Calculating the Per_Household figure was straightforward, because you had all the data you needed right there on the spreadsheet.

But you’re in a different situation with Region, since there’s nothing implicit in the data that you can use to calculate Region. You need to incorporate more data in order to determine the region for each row.

Brain Barbell

What kind of data do you need?What would you do with it?

Here’s a lookup key

You can poke around in search engines for a few minutes and find data like this: a key that shows how state names fit with regions.

Load this!

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

VLOOKUP will cross-reference the two data sources

A particularly useful function in Excel is VLOOKUP. The V stands for vertical, and what the function does is look up a reference value in a vertical list and then return the value from another column that matches the position of the value in the vertical list.

Bullet Points

• VLOOKUP searches for the lookup_value in the first column of the table_array.

• col_index_num asks for a number to point to the column containing the data you want returned, not a reference.

• [range_lookup] takes a Boolean argument.

• If you set the [range_lookup] to FALSE, VLOOKUP returns an error if there isn’t an exact match, and your table_array doesn’t have to be sorted.

• If you set [range_lookup] to TRUE, VLOOKUP returns a value near your lookup_value if there isn’t an exact match, but your table_array has to be in order.

It’s a big function. Let’s give it a shot....

Oh, bother. So it looks like you’re going to have to cross-reference the region to the state abbreviation, then use that abbreviation to cross-reference the region to your original data. Fortunately, VLOOKUP will make quick work of bringing these data sets together.

Load this, too!

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

Copy the state_region data into your state_abbrev spreadsheet like this....

Pool Puzzle

Your job is to take formula elements from the pool and place them into the blank lines in the formula. You may not use the same formula element more than once, and you won’t need to use all of them. Your goal is to make a VLOOKUP formula that puts Region and State_Abbrev into the same table.

Note: Each thing from the pool can be used only once!

Pool Puzzle Answers

You just wrote your first VLOOKUP formula to create a table that relates your Region field to your State_Abbrev field.

Do this!

If you haven’t already, type this formula into your cell C2 and copy/paste it for each row. You’ll use it in the next exercise.

Long Exercise

You’re just a step away from having all the data you need to create the chart the Geopolitical Grunts want. Create the VLOOKUP formula that will bring region data into your spending spreadsheet and then create the pivot chart your client wants.

1. Copy the lookup key sheet you’ve been working on in hfe_ch14_state_abbrev.xlsx into your original workbook. Right-click on Sheet1 and tell Excel to move the sheet into your hfe_ch14_usaspend.xlsx workbook.

   

2. Add a Region column to your original data and then write the VLOOKUP formula that will look up the region field based on the state abbreviation in that row.

   • NOTE: You will need to have the fourth argument of VLOOKUP be FALSE.

3. Create the pivot chart that the Geopolitical Grunts want. Just click “Pivot Chart” rather than “Pivot Table” and let Excel create the chart. You will have to change the title and formatting to get the chart right.

   

Long Exercise Solution

You just added another column to your data set and then created a pivot chart. How does it look?

1. Copy the lookup key sheet you’ve been working on in hfe_ch14_state_abbrev.xlsx into your original workbook. Right-click on Sheet1 and tell Excel to move the sheet into your hfe_ch14_usaspend.xlsx workbook.

   

2. Add a Region column to your original data and then write the VLOOKUP formula that will look up the region field based on the state abbreviation in that row.

   

3. Create the pivot chart that the Geopolitical Grunts want. Just click “Pivot Chart” rather than “Pivot Table” and let Excel create the table. You will have to change the title and formatting to get the chart right.

   

Create segments to feed the right data into your analysis

The data you receive to analyze inside Excel is just a starting point. If that data doesn’t do what you need it to do for your analysis, every tool of Excel is at your disposal to mutate the data into the form you need.

That’s what slicing and dicing data is all about.

There are no Dumb Questions

Q:	Q: Will I always use VLOOKUP to do this sort of thing?
A:	A: By no means! VLOOKUP is powerful, but there are other lookup functions as well. There’s HLOOKUP, which does horizontal lookups, as well as INDEX and MATCH, which are popular but slightly harder to understand.
Q:	Q: So there’s a lot of versatility in Excel’s functions when it comes to looking things up.
A:	A: Absolutely. But you never know what formulas you’ll be able to use to slice and dice data into the form you need. Remember, earlier in the chapter you used simple division to create a new field, which you graphed using a pivot chart.
Q:	Q: Now that I have lookup formulas and division, what else can I use to segment data points?
A:	A: Boolean functions are big here. Say you wanted to tag all the records in your database that were Department of Defense programs in Maryland. It’d be easy to use a simple AND function to create a Boolean value.
Q:	Q: And I could take that Boolean value and use it with a formula like COUNTIFS to compare it with other values in my data set.
A:	A: Or better yet, you could plug the Boolean value somewhere into a pivot table. Boolean functions are fantastic for segmentation, and they play really well with pivot tables.
Q:	Q: You know, this is kind of funny advice you’re giving me. What you’re basically saying is that there are a lot of different functions and techniques that can all do the same basic thing.
A:	A: Right! Go on....
Q:	Q: And at the same time you’re not giving me any quick and easy rules for how to use those functions and techniques for segmentation. Your advice is basically, “Look at the problem, and pluck an Excel feature out of thin air to solve it. Use whichever Excel feature is best.”
A:	A: That’s a fair observation. But you’ve learned enough about the features of Excel that all the stuff you don’t know consists of either super-advanced topics or subtle variations on the themes you’ve already picked up. At this point, your goal should be to play with the functions and think creatively about how to make them work for your specific problems.
Q:	Q: The student has become the master.
A:	A: That might be overstating it a bit, but you’re well on your way. Just as a book on Microsoft Word won’t show you how to write the Great American Novel, a book on Excel can’t teach you to create a brilliant spreadsheet.
Q:	Q: What can teach me how to do wild stuff in Excel?
A:	A: You can certainly always be on the lookout for people’s novel solutions to problems, but the best way to get good with Excel once you have a strong base of knowledge is just to learn as many functions as you can and experiment with making them work together. You’ll inevitably surprise yourself with the creative ways you can use Excel.
Q:	Q: It probably wouldn’t hurt to be good at math, either.
A:	A: Definitely. A solid book on general data analysis or analysis for your area of expertise would show you some of the theoretical points that will make your spreadsheets smarter. But that’s a whole different deal. In the meantime, let’s see what your friend at Geopolitical Grunts has to say about your pivot chart.

Geopolitical Grunts would like a little more nuance

Ah, clients.... What are you going to do? You flash a little skill, and once they know what you’re capable of doing, they’ll never leave you alone!

Exercise

Your client would like a little more detail in his chart. Looks like you’re going to have to create a new segment....

1. Adding dates to your pivot chart is the easy part. Drag the date field out of the Filter box and into the box that will make the pivot show many bars representing region and year, not just region.

2. Your raw data rows need a tag to denote whether each transaction is from a department-level organization or not. Create a Boolean data field that contains TRUE if the word “Department” is in the majagency_tr field. If you need help, take a look at the hint below.

3. Head back over to your pivot table. Since you’ve added data to your data table, you’ll need to press the button in the Ribbon to refresh your pivot table. Then add your new field as a filter, showing only data points where it’s TRUE that the transaction is from a department-level organization.

Hint...

FIND() returns an error if the requested text substring is not found. ISERROR() returns TRUE if its argument is an error. NOT() returns TRUE if its argument is FALSE. Put these formulas together to create a big nested formula that returns TRUE if “Department” is in the substring you’re looking at.

Exercise Solution

You just created a new segment to describe transactions originating from department-level organizations. How did it go?

1. Adding dates to your pivot chart is the easy part. Drag the date field out of the Filter box and into the box that will make the pivot show many bars representing region and year, not just region.

   

2. Your raw data rows need a tag to denote whether each transaction is from a department-level organization or not. Create a Boolean data field that contains TRUE if the word “Department” is in the majagency_tr field.

   

3. Head back over to your pivot table. Since you’ve added data to your data table, you’ll need to press the button in the Ribbon to refresh your pivot table. Then add your new field as a filter, showing only data points where it’s TRUE that the transaction is from a department-level organization.

   

You’ve enabled Geopolitical Grunts to follow the money trail...

Which is a good thing, because there’s a whole lot of it. So much of it, that it’s hard to keep track of it all. There’s so much of it that you could get buried in it all

Leaving town...

It’s been great having you here in Dataville!

We’re sad to see you leave, but there’s nothing like taking what you’ve learned and putting it to use. You’re just beginning your Excel journey, and we’ve put you in the driver’s seat. We’re dying to hear how things go, so drop us a line at the Head First Labs website, www.headfirstlabs.com, and let us know how data analysis is paying off for YOU!