excel for mac table source reference not valid

Like if you want use active data object, you will need to add reference to that object first.

In this article, we will learn why data source isnt valid occurs and how we can solve this annoying problem.

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.

Because I do not have any range called Table1 (I had changed it to Data).

When you refresh pivot tables and this error occurs, it is possible that you have changed some range or names in the data mode.

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.

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.

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.

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.

When the data source does not exist at the defined location, it is obvious that excel shows Data source reference is not valid.

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.

A window will open that will show the existing connection to the workbook.

Check the connections and if you dont need them anymore, remove them.

Go to the location of the data source to check if that exist or not.

In that case, you will need to open the data base and check the queries thoroughly.

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.

Excel For Mac Table Source Reference Not Valid