[Fixed]: Consolidation Reference Is Not Valid in Excel

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.

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.

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.

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

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

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

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