Function Problems

How to Fix INDIRECT Function Not Working in Excel for SharePoint Online

Amos Gikunda Sep 28, 2026 868 views

Question details

The INDIRECT function executes successfully in the Excel desktop application but fails to calculate or act as a working formula when accessed via Excel for the web on SharePoint Online.

How to Fix INDIRECT Function Not Working in Excel for SharePoint Online

Product: Microsoft Excel for the web
Device & OS: not provided
Scenario: Using the INDIRECT function to create dependent dropdown lists or dynamically reference cells in a workbook hosted on SharePoint Online.
Observed behavior: The formula fails to execute in the web browser version, and when users attempt to copy and paste the formula, it may paste strictly as plain text instead of a functional formula.

Before you start

Verify that your cells are formatted as 'General' rather than 'Text' before editing formulas, and temporarily open the file in the Excel desktop application to confirm the formula syntax is actually correct.

Solution 1Recommended

Use Defined Names Instead of Direct Cell References

Excel for the web can struggle with the INDIRECT function when parsing complex or dynamic cell references. Using a Defined Name instead can resolve compatibility issues.

Volatile functions like INDIRECT have known limitations in web-based spreadsheet environments, specifically regarding data validation and complex string evaluation.

Open Name Manager

Navigate to the 'Formulas' tab on the ribbon at the top of your screen and click on 'Name Manager' (or 'Define Name').

Create a New Defined Name

Click 'New', assign a clear name to your target data range (e.g., 'CategoryList'), select the cell range, and click 'OK'.

Update the INDIRECT Formula

Double-click the cell where your formula is located and update it to reference the defined name directly, such as =INDIRECT("CategoryList"), then press Enter.

Use Defined Names Instead of Direct Cell References

Tip: Ensure there are no spaces in your Defined Names, as this will cause the INDIRECT function to return a #REF! error.

Solution 2

Replace INDIRECT with a FILTER Array Formula

Bypass the INDIRECT function limitations entirely by using the modern FILTER function to dynamically generate dependent dropdown lists on a helper sheet.

Free Microsoft Office alternative

Try WPS Office for Unrestricted Formula Functionality

If Microsoft Excel for SharePoint Online continues to restrict advanced functions like INDIRECT in your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative that seamlessly processes complex formulas and dynamic data validation without web-based restrictions.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and complete the quick installation process on your computer.
2. Open Your Excel File: Launch WPS Spreadsheet, click on 'Open', and select the problematic .xlsx file previously hosted on SharePoint.
3. Calculate and Edit Unrestricted: Your INDIRECT formulas and data validation dropdowns will instantly work as intended. You can edit and save your file seamlessly.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Robust, native support for volatile functions like INDIRECT and complex data validationFamiliar user interface requiring zero learning curve for easy migrationLightweight application that operates flawlessly both offline and online

microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDIRECT function work on the desktop but not in Excel for the web?

Excel for the web runs in a browser environment with specific calculation limitations. Volatile functions like INDIRECT, especially when tied to data validation or cross-sheet dynamic references, may exceed the web application's execution limits to maintain browser performance.

How do I fix formulas pasting as plain text in Excel for SharePoint?

This usually happens if the destination cell's format is accidentally set to 'Text'. Select the destination cell, go to the Home tab, change the Number Format to 'General', double-click inside the cell, paste your formula, and hit Enter.

Can I build dependent dropdown lists in Excel for the web without using INDIRECT?

Yes. The modern and most reliable approach is to use dynamic array functions like FILTER or XLOOKUP on a hidden helper sheet to generate the required list, and then reference that spilled array (using the '#' symbol) in your Data Validation source.

Excel & Data Problems