How to Troubleshoot and Fix Excel Pivot Table Errors?

Power Pivot is an Excel add-in, a data modeling technology that helps the user to create data models, establish relationships, and create calculations. However, it is possible to encounter a few errors during the process. here in this article, we will discuss some of the Excel Pivot Table Errors that may occur and how we can fix them.

Usually, Excel Pivot works smoothly and efficiently, however, it is inevitable to encounter some errors while working with the software. Here are some of the common errors that users might encounter during working with Excel PowerPivot.

“Unable to load the Data Model” Error

Sometimes, A simple data change, for example, adding the pivot table data to the Data Model may cause a pivot table refresh error. It’s not that common, however, sometimes users do face such issues. As we can see in the image below:

Here, in the image, as we can see above the message is showing that the “PowerPivot is unable to load the Data Model”. 

Solution: 

The change from the UPPER case to the PROPER case or other similar changes in the source data headings may be the primary cause of this error resulting in the data model having a second instance of the field. As shown in the image below:

1. In such a case, the very first thing one might do is to change the heading back to its original case. That can help solve the problem. 
2. The second step would be to refresh the Pivot table. By doing this, the extra field will be removed from the PivotTable Field List. 

“Field Name is Not Valid” Error

Another issue, that we Sometimes face while working with Excel PowerPivot is the “Field name not valid” error. Such a thing happens when we try to create or refresh a pivot table in Microsoft Excel. As depicted in the image below: 

Here, the message emphasizes, “The PivotTable field name is not valid. To create a PivotTable report, we must use data that is organized as a list with labeled columns. To change the name of a PivotTable field, we must type a new name for the field.” As can be seen in the image given below:

However, when we analyze the source data for this pivot table, everything appears to be in order. As shown in the example below, there are 7 columns of data and no empty columns or rows in the list. Each column has a pivot table field name at the top as can be seen:

Solution: 

This type of error usually occurs when one or more of the heading cells in the source data are blank. To make a pivot table, we need a heading value for each column. while creating an Excel Table from the data, column headings are automatically added to columns with blank heading cells.

To prevent this from happening, we can use the following suggestions:

1. In the Create PivotTable dialogue box, double-check the Table/Range selection to ensure that no blank columns beside the data table have been selected.
2. Examine the source data range for hidden columns. if any column headers are missing, unhide them and add a header value.
3. Unmerge any merged cells in the heading row and add a heading in each separate cell.
4. Check the contents of each heading cell in the formula bar; text from one heading may overlap a blank cell beside it. 

“Overlapping” error in Excel Pivot Table 

Another common problem that might occur is “A PivotTable report cannot overlap another PivotTable report.”
This Excel error message appears when there are multiple pivot tables on the same sheet and there is insufficient blank space for one of the pivot tables to expand for new data. As the image shows below:

Solution:

At this point, the issue may easily be identified and repairable. However, in the case of large amounts of data, numerous pivot tables, and multiple data sources, it may appear difficult to pinpoint the problem.

To solve the problem, We can use the “List All Pivot Table – Details” macro to get a list of all the pivot tables in the workbook, along with information about where they are located and so on.

For example, the macro lists here show information about the location, size, and source data of each pivot table in the file. As depicted in the image:

Suppose, if the source is a worksheet list or table in the same Excel workbook, then, the macro will show the details about that source data. Like we can see in the image:

Duplication problem in Pivot Table

While creating a pivot table, we might see duplicate items in the pivot table. Initially, it groups the items from given data and calculates a total for each group, which sometimes causes such issues. 

Text Duplications

For example, as we can see there are duplicates of the text in the row below in the image:

Here in the image, we can see the word Boston is repeated a few times, which is causing a text duplication error.

Solution: 

Here, we can try a clean-up method to solve the duplication problem in the pivot table. To some extent, the issue can be resolved by cleaning up the source data. However, trailing spaces are ignored by the filters in an Excel table, making it difficult to identify the problematic entries. Drop-down filters can be used in this situation to check the source data. 

Here are a few steps to follow for one-time cleanup: 

Step 1. Firstly, set a filter so that the table shows only the records related to the word “Boston”. 
Step 2. Next, in order to replace the current City entry, type “Boston” in the first record.
Step 3. Then, choose that cell, and point to the ‘fill handle’ in the cell’s bottom right corner.
Step 4. To copy the entry to the last filtered record, simply, double-click on the ‘Fill Handle’. 
Step 5. At this point, make sure that all the records are updated by scrolling down.
Step 6. Next, remove the city column’s filter.
Step 7. Now, by refreshing the pivot table, the duplicates will vanish. As given in the image below: 

Duplication in Numbers

Same as text duplications, it can also appear in the numbers. Due to the use of floating-point precision in Excel, there may occasionally be small, undetectable differences in the numbers that manifest as duplication in the Row area. For instance, the pivot table for the “Carrot” under the category “Bars” contains some duplicates.

Solution: 

The ROUND function can be used to solve the issue by rounding all the numbers to a predetermined number of decimals. This will stop variations in the Excel-stored hidden numbers.

For example, suppose, Column C contains unit prices, and the Remove Duplicates feature detects some variations in those figures. 

Here, we can round the numbers by following these steps:

• To round the numbers to 12 decimal places, enter the following formula in cell D2.: =ROUNDE(C2,12)
• After that, Copy the formula down to the last row of data.

Here, the outcome must be a total of 15 digits or less. For instance, round to 10 or fewer digits if there are 5 digits before the decimal point.

So far, we’ve talked about a few typical Excel pivot table errors and how to troubleshoot or resolve them.