4 Replies to “How to Round to Significant Figures in Excel”

1. Amazing work. Great stuff. Concise and accurate. Well ordered and logically presented. This answer smells great, too! 🙂 I couldn’t think of any more compliments. Thank you!

   Reply

2. This works for some of my cases but not others. For example, if the value is 3.0001 and I want it to show to 2 significant figures, so it should show 3.0, Excel will automatically present that as 3 and not 3.0. How do you get around that?

   Reply

   1. Hi Danette…You’re absolutely right — Excel does not **retain trailing zeros** after the decimal point when it auto-formats numbers, even if you’ve calculated them to be a certain number of significant figures. Excel treats numbers like `3.0` and `3` the same numerically, and by default it displays them the same — as `3`.

      ### ✅ Goal:
      If you want to **round to a fixed number of significant figures AND preserve the display of trailing zeros**, you’ll need to **format the result as text** or **use custom number formatting**. Here’s how to do both:

      —

      ## ✅ Option 1: Use a Formula to Force Significant Figures & Display Trailing Zeros (as Text)

      You can use this formula to round to **2 significant figures** and show trailing zeros as needed:

      “`excel
      =TEXT(ROUND(A1,1-INT(LOG10(ABS(A1)))), “0.0”)
      “`

      – `A1` is the cell with your value.
      – This rounds the number to 2 significant figures.
      – `”0.0″` in `TEXT` ensures one decimal place is always shown, keeping the trailing `.0`.

      👉 **Example:**
      – `A1 = 3.0001`
      – Formula gives: `”3.0″` (as text)

      You can modify `”0.0″` to `”0.00″` or `”0.000″` etc., depending on how many **significant digits** you want to **force to display**.

      —

      ## ✅ Option 2: Use Custom Number Formatting (for display only, not rounding)

      If your numbers are already rounded and you just want Excel to **show the trailing zero**, use a **custom format**:

      1. Select the cell(s)
      2. Press `Ctrl + 1` (or right-click → Format Cells)
      3. Choose **Number** → **Custom**
      4. Enter a format like:
      “`
      0.0
      “`

      – This will display `3.0` instead of `3`, even though Excel internally stores the number as `3`.

      ⚠️ *Note:* This doesn’t round the number — just changes how it looks.

      —

      ## ✅ Option 3: Combine Both — Round + Custom Format

      If you want to round the number *and* make sure it’s displayed with correct significant figures:

      “`excel
      =TEXT(ROUND(A1,1-INT(LOG10(ABS(A1)))), “0.0”)
      “`

      This gives you a text version with both correct value **and** the formatting.

      —

      ## TL;DR

      | Task | Solution |
      |——|———-|
      | Round to 2 sig figs | `=ROUND(A1,1-INT(LOG10(ABS(A1))))` |
      | Round & keep trailing 0 | `=TEXT(ROUND(A1,1-INT(LOG10(ABS(A1)))), “0.0”)` |
      | Just show `.0` | Custom format: `0.0` |

      —

      Reply

3. Hi,
   The solution to Danette’s problem is unfortunately assuming a certain number of decimals. That is different from showing a certain number of significant figures…
   With 2 significant figures you’d like to present:
   – 3.0001 as 3.0 (correct)
   – 123 as 120 (not 120.0)
   – 11 as 11 (not 11.0)

   Reply