Make a pivot table even more useful by adding ‘data fields’

In Section 1 we tried out building sorted and grouped lists that can use your data to answer questions. But what else can a pivot table do? In this section we’ll look at how the ‘Data Field’ part of the pivot table works.

A quick task

Build a pivot table of the different types of snack again, as outlined in Section 1 above. This time however, we’ll add in a “Data Field” that will calculate how many of each type of snack there are:

• Your pivot table layout should look like the image below:

• After creating this pivot table, the data you get will look like this:

So, what’s happened?

The pivot table has grouped and summarised the data on the types of snacks, which you put into a Row Field. The data on the quantity of snacks – which you put in the Data Field – has been added up to create a total for each type of snack. Neat, huh? Let’s add in another Row Field, just as we did in Section 1, and see what it tells us:

• Bring up the secondary menu by right clicking on the pivot table, and select ‘edit layout’.

• Change the pivot table layout so it looks like the screenshot below:

• The data shown will change again. This time, the types of snack are sub-grouped by the sort of ingredient, along with the quantities:

A longer task

We can apply the same steps to the GRAIN dataset on landgrabbing to create more useful summary views of the data. For example, let’s find out how much land was reported as being acquired in each country:

• We won’t repeat in full the steps required to create a pivot table, but will show you the layout:

Note: in the image above, the tile in Row Fields is ‘Landgrabbed’. As noted above, the layout editor shortens it in an annoying manner. Hover your mouse over it to show the full fieldname.

• The effect is the same as with the example above in the short task. The data in the Row Field is summarised and grouped to show a list of countries, without duplicates. The data in the Data Field has been added up to give a total figure for each country. Here are some sample rows of what this pivot table will produce:

As before, we can continue to ask questions of the data by adding in different Row Fields. The data above shows the amount of land acquired in each country. Add in ‘Status of deal’ as a row field to refine this picture even further and show which deals are done, in process, proposed and so on.

• Again, here’s the layout of the pivot table:

• After creating the pivot table from the layout above, here are a few rows of the data it will generate for you:

Using your knowledge of choosing Row Fields, and now adding Data Fields, try creating pivot tables which show the following:

1. a little profile for each investor, showing the countries where they have acquired land, and the size of the land area they have acquired e.g. a pivot table that shows Adecoagro reportedly made deals in Argentina for 242000 ha, Brazil for 165000 ha and Uruguay for 8600 ha.
2. The total amount that that each investor has invested to acquire land e.g. this pivot table should show that Saxonian Estates reportedly made investments totalling USD 7.7 million.
3. The amount of land that has been acquired, organised by investment sector e.g. this pivot table will show that 160,000 ha have been acquired by investors that work in the telecommunications sector.
4. The amount of investment made, organised by the size of the land acquired, showing the country where the land was acquired e.g. the pivot table you make here should be able to quickly show us that land deals of 6000 ha were made in Australia for USD 335 million, in Russia for USD 39 million and in Nigeria where there is no record of the amount invested.

Bonus features: change which aspects of data are shown

The fields that you add to pivot tables have two useful features you should know about. We’ll provide a workthrough below, but here’s an overview:

1. The data that we have positioned in the Data Field of the pivot table is often just added up – that is, where there are multiple values they are added together to show the “sum”. However, the pivot table can show this data differently by:
   1. picking out the highest (the “max”) or lowest (the “min”) values from a list.
   2. giving a total of the number of values (the “count”).
   3. calculating the data as a percentage or running total.

   This feature is activated by double-clicking on any tile that you’ve dragged into any area of the pivot table layout editor.

2. As with the Row Fields, you can have more than one data field in a pivot table. This means you can display different aspects of the same data next to each other. To use it, just drag another fieldname into the Data Fields area.

Here’s an example pivot table layout that demonstrates both these features.

• This is the layout you’re aiming for:

• To get there, build your pivot table as usual. This time drag ‘Hectares’ into the Data Fields four times. You’ll see this:

• Next, change the way that the data are displayed. To do this, double click on one of the tiles you’ve dragged into Data Fields . A pop-up window will appear, like the one below:

• Choose an option from the list, then select OK.
• When you’ve done all four, the tiles in the Data Fields part of the layout will look like this below:

• After you’ve completed your layout, create the pivot table.<
• This pivot table will show four pieces of data for each country where land has been acquired: the number (or ‘count’) of deals where the amount of land is recorded, the largest acquisition (‘max’), the smallest acquisition (‘min’) and the total amount of land (‘sum’). Here’s a clipping from the pivot table which shows the entry for Argentina: