How to adjust the maximum number of decimal places in Excel

If you've ever seen a column full of ######## instead of numbers in Excel, or encountered amounts with more decimal places than make sense, 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 Excel's internal calculations is key to avoiding surprises in reports, invoices, budgets, or financial calculations. Throughout this article, we'll explore in detail how to adjust the maximum number of decimal places, how to round or truncate values, how to enforce a fixed format, and what limitations Excel has with large numbers.

Manually adjust the number of visible decimal places

Often, all we need is for Excel to display fewer decimal places on the screen, so that the numbers are not annoying, strange symbols do not appear , or absurd precision is not seen for the context (for example, prices with five or six decimal places).

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

Excel offers several simple methods for roughly adjusting decimal places without using formulas or functions, simply by adjusting the cell's number formatting . This is ideal when you don't want to change the actual value, only its appearance.

Use the Increase/Decrease decimal buttons

The most direct method is to use the icons on the Home tab > Number . In the ribbon, you'll see two buttons with an icon of zeros and an arrow: one for Increase Decimal and one for Decrease Decimal . These are the quickest way to adjust what you see without breaking anything.

Simply select the cells where you want to change the format, go to Home > Number , and click the corresponding button several times. Each click adds or removes a digit after the comma (or decimal point) , without affecting 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 Format Cells dialog box . Here you choose the number type (Currency, Accounting, Percentage, Scientific, etc.) and specify exactly how many decimal places you want to see.

To do this, go to Home > Number , click the small arrow in the corner of the group (or press Ctrl+1), and in the Category list , choose 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 criterion : for example, always two decimal places in amounts in euros, or three decimal places in quantities of product or technical measurements.

Rounding a number using Excel functions

It's a completely different matter when simply changing the visual format isn't enough, and we want the value to actually be rounded in the calculation . In that case, Excel's rounding functions come into play, which change the number itself, not just its appearance.

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

How the ROUND function works

The ROUND function has two arguments: the number you want to round and the desired number of decimal places. That number can be a cell reference (such as 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 the rounding is done to the right or left of the decimal, depending on whether it is positive, zero, or negative.

Imagine that cell A1 contains the value 823,7825 . From 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 round to the left of the decimal point (thousands, hundreds, tens), while positive arguments adjust how many decimal places are retained to the right of the separator. It's a very versatile tool.

Always round up: ROUNDUP

The ROUNDUP function 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 want to round up to zero decimal places, =ROUNDUP(3.2,0) would return 4. This logic is useful when you need to be conservative "on the high side," such as in certain financial or business applications.

Always round down: ROUNDDOWN

Conversely, the ROUNDDOWN function 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 number of 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 when rounding down.

Truncate decimals without rounding with TRUNCAR

When we adjust cell formatting to a fixed number of decimal places, Excel applies what's known as symmetric rounding : if the first decimal place to be removed is five or more, it adds 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 to simply trim the extra decimal places , without Excel modifying the value. In those cases, relying solely on cell formatting isn't enough, because the "real" value still contains all the decimal places, even if they aren't visible.

This is where the TRUNC function comes in , which removes decimals without rounding. Basically, it cuts off at the specified level and discards everything that comes after, without applying the logic of "if it's five or more, I round up."

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 truncated number , not a longer number disguised with a two-decimal format. This prevents strange discrepancies when you start adding or averaging truncated results.

Why the format can alter the visible totals

When you format only the decimal places, the numbers retain their full value below, and Excel continues to calculate with all the decimal places , even 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.

By using functions like TRUNC (or, if appropriate, ROUND with the correct settings), you ensure that both the addends and the total work with the same effective number of decimal places , preventing small differences from accumulating and affecting the hundredths 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 also how many decimal places are considered in 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 in A3 to have exactly three decimal places , and for C3 to calculate 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 want in this scenario is for Excel to ignore any extra decimal places beyond the third, so that A3 is always considered 0,112 in calculations, regardless of whether the user types 0,11265 or 0,1121. In other words, they want a "hard cut-off" of decimals, not rounding.

Use TRUNC to limit the decimal places used in the formula

An effective way to achieve this is to force the formula to use TRUNC(A3,3) instead of A3 directly . This way, Excel will always use 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 ensures that Excel discards any extra decimal places , keeps the maximum number of "real" decimal places used in the operation fixed, and prevents 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 with three decimal places to cell A3 , so that the display clearly reflects the sheet's precision policy. This way, even if the user enters more decimal places, the cell itself will only show three, aligning what is seen with what is calculated.

However, it's important to remember that formatting alone doesn't truncate the actual value . Without 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 decimal places using standard formats, Excel automatically applies the aforementioned symmetric rounding . This means that if the first digit to be removed is 5 or more, it adds one to the last displayed decimal place; if it is less than 5, it leaves 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 adding internally, 1,505 will be used. If you then round the total, there may be a difference of hundredths compared 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 have more than 15 significant digits . Beyond 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 rather strings.

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

Method 1: Format the cells as text

A clean solution is to format the cell as Text before entering the long data. This way, Excel won't 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, go to Format Cells , select the Number tab, and choose the Text category . Then click OK, 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 " Ignore error" to prevent it from bothering you again.

Method 2: Precede with a single quotation mark

Another very quick way to force Excel to treat data as text is to precede it with a single quote . For 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 acts as a signal for Excel to disregard any number rules, decimals, or scientific notation. This is especially useful when you only need to mark a few isolated cells as text without changing the formatting of an entire column.

The DECIMAL function and its particularities

In some versions and configurations of Excel you have the DECIMAL function (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 about this function help 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, numbers in Excel are limited to 15 significant digits , but the "decimals" parameter of this function can go up to 127. However, even if you request more than exist, it will only display the number that the source number allows.

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 `no_separar_millares` argument is omitted or set to FALSE, the returned text includes thousands separators according to the locale (periods or commas). Setting it to TRUE removes these separators, 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 key difference between formatting a cell from Home > Number and formatting it from DECIMAL/FIXED is that DECIMAL always returns text , not a number. This means that if you later want to perform mathematical operations with that result, you'll have to convert it back to a number or use it only for display purposes (for example, in reports or exports).

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