Jump to content


Default format

Date and column width

  • Please log in to reply
1 reply to this topic

#1 JPK1066


    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 15 April 2017 - 07:46 PM

I've been working with relationship and 1 WS Pt's and I find myself constantly reformatting the headers to an abbreviated date format as in 3/14 vs 03/14/2017. Each days refresh requires another reformatting. This is the same case with the column widths which default to 17 or 18 and take my data off the screen. Is there a way to reset the default date format and column width?





#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 16 April 2017 - 12:43 PM



I suspect you're encountering an old bug with Excel's pivot tables that causes the column widths to be reset incorrectly when a pivot table refreshes. You can prevent this from happening by going into the Pivot Table Options window and unticking Autofit column widths on update.


You can see a walkthrough of this in the video lesson: Expert Skills Lesson 5-25 Use slicers to filter multiple pivot tables.


You can also see a Preserve cell formatting on update option in this window. Keeping this enabled should prevent your date formats from being changed after a refresh.


If you've been setting the pivot table formats by formatting cells instead of using the pivot table field settings this could also account for the problem, since the pivot table will default to its own number format when adding new rows or columns.

You can see how to set the pivot table field settings in the video tutorial: Expert Skills Lesson 5-1 Create a pivot table.


I hope these are the answers you were looking for, but please feel free to reply if you are still having problems.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users