Stop Excel From Rounding Your Numbers

Excel is rounding in one of four ways, and only two of them change your data. A General-format cell that is too narrow, and a number format with fewer decimals than the value has, only change what you see: click the cell and the formula bar still shows the full number. The two that change what is stored are the 15-digit limit, which turns every digit after the fifteenth into a zero, and the Set precision as displayed setting, which rewrites stored values to match their formatting. Click the cell first. If the formula bar shows the number you expect, widen the column or add decimals. If the formula bar is already wrong, the damage happened when the number was entered.

That test takes five seconds and saves you from the most common mistake, which is reaching for a fix that does nothing, or worse, a setting that does more than you wanted. The rest of this post is a triage table, then each cause in turn, with the fix that works and a clear warning about the one setting you can't take back.

Is Excel changing the value or only the display?

Start with the formula bar, the long box above the column letters. It shows what the cell actually holds. The cell itself shows what the format lets through.

Formula bar shows the full number, cell shows less. The value is intact. You have a display problem, and nothing needs repairing.
Formula bar shows the same short number as the cell. Either the number was always short, or something already trimmed it. Look at causes three and four below.
Formula bar shows zeros where you typed other digits. The 15-digit limit got there first. The original digits are gone.

Microsoft's guidance on rounded numbers names the first two display causes directly: the column is too narrow for the full number, or the cell format shows fewer decimal places than the number contains. It also states that the rounding is display only, and that the full value stays in the formula bar and is used in calculations. That matters for your totals: a column of values that look rounded still adds up using the unrounded numbers.

Here is the whole picture in one place. Find the row that matches what you are looking at.

Cause	Quick test	What is happening	Stored value changed?	Fix	Reversible?
General format in a narrow column	Widen the column. If digits reappear, this is it	General shortens a number to fit the cell	No	Widen the column, or set an explicit number format	Yes, it is only display
A decimal format hiding digits	Click the cell and read the formula bar	The format shows fewer decimal places than the value holds	No	Click Increase Decimal on the Home tab, or set the decimal places in Format Cells	Yes, it is only display
The 15-digit limit	Does the formula bar show zeros after the 15th digit?	Excel keeps 15 digits of precision and writes zeros after that	Yes	Format the cell as Text before you enter or import the number	No. Re-enter from the original source
Set precision as displayed	File > Options > Advanced: is the box ticked for this workbook?	Every stored number is replaced by its displayed, rounded version	Yes	Untick it going forward and use the ROUND function where you need rounding	No. The extra digits are not stored any more

The first two rows are cosmetic. The last two are the ones that cost people money, because the file looks fine until a total doesn't match or an ID doesn't find its record.

Why does General format round to fit the column?

Because General is the default format and it is built to fit. Microsoft's available number formats reference says that when a cell is too narrow, General rounds numbers that have decimals. Its web-version example is 25.76 appearing as 26. The same page says General switches to scientific notation, the 1.23E+11 look, for numbers of 12 or more digits.

Nothing is wrong with the number. You get this when you paste a column of long decimals into a freshly made sheet where every column is at the default width.

The fixes, in order of preference:

Widen the column. Double-click the right edge of the column letter. If the number was only clipped by width, the digits come back.
Set an explicit format. Select the cells, press Ctrl+1, choose Number, and set the decimal places you want. A named format won't change its mind when the column width changes later.
Check for hashes instead. If the cell shows #### rather than a shortened number, that is a different display message with its own causes. Why Excel shows hash symbols covers it.

If you are also seeing scientific notation on IDs or long codes, jump to the section on card and ID numbers, because scientific notation on a long code is often the first sign of the 15-digit limit.

Does adding decimal places change the value?

No. This is the question behind most of these searches, and the answer is that Increase Decimal and Decrease Decimal are display tools. Microsoft's page says that clicking Increase Decimal on the Home tab until you reach the number of decimal places you need switches the format from General to Number and makes the column wide enough to show them.

So if a cell shows 3.14 and the formula bar shows 3.14159265, clicking Increase Decimal reveals the digits that were always there. It doesn't invent precision, and it doesn't take it away.

The reverse is the useful half. Showing 1,250 when the cell holds 1249.6 is a presentation choice, and your formulas still use 1249.6. That is why a column can look like it adds up wrong on screen: three cells showing 0.3 can sum to a cell showing 1.0 when the stored values are slightly different from what is displayed. The sum is right and the display is rounded. If a reader will check your arithmetic by hand, show the decimals that make the total visible, or round deliberately with a formula, covered at the end of this post.

The grammar of the formats themselves, including how to show thousands and millions without touching the value, lives in the number formatting guide, so I won't repeat the format codes here.

Why do long numbers lose their last digits?

This one is a genuine limit, not a display choice. Microsoft's Excel specifications and limits lists number precision as 15 digits. Microsoft's instructions for formatting numbers as text spell out the consequence: Excel has a maximum of 15 digits of precision and rounds any numbers that follow the 15th digit down to zero.

Type 1234567890123456789 into a General cell and the cell holds 1234567890123450000. The formula bar shows the zeros too, because the zeros are what is stored. Changing the format afterwards doesn't bring the last digits back. Nothing has been hidden. They were discarded at entry.

You hit this most often with:

Credit and debit card numbers
Bank account and routing strings joined together
Long order, tracking and invoice IDs
Product barcodes and serial numbers
Phone numbers stored with country codes and extensions in one string

An identifier is not really a number. You never add two card numbers together, so the right type for it is Text, not a numeric type with a precision ceiling.

How do you keep card and ID numbers intact?

Make the cell Text before the long number goes in. Per Microsoft's page, the steps are:

Select the cells, or a whole column.
On the Home tab, in the Number group, open the Number Format box and choose Text. If you don't see it, scroll to the end of the list.
Enter or paste the numbers now.

Microsoft recommends this for numbers of 16 digits or more, such as credit card numbers. There is one condition that catches people out. Microsoft's guidance on keeping leading zeros and large numbers says that applying Text to cells "will not change numbers that have already been entered". If the number is already in the cell with zeros on the end, formatting it as Text won't restore anything. Format first, then enter. As an alternative, typing an apostrophe before the number tells Excel to treat the entry as text. The same page notes this.

For data coming in from a file, the order matters even more, and the pain is highest with CSV. Opening a .csv straight into Excel converts every column on the way in, so a long ID is already truncated when you first see it. The fix is to bring the file in through Data > From Text/CSV, open the preview in the Query Editor, select the column, and set its type to Text under Home > Transform > Data Type, as Microsoft's page describes for importing text data. A full walk through that problem is in why a CSV opens wrong in Excel.

The trade-off is real and worth stating plainly. Numbers stored as text sort and filter differently from numbers, and Microsoft warns that they can cause problems with calculations. Use Text for identifiers you will look up and match, not for amounts you will add up. If one column mixes both, split it, or you will end up with a total that quietly skips the text cells. If that is already happening to you, Excel SUM not working walks through the symptoms.

What does Set precision as displayed actually do?

It makes the display the truth. Microsoft's rounding precision page says the option forces the value of each number in the worksheet to be at the precision that is displayed. Before it is on, a cell showing 2.35 might hold 2.3487. After it is on, the cell holds 2.35, and everything that references it uses 2.35.

To find it, go to File > Options > Advanced, then look under When calculating this workbook for Set precision as displayed. That is the path Microsoft gives for Excel for Microsoft 365, Excel 2024 and Excel 2021. Its page doesn't mention Mac or Excel for the web, so if your menus look different, check the version you are on and don't assume the path matches.

Two things about this setting deserve a loud warning.

Warning. Set precision as displayed overwrites the stored values in the workbook with their rounded versions. Microsoft warns that it can have cumulative calculation effects that make your data increasingly inaccurate over time, and advises using it only if you are certain the displayed precision will maintain the accuracy of your data. Because it works by replacing stored values, unticking the box afterwards cannot put the lost digits back. Make a copy of the file before you turn it on.

That last claim is my reading of how the setting works. Microsoft's page doesn't discuss undoing it, so I wouldn't count on any recovery. Treat it as one-way.

It is also applied to the workbook, not to a column. You can't rescue one rounded column and leave the rest at full precision. If you switched it on to make a total match a printed report, you changed every number in the file to do it.

You might also have it on without knowing. If a colleague turned it on in an older version of your file, the numbers in it have been rounded since then. The check in the table takes ten seconds: File > Options > Advanced, and look at the box for that workbook.

How do you show fewer decimals without rounding the math?

Use a number format and leave the stored value alone. Select the cells, press Ctrl+1, choose Number, and set the decimal places. Display drops to two decimals, storage keeps everything. This is the right default for any report: readers get a clean figure and your formulas keep full precision.

When you need the value itself rounded, because a total must equal the sum of the printed lines, round with a formula and not a setting. Microsoft suggests the same alternative on its rounding page: use the ROUND function to round numbers to the decimal places your calculation needs. For example, =ROUND(B2*C2,2) rounds that one result to two places.

The difference is control. A formula rounds exactly the cells you point it at, you can see it in the formula bar, and a colleague can find it. Set precision as displayed rounds everything, invisibly.

A short decision rule:

Report that people read. Number format, two decimals, no change to stored values.
Total that must match printed lines. ROUND in the formulas that feed the total.
Identifier that must not change. Text format before entry.
Anything else. Leave Set precision as displayed off.

Frequently asked questions

Why does Excel change my last digits to 0?

Because Excel keeps 15 digits of precision. Microsoft states that it rounds any numbers after the 15th digit down to zero. The zeros are stored, not just shown, so the original digits are gone once the number is entered into a General or Number cell. To prevent it, format the cell as Text before entering the number, then re-enter the original from its source.

How do I stop Excel rounding large numbers?

It depends on what the number is. If it is an identifier longer than 15 digits, such as a card number, store it as Text. If it is a quantity you calculate with, Excel can't hold more than 15 digits of precision, so look at whether you really need them all. If the large number is merely shown in scientific notation, that is General format at work, and setting the cell to Number with zero decimals shows it in full, up to the 15-digit limit.

Does adding decimal places change the value?

No. Increase Decimal and Decrease Decimal change the display only. Microsoft's guidance says the full value stays in the formula bar and is used in calculations. If you want the value itself rounded, use the ROUND function.

Can Set precision as displayed be undone?

You can untick the box, but that will not restore digits that were already replaced, because the setting works by changing the stored values to match the displayed ones. Microsoft's page doesn't describe any way to reverse it, so make a copy of the file before turning it on and keep the original.

What to do with the file in front of you

Run the formula bar test on one cell from each column that looks wrong. Most of the time you will find the column is only narrow or the format is only terse, and a few clicks fix it with no data lost. When the formula bar shows zeros, trace the file back to its source and bring the column in again as Text.

If you would rather have the column types checked before any format is applied, the Excel formatter reads what each column actually holds, then picks a number format to match. It doesn't change your stored values, and it doesn't upload the file anywhere. Drop an .xlsx or .csv on it and see what your columns really hold.