Discussion


Note: Images may be inserted into your messages by uploading a file attachment (see "Manage Attachments"). Even though it doesn't appear when previewed, the image will appear at the end of your message once it is posted.
Register Latest Topics
 
 
 


Reply
  Author   Comment  
rjmorgan

Registered:
Posts: 4
Reply with quote  #1 
On page 8 of the Second Edition (and elsewhere in the book), the author shows a horizontal bar chart that is a nice alternative to pie charts. I've managed to recreate it fairly easily in Excel, but cannot get the labels to justify (right-align). Only the percentages correctly right-justify. Using the example on page 8, "Company C" and then "8.85%" is below "Company A" and "13.03%." The 8.85% and 13.03% are right-aligned, but the alignment of the "Company" labels, while appearing to be right-justified, do not line up correctly. Is there any way to fix this in Excel, without having to resort to other software to fix after-the-fact?
jlbriggs

Registered:
Posts: 191
Reply with quote  #2 
can you post an image and/or the excel file?
rjmorgan

Registered:
Posts: 4
Reply with quote  #3 
Sure. I attached screenshots and copied the Excel data. (Note I had to mask the data.)

Playing with the spacing some more between the text label and the percentages, I did get the text labels to line up better (right-justified), but they're still not aligned perfectly. Maybe I'm expecting a little too much out of Excel, but I liked the author's chart and I was hoping to replicate it.

Thanks.

Capture.JPG 


Capture2.JPG 

Row 1: Column1Jrnswtilarnt    0%Ilkrsmtaahb    2%HJII    6%Glw    7%Faalkjjliii     8%Efjreze   10%DFA   11%CRC   14%Bfdkll   17%Afjfalk   24%

Row 2: Share0.4%2.0%6.3%6.9%8.3%10.3%11.2%13.7%16.9%23.9%
 





pzajkowski

Registered:
Posts: 46
Reply with quote  #4 
Here's one way to do it (see screenshot). The "trick" is to concatenate two fields in a formula. The key is to combine the % values with a string of spaces, and then take the right() portion of those values. Take a look at the formula that's visible in the screenshot to see how labels from column B are combined with the values in column D.

In case you're wondering, the TEXT() function converts the decimal values into a percent as string which is then combined with three spaces "   " of which I take the right-most 5 characters; combine the output from the Right() function with the labels in column B to complete the desired concatenation.

Right-Justified Axis labels barchart.jpg 
--Pete

rjmorgan

Registered:
Posts: 4
Reply with quote  #5 
Thanks Pete. I tried your formula (below), but my data didn't line up nearly as nicely as yours. Not sure why....

Capture3.JPG 

Below is the result; I tried using dummy data that was "better" (same number of characters & usage of caps):

Capture4.JPG 

The other issue is that as you can see, it didn't like a value of zero that I had - it returned just the label and the "%".

pzajkowski

Registered:
Posts: 46
Reply with quote  #6 
Technically, my example also suffers slightly from the right-alignment problem with the text labels. Any label that is associated with a smaller length of percentage (i.e., anything between 0% and 9%) will be off slightly.  One way to get around this problem is to use a mono-spaced font like Courier New. Mono-space fonts reserve an equal amount of space for any character, including spaces. Non mono-space fonts are proportional such that each character takes up their own unique space. My example and yours are using proportional fonts so the alignment will be off.

So, change your font to Courier New and you will find everything lines up.

As for the odd "%" without a zero, change the TEXT formula to use "0%" instead of "?%" and it should resolve that problem.

But, there are other things to consider. Stephen Few's examples have been worked on for perfect presentation in his books. A graph may have been initially produced in Excel, but he also uses Illustrator (I believe) to enhance the presentation so that he can publish an ideal graph exactly as he would recommend, regardless if any software presently has (or doesn't have) the capability to graph/label data as he presents it.

Anyway, attached is my attempt to improve on your example. I added an "IF()" clause to the Right() function to deal with percentages that are less than 10% so that an extra space can be included between the category label and the rate. Since I'm still using a proportional font, there's still the slightest difference in text alignment, but not noticeable if you aren't looking for it. I also discovered the changing the point size of the font can alter the perceived alignment. Didn't check what the impact is when actually printed, however.

Lastly, there are likely other ways to tackle this problem. I know a good way to pull this off successfully in Microsoft reporting services (SSRS). I'm sure this could be achieved in other software programs as well. Just a little trickier in Excel.

Right-Justified Axis labels barchart.jpg

jlbriggs

Registered:
Posts: 191
Reply with quote  #7 
So far the only way I have come up with to get things aligned this way is to

1) enable data labels for the data

2) set the axis label spacing high enough to move the labels away from the axis

3) align the data labels to 'inside base' and physically drag them to the left into the space between the axis and the axis labels

It's fine for a one time chart, but for something more automated or dynamic, it's useless.
I had hoped there would be additional options for aligning the data labels, but I haven't found any.

Another option would be to use the in-cell data bars, but then you lose the y axis scale.

rjmorgan

Registered:
Posts: 4
Reply with quote  #8 
Thanks guys for the options. Pete, great formula (I will be using again & again) and explanation. Appreciate it.
Previous Topic | Next Topic
Print
Reply

Quick Navigation: