Function Problems

How to Use a Dynamic Excel Range in MIN and MAX Formulas

Rana Garcia Oct 15, 2026 870 views

Question details

Calculate minimum and maximum values using a dynamic range that automatically adjusts when rows are added or removed.

How to Use a Dynamic Excel Range in MIN and MAX Formulas

Product: Excel
Device & OS: not provided
Scenario: A user needs their MIN and MAX formulas to automatically evaluate new data rows without having to manually update the cell references.
Observed behavior: Static range references like D4:D13 fail to include newly added rows unless manually updated, leading to inaccurate MIN and MAX calculations.

Before you start

Ensure your dataset does not have completely blank rows separating your data, and verify that your columns have clear headers before converting them to a dynamic table.

Solution 1Recommended

Convert Data to an Excel Table for Structured References

Converting your data into an official Excel Table is the most reliable way to make ranges dynamic. Table ranges automatically expand and contract.

Structured references use table and column names instead of standard cell addresses. When new data is added to the bottom of a Table, the structured reference automatically includes the new row in its calculations.

Select your data range

Click on any single cell within the dataset you want to calculate the MIN or MAX for.

Format as a Table

Press the keyboard shortcut Ctrl + T, or go to the Insert tab and click Table. Make sure 'My table has headers' is checked, then click OK.

Name your Table

Go to the Table Design tab on the ribbon. On the far left, rename your table to something recognizable, such as 'TableA'.

Enter your dynamic formula

Type your formula using the table name and header instead of a cell range. For example, use =MAX(TableA[Hdr2]) or =MIN(TableA[Hdr2]). The calculation will now update automatically as rows change.

Convert Data to an Excel Table for Structured References

Automatic Updates: Any rows appended directly below the table will automatically become part of 'TableA', meaning your MIN and MAX functions will instantly incorporate the new values.

Solution 2

Use the INDIRECT Function for Text-Based Dynamic Ranges

You can reference a cell that contains the desired range address as a text string, allowing you to change the range without modifying the MIN or MAX formula itself.

Effortless Data Management

Use WPS Spreadsheet to Handle Dynamic Formulas with Ease

WPS Office provides full compatibility with Microsoft Excel functions like INDIRECT and completely supports structured Table references. Easily manage expanding data ranges and calculate complex MIN/MAX values accurately.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your dataset.
2. Create a dynamic table: Highlight your data, navigate to the Insert tab, and select Table to format your dataset dynamically.
3. Input the formula: Type =MAX(TableName[ColumnName]) into your target cell to extract the highest value.
4. Expand your data smoothly: Type new data into the row immediately beneath your table. Watch your dynamic formula update the result without any manual adjustments.

100% compatible with Microsoft Excel formulas and table referencing formats.Free, lightweight, and incredibly fast, even with large datasets.Familiar user interface makes it easy to migrate and convert ranges to tables.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error?

The #REF! error occurs when the text string inside the INDIRECT function is not a valid cell reference. Check the cell you are referencing to ensure it contains a proper range address, like 'A1:A10', and has no typos.

Can I use the OFFSET function to create a dynamic range instead?

Yes, you can combine OFFSET with COUNTA to create a dynamic range (e.g., =MAX(OFFSET(A1,0,0,COUNTA(A:A),1))). However, like INDIRECT, OFFSET is a volatile function and can slow down your workbook if used excessively. Excel Tables are generally the more efficient solution.

Will structured table references work if I send my file to someone using WPS Office?

Yes. WPS Spreadsheet fully supports standard Excel table features and structured referencing. Formulas using table names and column headers will work seamlessly when opened in WPS Office.

Excel & Data Problems