Pivot Tables are one of the most powerful features in Excel. It may be because of that power the feature is one of the most feared items to touch. Pivot tables allow you to take a table and "pivot" it to show the data in a variety of methods. The ability to slice and dice the data how you see fit to represent your message is what makes a Pivot table so powerful. I want to take some of the fear out of using this feature by explaining ways to setup your data and display data in a few easy steps. This post is based solely on my opinion and experience so if you pick up a book or talk to others on this subject they could have varying opinions. This is what makes using Pivot Tables and charts easy for me.
The first component of using a pivot table is making sure you have your data ready to go. Most people have data in a spreadsheet and display that data so it looks good for them. Individuals are familiar with splitter bars, highlighting, leaving blank rows, etc. They don't always set that spreadsheet up in a usable format; it is more "does it look good". Listed below are some of my best practices and data cleanup techniques for ensuring quick and easy Pivot table analysis. These tips can also be utilized for creating spreadsheets without Pivot tables as they also make the sheet look good.
Format your data as a Table.
Excel can take a range of data and make it a table very easily. Simply select the data and go into the Insert Menu and select Table.
When doing this it is best to ensure your first row has column headers in every column and you check the box indicating you have those headers. You also want to avoid blank columns or blank rows as they unnecessarily separate your data.
Once the table is inserted you can rename the table and change the formatting. None of those changes are absolutely necessary, it all depends on how detailed you want to be with your work. In large spreadsheets I do rename the tables so I can determine what I am referencing later on more easily.
The finished product will filters at the top and allow you to add a total row at the bottom to apply various functions to the bottom of your columns. (sum, average, min, max, etc.)
Column formatting
It is important to tell Excel what the data is you are looking at so that when you use your pivot table you can properly sum up the data or group the data. This is especially true for date columns. In my sample data set I need to change the formatting of the date column. I can do this by selecting the entire column and formatting the cells. Due to the fact that I work on a global account I feel that the best format is DD-MON-YY (07-MAR-11). This is clear to most of the world and prevents confusion between the US and ROW standards. If you want to be even more inclusive for other languages I suggest going to the ISO 8601 standard.
I also formatted the value column to currency for the following finished product.
It is also important that your columns contain the right data that you are trying to display. This means following a few rules:
- Date columns should only have dates in them
- Avoid blanks, notice I have schedule and complete for my status columns
- Have a standard for your data. Whether this is a site ID or a name, make sure rows that are supposed to reference the same "thing" is named the same.
- Ensure that your table includes all the data you want to report on. Don't create separate tables when you can simply add a qualifying columns.
- In my example I have the scheduled and completed sites in the same table, not in separate tables.
Insert the Pivot Table/Chart
Creating the pivot table itself is very easy. Click anywhere in your table, select insert and choose Pivot table or Pivot Chart. If you choose Pivot Chart it will also create a pivot table.
You will be prompted for a location to place this Pivot table and a new worksheet is the best spot.
Depending on the version of Excel and/or the version the XLS file is saved in you may or may not see the Pivot table outline shown below. It doesn't really matter if you do or don't, older versions of Excel have it.
Creating the Pivot Table
So here we are. This is the moment most people are unsure what to do. They stare at this blank screen and then start randomly creating something. The best way I can combat this is to show you an example of a Pivot Table and explain why it has to be that way. What I am going to do is create a table that shows me the quantity of sites per month, by region that have cutover dates within those months. In order to do this we will go through a few steps.
1. Setting up the "per month" – This is accomplished by simply dragging the date field into the row labels. Once complete XLS will populate all of the unique dates into your pivot table. The screenshot below looks ugly though and details too much data before we even get started. The way to make this cleaner is to group your dates.
2. Grouping the dates – right click on the date field and select group. I choose Months and Years but you can pick a grouping that works for you.
NOTE 1: This only works if you only have dates in your date column. Any other data will not allow Excel to group the field.
NOTE 2: If you do not group by year it should be noted that Excel doesn't understand months repeat each year. All of your values for January 2009 and January 2010 will be in the same January column.
3. Next drag the region field into the column label since we want to show per region
4. The table is empty because we have nothing in the values field yet. The Pivot needs to count something to fill up the buckets we just created. Drag the site ID into the values section.
5. Since site ID is a number the pivot table summed them up by default. I want the table to count these. If we were using the values we may want it to sum up the values. Click on the sum of sites in the value section and select value field settings
6. Change the value to count.
7. The final product now shows the number of sites per region with cutover dates in those months.
8. The last thing we will do is graphically represent this data. Select Options from the ribbon and click pivot chart
9. In this instance a column chart will do
10. The graph now represents the pivot table data
In my next posting on this subject I will go into Pivot tables and Pivot Charts in greater detail discussing different types of charts, filtering, stacking, multiple criteria, etc.
