So. We're doing charts in Excel as our Sunday morning family project. Along the X axis, we have the weeks from now till the summer. Along the Y axis, we have our weights. No problem with maximum, minimum, major intervals (7 pounds), minor intervals (1 pound). How do I get the Y axis labels to say "14 st." or "14 st. 7lbs" -- ie, how can I tell it that the number format should be stones and pounds?

Oh, yes, I know we only use that format in the UK. But frankly, Microsoft have a great big customer base here and weight charts isn't exactly an uncommon use of spreadsheets, is it?

I do love the chart formatting in this version of Excel though. I'm pretty sure that I can't go into my work version of Excel and define the exact spot size, stroke and fill, shadow... mind you, roll on Apple spreadsheets, which I am sure will be MUCH PRETTIER.

- Hello All You Excel Peeps!

bugshaw(I've never actually tried this...)

andrewduckerbohemiancoastarmbDo it all in kg? :-)

andrewduckerGlad there are some very experienced people on here :->

annafddSelect the Y axis, either by clicking on it or by choosing "Value Axis" from the drop-down menu in the "chart" strip menu.

Right-click or command-click on it (or choose Format from the applicaiton menu), a menu pops up. Choose Format Axis.

In the window the pops up, choose "Number".

Select "Custom".

At this point, start from one of the options - for weights, you might start with 0.00 or delete a zero and choose just one decimal.

Now in the little window just above the list of options, put the cursor after the number and type "st" (include the ").

At this point, Anna notices the further complication of the lbs. Ouch. There must be a way to make the lbs appear... Well... 0"sts".00"lbs." does the trick, assuming that 10 lbs = 1 stone, which this Metric person doesn't know. If it's not, I'm afraid you have to resort to very, very complicated formulas.

annafddEnter stones and lbs in two separate columns, B (stones) and C (lbs) - I will assume for semplicity that A is the column with the week number. Now, in column D, you type: =TEXT(B2,0)&"st. "&TEXT(C2,0)&"lbs."

Now, making this the Y axis.... I don't think that's possible. :-(

del_cFirst, let's take care of turning a number in pounds or stone into text. If your numbers are in pounds, create a range in pounds starting with your desired Y-axis minimum, ending with the maximum, and having the values you want along the axis. Then use the formula

=SUBSTITUTE(TEXT(B1/14,"?0 ""st."" ?0/14"),"/14","lb")to convert into the form "13 st. 7lb", etc. B1 is the cell where the initial value in pounds is. If you're working with stone, use B1 instead of B1/14. Or you can just write the text in, it's not as if this is going to have any connection to your actual data, it will be completely a dummy axis.

To the right of the text, add a range of cells with zeroes in (or any number you like; it's just a dummy. But zeroes keeps it simple). Now add the text labels and the zeroes to the chart as another series. It will look weird at first, but persevere. Select the new series, and change the Chart Type of just that series to "Horizontal Bar Chart". You now have what's called a combination chart-- some of the series are one type, and others are of another. The thing about a bar (not column) chart is that it has the category axis *down the left side*. This is the only time you'll see text labels on a "Y" axis, because this is of course the bar chart's X axis.

The remaining steps are easier to do than to describe. Ensure that the stone labels match up with Y axis of the actual data-- you must do this by hand, because there's no logical connection between them, it's only for presentation purposes. Then format the dummy series so it's invisible, eliminate the series from the Legend (taking care not to actually erase the series; it must stay, but without being seen).

Because the combo chart has two X and two Y axes, format the second X axis, the one on the top, so that it's either invisible, or pleasingly matches the one on the bottom. You can get rid of it, but sometimes you wish you hadn't, so best to leave it on the chart, either invisible or making itself useful.

Because the two Y axes are both on the left, they can interfere annoyingly with each other when you're trying to change one, so use the Primary X axis properties to move the Primary Y axis over to the right, by checking the "Value (Y) axis crosses at maxmimum value" box in the Scale tab. When you've ensured the two Y axes match numerically, you can, again, either delete the primary Y axis, format it invisibly, or format it aesthetically.

I think that's the lot. Here's a web page that may help, by Charley Kyd. It's for a completely different kind of chart, an interesting form called a "dot plot" that Excel can't normally do. I mention it because it uses the same "bar chart" trick to put labels down the side of a graph, which is otherwise impossible.

Or you could follow

papersky's recommendation, and just use pounds or kilograms; it's less hassle in the end.bohemiancoastBut thanks to everyone for their hard work in sorting it out, and big Boos to Microsoft for not letting me format a number as stones and pounds.

bugshawI have had a bit of luck with pivot tables, dates against weight in 13:08 format, but it's not pretty. You can instantly see how many times your weight measurement has been x, though.