EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Dynamic Tables in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Dynamic Tables in Excel

By Jeevan A YJeevan A Y

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 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. If there are any changes in the source data, we just have to 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.

Start Your Free Excel Course

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

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 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: Once the pivot is inserted, drag and drop the Sales Person heading to Rows and Sales Value to Values.

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. 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.

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 rather;, 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 started to show the new values, including Feb month sales as well.

Dynamic Tables Example 1-16

Dynamic Tables in Excel – Using Formulas

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

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,547 ratings)

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 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 become 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 to by the heading of the 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 along with practical examples and 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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Dynamic Tables Excel Template

EDUCBA

Download Dynamic Tables Excel Template

EDUCBA

डाउनलोड Dynamic Tables Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more