Formatting Cells in Excel 2003

[Bernie]

We have recently converted from Office 2000 to Office 2003.
In Excel, say you have a list of numbers down a column,
with an autosum at the end to add all the numbers up.
Well, the cells all need to be of type 'number' in order
for the autosum to recognise the value within the cell and
include it in the calculation. Well, in Excel 2003, we're
finding that if you select all the cells included in your
calculation, click 'format cells', and choose the type of
format you want to use (ie. number), it doesn't update
automatically. You have to go and re-enter each each
number after the formatting has been applied before the
value changes to type 'number' (or whatever other type of
format you have chosen e.g. currency for that matter) and
the calculation works. Any ideas?

 

[Paul]

We have recently converted from Office 2000 to Office 2003.
In Excel, say you have a list of numbers down a column,
with an autosum at the end to add all the numbers up.
Well, the cells all need to be of type 'number' in order
for the autosum to recognise the value within the cell and
include it in the calculation. Well, in Excel 2003, we're
finding that if you select all the cells included in your
calculation, click 'format cells', and choose the type of
format you want to use (ie. number), it doesn't update
automatically. You have to go and re-enter each each
number after the formatting has been applied before the
value changes to type 'number' (or whatever other type of
format you have chosen e.g. currency for that matter) and
the calculation works. Any ideas?

And I suspect you would find exactly the same in Excel 2000 if you started
from EXACTLY the same data.
If SUM doesn't recognise them as numbers then they are text strings.
Changing the cell format alone will not change them from text to number. It
is also necessary to edit the cells (as you found) or to coax them to change
as follows:
Copy a blank cell that is formatted as general or number - NOT text.
Select the text strings that you want to make into numbers.
Use Edit > Paste Special > operation, Add > OK