"Data source reference is not valid" is one annoying problem that can be hard to get rid of, if you don't know the source problem. In this article, we will learn why data source isn't valid occurs and how we can solve this annoying problem.
So, as the name suggests the error occurs when Excel tries to refer to location that is no longer available, invalid or doesn't exist. Now this error can occur due to several reasons.
So, when we create a pivot table it requires a data range. Using that data range excel creates the pivot reports. Now if you try to create pivot table with invalid range or refresh pivot table that refers to a range that no longer exists, this can cause "Reference is not valid error".
For example, here I have a data table, initially whose name was "Table1" but I change it to "data" later. Later I wanted to create a pivot table on this table.
So I went to insert --> Pivot tables. When it asked for the data source, I mentioned "Table1". This returns a "Data source reference is not valid" error. Because I do not have any range called "Table1" (I had changed it to "Data").
So, this one of the main reasons for data reference invalid error.
How to solve Pivot Table Reference Error?
If you have several pivot tables in a workbook that refers to multiple ranges, named range or data tables, make sure that they refer to valid range. When you refresh pivot tables and this error occurs, it is possible that you have changed some range or names in the data mode. Check one by one each of them and save your excel file. This will solve reference error problem.
The named ranges are great tools when it comes to managing structured data. But you got to be careful with the named ranges. When you name a given range, excel tags that specific name to the given range.
By default excel adjusts the named ranges when you delete or add rows to the named ranges. But if you delete entire range, the named range loses its reference. All the formulas, validations and condtional formatting depending on that range will not work. And if you try to copy that sheet or file you may get "reference is not valid" error.
How to solve named range "Reference invalid error".
To solve this error, use the shortcut CTRL+F3 shortcut. This will open the name manger. Check the names and there ranges. Delete or fix all the named ranges that refers to contains #REF!. Save your file. This will remove the "data source reference is not valid" error.
Tables in Excel are great functionality. They help data management a lot. But they can cause the Reference errors to. It is similar to named ranges. When you use Table names in formulas, data validation, conditional formatting, and overtime when you delete or do some major changes to table, excel can through reference not valid error. This may not occur immediately but when you copy ranges or sheets to other workbooks or location.
How to avoid Table Caused Reference Error
To avoid such errors, you should check all the formulas, validations, conditional formatting that refers to table that has changed a lot earlier. Change or delete such formulas. This will solve the " Data Reference not Valid Error"
Excel has features of importing data from other sources. All these features can be accessed from data tab. "Get external Data", "Power Query", and "Power Pivot" are such examples. These features allow users to maintain data outside the Excel and import them whenever needed.
Now this feature is great but this cause problems when you accidentally or intentionally delete or move data source from the defined location. When the data source does not exist at the defined location, it is obvious that excel shows "Data source reference is not valid".
How to solve Data Source reference is not valid?
Check all the connections your file has. Check the connections to Access database if you have. Check it the location of the data source has changed. This is one of the main reasons of "Data source is not valid" Error. Even if you change the name of the data source, it can cause the error. You need to change the name in Excel connections too.
To solve this error, go to Data --> Get External Data --> Existing Connection. A window will open that will show the existing connection to the workbook. Check the connections and if you don't need them anymore, remove them. If you do need them. Go to the location of the data source to check if that exist or not.
A lot of time, the connection is valid but file still throws a reference isn't valid when excel refreshes data. It is possible that the query or table that you are trying to access from the database is changed or does not exist anymore. In that case, you will need to open the data base and check the queries thoroughly. You will find the route cause of the error here surely.
If the error "excel reference isn't valid" occurs when you run a macro than there can be a lot of reasons.
The first reason can be that macro references to a range that no longer exist in system. This can be easily solved by clicking on the debug method.
Other reason can be the database connection. If you are trying to access database from VBA and trying to run a query with a invalid connection or query than you may get this error. So you have to do all the things mentioned in previous step.
Other reason can be that you used an object whose reference is not added. Like if you want use active data object, you will need to add reference to that object first.
To add a reference to an object, open VBE using Alt+F11 key. Now go to tools in menu, click on References. It will open a dialog box that has all the available resource. Search the object you need. Check it and click OK button. This will add the reference to that object.
So yeah guys, these are the reasons and methods to solve the invalid data source error in Excel. I hope it was helpful. If you are still facing the difficulty removing this error, let me know in the comments section below. Till then keep Excelling.
Related Articles:
13 Methods of How to Speed Up Excel | Excel is fast enough to calculate 6.6 million formulas in 1 second in Ideal conditions with normal configuration PC. But sometimes we observe excel files doing calculation slower than snails. There are many reasons behind this slower performance. If we can Identify them, we can make our formulas calculate faster.
Center Excel Sheet Horizontally and Vertically on Excel Page : Microsoft Excel allows you to align worksheet on a page, you can change margins, specify custom margins, or center the worksheet horizontally or vertically on the page. Page margins are the blank spaces between the worksheet data and the edges of the printed page
Split a Cell Diagonally in Microsoft Excel 2016 : To split cells diagonally we use the cell formatting and insert a diagonally dividing line into the cell. This separates the cells diagonally visually.
How do I Insert a Check Mark in Excel 2016 : To insert a checkmark in Excel Cell we use the symbols in Excel. Set the fonts to wingdings and use the formula Char(252) to get the symbol of a check mark.
How to disable Scroll Lock in Excel : Arrow keys in excel move your cell up, down, Left & Right. But this feature is only applicable when Scroll Lock in Excel is disabled. Scroll Lock in Excel is used to scroll up, down, left & right your worksheet not the cell. So this article will help you how to check scroll lock status and how to disable it?
What to do If Excel Break Links Not Working : When we work with several excel files and use formula to get the work done, we intentionally or unintentionally create links between different files. Normal formula links can be easily broken by using break links option.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
