Ошибка при создании сводной таблицы в excel

Постройте сначала хоть какую-то. Далее научитесь делать группировку по датам:

Группировка данных в сводной таблице

Хотя сразу видно нестыковки какие-то в предлагаемом формате. У поволжья одновременно два итога(вверху и внизу), а это некорректно. Плюс в столбцах не для всех полей присутствуют месяцы — так в сводной не сделать.

Хотя…Исходные данные какие-то…Мягко говоря уродские для сводной. Есть подозрение, что все что от Вас требуется это убрать объединенные ячейки и при помощи сводной тупо разбить на Дивизион и Наименование. Остальные данные взять из одноименных столбцов.

Some Excel users have reported experiencing the Data source reference is not valid error while creating a pivot table in Excel. We will learn why this error occurs and how you can resolve it in this post.

Why is my data source reference not valid?

The common reason behind the Data source reference is not valid error in Excel is the fact that the filename of the Excel workbook contains square brackets which are invalid characters. Another primary reason for the same error is that the range from which you are trying to insert a pivot table doesn’t exist or is undefined. Or, the reference for the named range you are using for your pivot table is invalid. Besides that, it can also be the case that you are trying to open the file from an URL or attachment in an email.

Now, in any scenario, this post will help you resolve the error. Here, we will discuss working fixes to get rid of the “Data source reference is not valid” error in Microsoft Excel.

If you are experiencing the Data source reference is not valid error when creating a pivot table in Microsoft Excel, you can follow the below fixes to resolve it:

1. Delete brackets from the filename.
2. Save the workbook on your local disk.
3. Make sure that the range exists and is defined.
4. Ensure that the reference for the defined range is valid.
5. Repair the Excel file.

1] Delete brackets from the filename

This error is likely to occur if the filename of the Excel workbook consists of an invalid character i.e., square brackets. Hence, if the scenario applies, you can change the filename of the problematic Excel file and delete brackets from it.

To do that, first, you must close Excel and make sure the problematic workbook is not open in any other program. Now, open File Explorer using Win+E and navigate to the folder where you have saved the Excel file. Next, right-click on the file, choose the Rename option, remove the brackets from the filename, and press the Enter button.

After renaming the file, launch Excel and open your file. Check if you are able to create a pivot table without the “Data source reference is not valid” error. If not, you can move on to the next fix.

2] Save the workbook on your local disk

It might be the case that you are opening the Excel file directly from an online source or email attachment. If so, you are likely to get the “Data source reference is not valid” error. So, if the scenario applies to you, save the workbook on your local disk first and then open it in Excel to create a pivot table. Open the file, click on the File > Save as option, and then save it on your local drive. Once done, check if the error is now resolved.

Read: How to fix the #REF error in Excel?

3] Make sure that the range exists and is defined

You will face this error if you are trying to create a pivot table in a range that is not defined or non-existing. For example, you are inserting a pivot table by clicking on Insert > PivotTable > From Table/Range. Now, you have entered the name of a table or range inside the Table/Range as “TWC” under the Select a table or range option. But, the TWC range doesn’t exist. So, you are likely to encounter the “Data source reference is not valid” error.

Hence, to avoid this error, you must ensure that the range from which you are trying to insert a pivot table exists and is defined. To define the range, you can follow the below steps:

• Firstly, open your Excel file and tap on the Formulas tab from the ribbon bar.
• Now, press the Name Manager option from the Formulas tab.
• Next, click on the New button and enter the name of the range you are creating.
• After that, inside the Refers to box, enter the cells you are going to use in the created range. To do that, you can either use the built-in selector or manually enter the range.
• Finally, press the OK button to complete the process.

Once done, you can try inserting a pivot table from the defined range and check if the error is resolved or not.

4] Ensure that the reference for the defined range is valid

In addition to the above fix, you must ensure that referenced cells (Refer to) for the created range contain valid values. Else, you will experience the Data source reference is not valid error. So, make sure the reference for the defined range is valid. Here’s how:

• First, click on the Formulas tab > Name Manager option.
• Next, select the range you are using for your pivot table and check the entry for the Refer to value.
• If the reference is incorrect, double-click on the range and then make the modifications to the entry accordingly.
• When done, you can try inserting a pivot table and hopefully, the error will be resolved now.

Read: Excel ran out of resources while attempting to calculate formulas.

5] Repair the Excel file

If the above solutions didn’t work for you, try repairing the problematic Excel file. It might be the case that your file is corrupted which is why you keep getting this error. So, you can repair the Excel file and recover data to fix the error.

To do that, you can use the native feature in Microsoft Excel. First, close the problematic file and click on the File > Open option. Now, choose the corrupted file, press the arrow present next to the Open button, and select the Open and Repair option. See if this helps. If this method doesn’t work, you can also try using a third-party Excel repair tool and check if it works.

Hope, this post helps you resolve the Data source reference is not valid error in Microsoft Excel.

Now read: Error, Reference source not found – Microsoft Office issue.

How do I fix the Reference is not valid in Excel?

You can fix the “Data source reference is not valid” error in Excel by removing square brackets from the filename of your Excel file. Apart from that, save the Excel file on your local drive, define the range you are using in your pivot table, and make sure the referenced cell values for the defined range are valid. We have discussed these and more solutions in detail below. So, let us check out.

Перейти к контенту