3,931 questions
This is because cells contains blank or text. Check data source and only select the arrange which don't include blank cells.
Number format is missing from "Field Settings" and I cannot format dates on x-Axis of Pivot chart
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(115.19999999999999, 81%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMV%3C/text%3E%3C/svg%3E)
Mor va
11
Reputation points
Hey,
On Pivot Chart's X-Axis, I cannot adjust the Date format to anything.
My source data is in Power Pivot, the format on PowerPivot matches what I want.
I have attempted the fixes found online:
Change the PowerPivot source format
Remove the field and add it again and change format
Thanks
Microsoft 365 and Office | Excel | For business | Windows
Community Center | Not monitored
46,246 questions
{count} votes
-
Deva-MSFT 2,271 Reputation points Microsoft Employee2020-12-11T16:08:50.017+00:00 Added right tag/teams to help!!
Sign in to comment
6 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(208, 7.000000000000001%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAC%3C/text%3E%3C/svg%3E)
Alvin Cai 11 Reputation points2022-01-24T03:27:18.88+00:00 -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 74%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETF%3C/text%3E%3C/svg%3E)
Tony F 5 Reputation points2023-03-23T13:55:59.0066667+00:00 Thank you! Once I limited my data source range from '...$A:$I' to '...$A1:$I525' to include only rows with valid data, the 'Number Format' option became available. The good news is that I was able to change the date format and then revert the data source range back to '...$A:$I' and my date format stuck. Best of both worlds - I have the date format I want, and I can continue to add rows to my data which are reflected in my charts wit a simple 'Refresh'.
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 99%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHS%3C/text%3E%3C/svg%3E)
Herbert Seidenberg 1,191 Reputation points2020-12-12T02:20:44.067+00:00 Excel 365 Pro Plus with Power Pivot and Power Query.
Custom date format for PivotChart.
Share file if need be.
https://www.mediafire.com/file/3l9qujhf5ya9952/01_06_19a.xlsx/file
https://www.mediafire.com/file/w24c51tlntneahj/01_06_19a.pdf/file-
Mor va 11 Reputation points
2020-12-14T14:14:57.337+00:00 here is the link: https://drive.google.com/file/d/1jysLoGDKm1GECIKbU-3XjB441gpZtq2u/view?usp=sharing
**** All original data is altered
Sign in to comment -
-
Erin Ding-MSFT 4,476 Reputation points2020-12-14T08:23:12.887+00:00 According to my test, I could reproduce your issue that if source data is in PowerPivot, the option Number Format cannot be found.
As a workaround, you could try to add the source data just to Excel sheet instead of PowerPivot and then make sure there are no blank cells or cells that containing text.
Since if a field has blank cells or cells that contain text, you might not be able to format the numbers, because that field is treated as Text data type.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.-
Mor va 11 Reputation points
2020-12-14T14:15:41.13+00:00 Thanks for the information, however, that is not an option. do you think this is a bug? can we report bugs?
Sign in to comment -
-
Lz._ 9,016 Reputation points2020-12-14T18:04:16.35+00:00 Hi @Mor va
From what I read the formatting options are not the same for Pivots and Power Pivots...
Added 2 calculated columns (Year & Month) to your table as you already have others - if that was for me I would build a Calendar with Power Query (let me know if you want a sample) instead
Your updated workbook is avail. here. Any question let me know and if this solves your problem please mark this post as answer to help others with a similar challenge. Thanks
-
Erin Ding-MSFT 4,476 Reputation points
2020-12-24T02:09:44.183+00:00 This may be by design.
If you would like to report this, you could give your feedback on the Excel UserVoice forum.
This website is a place that Microsoft uses to collect users' feedback.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.