How to Fix the “Consolidation Reference Is Not Valid” Error in Excel (Easy Steps)

Reason Behind the Error

When consolidating data from multiple worksheets in Excel, sometimes the data doesn’t have the same headings with matching column and row numbers. As a result, the “Consolidation reference is not valid” error occurs.

Dataset Overview

Let’s say, we have a dataset that contains information about several Sales representatives of XYZ group. The Names of the Sales representatives and their sales in several quarters are given in columns B, C, D, and E respectively. Sales in several years are in multiple worksheets. We will sum up the sales in different years within a worksheet using the consolidate command. Here’s an overview of the dataset:

Step 1 – Use the Consolidate Command

• Select a cell (e.g., B4).

• Go to the Data ribbon, then navigate to Data Tools and select Consolidate.

• A dialog box named Consolidate will appear.

Step 2 – Consolidate Data with Proper Headings

• In the Reference typing box, type ‘Sales 2019’!$B$4:$E$14 and press Add to import data from the sheet named 2019.

• Repeat for other years: type ‘Sales 2020’!$B$4:$E$14 and ‘Sales 2021’!$B$4:$E$14.

• Check Top row and Left column and select Create links to source data.
• Press OK.

• By following these steps, you’ll be able to resolve the error.

Things to Remember

#N/A! error occurs when a formula or function fails to find referenced data.

#DIV/0! error happens when a value is divided by zero or the cell reference is blank.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel