EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Dynamic Tables in Excel
 

Dynamic Tables in Excel

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 16, 2023

Dynamic Tables in Excel

 

 

Dynamic Tables in Excel (Table of Contents)

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

Dynamic Tables in Excel

Dynamic Table is the Table where we have to update the range of data repeatedly. The Pivot Table option can create dynamic Tables in Excel. For this, select the complete data to be included in Dynamic Table and then click on the Pivot Table option under the Insert menu tab or press the shortcut ALT + N + V simultaneously to apply it. Then drag and drop the required fields into the relevant section to create a Dynamic Table. We have to refresh the pivot table if there are any changes in the source data.

Create Dynamic Range Using Excel Tables

If you have heard of Excel tables and have not used them 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 another tool called Data Tables, which is a part of What-If-Analysis. So please don’t get confused with it.

How to Create Dynamic Tables in Excel?

There are two basic ways to create dynamic tables in Excel – 1) Using Pivot Table and 2) Using Formulas.

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

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.

Dynamic Tables Example 1-1

Now I want to get the total of each Sales Person by using a pivot table. Follow the below steps to apply the pivot table.

Step 1: Select the entire data.

Dynamic Tables Example 1-2

Step 2: Select the pivot table from the Insert tab.

PivotTable Example 1-3

Step 3: Drag and drop the Sales Person heading to Rows and Sales Value to Values once the pivot is inserted.

Dynamic Tables Example 1-4

Step 4: Now I got sales updates for the month of Feb. I pasted it under Jan month sales data.

Dynamic Tables Example 1-5

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.

Dynamic Tables Example 1-6

This is the problem with the normal data ranges. We must change the data source range to update our reports every time.

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.

Example 1-7

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.

My table has headers Example 1-8

Step 3: Click the OK button; it will create a table for you.

Dynamic Tables Example 1-9

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.

Dynamic Tables Example 1-10

Step 4: Under the Design tab, gives a name to your Table. Give a name that is easy for you to understand.

SalesTable Example 1-11

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; instead, you can place a cursor in the Table and insert a pivot table.

Dynamic Tables Example 1-12

Step 6: Now add Feb month sales data to this Table.

Dynamic Tables Example 1-13

Step 7: Now go to Pivot Table and Refresh the pivot table. You can press the ALT + A + R + A shortcut key.

Refresh pivot table Example 1-14

Step 8: It will refresh the pivot table.

Dynamic Tables Example 1-15

Now the pivot table shows the new values, including Feb month sales.

Dynamic Tables Example 1-16

Dynamic Tables in Excel – Using Formulas

We can also create a pivot table with a dynamic table and apply it to formulas. Now, look at the difference between the normal and dynamic table formulas.

By applying the SUMIF function to the normal data range, I have received the total sales values of each salesperson.

SUMIF Formula Example 2-1

Now I will add Feb sales data to the list.

Dynamic Tables Example 2-2

The formula still shows the old values.

Dynamic Tables Example 2-3

Now I will apply the formula to the Dynamic Table.

Dynamic Tables Example 2-4

If you observe the formula, there are no ranges to the formula. It has names in it. Let me break down the formula into pieces.

=SUMIF(SalesTable[Sales Person],F2,SalesTable[Sales])

  • SalesTable: This is the name of the Table.
  • Sales Person: This is the column’s name we are referring to.
  • F2: This is the Cell reference of Sales Person names.
  • Sales: This is again the column we are referring to.

Once the Table is created, all the column headings become their references in Excel tables. Unlike in normal data ranges, we won’t see any cell ranges. These are called Named Ranges, 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.
  • We use the column heading to refer to the individual cell within that column.

Dynamic Tables

  • 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Tables in Excel
  2. Lookup Table in Excel
  3. Pivot Table Formula in Excel
  4. Excel Merge Two Tables

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Dynamic Tables Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Dynamic Tables Excel Template

EDUCBA

डाउनलोड Dynamic Tables Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW