Pivot Table Errors

27 January 2019

Pivot Table Errors

PivotTable in Excel is an wonderful tool for Data Analysis. But we get few common annoying problem in PivotTable which we will solve in this article.

Few Excel problems which we encounter in PivotTable is listed below, we will discuss these points step by step.

1. Column width changing when refreshed - prevent column width change

2. New data not showing in pivot table

3. PivotTable not Creating

4. Old Items in the Drop Downs - Clear Old Items in PivotTable

5. GetPivotData Formula - Remove GetPivotData from PivotTable

6. Change Values from Count to Sum

7. Show Zeros in Empty Cells

8. Pivot Table automatically grouping dates into Year, Quarter, Month

Lets discuss...

1. Column width changing when refreshed - prevent column width change

Steps :

1) Right Click anywhere inside the pivot table

2) Choose PivotTable Options

3) In the PivotTable Options dialog, choose Layout & Format at the top, and the uncheck AutoFit column widths on update, then click OK.

Column width changing when refreshed - prevent column width change

2. New data not showing in pivot table

This is because your newly added data is not selected by the PivotTable. You have to select the Range of data from PivotTable.

Steps :

1) Select any cell in the pivot table

2) On the Ribbon, click the Options tab

3) Click Change Data Source select the complete data source range.

4) Click OK

New data not showing in pivot table

3. PivotTable not Creating

Please check you Data Source, to create PivotTable you need column name continuously - no blank column should exist between other columns.

4. Old Items in the Drop Downs - Clear Old Items in PivotTable

Sometimes you will find that your PivotTable showing Old data in the filter although it is deleted from the source data.

To clear the old item from an existing PivotTable follow the following steps.

Steps :

1) Right Click anywhere inside the pivot table

2) Choose PivotTable Options

3) Go to Data tab (as shown in the below picture)

4) Change Automatic to None in the 'Retain items deleted from data source' section 'Number of items retain per field:' from the drop down button.

5) Click 'OK'.

Old Items in the Drop Downs - Clear Old Items in PivotTable

5. GetPivotData Formula - Remove GetPivotData from PivotTable

This was most annoying problem for me, you know sometimes we need to calculate beside PivotTable and for that we need to select the cells from the PivotTable - now when I select any cell after = , it always shows =GETPIVOTDATA("Target",$B$3,"Zone","North","Day","Friday").

Whereas I only need '=D7'. You can do it, you can stop GETPIVOTDATA, follow the following steps.

Steps :

1) Select any cell in the pivot table

2) On the Ribbon, click the Options tab

3) Click on the drop down button in the left side Option button and

4) Uncheck 'Generate GetPivotData'

GetPivotData Formula - Remove GetPivotData from PivotTable

6. Change Values from Count to Sum

In a PivotTable you may need Count of items or Sum of Item. It is very easy to change a data value from Sum to Count or from Count to Sum, pleas follow the following steps.

Steps :

1) Select any cell in the pivot table value column

2) Right click and select 'Value field settings' from the pop up option.

3) In the popup window you will see a list of functions, select as per your requirement and

4) Click 'OK' to get the desired result.

Change Values from Count to Sum

7. Show Zeros in Empty Cells

In PivotTable we get blank cells if there is blank in the source data, follow the following steps to get zeros in the blank cells in a PivotTable.

Steps :

1) Select any cell in the pivot table

2) Right click and select 'PivotTable Options...' from the pop up option.

3) Enter '0' or '-' or any other character in the box beside 'For empty cells show' as shown in the below picture.

Show Zeros in Empty Cells

8. Pivot Table automatically grouping dates into Year, Quarter, Month - solved

If date is the in the source data and we insert PivotTable in Excel 2016 date field comes in Qtr grouping.

Solution : If you need the date field just select that cell and Right click on that cells, from the popup options select 'Ungroup' and you will see that the Grouping is broken and date appeared in the PivotTable.