How to Set Decimal Places in Excel Formula (Easily)

Decimal places make the fractional part of a number separated by a decimal (a dot).

This is the non-integer part of a number, for example in the number 10.34, 0.34 is the non-integer part that represents 34 decimal places in addition to 10.

Decimal numbers add accuracy and precision to numbers. However, some of the time they can be recurring or just too long. If you do not want your Excel sheet to look cluttered or elsewise if you don’t desire accuracy down to the microscopic level, you can reduce the decimal positions to any desired number 🔎

This guide is meant to teach you how to set decimal places in Excel. So grab your free practice workbook for this guide here and continue reading.

Table of Contents

Using Increase / Decrease Decimal Button

Excel has designated buttons to set decimal places. Didn’t know about it before? Let me show you.

Here’s a list of some numbers in Excel, and God they look so clumsy.

Let’s set their decimal places through the following steps 👇

Step 1) Select the numbers for which you want to set the decimal places.

Step 2) Go to the Home tab > Number group > Decrease Decimal button.

Step 3) Clicking on it once will equalize the decimal places for all numbers. For example, I clicked it once and equalized all numbers’ decimal places to 4.

Step 4) Click it as many times as many decimal places as you want decreased.

Similarly, if you want more accuracy and precision in your numbers, you can increase the decimal places by using the Increase Decimal button next to the Decrease Decimal button.

Step 5) I have pressed it twice to increase the decimal places of each number by 2.

Both these buttons are in a prime central position on the Home Tab and can be quickly accessed. Makes setting decimal points easy 🧐

Using the Format Cells Command

Using the increase / decrease decimal points button is easy. However, there’s one drawback.

If the numbers before you have too many decimal places, it might be difficult to manually adjust them down to a lower number of decimals.

You can help this situation by formatting these cells to a lesser number of decimal positions 📚

For example, for the same list as above, I instantly want all the numbers to be set to 2 decimal places.

Step 1) Select the numbers for which you want to set the decimal places.

Step 2) Right-click on this selection to launch the menu of options.

Step 3) Select the Format Cells option.

Comment

Alternatively, you can also press Control + 1 as the shortcut key to launch the Format Cells dialog box.

This will launch the Format Cells dialog box.

Step 4) From the options on the left, select the Number format.

Step 5) Set the decimal places to 2 (or whatever number you want to set them to).

Step 6) Press Okay.

And instantly, all the selected cells will be formatted as Numbers with two decimal positions.

For both the above methods, the plus point is that your numbers are not changed to reduced decimal positions. They are only presented with reduced decimal positions on the face of the sheet.

Click on a cell and go to the formula bar to see that the number still has the same number of decimal places as it originally had ⚡

However, within the cell, it will only appear to have the set number of decimal places with through the above two methods.

Using Functions

You can also adjust the decimal places of the numbers in your Excel sheet by using formulas. When done with formulas, the numbers are changed to have the given number of decimals (and not just presented so).

There are a range of formulas that you can use for the said cause. Let’s look into them all 👀

ROUND Function

The ROUND function rounds a number to a given number of decimal places.

Step 1) Write the ROUND function as follows:

The first argument is the cell that contains the number to be rounded.

The second argument is the number of decimal places you want.

Step 2) Hit Enter to see Excel rounds the number to 2 decimal places.

Pro Tip!

The ROUND function rounds a number the way it should be rounded.

For example, it rounded 6.54321 to 6.54 and not 6.55.

This is because that’s how it should be rounded. The last two digits to be rounded (43 from 6.543) are on the first half of the scale from 40 to 50 so they should better be rounded down to make 6.54 rather than 6.55.

Step 3) Drag the results down and towards the right to have the same function applied to all the numbers.

This time the numbers have changed 🙌

ROUNDUP Function

The ROUNDUP function is a variation of the ROUND function. It is to be used if you want the numbers to be rounded up irrespective of how they should be rounded.

Step 1) Write the ROUNDUP function as follows:

Same arguments as the ROUND function.

Step 2) Hit enter to see the rounded-up results.

See – this time 6.543 is rounded up to 6.55. Although we know, mathematically, it should have been rounded down.

Step 3) Drag the same formula to all the cells.

That’s how ROUNDUP works 🚴‍♀️

ROUNDDOWN Function

Just like the ROUNDUP function, the ROUNDDOWN function works to round the numbers down, irrespective of how they should be rounded.

Step 1) Write the ROUNDDOWN function as follows:

Step 2) Drag the same results to all the remaining cells.

TRUNC Function

If you don’t just want to round a number to a certain number of decimal places but want to remove the decimal places in their entirety, use the TRUNC function ✂

Step 1) Write the TRUNC function as follows:

It truncates the number in Cell A2 down to an integer and removes the fractional part from it.

Step 2) Drag the same results to all the remaining cells.

When you use functions to set the decimal places of a number, a formula runs behind the number (see the formula bar).

If you want the decimal places adjusted actually (and not only through presentation) without a formula running in the background but only want to retain the values and not the background formula 🔖

Step 1) Set the decimal places using either of the functions discussed above.

Step 2) Once done, select these cells.

Step 3) Copy them by pressing the Ctrl key + C.

Step 4) Select the first cell of the cell range where they are already placed.

Step 5) Right-click to launch the menu of options > Paste options > Paste as values.

This will copy and paste all these cells as values only and the background formula will be removed.

No more formulas and actual values with decimal values set by you 💪

Conclusion

This guide explains several methods that you can use to set the decimal places of numbers in Excel. You can reduce them to any number, round them up or down, or simply remove them. All up to you.

Additionally, there’s other stuff that you can do about decimal numbers in Excel. Read my Microsoft Excel tutorials that come next to learn how to optimize working around decimals in Excel.

• Convert Time Into Decimals in Excel (Hours/Minutes/Seconds)
• EXCEL TRUNCATE: Get Rid of Decimals From Numbers (+ Trim Text)