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.

consolidation reference is not valid in excel

Dataset Overview

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

consolidation reference is not valid in excel

Step 1 – Use Consolidate Command

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

consolidation reference is not valid in excel

  • Go to the Data ribbon, then navigate to Data ToolsConsolidate.

consolidation reference is not valid in excel

  • 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.

consolidation reference is not valid in excel

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

consolidation reference is not valid in excel

  • 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.

consolidation reference is not valid in excel

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF