Top 5 Excel Pivot Table Problems & Their Fixes

If you are a regular user of Excel pivot table then you are also quite familiar with its associated problems that are frequently conquered meanwhile its usage.

Through this post, I will help you all to fix Excel pivot table problems. I have made a list of top pivot table problems in Excel. So, let’s catch detail on each pivot table problem and know how to fix it.

How To Fix Excel Pivot Table Problems?

Problem 1# Pivot Tables Not Refreshing Data

By default, the pivot table data are not automatically get refreshed by themselves.  But there is an option to automatically refresh the data of the pivot table, after opening the workbook.

Solution: Refresh pivot table data automatically

• Tap anywhere inside your Pivot Table as this will display Pivot Table Tools on your Excel ribbon.
• Hit the Analyze and then Options button.

• From the Data tab present in the Excel ribbon, choose the check box ”Refresh data when opening the file”.

Problem 2# Refreshing A Pivot Messes Up Column Widths

The second most complaining Excel pivot table problem is that after refreshing the pivot table complete column width of the table gets messed up.

If you don’t want that your pivot table column width and cell formatting mess up after refreshing the pivot table data then perform the following option settings.

• Tap anywhere inside your Pivot Table as this will display PivotTable Tools on your Excel ribbon.

• Hit the Analyze and then Options button.

• Now go to the Layout & Format within this tab, choose these two options: Autofit column widths on update and Preserve cell formatting on update.

Problem 3# Excel Pivot Table Sum Value Not Working

Another very annoying Excel pivot table problem is that all of a sudden Excel pivot table sum value not working

Whenever the fields are added in the value area of the pivot table, they are calculated as a sum. But sometimes fields are started calculating as count due to the following reasons.

There are three reasons behind this Excel Pivot Table Sum Value Not Working.

1. BLANK CELL(S):

Presence of blank cells in the values column of your data set.

Even though you have just one empty cell within the values column, Excel automatically considers the whole column as text-based.

2. TEXT CELL(S):

In the value columns of your data set, there are some “text” cells.

Suppose you have a cell that is formatted like a text within the values column. This will also turn your pivot table sum values to count.

Generally, this happens when any user downloads the data from any external system or ERP.

Workaround:

• Within the text, formatted cells or blank cell just assign any value or 0(zero).

• In your Excel pivot table, tap on the Count of….option and drag it out from the Values

• Now Refresh the Pivot Table

• Drop in the Values field (SALES)within the “Values” area once more.

Problem 4# Pivot Table Grouping Affects Another Pivot Table

Are you also facing such an Excel pivot table problem in which one pivot table grouping starts affecting another? Do you know why this happening? If not then let me clear this to you first….!

In Excel 2007 and later version, when you make a two pivot table from the same data source. They automatically start using the same pivot cache of source data.

Well, there is no such option available to create an individual pivot table. This means changing the grouping pattern of one pivot table will display the same grouping in another also.

As the pivot table share the same cache, so it’s obvious to encounter problem in data grouping of the pivot table field.

Solution: Create A Second Pivot Cache

For using dissimilar groupings in each of the pivot tables; you have to create an individual pivot cache for each of your pivot tables.

Follow the steps, to fix Excel pivot table grouping problem.

• Just cut down your 2^nd pivot table. After that open a new Excel workbook and paste it.
• It’s time to make changes in the grouping of your 2^nd pivot table.
• Now you have to cut your 2nd pivot table which you have recently pasted in the new workbook.
• After that paste it back within your original workbook.
• Doing this will generate two separate pivot caches in your original workbook. So now you can independently group each of your pivot tables.

Following the above step will create a separate pivot cache for the 2^nd pivot table.

Problem 5# Excel Pivot Table Not Showing Data

Another very frequently encountered Excel Pivot Table problem is when your Excel Pivot Table not showing data.

In such a situation, when you put 2 or more fields within the Row section, the complete item won’t appear in each of these sections. For keeping the layout consistent, it’s compulsory to show all items in each section.

Solution: Show All Pivot Field Data

You have to perform these changes in all the fields in which you want to display all your data.

• Make a right-click on the pivot table item and tap on its Field settings options.
• After the opening of the Field Settings dialog box, you have to hit the Layout & Print tab.
• Make a check across the check box having the text ‘Show items with no data’.

• Click OK.

Wrap Up:

I hope this article seems helpful to you. From now onward you don’t have to suffer from these annoying Excel pivot table problems. Moreover, you can utilize your precious time where it’s required. So what are you been waiting to start creating insightful reports with the Excel pivot table.

Feel free to ask any pivot table errors of which you are going through. I will try my best to resolve them for you.