One of the most legendary features of Microsoft Excel is the PivotTable. The pivot table is by far one of the most useful ways to analyze and visualize your data in Excel. If you have never used a pivot table or seen what it is capable of, we will be taking a fast track look in this article.

With the use of pivot tables, you can visualize your data different ways. For those of you who use Excel 2010 and later, you can also use a feature called Slicers to filter multiple pivot tables at the same time.

Let’s look at a scenario where pivot tables can help you see what you want in your data at a glance. We have sales data for the entire first quarter.

Note that we have five different data points to work with: Salesperson, Division, Client, Amount, and Month. This gives us a lot to work with and we are about to see how much flexibility pivot tables offer us.

GETTING STARTED

To insert a new pivot table, simply go to the Tables group on the Insert tab and click PivotTable. The Create PivotTable dialog will now open. Tip: if you click into your data table before you insert a pivot table, the Table Range textbox will automatically populate with your range.

Typically, you will just leave the default for ‘New Worksheet’ for where you want to place your pivot table and click OK.

Now you can begin to build your first pivot table just the way you want to. You should now see two things. First, you should see a blank pivot table (PivotTable1) on the left side of your worksheet.

Then you should also see the PivotTable Fields field list on the right side of the worksheet.

If for any reason the Field List is not shown, make sure it is selected (dark gray background) in the Show group on the Analyze tab.

Now let’s say we want to see totals for each Salesperson broken down by client. We can drag and drop Salesperson to the Rows box followed by Client. Then we want to drag Amount to the Values box.

We need to make sure the Value Field setting for amount is Sum, rather than Count, Average, or any of the other selections. Now our pivot table should look something like this.

Notice now how we can see Amounts broken down by their totals per Client for each Salesperson. To get anywhere close to this kind of data visualization with the raw data, you would have to do some pretty fancy filtering.

Also, if we drag and drop Month into the Filters area, we can filter this pivot table by month to drill down into the data even further.

MULTIPLE VIEWS OF YOUR DATA WITH MORE THAN ONE PIVOT TABLE

We can now make a copy of our current pivot table and paste it into another part of the worksheet. Then for this one, we will change our fields and place Month in the Columns area and leave Amount in the Values area. We will also place Division in the Filters area for both pivot tables.

By placing Division in the Filters area for both of our pivot tables, we set ourselves up for an interesting feature that will allow us to control that filter for both tables at the same time.

So, here’s what both of or pivot tables should currently look like.

Now you can see your original pivot table and a new one that allows you to see aggregate sales amount totals for each month in the quarter. Now let’s see about that method for controlling the Division filter simultaneously for both pivot tables.

SLICERS

As mentioned earlier, if you are using Excel 2010 or later, you can insert a Slicer which is a fancy filter. One of the greatest things about Slicers is that you can connect filters from multiple pivot tables for more control.

Just go to the Filter group on the Analyze tab and click on Insert Slicer.

Just check the box next to Division and click OK.

Now you have your Slicer and you can place it where you want it on your worksheet.

With our first pivot table selected, click on Filter Connections in the Filter group on the Analyze tab.

Then make sure the box next to Division in checked. Repeat for the second pivot table.

Now when you select a slicer, it will filter both tables on Division at the same time.

That’s a quick look at pivot tables and slicers. This is just scratching the surface of what is possible, but sure gets you off to a good start!