Pivot tables are a powerful data analysis tool that you can use to get insights into the data that you are working with. To create pivot tables, you need to first of all organize your data into clear rows and columns with labels that make sense for the data that you are working with, so that you can tell which rows you are manipulating at any given time.

Let’s take, for example, the idea of a martial arts club that has a lot of members. You might store the join date, town of residence, age, gender, and membership tie (monthly price) of each member, and also track whether they are adults or children (age group) for insurance purposes. You can use a pivot table to extract information about your overall membership.

Making a Pivot Table

Once you have the data in spreadsheet format, you can use the pivot table tool to make a table. Excel has a fairly powerful wizard which will recommend pivot table layouts to you. To access the tool, just select any cell in your source table, and then find the Insert tab in the top ribbon. From Tables, click ‘Recommended Pivot Tables.

Excel will show you suggestions for tables, such as “Sum of Membership Tier by Age Group” or “Sum of Membership Tier by Gender. There could be several options, and you will be able to get previews of each one.

Once you have found the basic table that you want to work with, you can modify it to access more information or to remove extra, unnecessary information. The new table will be created as an additional sheet in your workbook. If you select a cell inside that pivot table then the Pivot Table Field List will appear. You can check and uncheck boxes to add or remove fields from the table. Use the Filters and Columns section to alter the information that you show in the pivot table.

For example, you could filter by Age Group, and have a list of the different ages of members, with a count of the number of people of that age. This would allow you to discover how many ten year olds are members of the club. You could also set up filters for age and gender, and add a value for ‘count of gender’ to find out how many adult women there are, and how many young girls train too.

Tweaking Your Tables

It can take a little while to get used to the settings in pivot tables. The wizard is a good starting point, but you will probably want to make some changes, such as making sure that Count and Sum are used appropriately (sometimes Excel erroneously tries to Count columns where Sum would be more appropriate). You may also want to change the headings in the table too, since Excel prefixes them with “Sum of ‘Row Name'” or “Count of ‘Row Name'”, which can look unsightly and is typically not necessary.

Common Pivot Table Issues

Excel makes it easy to make a pivot table, but there are a few things that can go wrong with them. One of the most annoying issues is if you change the data that is in the main spreadsheet, the pivot table doesn’t update itself. The good news is that this is actually fairly easy to fix.

To refresh the pivot table as a one-off, right click on a cell in the pivot table and select Refresh. This will re-load the data from the main sheet in the spreadsheet.

If you want the sheet to automatically refresh as required, try the following:

Right click on a cell inside the pivot table and click on PivotTable Options.
Look for the tab titled Data in the Options window that appears
Check the box that says ‘Refresh Data When Opening the File’
Click OK

Updating The Range of a Pivot Table

Sometimes, you might need to increase the amount of data in the table because the range has changed. You can do this by selecting the Options tab from the ribbon at the top of the screen, and then selecting ‘Change Data Source’ from the Data Group. This will show you a window that lets you edit the table or range, including more columns or data as necessary.

Once you master working with pivot tables you will find that they are very flexible and powerful, and that you can do a lot with them. They are easier than most people think to use, and you will have the chance to impress your boss with your technology skills. Practice them today, so that you don’t find yourself caught out in the future working with a fussy data set. It’s much easier to troubleshoot the quirks of pivot tables on a small set of data, when you are not in a rush!