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.
