Excel Error Codes

How to Fix #NAME? Error with PIVOTBY and GROUPBY in Excel

Emma Brown Sep 25, 2026 868 views

Question details

The user is experiencing a #NAME? error when using the PIVOTBY and GROUPBY functions in an existing Excel workbook, even though the same formulas work correctly in a new workbook.

How to Fix #NAME? Error with PIVOTBY and GROUPBY in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to aggregate and summarize data using the new PIVOTBY and GROUPBY array functions in an existing workbook.
Observed behavior: The functions return a #NAME? error in one specific workbook, while identical formulas and tables produce the correct output in a different workbook, suggesting a file-specific conflict or corruption.

Before you start

Ensure you are using a compatible version of Excel (such as Microsoft 365 Beta or Current Channel Preview) that supports the new PIVOTBY and GROUPBY functions, and verify that your table ranges are spelled correctly.

Solution 1Recommended

Test Aggregation with a Qualified Function Name

Test if the standard aggregation function (like SUM) is conflicting with a defined name or legacy function in that specific workbook by using a qualified function name.

Sometimes, an existing workbook may have hidden corrupted names, legacy macros, or custom named ranges that use the word 'SUM'. This causes Excel's calculation engine to get confused when 'SUM' is passed as an argument in functions like PIVOTBY or GROUPBY, resulting in a #NAME? error.

Edit the formula

Click on the cell containing your PIVOTBY or GROUPBY formula.

Modify the aggregation argument

Locate the aggregation argument at the end of your formula (e.g., `SUM`) and replace it with the fully qualified internal function name `_xleta.SUM`. Your formula should look something like `=PIVOTBY(Table21[Fruit],Table21[Purchase Date],Table21[Value],_xleta.SUM)`.

Apply and test the formula

Press Enter to evaluate the formula. If the data calculates correctly without the #NAME? error, the issue is confirmed to be a naming conflict within the original workbook.

Test Aggregation with a Qualified Function Name

Check Name Manager: You can navigate to Formulas > Name Manager to inspect your workbook for any inadvertently defined names called 'SUM' and delete them to resolve the conflict permanently.

Solution 2

Transfer Data to a New Workbook

If the workbook is corrupted and modifying the function names does not work, migrate your data to a clean workbook.

Free Microsoft Office alternative

Try WPS Office for Reliable Data Aggregation

If your Excel workbooks frequently suffer from file corruption, legacy naming conflicts, or version discrepancies with new array functions, consider switching to WPS Office. It provides a highly compatible spreadsheet environment that easily handles complex data summarization and pivot tables without the hassle of unexplainable errors.

1. Download and Install: Visit the official WPS website, download the free suite, and complete the installation process.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files directly; your data formatting will remain intact.
3. Summarize Data Easily: Use the intuitive built-in PivotTable tools found on the Insert tab to easily group and summarize your data without relying on preview-channel formulas.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats, ensuring seamless file migration.Lightweight architecture that opens large data files quickly without freezing or corruption.Familiar user interface with robust, built-in PivotTable tools for straightforward data summarization.Completely free to download and use for your core spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does PIVOTBY return a #NAME? error in Excel?

The #NAME? error occurs if Excel doesn't recognize the function name. This typically happens if you are using a version of Excel that doesn't yet support PIVOTBY (which currently requires Microsoft 365 Preview channels), or if there is a naming conflict/corruption in the specific workbook preventing the aggregation argument from calculating.

How can I check if my Excel version supports PIVOTBY and GROUPBY?

Go to File > Account and look under 'About Excel' to check your update channel. PIVOTBY and GROUPBY are rolling out to users enrolled in the Microsoft 365 Beta Channel or Current Channel Preview. Standard enterprise channels may not have them yet.

What does _xleta.SUM mean in Excel formulas?

It is a fully qualified, internal identifier for the SUM function. Using it bypasses local naming conflicts or corrupted defined names in a specific workbook, ensuring that array functions like PIVOTBY and GROUPBY properly recognize the aggregation method.

Microsoft Office Error Codes