Last time we talked about how to get excel to format cells automatically. This time we are going to talk about how to analyze data without messing with the original information. So what are pivot tables? Pivot tables are like a report that shows you only the information you want to see. If you have a lot of information, imagine 50 columns by 1000 rows in a worksheet how can you determine what is going on in that large a spreadsheet? Pivot tables are the solution.
There are great examples from different industries, but they might be difficult for everyone to relate to. For something universal, let’s look at customer orders. To keep it simple, we’ll say that the order list has a couple of simple columns: Customer, State, Product, Date, Month, and Amount. Obviously this is a gross simplification, but once we know how easy pivot tables are, we can easily expand it. So below we have our fictitious orders. To download a copy of this spreadsheet to play along click here: Download PivotTablesExample
Once we have a more than a couple of screens of data its difficult to see where the money is coming from. To get started, all we need is a worksheet that has labeled columns. In order for excel to recognize a header row, all we need to do is have the labels formatted a little differently. For our example data I used Bold text and a larger font than the table information. If we have a properly formatted worksheet, then excel is smart enough to select everything in it. We can just select one cell in the table and even if there are 5000 rows, and 5000 columns excel will select the full extents of table. So, assuming we’re all using the same data, click a cell in the middle of the table, such as C10, then click Data > PivotTable and PivotChart Report…
We're going to accept the default options. Click Next. Verify that the selection box grabbed the entire table then click Next. Click Finish.
This will create a new worksheet with a blank pivot table. Although it is possible to do most of arranging and formatting within the pivot table wizard, I find it just as easy to do after the fact. It is so useful to change pivot tables around that we need to get good at it.
So from the Pivot Table Field List select Month and drag it into the area labeled 'Drop Row Fields Here.' Now we'll use a different method. Select State, verify Row Area is selected, then click Add To. Then using either method add Customer. If you drag and drop, be aware of exactly where you drop it into the row area. A vertical gray indicator line will appear where the field will be placed when you let go of the mouse. Make sure you drop it to the right of State.
You see that the pivot table automatically sorts the rows from left to right. So all of the August (shown as 8) data are grouped together. Same for States. Notice that there are automatic totals for the Months and States. If you don't want these, right-click in any total, such as CA Total and select Hide.
Now let's add some actual content. Add the Amount field to the data area. This will give us the total revenue broken out by the finest category (right-hand field in row area) which in this case is by Customer. To change the format of a pivot table item, avoid the temptation to just select the cells and format as usual. Since Pivot tables are dynamic in nature, we would lose this formatting if we changed or updated the table. So instead, right click on the grey header that reads 'Sum of Amount' and select field settings.
Here you can change the display name of the field, and the formatting. Click the number button bring up the familiar list of data types. Select accounting and change the number of decimal places from 2 to 0. Click OK, then OK again to close the field setting dialog box.
Now a bit of advice. Don't worry too much about messing anything up in a pivot table. With a little practice, you'll be able to recreate any pivot table in about 60 seconds. Additionally, even if you can't seem to get it to do what you want just know that there are ways to do almost anything that you want. Give it time and practice, practice. If you get stuck - ask for help. So let's get right on in there and mess with it!
You show the pivot table to your boss, and she points out 2 problems:
- There was only one sale from July listed here, and September isn't over yet. So, in other words - we only want to see items from complete months - August.
- She doesn't like using numbers, like 8 for months. She wants the text 'August' instead.
No problem. We'll fix the labels first. Select A5 (click on the 7) and type 'July.' Do the same for August and September. Excel will remember that for the field Month 7 = July. This is an important concept in pivot tables. We don't have to change the original data which we may or may not have control over. Think of output generated from another software program such as your ERP system. We change the pivot table, and leave the data alone.
Now for the second request. Notice that the grey header of Month has a drop down arrow similar to when you filter columns. Drill down on Month, uncheck (show all), and check 8. Click OK and now we are pivoting only the August sales.
Get the idea? Now for some major changes that are very easy. To change the pivot table to show only the data from Florida, we can grab the grey State header and move it to the left of Month. Then we drill down on State, deselect (Show All) and select Florida.
Now get rid of month. Simply select and hold the Month header and drag it out of the pivot table. When you are in a safe place to drop it, the mouse pointer will change to an X.
To show the information in another way, and trust me, you'll get plenty of special requests when you start showing your team your pivot tables, add Product to the column fields area. Remember that you can drag and drop or use the Pivot Table Field List to accomplish this. Now add month to the column fields. Since nothing was sold in Florida in July, add the State of Maine to the list. Deselect month August just to make the table smaller. Did you notice that Excel remembered that we wanted to see July instead of 7 for the months? Pretty slick.
The only basic area of the pivot table that we have not used is the page fields. Page fields are global. You use this when you want to isolate just one item from a field. When we moved State to be the leftmost row and selected only Florida to show, we could have instead put the month field into the page field area and selected Florida. The page field is not designed to multi-select. If you wanted to see customer 256 and customer 199, then put customers in the row area. If you only wanted to see customer 256, page fields work great. A very common use for page fields is pivot out the information by month to generate monthly reports.
Here are some keys to keep in mind when creating and maintaining Pivot tables:
- Although you are used to formulas recalculating as soon as the precedent cells are changed, pivot tables do not automatically update. To update a pivot table, click refresh on the pivot table toolbar. It is possible to make pivot tables automatically refresh but it requires VBA. Contact me if this the type of support you need.
- The pivot table toolbar has important tools that you need to know about. Particularly handy are the built in formatting and the button to re-display the field list if you inadvertantly close it.
- Most of the spreadsheets I create for clients have at least one pivot table. Once you start regularly using pivot tables you will find that you change the way you build your spreadsheets. What I find is that as soon as someone wraps their head around how to best incorporate this tool, they think about using Excel much more like a database than an accountant's ledger.
Comments