# Cell Format

##### New Member
I know this must be something very simple, such as the cell format or something, but I can't figure it out.
When I type in

=sum(b2:e2)

That's exactly what shows up. The formula doesn't calculate anything. I think the cell may be formatted to show everything as text, but if so, what format do I change it to, in order for it to calculate the values? Scientific? Currency? Time? I've tried them all, and nothing makes the formula work.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try formatting the cell as General then re-enter the formula.

Your cell was formatted as text when you entered the formula. Change the cell format to general, then go back into the formula bar and hit enter.

OK, well I tried that, and now it's calculating, but it's wrong.
Here are the values for the cells, and the sum it's coming up with

B2 C2 D2 E2 total

44 49 36 64 0 <--- this is where I have the formula =sum(b2:e2)

Why would it show a total of zero? It should come out to 193.

I don't understand why it's calculating now, but it's wrong.

What does this return?

=ISNUMBER(B2)

Those cells might have gotten stored as text too. To check if they are stored as a numerical value, try:

=ISNUMBER(B2)

What did that return?

I input isnumber(b2) and it now says "FALSE"

Ok - the "numbers" you have in your spreadsheet are actually stored as text. (Right-click on their cells, go to format, and you will likely see the number format as "text")

How are these numbers generated? Do you manually type them in? Are they the result of a formula? Are they imported from somewhere else?

MrKowz, when you said that, about them being stored as text, it got me to thinking, and so I checked and you're right.... all the cells it's trying to sum up are in text format. I changed them to general format, re-entered the values and =sum(b2:e2) now works right.
Ugh.....
But this means I'll have to do this for every value that isn't working right across my whole spreadsheet.

Aaaaaooooooaaaaooooohhhhh!!! (<------ that's the sound of me wailing loudly!)

Thanks for helping me straighten this out.... now I can continue on with my statistics class.
thanks again -

Steve

We can use a macro to speed up this process.

Run this macro on your worksheet. Be sure to back up your worksheet prior to running:

Code:
``````Public Sub FixNumberFormat()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In ActiveSheet.UsedRange
With rng
If .Formula = .Value Then
.NumberFormat = "General"
.Value = .Value
End If
End With
Next rng
Application.ScreenUpdating = True
End Sub``````

Replies
15
Views
512
Replies
7
Views
201
Replies
1
Views
150
Replies
3
Views
178
Replies
0
Views
144

1,221,424
Messages
6,159,824
Members
451,592
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back