Dynamic Tables in Excel (Table of Contents)

• Dynamic Tables in Excel
• How to Create a Dynamic Tables in Excel?
• Using Pivot Table
• Using Formulas

Dynamic Tables in Excel

In Excel often we struggle with updating reports as the data increases over a period of time. This is the issue of any new user in excel. Every month we maintain a sales Table for our reporting purpose. Every month creating new reports is a time-consuming process.

As the data increases our charts, pivot tables become out of date due to our static data structure. Every time the main data sheet changes we need to correct all the reporting formulas by changing the ranges of the data source.

For example: Let us say last month our data range was from A1 to C100. In this month it has increased from A1:C100 to A1:C200. If you have already applied pivot table or any other formula it is working for last month data range i.e. until C100 cell, but what about the updated data source?

It takes a considerable amount of time to update all your pivot tables, formulas, charts etc.… and kills your productivity level. One more important thing is we are humans who are bound to make mistakes even after all the scrutiny, so chances of making errors are very high.

However, you need not worry anymore because we can create a dynamic data table in excel which can update our reports by refreshing our pivot tables.

We can create a dynamic data table in excel by using multiple ways. In this article, I will show you some of them in detail. Follow this article to learn this technique.

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.

You can download the Dynamic Tables Excel Template here – Dynamic Tables Excel Template

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 –

1. How to Create Tables in Excel?
2. Create Histogram Chart in MS Excel
3. Scatter Chart in Excel
4. How to Use SUMIF Function in Excel?

The post Dynamic Tables in Excel appeared first on EDUCBA.