Excel Dynamic Range (Table of Contents)
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:
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.
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).
Drag until the required range. It will give the continuous series as per the first two numbers pattern.
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).
Drag until the required range. It will give the continuous series as per the first two numbers pattern.
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.
Drag until the required length. It will give the continuous series as per the first two numbers pattern.
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.
Give the name in the name box (highlighted in the screenshot).
Please 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.
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.
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.
- Using Excel table feature
- 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.
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 colour.
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 colour also.
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’.
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 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.
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
- 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.
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 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.
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.
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.
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
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 to automatically highlight the table range, 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.
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 –
