Pivot tables based on tables have better integrity, as tables automatically expand to incorporate new data that you append. You must still refresh the pivot table when you add data to the table, but you won't need to manually resize the source data range.
If you are creating dashboards based on pivot tables and you want the dashboards to be dynamic, then make sure you format the raw data as a table.
Any chart that you build based on a table will automatically be dynamic. (This method is not available for versions earlier than 2007. You can create dynamic charts but they have to be formula driven using the OFFSET function.)
How do I use the Table feature?
Since version 2007, Excel has had a feature called Format as Table (found in the Styles group of the Home Ribbon or on the Insert Ribbon -> Tables group as Insert Table).
The purpose of an Excel Table is to help you manage and analyse data. If you store your information in a consistent format Tables allow for easier sorting, filtering and formatting.
TIP: To insert an Excel Table
Select a cell anywhere in the data range you want converted to a table and press CTRL + T.
Once your table is inserted you will see that formatting is applied automatically and a new tab will appear on the ribbon called ‘Design’.
Turning your data into an Excel Table gives you some really useful features.
The header row remains in place
You don’t need to freeze the top row any more if you have formatted the data as a table. As you scroll down the header row will remain visible.
There are 61 different built in styles to change the format or you can create your own.
If you don’t want to work with your data in a Table but you like the instant formatting it provides, you can convert the table to a regular range while keeping the style formatting you applied.
You’ll find this option on the Design tab of the Ribbon, or simply right click anywhere in the table and select Table/Convert to Range.
Insert a Total Row
This gives you many more options to what you would get if you manually summed the list.
1. You can toggle the Total Row on and off and Excel will remember the functions that exist.
- Formulas within the Total Row only calculate the visible rows, making it easy to view real-time statistics as you filter the table. And any new rows added are automatically included in the total.
- The Total formulas use the SUBTOTAL function so that if you filter your table the Totals will only SUM the visible cells.
2. Table auto expands as soon as you type a new heading immediately after the last column. The formatting is copied down.
3. Enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.
4. Add rows and your formula will AutoComplete to fill the new rows.
5. Any changes you make to the formula will automatically be copied to the rest of the column.
6. The formula does not refer to cell references. Instead it uses what Excel calls “structured referencing” which is very similar to Named Ranges.
Structured references automatically update as you add or remove data. The name for the table is usually something like ‘Table1’ and the columns will have names based on the column headings in the table. If you don’t have any column headings Excel will automatically insert headings like Column1, Column2...and so on. These names can be changed (just type the new name in) and all references to those names will automatically change.