Excel 2010 Pivot Tables: Introduction to Tables, Fields, and Filters

Pivot tables are a great way to summarize and manipulate large amounts of data. They’re also a fantastic tool for transposing data: with the help of pivot tables, you can move data from rows to columns or columns to rows without un-linking the data sources. By making calculations for the core data almost completely without formula construction, creating a pivot table is an efficient and quick way to make your data work for you in a myriad of ways, with little to no error. The PivotTable tool is unquestionably one of the most powerful tools of Excel, and combines many of the features found under the Data tab, including Sort, Filter, Subtotal, and more. Click here for an in-depth guide on pivot tables for business.

Today, let’s start simply, with a quick look into basic actions like creating a table, assigning fields, and filter options. The PivotTable function can be found under the ‘Insert’ tab.

Your data can come from many different sources. Excel’s pivot tables can retrieve their data from such sources as the current worksheet, a different worksheet in the same workbook, text files, Microsoft Access tables/queries, SQL server databases, and more. For the purpose of this exercise, we will be building a table using the data from the existing worksheet. Select the data range you want from the spreadsheet and create a pivot table. Likewise, if you would like to practice using data from another file, simple select the ‘Use an external data source’ button. Note that sometimes the pivot table does not capture your entire data range, so be careful that after the range is selected, it contains everything you want included.

Setting up a table:

• Columns (fields) must have titles
• Only the first row will be used for titles (all other rows will be treated as data)

In the box above, you would select the range of data you want and where you want the pivot table to be placed (same worksheet or another page within the workbook). You will then access the PivotTable Field List where you can select the fields to add to your report. If you don’t see the Field List window pop up, be sure to click in the white box on the left (“Click in this area to work with the PivotTable report”) so that the list will come up on the right.

Now that your Field List table is up, you can see all the fields that are available to you. The bottom part displays the four areas of the pivot table where you can place the fields. There is also a drop down menu that allows you to manage the structure of the Field List.

Note: When you check a non-numeric field in the pivotTabel Field List, Excel automatically places it in the Row Labels area. If all the data in a field is numeric, it is first placed in the Values area and summed up.

Check out this guide for advanced Excel training or learn Excel formulas here.

Now that you have your basic pivot table set up, you can see how the table automatically generates the subtotals for you at the bottom. Currently, the Values tab is in the Column Labels field, and if we move it to the Row Labels field, it completely changes the structure of the table but gives us the same grand totals. It’s all up to you how you want to structure your pivot table.

You can also filter in a pivot table. By manually filtering row and column items, you are adding or deleting certain values in the pivot table automatically. All subtotals will be adjusted to reflect only the visible items. In Excel 2010, if there is more than one field in the Row Labels area of in the Column labels area, there will be only one filter displayed, so you will need to speicy which field you want the filter to work with. Hint: it is faster to un-check Select All and then check the one item you want filtered if you’re working with a large amount of labels.

Tip: You can clear all filters/sorts options using the Home, Sort & Filter, Clear, or Data, Clear command, as well as going into the Filter sub-menu. There you can see which filter has been applied and choose to keep or clear that filter. Try right-clicking the cell you would like to change the filter for and choose the filter command.

There are other filters that are available in Excel, such as the Label Filter dialog box which gives you many more advanced options to choose from using Contains, Begins, With, and Between logic.  The Value Filter are slightly more complex, applying to the Values area of the pivot table but only to those corresponding to the currently specified row label. Be more cautious working with this filter.

Now that you’ve learned about pivot tables, it just might be time to unlock some of the other features of this powerful program, and find out what else you can do. For a tutorial on Excel basics and principles, take a basic to advanced Excel course.