[Fixed]: Consolidation Reference Is Not Valid in Excel

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.


Reason Behind Excel Shows Consolidation Reference Is Not Valid Error

When we do consolidation in Excel from several worksheets, sometimes these data do not have the same heading with the same column and row number. So, the “Consolidation reference is not valid” error occurs.

consolidation reference is not valid in excel


How to Fix If Consolidation Reference Is Not Valid Error in Excel: with Easy Steps

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


Step 2: Consolidate Data with Proper Heading from Several Worksheets

In this portion, we will consolidate data from multiple ranges in Excel. Let’s follow the instructions below to fix the error!

  • First, 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 and Left column, and Create links to source data Lastly, 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


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.


Download Practice Workbook

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


Conclusion

I hope all 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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo