Pivot tables transform Excel from a simple tool for logging data to a robust system for analysis, projection and visualization. If you're like a lot of entrepreneurs, you've sat in on meetings with people who regularly allude to pivot tables, but when you've never seen one in action, it's hard to grasp just how awesome they are.
When you begin logging your business's cashflow in Excel, it's easy enough to navigate your workbooks and view items like quarterly, monthly or annual revenue. However, as time goes on and you accrue more and more data, making sense of your spreadsheets gets more difficult. When you apply a pivot table format to a data set you can seamlessly alter the way you view your data, emphasize certain data points and hide irrelevant information. Pivot tables also allow you to create visualizations from your data, so you can view your company's revenue over time on a line graph or check out your expenditures on a pie chart. On the advanced end of the spectrum, you can even use pivot tables to perform basic projections.
Why you should use pivot tables
For the sake of this tutorial, we're using a mock data set that relates to small business. This spreadsheet contains the monthly revenue for two store locations: East Falls and West Falls. The revenue for each store spans two years (2015 and 2016), and if you look closely at the screenshot, you will see that the quarter is listed as well. You may also notice that you cannot see the entire data set in this screenshot, because several rows are out of view. This is a common problem with displaying large data sets, and part of why pivot tables are so fantastic.
Let's say you were hosting a company meeting, and you wanted to show your staff at the East Falls and West Falls branches how each store compares in terms of total revenue for 2015 and 2016. How would you do that? You'd likely go through the spreadsheet, highlight the relevant rows, copy and paste them in a new workbook, and then sum them. You might even spend your valuable time messing around with the format to make it visually appealing. This method is inefficient and prone to error. Instead, you can use a pivot table to dynamically change the data format.
How to create a pivot table
Before you begin, remember it is vital that there are no blank rows in your data set (there can, however, be blank cells). If there are entirely empty rows, the pivot table format will not be applied to your entire data set.
Now, to create a pivot table, select any cell in your data set. Go to the Insert tab and click "PivotTable." Before you click "OK," make sure the dialog box settings match what you want in terms of data set selection and the location of your new pivot table.
While it doesn't look like much yet, this is the basis for your pivot table. On the right-hand side of the screen, you can see all the pivot table fields, as well as four different areas: Filters, Columns, Rows and Values.
The best way to get to know how these various fields and categories work is to play with them by selecting fields, and then dragging them to one of the four designated areas below. You should only do this with a copy of your data set; be sure to maintain a clean original so you can experiment freely without fear of losing information.
We're going to start simple with pivot table formatting. Here's what the test data looks like after I did the following:
- Selected "store location" and dragged it into Rows.
- Selected "total revenue" and dragged it into Values.
As you can see, this instantly gives you an easy to view comparisons of what each store branch made, in total, over 2015 and 2016.
To see the same comparison of total revenue, but separated by year, I dragged "year" into the Columns field. Now you can see not only how much the two branches made over the past two years, but how they performed each year. You could drill down further and compare these branches based on quarter, month or other criteria in your data set.
By now, you're starting to see the beginnings of the power of pivot tables.
Want to start over? To remove something from the current view on your pivot table, just drag each selection under Filters, Columns, Rows and Values back up to where you first selected it. I've done it three times to fully clear my pivot table. This feature is major. Pivot tables allow you to play with how you view< your data without the hassle of constantly creating new workbooks and spending hours reformatting your data.