Dynamic Tables in Excel (Table of Contents)
Dynamic Tables in Excel
Dynamic Table is that table kind where we do have to update the range of data again and again. Dynamic Tables in Excel can be created by Pivot Table option. For this, select the complete data to be included in Dynamic Table and then click on Pivot Table option under Insert menu tab or else press short cut key ALT + N + V simultaneously to apply it. Then drag and drop the required fields into the relevant section to create a Dynamic Table. Now, if there are any changes in the source data, then we just have the refresh the pivot table.
Create Dynamic Range Using Excel Tables
If you have heard of excel tables and have not used it before then this is the article you need the most. Excel Tables are dynamic and allow us to interpret the data once the addition and deletion happen.
We have one more tool called as Data Tables which is a part of What-If-Analysis. So don’t get confused with it.
How to Create a Dynamic Tables in Excel?
There are two basic ways to create dynamic tables in excel – 1) Using Pivot Table and 2) Using Formulas
Dynamic Tables in Excel – Using Pivot Table
I have a sales table for the month of Jan. This sales data includes Date, Month, Sales Person, and Sales Value.
Now I want to get the total of each Sales Person by using a pivot table. Follow below steps to apply pivot table.
Step 1: Select the entire data.
Step 2: Select the pivot table from the Insert tab.
Step 3: Once the pivot is inserted drag and drop the Sales Person heading to Rows and Sales Value to Values.
Step 4: Now I got sales updates for the month of Feb. I pasted under Jan month sales data.
Step 5: If I refresh the pivot table it will not give me an updated report because the data range is only limited to A1 to D11.
This is the problem with the normal data ranges. Every time we need to change the data source range to update our reports.
Create a Dynamic Table to Reduce Manual Work
By creating tables we can make the data dynamic.
Step 1: Place the cursor anywhere in the Jan month sales data.
Step 2: Now press Ctrl + T, which is the shortcut key to insert tables. It will show you the below dialogue box. Make sure My table has headers checkbox is ticked.
Step 3: Click OK button it will create a table for you.
If you observe as soon as the cursor is placed anywhere in the data it will show you the new tab in the ribbon as Design.
Step 4: Under the Design tab give a name to your table. Give a name which is easy for you to understand.
Step 5: Now Insert a new pivot table to this table. (Apply previous technique) One beauty about this table is you need not select the entire data set rather you can place a cursor in the table and insert pivot table.
Step 6: Now add Feb month sales data to this table.
Step 7: Now go to Pivot Table and Refresh the pivot table. You can press ALT + A + R + A shortcut key.
Step 8: It will refresh the pivot table.
Now the pivot table started to show the new values including Feb month sales as well.
Dynamic Tables in Excel – Using Formulas
We can not only create a pivot table with a dynamic table but also apply to formulas as well. Now, look at the difference between the normal formula and dynamic table formula.
By applying SUMIF function to the normal data range I have received the total sales values of each sales person.
Now I will add Feb sales data to the list.
The formula still shows the old values.
Now I will apply the formula to the Dynamic Table.
If you observe the formula, there are no ranges to the formula. It has names in it. Let me break down the formula in pieces.
=SUMIF(SalesTable[Sales Person],F2,SalesTable[Sales])
SalesTable: This is the name of the table.
Sales Person: This is the name of the column we are referring to.
F2: This is the Cell reference of Sales Person names.
Sales: This is again the column we are referring to.
In excel tables once the table is created all the column headings becomes their references. Unlike in normal data range, we won’t see any kind of cell ranges. These are called Named Ranges which are created by the Excel Table itself.
Things to Remember About Dynamic Tables in Excel
- Ctrl + T is the shortcut key to create tables.
- All the headings refer to their respective columns.
- Even the individual cell in the column is referred by the heading of the column.
- We can create dynamic named ranges as well.
Recommended Articles
This has been a guide to Excel Dynamic Tables. Here we discuss how to create a dynamic table in excel using Pivot Table and Formulas along with practical examples and downloadable excel template. You can also go through our other suggested articles –
