Understand PivotTable Structure

Understanding Pivot Table Structure

Starting with the Pivot Table I created in the Creating a Quick Pivot Table video, let’s look more closely at our options for which fields go where and what that means for your Pivot Table.

Before we start, let’s look at the data involved – here’s the Food Sales Data worksheet, which stores information about the sales of different type of food in different regions around the US. It includes quantities sold and total sales dollars, and the foods are broken down by Product and Category, so, for example, Potato Chips are snacks, and Chocolate Chips are cookies.

Next, let’s look at the simple Pivot Table I made from that data, placing Category in the Filter box, City in Rows, and Total Sales in the Values box.

The resulting Pivot Table showed us total sales of our 4 food categories by City, and we can either see all four of the categories, or we can choose to see just one, two, or three of them instead.

Now, let’s look at our 4 Pivot Table Fields panel boxes, and how what we put in each box determines the layout and usability of our PivotTables.

First, the Filter box is where you put the field or fields that are the main focus of the Pivot Table. When my focus was on Category, I put that field in that box so I could see everything from that perspective. We sell 4 types of products, and how are they selling?

Second, I put City in the Rows box, because I wanted a vertical list of the cities selling our products. Had I placed the field in the Columns box, the list would be horizontal, as you can see when I move the field from Rows to Columns.

Third, I put Total Sales in the Values box because it’s the field I want to look at from a quantitative perspective. You put number and currency data in the Values box, so you can Sum it, which is the default function applied. If you want to choose a different function, click the drop list in that box and choose Value Field Settings, and from there, as shown in the dialog box, you can pick a different function – such as Average, Count, Mix, Max, and so on. There’s also a button for formatting the numbers, so that if they weren’t stored as currency in your data range but you want them to show as such in the Pivot Table, you can apply that formatting.

You can add as many numeric fields as you want to your Values box – I can add, for example, Quantity Sold, and see a new column appear showing that number for each City.

Now – this all seems rather regimented, and while it is, to some extent – Excel needs to maintain a tabular, organized structure – you don’t have to use all of the boxes in the Pivot Table Fields panel.

For example, if I move Category out of Filter and put it in the Rows box, I get a tiered effect – I can see each Category, and then for each one, the 4 Cities are repeated, each showing their total sales and quantity sold.

I can then add Product to the Filter box and choose which product or products to see in each Category and City.

And then I can move Product to Rows as well, and create another tier, or level, of perspective on my data.

I can also drag one or more of those fields out of Rows and put it in Columns, to create a different view. I’ll advise you to do this only with a field that has many duplicates and therefore only a few columns to create. If I sold food in 100 cities, that would be an insane way to view my data. But it works just fine with 5 or 10 possible values – so if I did sell in a lot of cities, Product might be one to add to Columns.

The order of your fields in the Filter and Rows boxes is important – always remember that the data in these boxes is automatically sorted, so the field appearing first is creating the first level of sorting. This should be a field with a lot of duplicate values, and thus a lot of groups. The second level can have fewer duplicates, but if you have a third field, the second one can’t be a field that’s all unique values, like names – of people or products.

So the order I have here – Category, Product, and then City, works well – going from the field with the greatest number of duplicates to the one with the fewest.

As you experiment with moving fields around in the Pivot Table Fields panel, dragging them into and out of the boxes – and back to the field list if you no longer want them in the Pivot Table – there’s no harm to your data at all. The data is unaffected – it’s just being referred to in the Pivot Table.

Oh, and if your data has changed since you started your Pivot Table? Just click the Refresh button on the Pivot Table Analyze tab, and you’ll be referencing the latest version of your list.