How to adjust the maximum number of decimal places in Excel

If you've ever seen a column full of ######## instead of numbers If you've ever encountered amounts with more decimal places than make sense in Excel, you're not alone. Excel handles numbers very well, but without clear rules, it can display more precision than necessary or, conversely, hide some of the real information without us realizing it.

Controlling how many decimal places are displayed and how many are actually used in the internal Excel calculations This is key to avoiding surprises in reports, invoices, budgets, or financial calculations. Throughout this article, we'll see, in detail and without leaving anything out, how to adjust the maximum number of decimal places, how to round or truncate values, how to force a fixed format, and what limits Excel has with large numbers.

Manually adjust the number of visible decimal places

Often all we need is for Excel display fewer decimal places on screen, so that the numbers are not distracting, they do not appear Symbols rare or there is an absurd precision for the context (for example, prices with five or six decimal places).

When you see something like this in a cell ######## Instead of the number itself, the problem is usually that the value has many digits (whole or decimal) and the column is too narrow to display it in the current format. A quick way to reduce this effect is decrease the number of visible decimal places or widen the spine, whichever suits you best.

Excel offers several simple methods for adjusting decimal places by eye, without touching formulas or functions, just by playing with the cell number formatThis is ideal when you don't want to change the actual value, just its appearance.

Use the Increase/Decrease decimal buttons

The most direct method involves using the icons on the tab Home > NumberIn the ribbon you'll see two buttons with an icon of zeros and an arrow: one for Increase decimal places and another for Decrease decimalsThey are the fastest way to adjust what you see without breaking anything.

You only have to select the cells Wherever you want to change the format, go to Home > Number and press the corresponding button several times. Each click adds or removes a digit after the comma (or decimal point), without touching the actual value saved by Excel.

Configure the number of decimal places from "Format cells"

If you want something a little more refined, you can change the format using the classic box of Cell formatHere you choose the type of number (Currency, Accounting, Percentage, Scientific, etc.) and mark exactly how many decimal places you want to see.

To do this, go to Home > Number, click on the small arrow in the corner of the group (or press Ctrl+1) and, in the list CategoryChoose the format that best suits your data: Currency, Accounting, Percentage, or Scientific, among others. In that same window, you'll see a box called Decimal places, where you can specify how many decimal places to display.

With this option you ensure that all numbers of the same type follow a homogeneous criterionFor example, always two decimal places for amounts in euros, or three decimal places for quantities of product or technical measurements.

Rounding a number using Excel functions

It's a very different matter when it's not enough to simply change the visual format, but we want the value to be round up properly in the calculationIn that case, Excel's rounding functions come into play, which change the number and not just its appearance.

One of the most used functions is ROUND OUTThis function adjusts a number to the number of digits you define, both to the right and left of the decimal point. It's very useful when you need mathematical consistency in sums, averages, or calculated prices.

How the ROUND function works

The function ROUND OUT It has two arguments: the number you want to round and the desired number of decimal places. That number can be a cell reference (like A1) or a value typed directly into the formula.

In terms of syntax, you would write something like =ROUND(number; digits)The first argument is the starting value; the second controls how many digits are kept and whether rounding is done to the right or left of the decimal, depending on whether it is positive, zero, or negative.

Imagine that in cell A1 you have the value 823,7825From there, you can round it to the nearest thousand, hundred, ten, or a certain number of decimal places:

• To round to the nearest thousand, you would use =ROUND(A1,-3)In this case, the result would be 1.000, because 823,7825 is closer to 1.000 than to 0, and 0 and 1.000 are multiples of one thousand.
• If you want to round up to the hundred, would you write =ROUND(A1,-2), which would give 800. At this point, 823,7825 is closer to 800 than to 900.
• With a rounding to tenths, would you use =ROUND(A1,1), with a result of 823,8, because you leave only one decimal place.

Negative arguments are used to round to the left of the decimal point (thousands, hundreds, tens), while positive arguments adjust how many decimals are preserved to the right of the separator. It's a very versatile tool.

Always round up: ROUNDUP

The function ROUND UP It behaves very similarly to ROUND, but with one key difference: it always rounds the number up, regardless of whether the cutoff decimal is greater or less than 5.

For example, if you have 3,2 and you want to round up to zero decimal places, =ROUNDUP(3,2,0) I would give you back 4. This logic is useful when you need to be overly conservative, such as in certain financial or commercial applications.

Always round down: ROUNDDOWN

Conversely, the function ROUND DOWN It forces Excel to always round down, that is, to get closer to zero in positive numbers or to become more negative in negative numbers, depending on the decimal places you specify.

If you want to round the number 3,14159 to three decimal places without allowing it to go up to 3,142, you would use =ROUNDDOWN(3,14159,3)The result would be 3,141, because any remainder after the third decimal place is ignored and rounded down.

Truncate decimals without rounding with TRUNCAR

When we adjust the cell format to a fixed number of decimal places, Excel applies what is known as symmetrical roundingIf the first decimal place to be dropped is five or higher, add one to the last decimal place that is kept. This is exactly what happens when we round 9,676 to two decimal places and get 9,68.

The problem arises when we don't want that rounding to happen, but rather we want trim off the excess decimal places And that's it, without Excel modifying the value. In these cases, relying solely on cell formatting isn't enough, because the "real" value still has all the decimal places, even if they aren't visible.

This is where the function comes into play TRUNCATEIt eliminates decimals without rounding. Basically, it cuts off at the height you specify and discards everything that comes after, without applying the logic of "if it's five or more, I increase by one."

If, for example, you have a series of values ​​in the range A2: A10 and you calculate the average with =AVERAGE(A2:A10)You can limit that average to two decimal places without symmetric rounding with =TRUNC(AVERAGE(A2:A10);2)Here the first argument is the starting number (the result of AVERAGE) and the second is the number of decimal places you want to keep.

The added advantage is that, when using TRUNC, the value stored in the cell is already the number cut offnot a longer number disguised with a two-decimal format. This avoids strange discrepancies when you start adding or averaging truncated results.

Why the format can alter the visible totals

When you only format decimal places, the numbers retain their full value below and Excel continues doing calculations with all decimal placeseven though you only see two. This can lead to visually jarring results.

Consider this example: if you add 1,503 + 1,503, the actual result is 3,006. So far, so good. If you then apply a two-decimal-place format to all the cells, you'll see 1,50 + 1,50 = 3,01. The thousandths that were removed from each addend are not displayed, but they are still present and become significant in the final result.

When using functions like TRUNCATE (or, if appropriate, ROUND with the appropriate settings), you ensure that both the addends and the total work with the same effective number of decimal placespreventing small differences from accumulating and ultimately affecting the hundredths of a percent in the result.

Strict control of decimal places entered by the user

A very common situation is wanting to limit not only how many decimal places are displayed, but How many decimal places are taken into account in the calculations? when someone enters data. This is very typical in price sheets, shared templates, or internal forms where the user fills in certain fields.

Unit Price In row 3 you have the unit price of a product in column A, the quantity in column B, and the total price in column C. Something like: A3 = "Price of X", B3 = "Number of X", C3 = "Total Price of X", with a formula like =SUM(A3*1000) in C3.

Suppose you want the price on A3 to have exactly three decimal placesand that C3 calculates the total amount by multiplying by 1.000 units. The problem arises when the person filling out the sheet decides to use more decimal places than necessary.

For example:
• If you enter 0,112 in A3, C3 will display 112,000 (all good).
• If you enter 0,1121 in A3, C3 will change to 112,100.
• If you enter 0,11265 in A3, the format may display 0,113, but the calculation in C3 will go to 112,650, because Excel continues to use the full value.

What many people are looking for in this scenario is for Excel to ignore any extra decimal places after the third one, so that A3 is always considered as 0,112 in the calculations, regardless of whether the user types 0,11265 or 0,1121. That is, they want a "hard cut" of the decimals, not rounding.

Use TRUNC to limit the decimal places used in the formula

One effective way to achieve this is to force the formula not to use A3 directly, but TRUNC(A3,3)In this way, Excel will always take a maximum of three decimal places, ignoring the rest without rounding.

In the previous example, the formula for C3 could become something like =TRUNC(A3,3)*1000Thus, the three inputs would have this effect:
• A3 = 0,112 → TRUNCATE(A3;3) = 0,112 → C3 = 112,000.
• A3 = 0,1121 → TRUNCATE(A3;3) = 0,112 → C3 = 112,000.
• A3 = 0,11265 → TRUNCATE(A3;3) = 0,112 → C3 = 112,000.

This allows Excel to... Discard any extra decimal places, keep the maximum number of "real" decimal places used in the operation fixed and do not allow variations in the total based on the precision of the input.

Combined with cell formatting for greater clarity

In addition to adjusting the formula, it makes sense to apply a number format to A3 with three decimal placesThis ensures the screen clearly reflects the spreadsheet's precision policy. In this way, even if the user enters more decimal places, the cell itself will only display three, aligning what is seen with what is calculated.

However, it's worth remembering that the format itself it does not reduce the real valueWithout a function like TRUNC within the formula, hidden decimal places would still affect the results. Ideally, you should combine both: a function that truncates the number and formatting that limits the visible decimal places.

Symmetric rounding and its effects on calculations

When we reduce decimals using standard formats, Excel automatically applies the aforementioned symmetrical roundingThis means that if the first digit to be removed is 5 or more, add one to the last decimal place shown; if it is less than 5, leave it as is.

This behavior is common and mathematically sound, but in certain spreadsheets it can generate results that are undesirable from an accounting or control perspective. Consider reports where you sum many values ​​with three or four "hidden" decimal places and only display two: the discrepancies can accumulate to a cent, or more, in total.

For example, if you have many values ​​like 1,505, 1,505, 1,505, and format them with two decimal places, you will always see 1,51, but when summing internally, 1,505 will be used. If you then round the total, you might see... hundredths of a difference with respect to what the eye thinks it sees when mentally adding "1,51 + 1,51 + 1,51…".

Using TRUNC, ROUNDDOWN, or even ROUND with the appropriate rules allows you to define a consistent criterion for the entire chain of calculations and prevent the decimals discarded at each step from becoming significant later on.

Formatting long numbers and limiting to 15 significant digits

Excel has one important restriction: numbers cannot be larger than 15 significant digitsBeyond that limit, Excel begins rounding and may transform the last digits into zeros. This is especially noticeable with numbers like credit card numbers, long codes, or identifiers that aren't actually "numbers" but strings.

To work with data of this type (for example, 1234567890123456789) without losing information, it is essential that Excel treats them as textnot as numerical values. Otherwise, the last digits will be modified and will no longer match the original data.

Method 1: Format the cells as text

A clean solution involves formatting Text Enter the cell reference before writing the long data. This way, Excel will not try to interpret it as a number, but will save and display it as is, without applying rounding or scientific notation.

To do this, you can right-click on the cell, enter Cell format, go to the tab Number and select the category TextThen you accept, and you can enter the long number without Excel truncating or transforming it.

A small green warning triangle may appear, indicating that the value appears as a number stored as text. If you don't want to see this alert, you can open the menu by clicking the yellow icon next to it and then click on it. Ignore errorso that it won't bother you again.

Method 2: Precede with a single quotation mark

Another very quick way to force Excel to treat data as text is to write a single quoteFor example, if you type '1234567890123456789', Excel will display 1234567890123456789, but internally it will consider it a text string, not a number.

The quotation mark disappears after you press Enter, but it signals Excel not to apply any number rules, decimals, or scientific notation. This is especially useful when you only need to mark it as text. a few isolated cells without changing the format of an entire column.

The DECIMAL function and its particularities

In some versions and configurations of Excel you have the function DECIMAL (in certain environments it appears as FIXED or with an equivalent name), whose objective is to round a number to a certain number of decimal places, apply a format with thousands separators and return the result as text.

The usual syntax is something like this: DECIMAL(number; ; ), with these arguments:
• Number: the value you want to round and convert to text.
• Decimals (optional): how many digits to the right of the decimal separator are retained.
• Do not separate thousands (optional): a logical value (TRUE/FALSE) indicating whether thousands separators should be omitted.

Some key observations This function helps to understand how it behaves and how it differs from conventional cell formatting or other rounding functions.

Behavior and examples of the DECIMAL function

First, the numbers in Excel are limited to those 15 significant digitsHowever, the "decimals" parameter of this function can go up to 127. Even if you request more than exist, it will only display the number allowed by the source number.

If "decimals" is negative, rounding is performed to the left of the decimal point, just as with ROUND when using negative digits. If you omit the "decimals" argument, Excel usually assumes a default value of 2, which is equivalent to working with two decimal places.

When the argument do not separate thousands If omitted or FALSE, the returned text includes thousands separators according to the regional settings (periods or commas). If you set it to TRUE, those separators disappear, leaving the number without thousands separators.

For example, with the following data:
• A2 = 1234,567
• A3 = -1234,567
• A4 = 44,332
You could use:
• =DECIMAL(A2,1) → rounds to one decimal place: result «1.234,6» (as text).
• =DECIMAL(A2;-1) → Rounds to one place to the left of the decimal point: "1.230".
• =DECIMAL(A3;-1;TRUE) → Rounds to one position to the left, without thousands separators: «-1230».
• =DECIMAL(A4) → without specifying decimals, leave 2 decimal places: «44,33».

The fundamental difference with formatting a cell from Home > Number DECIMAL/FIXED always returns textnot a number. This means that if you later want to perform mathematical operations with that result, you will have to convert it back to a number or use it only as a presentation value (for example, for reports or exports).

Ultimately, mastering decimal control in Excel involves combining several approaches: cell formatting for presentationRounding functions (ROUND, ROUNDUP, ROUNDDOWN) when you want consistent mathematical precision, TRUNC to cut off without rounding, DECIMAL/FIXED when you need formatted text, and text formatting or quotation marks for long numbers. Using each tool in its context allows you to avoid errors, discrepancies of cents, and surprises with sensitive data, keeping your spreadsheets clear, reliable, and easy to interpret.