Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

FIXING EXCEL PIVOT TABLE ANNOYANCES

Courtesy of
DJH Training & Application Solutions Inc.

 

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

 

 

 

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

 

 

 

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

 

 

 



More Newsletter Articles

© 2017 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 25 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit www.djhsolutions.ca.