Excel Dynamic Range (Table of Contents)
Dynamic Range in Excel
Dynamic Range in excel allows us to use 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 static range where value cells are fixed and here with 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 Formula tab under defined names as Name Manager.
Below are the examples for range:
Uses of Dynamic Range
Let’s assume you have to give 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. If 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 below screenshot, here I give the example only for 10 rows.
Give the first two numbers under the index number and select the range of two numbers as shown below and click on the corner (Marked in red color).
Drag until the required range. It will give the continues series as per first two numbers pattern.
If we give the first cell as 2 and 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 color).
Drag until the required range. It will give the continues series as per first two numbers pattern.
The same will apply for months, Days and dates etc…Provide the first two values of the required pattern, highlight the range then click on the corner.
Drag until the required length. It will give the continues series as per first two numbers pattern.
Hope you understand what is range, now we will discuss the dynamic range. You can give a name to a range of cells by simply selecting the range.
Give the name in the name box (highlighted in the screenshot).
Select the Name which we added and press enter.
So that when you select that name the range will pick automatically.
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.
In May month when we update the sales of May below the Apr month data the sales chart will update automatically (Dynamically). This is happening because the range we used is dynamic range hence the chart taking the dynamic range and making changes in the chart accordingly.
If the range is not a dynamical range, then the chart will not get an update automatically when we update the data to the existing data range.
We can achieve the Dynamic range in two ways.
- Using Excel table feature
- Using Offsetting entry
How to Create Dynamic Range in Excel?
Dynamic range in Excel is very simple and easy to correct. Let’s understand how to Create Dynamic range with some Examples.
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.
Select the entire data range and click on the insert button at the top menu bar marked in red color.
Later click on the table below the insert menu.
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’.
Then the data format will change to table format you can observe the color also.
If you want to change the format(color), select the table and click on the design button at the top menu bar. You can select the required formats from the ‘table styles’.
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.
A line Chart is added as shown below.
If you add one more data to the table i.e Jun and sales as 100 then the chart is automatically updated.
This is one way of creating 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 offsetting formula. Below is the data sample of student table for creating a dynamic range.
To explain you the format of offset I am giving the formula in the normal sheet instead of applying in the “Define name”.
Offset entry format
- 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, here student is the reference cell where you want to start a data range.
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.
Now give the columns as 0 as we are not going to consider the columns here.
Now give the height of rows as we are not sure how many rows we are going to add in future hence give the ‘counta’ function of rows (select the entire ‘A’ column). But we are not treating the header in to range so reduce by 1.
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 not aware how many columns we are going to add in future then apply ‘counta’ function to entire row as how we applied to height. Close the formula.
Now we need to apply the offset formula in define 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.
A popup will come, give the name of table range as ‘studenttable’ 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
Now we can check the range by clicking on the Name manager and select the Name you provided in the Define name
Here it is “Studenttable” and then click on the formula so that the table range will highlight automatically as you can observe in the screenshot.
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.
Hope you understand how to work with 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 make sure every one updating 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 Meaning of Range and how to Create Dynamic Range in Excel with examples and downloadable excel templates.
You may also look at these useful functions in excel –
