EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Dynamic Range
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

Excel Dynamic Range

By Madhuri ThakurMadhuri Thakur

Dynamic Range in Excel

Excel Dynamic Range (Table of Contents)

  • Dynamic Range in Excel
  • Uses of Dynamic Range
  • How to Create a Dynamic Range in Excel?

Dynamic Range in Excel

Dynamic Range in excel allows us to use the newly updated range always whenever the new set of lines are appended in the data. It just gets updated automatically when we add new cells or rows. We have used a static range where value cells are fixed, and herewith a Dynamic range, our range will change as well add the data. This can be done in any function in excel. And for this, we have an option in excel available in the Formula tab under defined names as Name Manager.

Below are the examples for range:

Horizontal Range

Start Your Free Excel Course

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

Vertical Range

Uses of Dynamic Range

Let’s assume you have to give an index number for a database range of 100 rows. As we have 100 rows, we need to give 100 index numbers vertically from top to bottom. Suppose we type that 100 numbers manually; it will take around 5-10 minutes of time. Here range helps to make this task within 5 seconds of time. Let’s see how it will work.

We need to achieve the index numbers as shown in the below screenshot; here, I give an example only for 10 rows.

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,725 ratings)

Range 1

Give the first two numbers under the index number, select the range of two numbers as shown below, and click on the corner (Marked in red colour).

Range 1-1

Drag until the required range. It will give the continuous series as per the first two numbers pattern.

Range 1-2

If we give the first cell as 2 and the second cell as 4, then it will give all the even numbers. Select the range of two numbers as shown below and click on the corner (Marked in red colour).

Range 2

Drag until the required range. It will give the continuous series as per the first two numbers pattern.

Range 2-1

The same will apply for months, days, dates etc.…Provide the first two values of the required pattern, highlight the range, and then click on the corner.

Range 3

Drag until the required length. It will give the continuous series as per the first two numbers pattern.

Range 3-1

I hope you understand what it is a range; now, we will discuss the dynamic range. You can give a name to a range of cells by simply selecting the range.

Dynamic Range 1

Give the name in the name box (highlighted in the screenshot).

Dynamic Range 1-1

Please select the Name which we added and press enter.

Dynamic Range 1-2

So that when you select that name, the range will pick automatically.

Dynamic Range 1-3

Dynamic Range in Excel

Dynamic range is nothing but the range that picked dynamically when additional data is added to the existing range.

Example

See the below sales chart of a company from Jan to Apr.

Dynamic Range Example

When we update May below the Apr month data’s sales in May month, the sales chart will update automatically (Dynamically). This is happening because the range we used is dynamic range; hence, the chart takes the dynamic range and changes the chart accordingly.

Dynamic Range Example update

If the range is not a dynamical range, then the chart will not automatically update when we update the data to the existing data range.

We can achieve the Dynamic range in two ways.

  1. Using Excel table feature
  2. Using Offsetting entry

How to Create a Dynamic Range in Excel?

Dynamic range in Excel is straightforward and easy to correct. Let’s understand how to Create a Dynamic range with some Examples.

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

Dynamic Range in Excel Example #1 – Excel Table feature

We can achieve the Dynamic range by using this feature, but this will apply if we use the excel version of 2017 and the versions released after 2017.

Let’s see how to create now. Take a database range like below.

Dynamic Range Example 1

Select the entire data range and click on the insert button at the top menu bar marked in red colour.

Dynamic Range Example 1-1

Later click on the table below the insert menu.

Dynamic Range Example 1-2

The below-shown pop up will come; check the field ‘My table has header’ as our selected data table range has a header and Click ‘Ok’.

Dynamic Range Example 1-3

Then the data format will change to table format; you can observe the colour also.

Dynamic Range Example 1-4

If you want to change the format(colour), select the table and click on the top menu bar’s design button. You can select the required formats from the ‘table styles’.

Dynamic Range Example 1-5

Now the data range is in table format; hence whenever you add new data lines, the table feature makes the data range update dynamically into a table; hence the chart also changes dynamically. Below is the screenshot for reference.

Click on insert and go to charts and Click on Line chart as shown below.

Dynamic Range Example 1-6

A-Line Chart is added, as shown below.

Dynamic Range Example 1-7

If you add one more data to the table, i.e. Jun and sales as 100, then the chart is automatically updated.

Dynamic Range Example 1-8

This is one way of creating a dynamical data range.

Dynamic Range in Excel Example #2 – Using the offsetting entry

The offsetting formula helps to start with a reference point and can offset down with the required number of rows and right or left with required numbers of columns into our table range.

Let’s see how to achieve dynamic range using the offsetting formula. Below is the data sample of the student table for creating a dynamic range.

Dynamic Range Example 2

To explain to you the format of offset, I am giving the formula in the normal sheet instead of applying it in the “Define name”.

Offset entry format

Dynamic Range Example 2-1

  • Reference: Refers to the reference cell of the table range, which is the starting point.
  • Rows: Refers to the required number of rows to offset below the starting reference point.
  • Cols: Refers to the required number of columns to offset right or left to the starting point.
  • Height: Refers to the height of the rows.
  • Width: Refers to the Width of the columns.

Start with the reference cell, where a student is the reference cell where you want to start a data range.

Example 2-2

Now give the required number of rows to go down to consider into the range. Here ‘1’ is given because it is one row down from the reference cell.

 Example 2-3

Now give the columns as 0 as we are not going to consider the columns here.

Example 2-4

Now give the height of rows as we are not sure how many rows we will add in the future; hence, give the ‘counta’ function of rows (select the entire ‘A’ column). But we are not treating the header into range, so reduce by 1.

Example 2-5

Now in Width, give 2 as here we have two columns, and we do not want to add any additional columns here. In case if we are not aware of how many columns we are going to add in future, then apply the ‘counta’ function to the entire row as how we applied to height. Close the formula.

Example 2-6

Now we need to apply the offset formula in the defined name to create a dynamic range. Click on the “formula” menu at the top (highlighted).

Click on the option “Define Name “ marked in the below screenshot.

Example 2-7

A popup will come, give the name of table range as ‘student table without space. ‘Scope’ leave it like a ‘Workbook’ and then go to ‘Refers to’ where we need to give the “OFFSET” formula. Copy the formula which we prepared priory and paste in Refers to

Example 2-8

Now we can check the range by clicking on the Name manager and select the Name you provided in the Define name.

Example 2-9

Here it is “Studenttable”, and then click on the formula to automatically highlight the table range, as you can observe in the screenshot.

Example 2-10

If we add another line item to the existing range, the range will pick automatically. You can check by adding a line item and check the range; below is the example screenshot.

Example 2-11

I hope you understand how to work with the Dynamic range.

Advantages of Dynamic Range in Excel

  • Instant updating of Charts, Pivots etc…
  • No need for manual updating of formulas.

Disadvantages of Dynamic Range in Excel

  • When working with a centralized database with multiple users, update the correct data as the range picks automatically.

Things to Remember About Dynamic Range in Excel

  • Dynamic range is used when needing updating of data dynamically.
  • Excel table feature and OFFSET formula help to achieve dynamic range.

Recommended Articles

This has been a guide to Dynamic Range in Excel. Here we discuss the Meaning of Range and how to Create a Dynamic Range in Excel with examples and downloadable excel templates. You may also look at these useful functions in excel –

  1. How to Use the INDIRECT Function in Excel?
  2. Guide to HLOOKUP Function in Excel
  3. Guide to Excel TREND Function
  4. Excel Forecast Function -MS Excel
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
0 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, 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 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 Range Excel Template

EDUCBA

Download Dynamic Range Excel Template

EDUCBA

डाउनलोड Dynamic Range 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