Blog

Dynamic Ranges

Dynamic ranges in Excel are essential for creating flexible and scalable data analysis tools. They allow you to automatically adjust your data range when new data is added, ensuring that your charts and tables are always up to date.

Use OFFSET and INDIRECT to Create Dynamic Ranges for Charts and Tables

Creating dynamic ranges can be achieved using the OFFSET and INDIRECT functions. These functions help you create ranges that automatically expand or contract based on the data available, making your charts and tables dynamic.

OFFSET Function

The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. You can define the size of the range as well.

INDIRECT Function

The INDIRECT function returns the reference specified by a text string. It can be used to create dynamic references that change based on the contents of other cells.

Example

Below is an example demonstrating how to use the OFFSET and INDIRECT functions to create dynamic ranges for a table.

MonthSales
January100
February120
March130
April140
May150
June160
July170
August180
September190
October200
November210
December220

Exercise

Create a dynamic chart using the data in the table. Follow these steps:

  1. Use the OFFSET and INDIRECT functions to create dynamic ranges for the sales data.
  2. Create a chart that uses these dynamic ranges.

Solution

In the embedded sheet, the formulas for the dynamic sales ranges are provided in the third and fourth columns. These formulas adjust automatically as new data is added to the "Sales" column.

Explanation

  1. OFFSET Function:
    • The formula =OFFSET(B2,0,0,COUNTA(B:B)-1,1) starts at cell B2 and expands down based on the count of non-empty cells in column B, minus one (to exclude the header).
  2. INDIRECT Function:
    • The formula =INDIRECT("B2:B" & COUNTA(B:B)) creates a reference to the range starting from B2 to the last non-empty cell in column B. It constructs the range as a text string and converts it to a reference.

Using these dynamic ranges in your chart ensures that the chart updates automatically as new sales data is added.

Please enable JavaScript in your browser to complete this form.
How would you rate this post?
Do you think adding a video explanation would be useful?
Your Excel Skill Level