Now that you know how to create pivot tables, you can start to explore how robust a tool they really are. In this beginner's guide, you will learn how to manage pivot table options, use pivot table data in formulas and apply conditional formatting.
How to format your data
We'll start with a basic Excel workbook that details the monthly, quarterly and annual sales of two different store locations over two years. Remember that a pivot table is only as good as the data it contains. The more detailed your data, the more options you have to pivot your data.
While you can certainly create a pivot table directly from data in a worksheet, like we did in the introductory article, it's considered a best practice to format it as a table first. It doesn't matter which color you choose; that's purely preference. I selected blue.
Once your data is a table, you can turn it into a pivot table. By selecting Insert and then Pivot Table by the left arrow, or by selecting Recommended PivotTables by the right arrow, and then choosing a layout. It doesn't really matter which one of these you choose. I clicked on Insert and then PivotTable.
How to pivot your pivot table
I'm starting the pivoting process by putting Year under Columns, Store Location under Rows, and Total Revenue under Values. This is the resulting layout. If I wanted to view this same information but focus more on the performance of each store branch during a year, I could do this.
I created this layout by simply dragging Year from Columns to Rows, and placing Year above Store Location. In doing this, I made Year the most important factor in terms of presentation.
If I had dragged Year to Rows but placed it under Store Location, this would be the result. By placing Store Location above Year, I've made Store Location the most important factor.
Now we're going to add in more information, which will make it more clear why pivoting data is so essential. So far, all we're viewing from that initial Excel workbook is Store Location, Year and Total Revenue, but what happens if I add in Month and Quarter too?
By clicking Month and Quarter, I automatically added them to my pivot table, and as you can see, the result is terrible. This spreadsheet is extremely difficult to read, and it's hard to tell what the takeaway is.
If you look closely, you'll also notice that Excel is now trying to sum the quarters included in this table, because Quarter is placed under Value. All Excel knows is that the information in the cells associated with Quarter are digits; it doesn't know they're a representation of time as opposed to currency. By pivoting, you can fix this display issue.
First, I took Quarter and dragged it out of Values and back into the main Pivot Table Menu. Then, I right-clicked on Quarter and selected Add to Report Filter.
Now the same set of data is much easier to look at. In fact, it all fits in one frame. The best part is, if I want to view this data based on Quarter, I still can.
By clicking on the Quarter Filter arrow, I can filter what I look at based on one or multiple Quarters. For now, I'll choose to only see the third quarter of each year.
Now it's easy to view the information based on Quarter and sorted by Store Location and Month. The orange arrow on this screenshot is pointing out the small filter icon next to Quarter. Anytime you see that funnel shape, it means a filter has been applied to the pivot table, so if you're ever missing information and you're not sure why, check for that icon.
Additional pivot table functionality
You can configure pivot tables in nearly endless ways based on what you need and the type of data you have. In addition to pivoting the data, like we did here, you can use complex formulas within pivot tables, apply conditional formatting, create visual charts from your data and even project future values.