Cell Format

[BeveyG]

Hi,

I am working with a spreadsheet that has cells with dollar values in the
millions. Can I format the cells from the typical $3,775,000 to $3.77? When
the spreadsheet is updated and a chart is created, it is difficult to read
the cells as the chart covers a 3 year period.

Thanks.

 

[art]

Why don't you use this?

For example the number is in cell A1

=A1/1000000

 

[BeveyG]

Hi Art,

That worked for me but now I am not getting the actual to date to show up.

Row B is Plan Per Baseline $3,775,000
Row C is Planned Cumulative a forumla = B3+C2 = $4,747,500 - cell B3
contains 972,500
Row D is Actual to Date and repeats the numbers from Row B
Row E is Actual Cumulative and is a sum of C5+D4

I modified B2 to =3775000/1000000 and changed format to number with 1
decimal point
Row C remains a formula
Row D is manually typing in 3.8 with format to number with 1 decimal point

The information then disappears from the chart... What am I doing wrong?

Thanks so much for your help.

BeveyG

 

[Gord Dibben]

About the closest I can get is a custom format of $#,##,

which returns $3,775

Remove the $ sign for one less character 3,775

Anyone looking at your chart knows it is currency.....right?


Gord Dibben MS Excel MVP

 

[Reeza]

Hi,

I am working with a spreadsheet that has cells with dollar values in the
millions.  Can I format the cells from the typical $3,775,000 to $3.77? When
the spreadsheet is updated and a chart is created, it is difficult to read
the cells as the chart covers a 3 year period.

Thanks.

For the Chart I know you can change the Display Unit, go under scale
and modify from one to million or some suitable data.

 

[art]

I don't understand when you say the information from the chart is missing.
Which chart? Also please refer to columns as A, B, C... and to rows as
1,2,3,4 and so on. Columns are only alphabets and rows are only numbers.

Let me know so I should be able to help.

 

[BeveyG]

Hi Reeza,

Thanks for your response.

I attempted that yesterday, but because the number I entered in the
worksheet was 3.8 (representing 3,775,000 after using the suggested formula
of =3,775,000/1000000) the chart would not recognize the number and show a
column because the number did not match the scale. I worked with the scale
and still could not get the column to appear. Any suggestions. I am working
with a scale from $0 to $8,000,000.

I appreciate any suggestion you might have as we are trying to make the
chart more legible for our client.

Have a great morning,

BeveyG

 

[Rick Rothstein]

Try this Custom Format...

$0.00,,

 

[BeveyG]

Hi Rick,

THANK YOU SOOOOOOOOOOOO MUCH. That worked.

The next problem I end up with is that the chart that I create from the data
won't reflect the millions of dollars. Because the custom number you
suggested takes the monetary recognition to a $3.78 value the chart axis
then is scaled in millions and
the chart would not recognize the number and show a column because the
number did not match the scale. I worked with the scale and still could not
get the column to appear. Any suggestions. I am working with a scale from
$0 to $8,000,000.

I appreciate any suggestion you might have as we are trying to make the
chart more legible for our client.

Have a great morning,
BeveyG

 

[Gord Dibben]

Did you try my custom format of $#,##,

It won't round, but gives you a couple more digits displayed.


Gord Dibben MS Excel MVP

 

[BeveyG]

Hi Gord,

I did and the format worked, however when I update the spreadsheet to modify
the chart, I don't get any columns because I can not figure out what range to
type in on the axis to reflect the modifications to the numbering format.
Are you a wiz with charts too? I have spent too much time trying to modify
this and would really appreciate help.

Thanks again,

BeveyG