Hi,

Is it possible to format cells so that Excel shows the data to 3 significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

Yes, it is possible to format cells in Excel to display data to 3 significant figures. Here's how you can do it:

1. Select the cells that you want to format.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Number" tab.
4. In the "Category" list, select "Custom".
5. In the "Type" field, enter the following format code:
   Formula:

   0.###E+0 

6. Click "OK" to apply the format to the selected cells.

This format code will display numbers to 3 significant figures and will use scientific notation for very large or very small numbers. For example, the number 1234 will be displayed as 1.23E+3, and the number 0.00123 will be displayed as 1.23E-3.

Note that this format code will always display a decimal point, even if the number is a whole number. If you want to remove the decimal point for whole numbers, you can use a conditional formatting rule to apply a different format to whole numbers.

hi
what's wrong with formatting it to Number with 3 decimals places?
or did i miss somthing?

regards
FSt1

"wb198" wrote:

Hi,

Is it possible to format cells so that Excel shows the data to 3 significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

Give an example of a few original #'s and then how you want them
displayed.

The OP didn't want 3 decimal places (123.456), he wanted 3 significant
figures (0.456 or 123 or 3.46).
--
David Biddulph

"FSt1" wrote in message
...
hi
what's wrong with formatting it to Number with 3 decimals places?
or did i miss somthing?

regards
FSt1

"wb198" wrote:

Hi,

Is it possible to format cells so that Excel shows the data to 3
significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a "." if the number has no decimals.




"HKaplan" wrote:

Give an example of a few original #'s and then how you want them
displayed.

Hello,

You can take the number format
##0E+0
but it is the scientific format...

Regards,
Bernd

On Jan 18, 10:25*am, wb198 wrote:
If this isn't possible, maybe someone knows a format that will
at least allow me to control the decimals (limit to 2 or 3 decimal
places), and not put a *"." if the number has no decimals.

Scientific format with 2 decimal places is the only way I know to
ensure that only the first 3 significant digits are displayed. But I
presume that is not acceptable, especially considering your last
comment.

You might process the Scientific-formatted number. For example:

=left(text(A1,"0.00E+00"),4)*10^right(text(A1,"0.0 0E+00"),3)

Using the General format, that seems to get what you want for numbers
with no more than 8 significant digits to the left of the decimal
place or 6 significant digits to the right of the place. That is,
numbers between 0.000100 and 99949999. Some numbers less than 0.0001
will work if they have less than 3 significant decimal digits (e.g.
0.000009, but not 0.0000091).

PS: Of course, it would be more efficient if you put =TEXT(A1,"0.00E
+00") into a helper cell, which you can hide.

Errata...

On Jan 18, 11:14*am, I wrote:
=left(text(A1,"0.00E+00"),4)*10^right(text(A1,"0.0 0E+00"),3)

Using the General format, that seems to get what you want for
numbers with no more than 8 significant digits to the left of the
decimal place or 6 significant digits to the right of the place.

Probably more correct for me to say "no more than 8 digits to the
left" or "6 digits to the right".

Also, the LEFT(...,4) expression works only for non-negative values in
A1. To accomodate negative values, change that expression to:

left(text(A1,"0.00E+00",4+(A1<0))

Hello,

IF using a formula, why not
=--TEXT(A1,"##0E+0")
formatted General?

Regards,
Bernd

On Jan 19, 2:13*am, Bernd P wrote:
IF using a formula, why not
=--TEXT(A1,"##0E+0")
formatted General?

Does that work for you? If so, what version of Excel are you using?
Using Office Excel 2003, that does not meet the OP's specifications,
if I understand you correctly. I put that formula into B1, formatted
General. In A1, I try the following values:

A1: 4567000
OP wants: 4570000
My formula: 4570000
Your formula: 5000000

A1: 2.4678
OP wants: 2.47
My formula: 2.47
Your formula: 2

A1: 12.35
My formula: 12.4
Your formula: 12

A1: 0.001235
My formula: 0.00124
Your formula: 0.001

Note: Your formula does work the OP's examples of 567.9 and 0.235.

Hello,

A dot (comma) did not show up, sorry:
=--TEXT(A1,"0.00E+0")

Regards,
Bernd

On Jan 19, 1:15*pm, Bernd P wrote:
=--TEXT(A1,"0.00E+0")

Yes. Getting your point moments after pressing Submit and leaving for
the gym, I realized that would work. Klunk! But arguably, my
original solution is better for anyone who gets paid by the character
<g.

No, I think you'll need a ROUND formula, such as =ROUND(A2,2-INT(LOG10(A2)))
--
David Biddulph

"wb198" wrote in message
...
Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having
to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a "." if the number has no decimals.

"HKaplan" wrote:

Give an example of a few original #'s and then how you want them
displayed.

On Fri, 18 Jan 2008 07:06:01 -0800, wb198
wrote:

Hi,

Is it possible to format cells so that Excel shows the data to 3 significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

In Excel, "format" usually refers to changing the display without changing the
underlying value.

That being the case, I don't believe you can, other than by using the
Scientific format as pointed out by others.


--ron

On Jan 18, 1:25*pm, wb198 wrote:
Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a *"." if the number has no decimals.



"HKaplan" wrote:
Give an example of a few original #'s and then how you want them
displayed.- Hide quoted text -

- Show quoted text -

Yes, you CAN do this. It is done with the Thousands separator in
custom number formats

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a
multiple of 1,000, include a comma in the number format. To
display As Use this code
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,

On Mon, 18 Feb 2008 05:16:46 -0800 (PST), wrote:

On Jan 18, 1:25*pm, wb198 wrote:
Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a *"." if the number has no decimals.



"HKaplan" wrote:
Give an example of a few original #'s and then how you want them
displayed.- Hide quoted text -

- Show quoted text -

Yes, you CAN do this. It is done with the Thousands separator in
custom number formats

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a
multiple of 1,000, include a comma in the number format. To
display As Use this code
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,

The OP gave some sample's and how he would like them displayed.

How would you devise a format that will meet his requirements, and not have to
be individualized for each entry?

It can be done fairly easily with VBA, but I don't see how your advice applies,
without manually changing the format for each entry variation.

Here are the OP's samples:

Sample #'s Desired display
4567000 4570000
569.9 568
2.4678 2.47
0.235 0.235

--ron