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.
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.
Read More: Data Validation and Consolidation in Excel (2 Examples)
Similar Readings
- How to Consolidate Two Sheets into One in Excel (3 Useful Methods)
- How to Automate Consolidation in Excel (with Easy Steps)
- How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)
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.
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
- Create a Linked Consolidation in Excel (2 Useful Methods)
- How to Build a Static Consolidation in Excel (2 Suitable Examples)
- Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
- How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)
- Consolidate Function for Text Data in Excel (with 3 Examples)
- How to Remove Consolidation in Excel (2 Handy Methods)
- Consolidate Data from Multiple Rows in Excel (4 Quick Methods)
