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

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.

Table of Contents hide

Download Practice Workbook

Reason Behind Excel Shows Consolidation Reference Is Not Valid Error

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

Step 1: Use Consolidate Command to Fix Error

Step 2: Consolidate Data with Proper Heading from Several Worksheets

Things to Remember

Conclusion

Download Practice Workbook

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

Consolidate Data Error.xlsx

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.

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.

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.

After that, from your Data ribbon, go to,

Data → Data Tools → Consolidate

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

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

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

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.

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.