• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Horizontal Axis Labels Weirdness

I have two column charts on the attached excel doc. Both data sets are fairly identical, using the same formulas and such, and the charts are set up the same (from what I can tell, but the horizonal axis labels are inconsistent between them both and I cannot figure out what is causing the difference. I want the top chart axis label to be formatted the same as the bottom chart. NOTE: the datasets in the charts are power query tables (along with my own formulas), so I'm hoping that won't be an issue for viewing.

I have column charts showing fees by practice by year where each year is its own series; I've created a custom column for the label that shows year as well as the total fees for that year so as to plot like the layout below (on my datasets, the label and practice are to the right of the year data).

LABEL PRACTICE 2019 2018 2017
2019 (97,857) EMPLOYMENT 20,867 0 0
GENERAL 3,360 0 0
2018 (161,628) EMPLOYMENT 0 7,419 0
GENERAL 0 6,254 0


In the top chart, the label is "centered" between the first practice category of the year, and axis lines separate each practice within the same year, whereas in the bottom chart, the label is centered between all categories in the year, and there are only axis lines between the years.

The attached doc has both charts, the datasets, and screen shots of the select data dialogue box. In the top chart, the horizontal axis labels look like this:

2019 (97,587) EMPLOYMENT
FAB
GENERAL
GOVERNNANCE

In the bottom chart, they look like this:

2019 (147,879) EMPL
2019 (147,879) FAB
2019 (147,879) INS

Any thoughts as to how to get the top chart to be formatted like the bottom chart? It's probably something very simple, but I absolutely cannot figure it out!

Thanks!
Y
 

Attachments

  • Charts Question.xlsx
    274.2 KB · Views: 5
Seems to be due to your central alignment. It you'd align left it is showing more correctly.
62430

I guess it has to do with the fact your "sub labels" aren't the same numbers per section (8-10).
Try to make all labels appear for each year, and order them in the same way.
 
Thanks for taking the time to look into this.

I actually did figure it out. The columns that I used for the axis labels were formatted with this custom formatting: _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_) (numeric formatting). When I changed the formatting of both of those columns to General, it worked the way I wanted it to. I figured it had to be something simple, but I never even thought to check that until I did more troubleshooting this morning.

Thanks again!!
 
Good to know, so thanks for posting that. I would never think about looking at source format. Still I find it a strange side effect of a custom format.
If anyone has an explanation for it, I would like to know.
 
Seems to be due to the padding for the brackets in the text format section. If you check Len(activecell.text) for one of the supposedly blank cells in the top table, it returns 2 and not 0, which I assume prevents Excel from ignoring that cell for its labels.
 
Back
Top