Number Display (Excel 97)

Topic

New Reply

Hello,
I was wondering if it was possible in excel to display figures in ‘000s…meaning…if I type 1000000, can I have it display 1000 in the cell just by formatting it, and not use a calculation?
Thanks,
kingming

Reply | Quote

Replies

Select the cells you want to format.
Select Format | Cells…
In the Number tab. select the Custom category.
Enter 0, in the Type box (zero followed by the thousands separator)
Click OK.

Reply | Quote

thanks hansV that works perfectly.

Reply | Quote

thanks hansV that works perfectly.

Reply | Quote

Select the cells you want to format.
Select Format | Cells…
In the Number tab. select the Custom category.
Enter 0, in the Type box (zero followed by the thousands separator)
Click OK.

Reply | Quote

Try this Custom Format:

#,###,

Reply | Quote

Try this Custom Format:

#,###,

Reply | Quote

I have a similar question. How can I make a number display as thousands, e.i., I’d like to enter the number 1 and have it display as $1,000.

Reply | Quote

Format – cells – number(tab)
Category: custom
Type:$0″,000″

Steve

Reply | Quote

Select the cell or cells.
Select Format | Cells…
Activate the Number tab (if necessary).
Select the Custom category.
Enter the following custom format in the Type box:

$ #,##0",000"

Click OK. the text between quotes will be added to every number you enter.

Reply | Quote

Thanks Hans.

Reply | Quote

Thanks to your great customizing cells instructions, the numbers I enter are now displaying as thousands (i.e., when I type 3, the cell displays $3,000).

My new problem is that the cells customized with $#,##0″,000″automatically round numbers up or down to the nearest thousand, rather than showing actual number entered (i.e., when I type 1.3 the number displays as $1,000, and when I enter 1.5, the number displays as $2,000). Is there a way to stop the rounding so the cell shows (and more importantly adds) $1,300 and $1,500?

Reply | Quote

This is quite a different question – this can’t be handled by a custom format. You could create a Worksheet_Change event procedure, but it has a disadvantage, as I will explain below.

– Set the format for the cells to a standard currency or financial format.
– Right click the sheet tab of the worksheet.
– Select Show Code from the popup menu.
– Copy the following code into the module that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range(“A1:A10”))
oCell = oCell * 1000
Next oCell
Application.EnableEvents = True
End If
End Sub

– Replace both occurrences of A1:A10 with the range you want to have multiplied by 1,000 automatically.
– Switch back to Excel.

The downside of this code is that you cannot easily edit existing values. If you enter 1.3, this will be changed to 1,300. If you then edit this to 1,400, this input will be multiplied by 1,000 again, resulting in 1,400,000. So instead of editing the existing value, you must enter 1.4; this will be converted to 1,400.

Reply | Quote

Hans:
I hope I’m not exceeding your patience with this follow-up.

The code works (thanks very much) but only after I enter the number in the cell, click into any other cell and click back into the original cell. For example, when I type 1.3 into a cell in the code range, then Enter; the cell shows 2; I click out of the cell then back into it and it shows $1,300.

I’ve reviewed your instructions and think I followed them accurately. Also, I am actually in Excel 2003 and not 97, if that is relevant. Is there a way to have the final number show without having to exit and reenter the cell?

Many thanks,
Susan

Reply | Quote

You can click the green check mark to the left of the formula bar to confirm data entry without moving to another cell.

Reply | Quote

It sounds like you put Hans code into the wrong event routine. The behavior you describe would be the case if you put his code in the Worksheet SELECTION Change event routine. The code must go in the Worksheet Change event routine. Those are easy to get confused.

Reply | Quote

I had entered the code into the wrong place and moving it rectified the problem. Many thanks.

Reply | Quote