Pivot Tables built on top of Excel Tables recognise when new data is added without the need for you to resize your data source.
In an earlier post we showed you an Excel time saving tip where your pivot table’s data source became dynamic by using a named range as the source of the data. Whilst this works perfectly well you may feel you’d prefer an easier solution that doesn’t require you to know how an offset function works.
Did you know that if you convert your data into a Table (click anywhere in your data then via the ribbon menu go Insert > Table) then when you come to make a pivot table on this data the table’s name is referenced rather than some range that you’ve set. The example below shows this in more detail.
Excel Insert Table
In the above example we have some simple data that we might want to use in a pivot table. The usual method would be to insert a pivot table and the pivot table’s data source would be set as A1 to C35 by default. This is fine up until you add more rows or columns to your raw data. You’d now need to go back and edit it to be A1 to C36 and so on…All very annoying.
Before you make your pivot table though you could convert your data into an Excel Table, as shown in the above image.
Make Pivot Table on Table data
Now when you make a pivot table (Insert > Pivot Table) as above, you’ll see that the Table Range used for the table is Table1. This is a named range that refers to our table of data. If you opened up your name manager you’ll see it in there too (in [...]
The tip for Excel tutorial teaches you everything you need to learn about pivot tables.
Anyone using Excel to analyse data will at some point come across the Excel pivot table. A Pivot Table sits on top of raw data (a customer database for example) and lets you design a new table where you can view results. Then when the raw data is updated the changes are reflected in the pivot table you have designed.
What is a pivot table?
A Pivot Table is made up of many parts. Above is an image of a simple pivot table.
Pivot table field list – this shows you all the fields (metrics and dimensions) that are available. These will be all the columns in your raw data plus any calculated fields you have created Report Filter – here you drag fields that you want to use to be able to filter your data. In this example we have chosen to filter by date Column labels – here you list the fields that go along the top of your pivot table – the columns. These can be metrics or dimensions (city, country etc). In our example we have “Week” Row labels – here you list the fields that go along the left of your pivot table – the rows. These can be metrics or dimensions (city, country etc). In our example we have the values. The values are those in step 5 The Values – these are all the metrics. You may be counting, averaging or summing up any of the metrics you had in your raw data. In our example we have summed up Total Visits plus other metrics Filters – Number 6 in the diagram shows you how you can use filters to slice and dice your data. Maybe [...]
This Tip for Excel guide shows how to make Pivot Tables in Excel
What is a Pivot Table? How to make a Pivot Table What data should look like for a Pivot Table How to make a Pivot Table’s data range dynamic How to add calculated fields to Pivot Tables Formatting Pivot Tables Pivot Table Charts