Best Practice: Always create a Table before creating Pivot Table

Pivot Table is based upon the current block of data.Sooner or later you are going to update the data.

Creating a table eliminates the need for refreshing the new range manually.

Estimated reading time 4 min.

The need

You need one block of data while creating a pivot table. Invariably, the data is bound to be updated at some point of time in the future.

Adding more data does NOT update the source range in Pivot Table. Therefore, we have to go to the Pivot Table Analyze tab (earlier known as the Options tab) to update the range.

You must remember to update the data. If you forget, you will see outdated and wrong results.

Even if you update the range, it is cumbersome if large amount of data is added.

Here is a simple solution to the problem.

Create Table first

I have covered tables in many articles in the past. Here are the references:
Excel Tables
Copy formulas automatically and accurately (even with blanks)

The idea is that any piece of raw, tabular data must be a table before you do anything else with it. Whether you want to calculate, create pivot, create charts, whatever… it must be a table first.

Creating a table is easy – just click anywhere inside the data and create a table.

Usually this works fine. However, you have to be careful.

Precautions to take while creating a table

Often, the data has a title in the first row.

Clicking inside the data and choosing Insert – Table forces Excel to select the range. If the title is stuck to the tabular data, the title will get selected and the remaining columns will get generic names like Column1, Column2, Column3, etc..

To avoid this, check if there is a title and insert a row after the title. This way, the selection will work appropriately.

Grand Total at the end

Similarly, we have a problem with the end of the data. Often the tabular data has a grand total at the end. It is difficult to notice it because we don’t scroll to the bottom usually. But you must.

If there is a grand total row, add a blank row above it so that the table selection does not included it.

If you miss this step, the grand total row will become a part of the table and interfere with all the analysis in Pivot Table.

Give a name to the table

Although this is not technically required, it is a very good practice. Any table gets a generic name like Table4, Table24 or something like that.

But it is a good idea to specify a more legible name to the Table. Just click Table Tools – Design. The left most option is Table Name. Specify a good table name.

Table names are unique within a workbook. There are some simple rules you have to follow while choosing the name.

Where can you use the name?

In many places…

Navigate to this data easily

Open the name dropdown and choose the name.

Or press CTRL G (or F5) to open the GoTo dialog. In either case the names of tables are visible.

 

Now create the Pivot Table

Click anywhere inside the table and choose Insert – Pivot Table.

Now the name of the table will be picked up. No explicit range will be chosen.

How to update the pivot table after adding more data?

When you add more data below the existing table, the table expands automatically. It also informs the pivot table that the range has expanded.

This eliminates the need for you to remember and update the Pivot Table Source.

However, one action must be performed – Right click in the Pivot Table and choose Refresh.

Refresh does not happen automatically. Why? Because data may be added in many different ways. You can add data row by row by typing as well.

Creating and refreshing a Pivot Table is a time consuming activity. Excel does not want to slow down your data entry by refreshing the Pivot table WHILE you are adding or updating data.

Therefore, Refresh must be done manually.

What about pivots created with non-table data?

I am sure many of your Pivot Tables are working on raw data which is NOT a table.

No problem. Check that the pivot source range is correct, go to the raw data and create a table.

The Pivot Table source range will NOT use table name in such cases. It will still be a hard-coded range.

But do not worry. When you add more data at the end of the table (or even the right side of the table), it will update the source range of Pivot Table.

Summary

When you are about to click on Pivot Table, look at the Table icon – it should be inactive!

This means that your cursor is already inside a table. Which is good.

I will cover recommended Pivot Tables in a separate article. It is a new feature of Office 2013.

 

Enjoy!