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
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
6 answers
Sort by: Most helpful
-
-
Herbert Seidenberg 1,191 Reputation points
2020-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 -
Erin Ding-MSFT 4,466 Reputation points
2020-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. -
Lz._ 9,011 Reputation points
2020-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) insteadYour 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,466 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.