[Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)

Last updated: July 20, 2022
Get FREE Advanced Excel Exercises with Solutions!

While consolidating data from several worksheets in  Microsoft Excel, sometimes we will see that the consolidation reference is not valid which is a common scenario. We can easily fix the error easily. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable steps to solve the error named consolidation reference is not valid in Excel graph effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Reason Behind Excel Shows Consolidation Reference Is Not Valid Error

When we will consolidate data from several worksheets if those data do not have the same heading with the same column and row number the “consolidation reference is not valid” error occurs.

consolidation reference is not valid in excel


2 Quick Steps to Fix Consolidation Reference Is Not Valid Error in Excel

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 for our today’s task.

consolidation reference is not valid in excel


We can solve the error named “consolidation reference is not valid” by creating several datasets with the same heading with the same column and row number. Let’s follow the instructions below to fix the “consolidation reference is not valid” error!

Step 1: Use Consolidate Command to Fix Error

We will apply the Consolidate command to fix the error named “consolidation reference is not valid”. Let’s follow the instructions below to learn!

  • First of all, select a cell for the convenience of our work. Let’s say, we will select cell B4.

consolidation reference is not valid in excel

  • After that, from your Data ribbon, go to,

Data → Data Tools → Consolidate

consolidation reference is not valid in excel

  • As a result, a dialog box named Consolidate will appear in front of you.

Read More: Data Validation and Consolidation in Excel (2 Examples)


Similar Readings


Step 2: Consolidate Data with Proper Heading from Several Worksheets

In this portion, we will import data from several worksheets to consolidate those data. Let’s follow the instructions below to fix the error!

  • First of all, type ‘Sales 2019’!$B$4:$E$14 in the Reference typing box. Hence, press the Add option to import data from the sheet named “2019”.

consolidation reference is not valid in excel

  • Later, again, type ‘Sales 2020’!$B$4:$E$14 in the Reference typing box. Hence, press the Add option to input data from the sheet named “2020”.

  • Further, type ‘Sales 2021’!$B$4:$E$14 in the Reference typing box. Hence, press the Add option to insert data from the sheet named “2021”.

consolidation reference is not valid in excel

  • After inserting those data from several worksheets, firstly, check the Top row, Left column, and Create links to source data At last, press the OK option.

  • After completing the above process, you will be able to solve the error which has been given in the below screenshot.

consolidation reference is not valid in excel

Read More: How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)


Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

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


Conclusion

I hope all of the suitable steps mentioned above to fix the consolidation reference is not valid error and will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo