Pivot Chart - Axis

[TheRevenant]

In the attached Excel sheet, I have created a pivot table and a pivot chart.

In the pivot chart you will see that the year doesn't look good.

1. I would like the month to display, preferably horizontally than vertically.
2. The year is option and can be removed

Does anyone know how to do this without manually edited the original data source?

I tried doing it but it will not sort the month chronologically if I do.

[stasinek]

Hello,
At the beginning, add dates to the row labels. Group by month by clicking the right mouse button on data with dates.
Insert column chart - ready. You can add a slicer to the category.

[TheRevenant]

Sorry, it has to be a clustered bar not a clustered column. The reason for doing so is because I have too many entries and need to present it in a simple way.

I also need the Month and Day. Year is not necessary.

Is this possible?

[stasinek]

That maybe in this way?

[Roel Jongman]

It looks like that Excel is not that great in handling multlevel category labels, as far as I know you have little influence on how they are presented on the Y axis.

I did make it a little better. The date is now shown under the letter by unchecking the multilevel category level option in the Formating Axis menu item
Also reformated the date in the pivot to only show day and month.

see attached workbook for the result and the screensshot on what settings I changed to get it to look like that.

[TheRevenant]

It looks like that Excel is not that great in handling multlevel category labels, as far as I know you have little influence on how they are presented on the Y axis.

I did make it a little better. The date is now shown under the letter by unchecking the multilevel category level option in the Formating Axis menu item
Also reformated the date in the pivot to only show day and month.

see attached workbook for the result and the screensshot on what settings I changed to get it to look like that.

Roel that's really close. Can you explain the 4th column in Sheet1 ? How did you remove the year?

Is there a way to change the month format? '01' to 'January' ? without manipulating the data at the source? I'm not sure if you can construct the pivot table in order anyways if you do this.

[Roel Jongman]

Sure,

that is by formatting the column of the pivot, simular to how you format normal cells.

- While on any date in column A. do right mouse click
- Pick "Field settings" from the menu
- On the bottom right there should be a "number format" button where you can pick the desired date format from a list.
There should also be an optoin to show "26-jan" instead of 26-01.

[TheRevenant]

Hey Roel,

this is almost close! Is there a way to rotate the date horizontally as opposed to viewing it vertically so you don't have to angle your head? it is related to the (Format axis->Labels->)Multi-level Category Labels but you cannot rotate the label.


The thing is, I could have multiple "Category A"'s in your sheet with the same date.

How did you get Category A to appear in its separate column in the Pivot table? I just have

RowLabels and Sum of Value where the dates drill down by Category A

[TheRevenant]

Hey Roel,

how did you get column to appear as it did in the Pivot Table from Sheet 1?