Formatting Pivot Chart Dates

If you create a pivot chart in Excel, with dates along the axis, you might want to change the format of those dates. For example, if the date format is November 10, 2008, you may prefer something shorter, such as 10-Nov. However, if you try to format the dates through the Format Axis dialog box, the change won’t be successful.

The pivot chart date formats are controlled by the field format in the pivot table. To change the date format:

  1. Right-click a date in the pivot table (not the pivot chart).
  2. Click on Field Settings
  3. Change the Number Format to the date format that you want.

This tip, and the answers to your other burning pivot table questions, can be found on the Pivot Table FAQ page on my web site.

You may also like...

19 Responses

  1. Gary says:

    This only works if the date field is a value. If the date field is a row (very common usage), there is no number format button available in the Field Settings. How does one change the x-axis date format? All the obvious solutions have zero impact on what date format the chart displays…it’s always the original source date format (even if that format was changed on the source cell).

  2. Gary, in this example the date is a row field, and the Number Format button is available.

    However, you won’t see that button in the row field if there are blank cells, or non-numeric entries, in the Date field.

  3. Gary says:

    Ah. What I meant was my date field is a row label (as in months) in the PivotChart. In this case there’s no number format and the chart axis is stuck in only one date format.

  4. Cyrus says:

    Gary, Debra’s comment is still valid. If there are blank cells or non-numeric entries, you won’t see the “Number Format” button available for your X-Axis dates. I was encountering the same problem my dates were stuck in 1 format, i.e. 1/1/2008 – I checked if there were blanks in my date column and there were – this is because my pivot range for the table/chart was set to all 65,000 rows in the worksheet, even though I only had dates filled in for my data which didn’t take up the spreadsheet.

    Once you edit your range to fit exactly your data, the number format button will appear for the dates, and you will be able to change the format. This will be reflected in any subsequent charts you create. Hope this helps…

  5. Gary says:

    Cyrus, thanks for responding. It’s been a while and i don’t have the file any longer, but I do remember checking the range, looking for blanks, etc. Unfortunately that did not resolve the problem. The range was tight to the specific data, no blanks in the date cells.

  6. Stephanie says:

    Did you ever solve this problem? I am having a similar problem. I have a label that is in plain number format, and it keeps converting to a date.

    Thanks,
    Stephanie

  7. Irina says:

    Here’s a solution that worked for me. The end goal – have a (pivot) chart build from “endless” range (e.g. $A:$D, not $A$1:$D$10), where column A is represented by dates.

    1. Create a pivot for a finite range first ($A$1:$D$10 in my example)
    2. Make a pivot chart off that table
    3. Format dates in pivot table to what you need them to be (mm/d for me) via Fields List -> Row Labels -> Field Setting. The format will update in both the table and the chart.
    4. Go to “Change Data Source” and correct the reference from $A$1:$D$10 to $A:$D. Blank dates get introduced immediately and in the table, date format re-sets; however, in the pivot chart, my format of choice remains.

    (based on Excel 2010)

  8. Smiley says:

    I had the same problem and Irina’s detailed solution (the post above mine) worked! I had to go to the Pivot Table’s “Field list” to modify the field setting because the other solutions did not work.

    Thanks Irina :-)

  9. Frank Szalai says:

    Thanks for this great tip! Lifesaver and instant headache-reliever! :)

  10. Ayush Jain says:

    Thanks all, It really helped a lot.

    I was having blank cells as I was using the $A:$D range. Instead, i will use the dynamic named range for the pivot.

    Ayush Jain
    Microsoft Excel MVP
    discussexcel.com

  11. Andy Britton says:

    Had a similar problem. A PT based on and Excel ‘table’ in another sheet. I could not change date format on PT row labels – all locked on ‘d-mmm’. I tried copying the source table and making new PT – this worked so reasoned problem must be with the table. I set all number formats on source table to ‘General’ then refreshed the PT. This caused row lables to change from ‘d-mmm’ format to ‘dd/mm/yyyy’ format, which is what I wanted. Finally I re-applied the formatting to the source table and refreshed the PT again. Date formats stayed at ‘dd/mm/yyyy’.

  12. Amy G says:

    Wanted dates to be formatted MMYY. The prior person had manually entered “Jan 14” ,etc. for each date. This was reading as text in the pivot table. Initially I copied dates that were written out as 01/01/2014 into the ajacent cell with formula =DATE(YEAR(A1),MONTH(A1),1). My goal was to show the date as Jan-2014. I then formatted the column to match this. The old pivot table was refreshed but the dates were showing as 01/01/2014. My solution: I removed the “Date” column from the Field list and then placed it back. Finally the number formatting option was avaible.

  13. alamo says:

    I as much having same problem and no solution has worked. My source is a data model, in 2013, connected to a view query from sql server.
    No blanks…verified isnumber=true…and cannot change format in table or chart.
    No “number” button shows up as long as Date field is a Row…but does show “number” format button when Date field is a Value …..

    Thanks for any help on this!!!

  14. Diogo Costa says:

    It’s true, once you change the range to the exact data range, the button comes up. It’s silly to have such retriction, because if the source changes all the time, like increasing every day, you are forced to increase the pivot data range manually every day as well, in order to show the new content.

  15. Kamran says:

    Thanx my problem is Solved.Problem Was Date Formating in Piovt Chart.

  16. cincrin says:

    This worked for my instance. You are my hero.

  1. August 12, 2008

    […] Formatting dates in pivot chart, of course if you try the usual route of “format axis > number format” and set it to show the date nov-10 instead of 11/10/2008, it wouldnt work. You have to use the technique highlighted at the contextures blog. […]

Leave a Reply to Frank Szalai Cancel reply

Your email address will not be published. Required fields are marked *