Dynamic Tables In Excel

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

 

What Is Dynamic Tables In Excel?

Dynamic Tables in Excel are the tables where we add, or update new values in an existing dataset. As a result, the table readjusts itself w.r.t the size, also refreshing or modifying the linked generated reports and PivotTables with the changes in the datset.

We can create Excel Dynamic Tables with two different methods: making a table of the data from the Table section, and another using the OFFSET function.

Table of contents

How To Create Dynamic Tables In Excel?

There are two basic ways of using Dynamic Tables in Excel –

  1. Using TABLES.
  2. Using the OFFSET function.

Before you dive deeper into the concept through examples, you can explore more Excel functions and their usage in different scenarios through this Crash Course In Excel LOOKUP Function.

Grasping these principles is essential for anyone looking to excel in this area. For those who want to take their learning to the next level, this Excel power query course is designed to build on this foundation and enhance their expertise.

Examples

We will consider examples to create Dynamic Tables in Excel using the above-mentioned methods.

Example #1 – Using Tables to create Dynamic Tables in Excel

Using tables, we can build a Dynamic Table in Excel and base a pivot over the Dynamic Table.

We have the following data,

Dynamic Range with Data Tables example 1

When we create a pivot table with this data range from A1:E6, and then insert new data in dataset row 7, it will not reflect in the PivotTable.

So, we will first make a dynamic range. The steps are,

  1. We must first select the data, A1:E6.


    Dynamic Range with Data Tables example 1-1

  2. Now, in the “Insert” tab, we must click the “Table” under the “Tables” section.


    Dynamic Range with Data Tables example 1-2

  3. Next, we have to select the data. Then, in the “Insert” tab under the Excel “Tables” section, click on “PivotTable.”
     

    Dynamic table insert pivot table

  4. As a result, a dialog box will pop up, as shown below, then click “OK.”


    Dynamic Range with Data Tables example 1-5

  5. As our data has headers, we must remember to check the box “My table has headers” and click “OK.”


    Dynamic Range with Data Tables example 1-3

  6. Now, our dynamic range is created.


    Dynamic Range with Data Tables example 1-4

  7. As we have created the table, it takes a range as Table 2. Click on “OK,” and in the “PivotTable,” drag “Product” in rows and “Sales” in values.


    Dynamic Range with Data Tables example 1-6

  8. In the sheet where we have our table, we must insert another piece of data on the 7th.


    Dynamic Range with Data Tables example 1-7

    Refresh the pivot table.

    Dynamic Range with Data Tables example 1-8

    Our dynamic PivotTable has automatically updated the “Product 6” data in the PivotTable.

Example #2 – Using the OFFSET Function to create a Dynamic Table in Excel

We can also use the OFFSET Function to create Dynamic Tables in Excel. Let us have a look at one such example.

We have a price list for the products we use for our calculations.

Using OFFSET Function example 1

First, we must select the data and give it a name.

Using OFFSET Function example 1-1

Whenever we refer to the data set price list, it will take us to the data in the range B2:C7, which has our price list. But if we update another row to the data, it will still take me to the range of B2:C7 because our list is static.

The steps to use the OFFSET function to make the data range dynamic are,

#1 – Now, under the “Formulas” tab in the “Defined Range,” we must click on “Define Name”.

Dynamic Table in Excel - Define name

The “New Name” dialog box will pop up.

Using OFFSET Function example 1-2

#2 - We can type any name in the Name Box. We will use the “Product.” The scope is the current workbook, which refers to the current selected cell B2.

In “Refers to”, we must write the following formula:

=offset(Sheet2!$B$2,1,0,counta(Sheet2!$B:$B)-1,2)

=offset(

Using OFFSET Function example 1-3

#3 – Now, we must select the starting cell, which is B2.

Using OFFSET Function example 1-4

#4 – Now, we must type 1,0 as it will count how many rows or columns to go.

Using OFFSET Function example 1-5

#5 – Now, we need it to count the data in column B and use that as the number of rows so that we may use the COUNTA function and select column B.

Using OFFSET Function example 1-6

#6 – As we do not want the first row, the product header, to be counted, so (-) 1 from it.

Using OFFSET Function example 1-7

#7 – Now, the number of columns will always be two, so we must type “2” and click “OK.”

Using OFFSET Function example 1-8

#8 –This data range would not be visible by default, so to see this, we must click on Name Manager under the “Formula” tab and select “Product.”

Using OFFSET Function example 1-9

#9 – If we click on “Refers to,” it shows the data range,

Using OFFSET Function example 10

#10 – We will add another product -“Product 6.”

Using OFFSET Function example 11

#11 – Lastly, click on “Product Table” in the “Name Manager.” It also refers to the new data inserted.

Using OFFSET Function example 12

Like this, we can use the OFFSET function to make Dynamic Tables.

Important Things To Note

  1. The PivotTables based on dynamic range automatically gets updated when refreshed.
  2. Using the OFFSET function in “Defined Name” can be seen from the “Name Manager” in the “Formula” tab.

Frequently Asked Questions

1. What are the advantages of Dynamic Tables in Excel?

The advantages of Dynamic Tables in Excel are,
• In Excel when we create lists or data in a workbook and make a report out of it. But if we add, remove, move, or change the data, then the whole report can be inaccurate. Dynamic Tables help us with the same because whenever a list or data range is updated or modified, it ensures that it will change the report as per the data change.
• A dynamic range will automatically expand or contract as per the data change.
The PivotTables based on the Dynamic Table in Excel can be automatically updated when the pivot is refreshed.

2. What is the difference between a Dynamic Table in Excel and a Static Table?

As we considered in the article above, the Dynamic Tables take a structured reference, however, the Static Tables take cells or a cell range as a reference.

3. Why is the Dynamic Table in Excel not working?

The Dynamic Table in Excel may not work for the following reasons,
• The generated PivotTable did not get automatically refreshed once the data was modified in the dataset. Then, we can manually refresh the PivotTable.
• The named range table might be deleted or renamed. Ensure to keep the right Table linked throughout.

Download Template

This article must help understand Dynamic Tables in Excel with its formulas and examples. You can download the template here to use it instantly.

 

Recommended Articles

This article is a guide to Dynamic Tables in Excel. Here we generate PivotTables for dataset that updates when data is modified, OFFSET(), examples, Template. You may learn more about Excel from the following articles: -