# In Excel, how can I round or display numbers to 2 significant figures (the two leading non-zero digits of a decimal number)?

In Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example:

• `0.00002423` should read `0.000024`,
• `0.242` should read `0.24`,
• `0.0032255` should read `0.0032`,

and so forth.

• You have chosen your examples badly -- they are all correctly rounded, simply because all of them round down. So we don't know whether you require correct rounding or not. What should 0.0032789 read as? 0.0032 or 0.0033? – TonyK May 12 at 14:54
• Your third example is either a mistake or nonsense. – Kirk Woll May 12 at 21:38
• By the way, these are known as "significant figures". So a phrasing of the question using the technical vocabulary would have been "how to round numbers to two significant figures in Excel?" — for which I am sure there are many google hits. – theonlygusti May 12 at 23:03

If rounding is required, you can also try this formula `=ROUND(A1,INT(2-LOG(A1)))`.

• In few cases it round the vale like 0.0000145 returns 0.000015 ,, should be 0.000014 !! – Rajesh S May 12 at 6:03
• @RajeshS Well, there is always ROUNDDOWN() – Stian Yttervik May 12 at 11:50
• you could combine both approaches: `=LEFT(A1,LEN(ROUND(A1,INT(2-LOG(A1)))))` – Yorik May 12 at 21:32
• @Yorik,, this one is perfect, this `=ROUND(A1,INT(2-LOG(A1)))` doesn't works perfectly in few cases like for me `0.0000145` it returns `0.000015` !! – Rajesh S May 13 at 4:37

Try,

In `B2` formula copied down :

``````=0+LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9},A2&1/17))+1)
``````

Edit :

1] 1/17 =0.0588235294117647 which is called Pandigital number that contains digits from 0 to 9

2] A2&1/17 is a short form of A2&1234567890 in which to shorten the formula length.

3] Other Pandigital number can also be used :

A2&5^19

A2&7^18

A2&3^45

..........

• It's good one +10 ☺ ,,, better explain use of 1/17,,, will help the community to solve many such issue!! – Rajesh S May 12 at 5:45
• @RajeshS, Please see my post new editing. – bosco_yip May 12 at 6:37
• @MátéJuhász, this formula is based on to find the the position of 1st digit, explanation please see : ablebits.com/office-addins-blog/2017/11/22/… – bosco_yip May 12 at 11:27
• This isn't CodeGolf.SE. I'd just stick with `A2&1234567890` for clarity. Anyone seeing this code years later is going to be very confused otherwise. – Darrel Hoffman May 12 at 20:37