Making Data and Axis Labels Different

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

I have a simple column chart that uses millions of units on its Y axis and
puts a label above each column showing the exact value. At the moment the Y
axis shos all the trailing zeros behind each million value, somethiong like:

40000000
36524102
35000000 x
.. x
.. x
.. x
5000000 x
colummns here

What I would like to show is the Y axis values without the trailing zeros
but the data label in full. I seem to be able to do one or the other but not
both.

Hope this makes sense!

This is in Office 2003.

TIA

Dave
 
you can create a secondary axis and hide with same data series you already use. Then, display primary verticle axis values in millions and do not add labels but add data lables to 2nd axis with full number format. this is because your 2nd data series is behind the 1st
 
Use a custom number format that removes the excess zeros.

To show all digits, use a number format like '0' (entered without the
quotes).

Add a comma after the format to drop three zeros:

'0,' shows 1000 as 1
'0.0,' shows 1200 as 1.2

Add another comma for each block of three zeros you want to suppress.

'0,,' shows 14,000,000 as 14
'0.00,,' shows 12,340,000 as 12.34

To use a suffix to indicate millions:

'0,,"M"' shows 14,000,000 as 14M
'0.00,,"M"' shows 12,340,000 as 12.34M
Don't enter the single quotes, but do enter the double quotes.

More on number formats:

Number Formats in Excel
http://peltiertech.com/Excel/NumberFormats.html

- Jon
 
Back
Top