Formula Errors

How to Use INDIRECT to Exclude Rows 1-3 in Excel and Fix #NAME? Errors

Phi Hung Vo Oct 20, 2026 868 views

Question details

The user wants to use the INDIRECT function to reference a data range starting from row 4, intentionally excluding the first three rows, but is encountering a #NAME? error.

How to Use INDIRECT to Exclude Rows 1-3 in Excel and Fix #NAME? Errors

Product: Microsoft Excel
Device & OS: not provided
Scenario: Referencing a specific data range that skips header or introductory rows using the INDIRECT function.
Observed behavior: The formula returns a #NAME? error, preventing the spreadsheet from correctly calculating or referencing the desired data range.

Before you start

Ensure your Excel language settings correspond to the formula separators you are using (commas vs. semicolons) and that the function name 'INDIRECT' is spelled correctly.

Solution 1Recommended

Correct the Formula Syntax to Resolve the #NAME? Error

The #NAME? error primarily occurs when the text inside the INDIRECT function is not properly enclosed in quotation marks, causing Excel to interpret it as an undefined variable.

The INDIRECT function converts a text string into a valid cell reference. If you want to skip rows 1 through 3 and reference starting at row 4, you must wrap the static part of the range in double quotes.

Select the error cell

Click on the cell displaying the #NAME? error to make it active, then place your cursor inside the Formula Bar at the top.

Add quotation marks to the reference

Ensure that the range you are trying to reference is wrapped in double quotes. For example, type =INDIRECT("A4:A100") instead of =INDIRECT(A4:A100).

Fix concatenated variables

If you are dynamically building the row number using other formulas or cells, ensure you use the ampersand correctly. Type =INDIRECT("A" & 4 & ":A100").

Apply and calculate

Press Enter on your keyboard to apply the corrected formula. The #NAME? error should disappear, displaying your targeted values.

Correct the Formula Syntax to Resolve the #NAME? Error

Formula check: Always ensure that the text string evaluated by INDIRECT points to a valid range, otherwise you may run into a #REF! error.

Solution 2

Fix Worksheet Name References for External Sheets

When your INDIRECT formula references a range on a different worksheet, missing single quotes around a sheet name with spaces will trigger an error.

Advanced Formula Processing

Use WPS Spreadsheet for Seamless Formula Handling

WPS Spreadsheet offers high-performance processing for complex formulas like INDIRECT. It provides accurate error checking and hints to help you easily manage dynamic ranges while avoiding #NAME? or syntax errors.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you want to exclude rows 1-3.
2. Enter the formula: Select your target cell, type =INDIRECT("A4:A100"), and press Enter.
3. Use the error tracing tool: If a syntax error occurs, click the small warning icon next to the cell to view WPS's smart troubleshooting suggestions.

Fully compatible with Microsoft Excel formulas and .xlsx formatsBuilt-in error checking tool to easily trace formula errorsFree and lightweight alternative to standard Office suitesSmooth handling of volatile functions without lagging

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error instead of #NAME?

A #REF! error indicates that the text string successfully evaluated as text, but the resulting range does not exist (e.g., pointing to row 2,000,000) or it references a closed external workbook. INDIRECT requires external workbooks to be open to function properly.

Is there a non-volatile alternative to INDIRECT for excluding rows?

Yes, you can use the INDEX function, which is non-volatile and often calculates much faster. For example, using INDEX(A:A, 4):INDEX(A:A, 100) will dynamically create a reference from row 4 to row 100 without slowing down your workbook.

How can I dynamically exclude rows 1-3 using a variable in another cell?

You can combine the INDIRECT function with a cell reference using the ampersand (&). If cell B1 contains the number 4, you can write the formula as =INDIRECT("A" & B1 & ":A100"). This will automatically adjust the starting row if you change the value in B1.

Excel & Data Problems