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 |
 |
| 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.
 |
| Pivot Table automatically grouping dates into Year, Quarter, Month |
I assume that you know how to create PivotTable in Excel,
if you are not familiar with Pivot Table, you may read the following Articles .
Previous Articles : -
Hope you have enjoyed ...
Keep reading & Stay blessed
...
No comments:
Post a Comment