How to Create a Pivot Table in Excel 2007

Creating Pivot Tables in Excel 2007:

I’ve always thought that pivot tables were the epitome of what you could do with Excel. You can look up formulas and you can stumble through the sort & format tools by trial and error, but pivot tables take effort. (Usually more than the average person wants to give.)

Not so. Pivot tables are easy.

So let’s give this puppy a try, shall we?

Step 1: Set up Your Data

Here’s an example of data. All well-organized world dominatrices should have a list of all the countries of the world. You should also have one of those maps with the red and green territory pins, but that’s neither here nor there. Anyway, be sure to give your columns headers, and try for a solid block of data. (No empty cells.)

Next, highlight your table of data. (Key command = CTRL + A) With the info still highlighted, go to Insert>Pivot Table>Pivot Table. A prompt will come up, asking if you want to create a pivot table. Click OK, and don’t freak out when all sorts of strange things happen.

Now, this is the fun part. When you clicked okay, a new sheet should have opened up, and it should look something like this:

Step 2: Choose the Data You Want to in Your Pivot Table

1. Choose Your Row Data

I’ve chosen “Country Name,” since this is what I want to see on the left-most row. To select data, go to the box on the right, called the PivotTable Field List. If you don’t see this box, click anywhere within the pivot table on your spreadsheet to make it appear.

Drag the data name (in this case, Country Name) to the Row Labels box in the PivotTable Field List.

Alternatively, you can also drag your selected data directly onto the pivot table, where you want it to appear.

2. Choose Your Column Data

Choose your column data the same way as you’ve chosen your row data. I’ve chosen “Resistance” for my columns. You’ll probably want to choose a column where the fields are numerical. Dates also work well here, and can be sorted by ranges including months, years or quarters.

3. Choose Your Report Filter and Values

I’ve chosen “Population” for my report filter, and “Cost” for my Values. Again, try to choose data with numerical fields. Your PivotTable Field List should look something like this:

And, your pivot table should look something like this:

So, now you know how much it will cost you to conquer each country, sorted by the anticipated level of resistance.

But there’s still a lot of information here, so much so that it’s almost overwhelming. That is where your “filter” function comes in handy.

Step 3: Breaking Down Your Data So It’s Useful

To create a data filter, click on the down-arrow to the right of your column, row or report labels.

Un-check “Select All” and select just the items you want to view. I’ve chosen Albania, Croatia, Eritrea and Spain for my rows. I selected 1,2 & 3 for my columns, and I’ve left the population filter alone. So, now, you have a much more manageable data table.

From this data, you know that of the 4 countries you’ve selected, either Croatia or Eritrea will be slightly less expensive and less difficult to attack and conquer than that pesky Albania.

Step 4: Analyzing Your Data

Sum vs. Counts

Now, you can also change the function, and find out the number of countries that fit your specifications, rather than the cost.

Right-click “Sum of Costs” or whatever you have in your value slot, and select “Value Field Settings.” This should get you to a screen like this:

Change the value field to “Count” of whatever else you would like to measure.

Charts

You can also use this data to create charts. Highlight your data, click on the insert tab, and select the type of chart you would like to view. This one’s not terribly fascinating, but it does underline the point that, if you’re budget-conscious, Croatia or Eritrea are the way to go.

Detailed Data

Another neat something about pivot tables. If you double click any number in your table, it will automatically create a new sheet, with more detailed information. For example, if I click on C6:

This is what I get:

Creating Page Fields

Finally, to create a sheet for each page field, go to Options>Options>Show Report Filter Pages. If you do not have a lot of repeating values in your data, I would not recommend doing this, as you will suddenly have 6000 pages of data. If you do have a lot of repeating values (for example, if 200 countries cost 500 goats each), this can be a useful tool.

Step 5: Where to Learn More

I learned how to create pivot tables at a Skillpath Excel course, taught by Instructor Tom Fragale. I found the class to be useful, and the instructor to be helpful and knowledgeable.

Additionally, as a result of the class, I learned how to create macros, scroll bars, drop-down lists, forms, auto-fills and histograms, but most importantly, I can also set up little buttons that generate prompts telling people if they click “yes,” I will drop a bag of scorpions on their heads. Granted, the other things might be more useful, but that one is far and away the most satisfying.

I hope this has enabled you to create a pivot table in Excel 2007. If you have any questions, please post them, and I will do my best to answer them. If I don’t know the answer, I will either 1) Smite you or 2) Find the answer elsewhere and post it and the referring link.

9 Comments

  1. Sarah

    I thought I was the ultimate nerd when I was so excited about the things I learned in that class. By actually blogging about it you have taken the title. Thank you and congratulations!

  2. This is so your fault for recommending the course in the first place. :-P

    Although, yes, this is ultimate nerdjoy that I’m experiencing here. It takes it to the next level.

  3. Danielle

    Wow, that post hurt my head….I don’t think there was anything I hated more than figuring out formulas in Excel. I am impressed by your daunting abilities and plan to come to you any time I have excel questions (of if I’m having hard time converting data into ‘costs in goats’).

  4. Terry

    Interesting Information which did help. I would like to know how to create pivot table using two column lables based on data such as Qtr 1 and Qtr 2

  5. Hi Terry,

    If “Date” is your column label, you can sort data by quarters. Right click the first date header in the column, and click on “Group.” (For example, if your data is divided by month, click on the first month name.)

    Then, you can select different date groupings, including quarter. If you’d like the data to show months as well, just select both quarter & month.

    – Leanne

  6. […] I’ve been working in projects and operations for this company for a year, and in operations before that. For the most part, I’ve found that projects can be accomplished through organization, networking, and common sense. It also helps if you are kind of OCD – you can kind of get an idea of my loving relationship with Excel from this post. […]

Leave a Reply

Your email address will not be published. Required fields are marked *