Adding columns to pivot tables

In the previous sections, we looked at how to add row fields and data fields to your pivot tables. We also looked at how to sort and filter data in pivot tables, and how to adapt the display of data to pick out the largest and smallest values in a list. In this section, we’ll add the final basic component: Column Fields.

A quick task

After building nearly 30 pivot tables in this course, we’re sure you’re now getting the hang of this. The next step is to choose the data that can be a Column Field in your pivot table.

• Take as a starting point the pivot table you made about snacks in Section 2. Edit the layout. This time, drag the tile labelled ‘Risk to Health’ into the Column Fields area. It will look like this:

• After creating the pivot table, below is how the data in it will look with the new columns added:

The effect of adding the Column Field is to further sub-group the data. Here’s the original pivot table from Section 2, so you can see the difference:

The version that includes columns enables you to see at a glance which the high risk snacks are, what they are made of, and how many of them there are. Better avoid chocolate biscuits and cream cake!

A longer task

Returning to the GRAIN dataset, we can see how adding this final dimension affects how the data is shown.

1. Create a basic pivot table which shows how much land (‘Hectares’) has been acquired in each country (‘Landgrabbed’).
2. This time include the ‘Status of deal’ field in the Column Fields area of the pivot table layout editor:

The effect should be quite predictable for you by now. The pivot table will give an overview of the total amounts of land acquired for each country, broken down by the status of the deal:

The ‘Status of deal’ field is a fairly convenient field to add to the Column Fields area. When summarised by the pivot table it has only five distinct categories. This means it fits easily into the screen area! Something like ‘Production’, which has over 100 categories, would not be as easy to view.

Have a go at changing the layout of the pivot table whilst keeping ‘Status of deal’ as a column:

1. Replace the tile in the Row Fields with ‘Landgrabber’ (ie. the investor) and change the tile in Data Fields to ‘Projected Investment’ (ie. the amount paid for land). This shows how much money investors have tied up in done deals, deals that are signed, proposed and so on.
2. Replace the Row Fields with ‘Sector’ and the Data Fields with a count of the number of investors. We covered how to do this in Section 2’s Bonus Feature section. This pivot table will show the number and status of deals by the sector that the investor is most associated with.

Bonus features: standard filters

As we noted in Section 1, the sort and filter features of the spreadsheet work in pivot tables. Another useful feature that operates in pivot table data is the standard filter. We can use this to exercise far more control over what data is displayed in a worksheet, and in pivot tables. Let’s see how it works.

• Create this pivot table from the GRAIN data. It has ‘Landgrabbed’ and ‘Landgrabber’ as the Row Fields, ‘Status of deal’ as a Column Field, and a sum of the total size of deals (‘Hectares’) as the Data Fields:

• Click OK. The data it produces will be like this:

• In the resulting pivot table click on the tile called ‘Filter’ in cell A1. The Filter Criteria window will pop-up.
• Change the fields to make them look the same as the below. Then click ‘OK’ to apply this filter to the pivot table:

• This will filter the data to show only those deals that are equal to or larger than (>= in mathematical notation) 100,000 ha.
• The filter can be removed by opening the Filter Criteria window and selecting ‘none’ in the field name drop-down menu.