Forum Discussion
Spacing on stacked bar charts
Hi all, I searched for a solution, but changing to 'text' axis doesn't work and that's the only solution I have seen offered.
Issue:
- When using a date axis, there is too much white space / bars are too skinny to be legible despite gap width set all the way at 0.
- When switching to a text axis, the bar spacing is perfect, but the spacing between dates are at times nonsensical or for some reason it thinks the range is way different than what is highlighted.
Here is how the data is highlighted, I only hid columns to make the picture a reasonably proportion the screenshots from above are when no columns are hidden.
Is there a way to re-scale the gap-width when using a data axis to effectively to go something like -50%? Or to somehow manipulate the range and the months between labels when using a text axis?
Thank you for the help.
Matt
4 Replies
I used these settings to come close:
Played around a bit with your problem and some random number series. Please try the following:
Step 1: Set the x-axis to a Date axis with Auto bounds and both Major and Minor Units to 1 Month. Your dates are already quarterly. What other quarterly grouping would you want? (Note I'm set to European date format dd/mm/yyyy).
Also set a custom label format to mmm-yy.
The chart will look like this:
Step 2: Now switch the axis type to Text and choose the Overlay and Gap Width that suits you. I took 100% and 15% respectively. The chart then looked like this.
Fiddling with the Min and Max bounds (Step 1) causes the chart to be compressed into the left part of the chart area, after switching to a Text type axis.
Can't explain why. Sorry.
Perhaps better to Unpivot the data first with Power Query and then load to a Pivot Chart. Without changeing too much in the default settings, you can create something like this:
Hi, Excellerator1. The date axis is preserving the actual time gaps between your dates, so if there are missing dates or uneven intervals, you'll end up with extra white space. If your goal is simply to compare values by month, a text axis is usually the better choice, but make sure the source data is sorted chronologically and the labels are true dates (or consistently formatted text).
Hi Henry,
I can confirm - as the screenshots above attest to that there are no gaps between the dates and the intervals are all even.
I think I found the source of the issue/glitch/missing feature: Excel only lets you toggle between days, months, and years for the intervals, not quarters - which is a very common interval in finance or any public business (since financials are reported on quarterly.) Is there a way we can contact someone at Microsoft to add in a Quarterly feature to the below drop down? In this case, Excell thinks there are 2 missing month between each quarter when there really is not. The text axis thing just plain does not work, the data is sorted chronologically with the same format - so that doesn't seem like a relevant path to go down.
