3 Guidelines to Simplify Your Excel PivotTables

PivotTables are one of the most useful of all tolls in Excel; however their use causes some concern with many users. This is due sin part because there appears to be no definite rules when building PivotTables. If you have let’s say 8 columns of data this will produce 8 PivotTable ‘fields’, but there are only 4 ‘field areas’ – so where do they all go?! The answer is that whilst there may be no definite rules for the construction of PivotTables, there are three very useful guidelines which help greatly when building them.

First Identify your Values

The first guideline is to initially isolate all of your ‘value fields’. These fields will almost always need to be placed into the value field area which is situated in the bottom right hand corner of the values pane. It doesn’t matter how many values there are as you can simply stack them on top of each other by clicking and dragging into the particular area. The order in which you stack these values will determine the sequence in which they appear from right to left in the actual PivotTable. The first value field will appear in column A with the next one down in the list appearing in column B and so on. In this way you can take care of many of your columns of data in one go.

Order your Rows & Columns

The second guideline concerns the row fields. As with value fields, the order in which you stack these will determine the sequence in which they appear within the PivotTable itself. One handy piece of advice is to consider the fields which are to be placed in rows and determine how many separate areas of data relate to each one. For instance if you have one field for months and another for quarters, there will of course only be 4 quarters as compared with 12 months. If you therefore click and drag the quarters onto the row area first, followed by the months, you will create a form of data hierarchy which will make filtering and analysis somewhat easier.

Set up Report Filters

The third guideline is in respect of the ‘Report filter’. Bringing columns into this field creates a filter which sits outside of the PivotTable itself. This enables us to filter all of the date within the PivotTable in one action. What can be useful about the Report filter is that because it is located outside of the main table, we can bring multiple fields into this area that we might otherwise struggle to find a logical place for. Therefore, any fields that you have left over after laying out the basic PivotTable can be brought into the report field area, providing you with enhanced filtering capability.

PivotTables are an immensely useful tool in Excel, but many are put off using them due to the amount of data in their spreadsheet. The hope is that this article might encourage more people to experiment with them and eventually include them in their daily office work .