Pivot Tables are one of the greatest features of Excel. They allow you to quickly summarize large data sets. You can quickly gain further insights into your data, and in a business setting pivot tables are usually made for the purposes of presenting the data to a key decision maker.
Once you get your hands dirty with a few examples, you’ll see how truly easy it is to create a pivot table.
Pivot Table Data
To quickly create a pivot table summary of your data, make sure of two things:
- Your data has a header row (with no blanks)
- There are no huge gaps in your data
Creating a Pivot Table
To create a pivot table, simply click anywhere inside the range of data and click “insert – pivot table.” Next, it will ask you if you want to place the pivot table on a new sheet or a current one (up to you!).
Customizing Your Table
The next thing to do is choose which data will fall in rows vs. columns vs. filters (also called metrics vs. dimensions, but that’s a discussion for another time). It takes a few times making these to get an instinct as to which metrics will make for better rows vs columns vs filters.
Don’t know what rows or columns or filters are? Don’t fear! I’ve got your back.
Below, we’ve selected Year for the rows and “Sales” for the values. By default, the pivot table chose to sum the values. Looks good!
Our pivot table looks good so far and we can see very clearly how well we did each year. But let’s see how each BRANCH did during each year.
This looks pretty good, but let’s try moving branch over to be a column:
Much better! Now we can quickly scan year to year, branch to branch to see who our top performers are.
Part 2: Conditional Formatting inside a Pivot Table
Even though our pivot table looks ready to send off, let’s put a nice finishing touch before shipping it.