How to Use Excel Pivot Tables in Your Financial Spreadsheet
Do you have a large set of data you need to manipulate? Are you looking to improve the functionality of your financial spreadsheets? If so, Pivot Tables might be a great solution. They’re one of Excel’s most powerful features, so if you don’t yet know how to use them, you’re missing out – in other words, you’re probably doing more of the heavy lifting than you have to.
What Are Pivot Tables?
Pivot Tables are interactive tables that make data reporting and analysis much easier. Even better, they can be created using simple drag-and-drop functionality. If you’d like to sort your data, count items, or sum totals, Pivot Tables might just be for you.
How Do You Create a Pivot Table?
First, you’ll need a data set, entered in Excel in rows and columns. For example, let’s say your financial spreadsheets are tracking sales for your company, which sells refrigerators and dishwashers. Each row has the model of an appliance, each column tells us the Model, Appliance Type, State Sold In, and Price.
To create your Pivot Table, click on any cell within the data set. On the Insert tab, click Pivot Table. A dialog box will pop up in which you can confirm the table or range that you want the Pivot Table to pull the data from, and you can also choose where you want the Pivot Table to be created. The default location is a new worksheet.
Once you click “Okay,” you’ll see the Pivot Table field list. This is where you get to choose what you want in the rows, what you want in the columns and what you want the report filtered by. For example, you could choose to only examine dishwashers and the table would leave out refrigerators. Or, you could choose to examine only appliances sold by state, so if you choose California, the table would not show sales from any other states. The beauty of the Pivot Table is that once you’ve decided to have the filtering option, you can choose to see only California sales, and then you easily make a different choice and see only Oregon sales. You won’t have to rebuild the table, you simply click a different state and the Pivot Table will do the work for you.
Filters aren’t the only way you can choose to see the data from your financial spreadsheets in a different light. You can also change the summary calculation. What this means is you can choose to have Excel either total your data set or count it, for example, you might want to know the total dollar value of refrigerator sales, or you might prefer to simply know how many refrigerators were sold. Changing the summary calculation gives you the option to do both within the same table. And those aren’t the only two options. To see your choices, click any cell inside the Total column. Then, right click and click on Value Field Settings. Next, choose the type of calculation you want to use (i.e. Sum, Count, Average, etc.). Then, click “Okay,” and you’re all set! 
What Else Can I Do With Pivot Tables?
One excellent use of Pivot Tables is to compare sales totals of different products listed in your financial spreadsheets. In our example above, this could mean comparing the number of dishwashers sold to the number of refrigerators sold, or comparing the number of Model A dishwashers sold to the number of Model B dishwashers sold. Without a Pivot Table, you’d have to manually look through the worksheet and tally every time you saw a Model A or a Model B. But what if you’re looking at dishwasher sales for the whole country? That could be pages and pages of line items that you’d have to flip through. That could take hours if not days. The Pivot Table automatically aggregates the data so that you have the numbers right in front of you – in less than a minute – and it also makes it easy for you to look through the line items of just the relevant products, if you so choose.
Another great use of Pivot Tables is to combine any duplicate data found in your financial spreadsheets, or in any spreadsheets. Let’s say you’ve just completed a blog redesign and, as a result, updated a bunch of URLs. We’ll assume that your blog reporting software didn’t handle it very well and ended up splitting the “view” metrics for single posts between two different URLs. Now, in your spreadsheet, each individual blog post is listed twice. To have accurate data, you’ll need to combine the view totals for each of these duplicates. This is where a Pivot Table can come in really handy. With a Pivot Table, you won’t have to manually search for and combine all the metrics from the duplicates, you can summarize your data by the title of blog posts, and within seconds the metrics on blog post views from your duplicate listings will be automatically aggregated.
Why Wasn’t I Using Pivot Tables Before?
Exactly. Now that you know what Pivot Tables can do for you, there’s no reason not to start experimenting. These examples are just the beginning. Once you start manipulating the data from your financial spreadsheets using a Pivot Table, you’ll be impressed what you can do – or, more accurately, what you don’t have to do, because the Pivot Table does it for you!
Written by DSD Business Systems
