top of page

Microsoft Excel
Fix Pivot Table Annoyances

Excel Icon.png
Back Button

1. COLUMN WIDTHS AUTO-ADJUST ON REFRESH

After spending time adjusting pivot table column widths so they fit perfectly, they suddenly resize when the pivot table is refreshed - so frustrating! Here is how to turn this feature off.

 

1. Right-click anywhere in the pivot table, select Pivot Table Options... > Layout & Format tab

2. Clear the check mark from the Autofit column widths on update option

Pivot Table Annoyances 1.jpg

2. OLD DATA STILL SHOWS IN DROP-DOWNS

Data has been deleted from the pivot table source but still appears in pivot table drop-downs, even after refreshing. To resolve this, follow the steps below.

 

1. Right-click anywhere in the pivot table, select Pivot Table Options... > Data tab

2. Select None from the Number of items to retain per field option

3. Refresh the pivot table

Pivot Table Annoyances 2.jpg

3. FORMATTING IS LOST WHEN PASTING VALUES

We often need to send a pivot table to someone without retaining the link back to the source data. The solutions is to copy and paste values, which will turn the pivot table into a regular data range. The values paste fine, but the formatting is lost.

There is a simple but powerful solution to this.

 

1. On the Home tab > Clipboard group click the dialog box launcher button

2. Note this opens the Office Clipboard window on the left of the screen

3. Select and copy the pivot table - notice the thumbnail that appears in the Office Clipboard

4. Select the cell where you wish to paste and click the thumbnail

5. The pasted data looks exactly like the original pivot table, but with no link to the original data

Pivot Table Annoyances 3.jpg

© 2017 DJH Training & Application Solutions Inc.

bottom of page