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 Excel Dynamic Range
 

Excel Dynamic Range

Madhuri Thakur
Article byMadhuri Thakur

Updated May 15, 2023

Dynamic Range in Excel

 

 

Excel Dynamic Range (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • 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 whenever the new set of lines is appended to 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 and 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 the range:

Horizontal Range

Vertical Range

Uses of Dynamic Range

Suppose you must 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. Here range helps to make this task within 5 seconds. Let’s see how it will work.

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

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

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 2 and the second cell 4, it will provide all the even numbers. Select the range of two numbers as shown below and click on the corner (Marked in red color).

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 a range is; now, we will discuss the dynamic range. You can name 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, the sales chart will update automatically (Dynamically). This happens because we used dynamic range; hence, the chart takes the dynamic range and changes the chart accordingly.

Dynamic Range Example update

If the range is not dynamical, 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 the 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 the one below.

Dynamic Range Example 1

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

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

Dynamic Range Example 1-4

If you want to change the format(color), select the table and click on the top menu bar’s design button. You can choose 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 updates the data range dynamically into a table; therefore, 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 the required number 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 the offset format, 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 0 as we will not 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, provide 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 we have two columns here, and we do not want to add any additional columns here. If we are unaware of how many columns we will add in the future, then apply the ‘counta’ function to the entire row as we applied to height. Close the formula.

Example 2-6

We must apply the defined name’s offset formula 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 pop-up will come, giving the name of the 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 selecting the name you provided in the Define name.

Example 2-9

Here it is “Studenttable,” 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 checking 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

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 Range Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Dynamic Range Excel Template

EDUCBA

डाउनलोड Dynamic Range Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW