• 2020-3-31  Floating-point arithmetic may give inaccurate results in Excel. 3/30/2020; 8 minutes to read; Applies to: Excel 2010, Excel 2013, Excel for Office 365, Microsoft Excel for Mac 2011, Excel for Mac.
  • On the Home tab, click next to Number. In the Category list, pick the number format you want to use, making sure it has decimal places, like Number, Currency, Accounting, Percentage, or Scientific. In the Decimal places box, enter the number of decimal place you want to show, and click OK.

Use the Fraction format to display or type numbers as actual fractions, rather than decimals. If no fraction format is applied to a cell, and you type a fraction such as 1/2, it will be formatted as a date. To display it as a fraction, apply a Fraction format, and then retype the fraction. Round a number to the decimal places you want by using formatting and how to use the ROUND function in a formula to round to the nearest major unit such as thousands, hundreds, tens, or ones. Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel. Calculate percentages in Excel 2016 for Mac. Excel provides different ways to calculate percentages. For example, you can use Excel to calculate sales tax for purchases, the percentage of correct answers on a test, or the percentage of change between two values. 2019-12-7  One of my columns is formatted as currency until 2 decimal places. When i use add the values up using SUM formula or +, the total is different from what i manually calculated based on two decimal places. If 1.16 + 2.25 = 3.41, excel will give me a slightly different value. I am guessing this is internal rounding from excel.

Use Excel’s rounding feature to round decimals or large numbers in a spreadsheet. Round with functions, round up, round down, or round numbers to a certain decimal place in Excel. In this article, we’ll show you how to round numbers in excel. If you use Excel a lot, you have probably noticed that it is frequently used to organize data in numerical form. While this is very helpful, sometimes you don’t really want to work with numbers like decimals. In certain cases, rounding your data up best suits your needs. In this guide, we are going to learn how to use Excel to do the rounding for us! Don’t sweat it. Using this feature is much simpler than you may think.

What is the Excel Round Function?

Have you ever heard of the Excel round function? Before we dive into how to round numbers in Excel, we need to address this question. While learning how to use different aspects of Microsoft software can often seem daunting, it is more simple than you may expect. The Excel round function brings a number to a given amount of rounded digits. You can round either to the left or the right of a decimal point.

Why Should We Round?

To keep it simple, rounding numbers just makes them easier to deal with. First of all, if you are working with decimals, it can be such a pain to record them. Even worse, if you are presenting these numbers to someone out loud, reading decimals can get tedious.

Not just decimals are rounded, though. Whole numbers can be rounded too, most often to the nearest 10. Rounding is often used in business when dealing with money amounts or sales.

Instead of reporting in a meeting, “we sold $5,999,956.97 worth of products this year,” it is easier to say “we sold about $6 million worth of products this year.” If a business is looking for an estimate of numbers, they can easily use Excel to do so.

How to Round Numbers in Excel

Let’s say a company is trying to figure out its estimation of sales for the week by the transaction. Each transaction has a very different and specific number, but the company is just looking for rounded numbers. All of the sales are listed in Excel. How can we round them?

We’ll use a shortened version of their sales list as our example. Here are some examples of the types of numbers we are dealing with:

  • 56.09
  • 22.31
  • 43.33
  • 90.01
  • 87.55
  • 15.78
  • 25.36
  • 38.72

Using Functions To Round Numbers In Excel

Start by opening a new Excel sheet and listing your numbers in the first column. Excel uses functions to round. With the rounding function, you can use =ROUND, =ROUNDUP, and =ROUNDDOWN. For our first example, we are going to use the =ROUND function.

To begin, we are going to start by rounding just our first number, 56.09. Select the cell where you want the new number to appear.

For example, it may be best to put the new number directly next to the old number.

In the function bar, type =ROUND(A1,0) and hit Enter. A1 represents the cell we are round, while the 0 represents how many decimal places we would like to round to. Since we want to get rid of our decimals, we are typing 0. Our new number will appear in the cell. In this case, the number is 56.

Additionally, you can use the =ROUNDUP function to round up to the next whole number, as well as the =ROUNDDOWN function to round down to the next whole number. Let’s do this with our second number, 22.31.

In the cell directly next to 22.31, type =ROUNDUP(A2,0). This will take cell A2, or 22.31, and round it up to the next whole number. Hit Enter, and you will get 23. In the next cell over, type =ROUNDDOWN(A2,0) and hit Enter. This will round 22.31 down to the next whole number, or 22.

If the Numbers have Several Decimal Places

If you are working with numbers that have several decimal places, such as 45.6726865, you may want to narrow these numbers down to just 2 decimals.

To achieve this in Excel, list your numbers in column A. Select the column next to your first number and type the function =ROUND(A1,2). This will round your number to 2 decimal places instead of its high number of decimal places.

You can complete rounding actions with any number of decimal places you desire, like simply by changing the second number in your function.

Rounding Multiple Numbers at Once

If you are working on rounding numerical data, there is a good chance you are probably working with a lot of numbers. It wouldn’t be very convenient if you had to go through and round each of those numbers individually.

Luckily, Excel makes it easy to round multiple numbers at once while still using its rounding function.

There is a simple way you can accomplish rounding all of your numbers.

The way to do this is to list out all your numbers in the first column. Then select the cell next to your first column and enter the rounding function you want to use for all of your numbers.

Let’s use =ROUND(A1,2). Hit enter to get the result for that number. Then, select that same cell and move your mouse over the bottom, right-hand corner of the cell until you see a plus (+) sign. Click and drag down until you reach the last cell you filled out.

Excel will automatically drag your function formula down through the selected cells. It will then perform the function for each number you have listed, giving you your result in the rounded column.

It is also possible to drag the function down past your entered information.

Zeros will appear where there is no information in the first column. Additionally, if you need to enter more information, anything entered in column A will automatically incorporate the function in column B; the zero will then change to reflect the newly entered number.

Numbers can be difficult to deal with in general, but when we add decimals to the mix, sometimes we are asking for trouble. Rounding can be difficult, especially when you are using lots of them or very large numbers with several decimal places.

For a quick, easy solution to your rounding problem, follow this guide on how to round numbers in Excel. You will be a rounding expert in no time, and you’ll save yourself a lot of effort!

No related posts.

The tutorial explains how to effectively use AutoCorrect in Excel and how to stop it completely or only disable for specific words.

Excel AutoCorrect is designed to correct misspelled words automatically as you type, but in fact it is more than just correction. You can use this feature to change abbreviations to full text or replace short codes with longer phrases. It can even insert check marks, bullet points and other special symbols on the fly without you having to access anything. This tutorial will teach you how to do all this and more.

Excel AutoCorrect options

To have more control over how Excel performs autocorrection in your worksheets, open the AutoCorrect dialog:

  • In Excel 2016, Excel 2013 and Excel 2010, click File > Options, select Proofing on the left-hand pane, and click AutoCorrect Options.
  • In Excel 2007, click the Office button > Options > Proofing > AutoCorrect Options.

The AutoCorrect dialog will show up and you can switch between the 4 tabs to enable or disable specific corrections.

AutoCorrect

On this tab, you can view the list of typical typos, misspellings and symbols that AutoCorrect uses by default. You can change and delete any of the existing entries as well as add your own ones. Additionally, you can turn options on or off the following options.

The first option controls the autocorrect logo (lightning bolt) that appears after each automatic correction:

  • Show AutoCorrect Options buttons - shows or hides the autocorrect logo.

Please note that the autocorrect button does not appear in Excel anyway, clearing this box prevents the lightning bolt from appearing in Word and some other applications.

The next 4 options control the automatic correction of capitalization:

  • Correct Two Initial Capitals - changes the second capital letter to lowercase.
  • Capitalize first letter of sentence - capitalizes the first letter after a period (full stop).
  • Capitalize names of days - self-explanatory
  • Correct accidental use of cAPS LOCK key - fixes words in which the first letter is lowercase and the other letters are uppercase.

The last option enables or disables all automatic corrections:

  • Replace text as you type - turns AutoCorrect off and on.

Tips and notes:

  • Text included in formulas and hyperlinks is not automatically corrected.
  • Every change you made in Excel AutoCorrect options applies to all workbooks.
  • To prevent automatic capitalization after some abbreviation or acronym that ends with a period, add it to the Exceptions list. For this, click the Exceptions… button, type the abbreviation under Don't capitalize after and click the Add button.
  • Not to correct 2 initial capital letters, for example 'IDs', click Exceptions, switch to the INitial CAps tab, type the word under Don't correct, and click Add.

AutoFormat as you type

On this tab, you can disable the following options, which are enabled in Excel by default:

  • Internet and network paths with hyperlinks - turns text that represents URLs and network paths into clickable hyperlinks. To disable automatic creation of hyperlinks in Excel, clear this box.
  • Include new row and columns in table - once you type anything in a column or row adjacent to your table, such column or row is included in the table automatically. To stop the automatic expansion of tables, clear this box.
  • Fill formulas in tables to create calculated columns - uncheck this option if you want to prevent the automatic replication of formulas in Excel tables.

AutoCorrect actions

By default, additional actions are disabled. To turn them on, select the Enable additional actions in the right-click menu box, and then select the action you want to enable in the list.

For Microsoft Excel, only the Date (XML) action is available, which opens your Outlook calendar on a given date:

To trigger the action, right-click a date in a cell, point to Additional Cell Actions, and click Show my Calendar:

Math AutoCorrect

This tab controls the automatic insertion of special symbols in Excel equations (Insert tab > Symbols group > Equation):

Please note that the math conversions only work in equations, but not in cells. However, there is a macro that allows using Math AutoCorrect outside math regions.

How to stop AutoCorrect in Excel

It may sound strange, but AutoCorrect in Excel is not always a benefit. For example, you may want to insert a product code like '1-ANC', but it is automatically changed to '1-CAN' each time because Excel believes you've misspelled the word 'can'.

To prevent all automatic changes made by AutoCorrect, simply turn it off:

  1. Open the AutoCorrect dialog by clicking File > Options > Proofing > AutoCorrect Options.
  2. Depending on what corrections you want to stop, uncheck the following boxes on the AutoCorrect tab:
    • Clear the Replace text as you type box to disable all automatic replacements of text.
    • Clear some or all check boxes that control automatic capitalization.

How to turn off AutoCorrect for certain words

In many situations, you may not want to stop autocorrect in Excel completely, but disable it for particular words. For example, you can keep Excel from changing (c) to the copyright symbol ©.

To stop auto-correcting a specific word, this is what you need to do: Where are microsoft office files stored on mac.

  1. Open the AutoCorrect dialog (File > Options > Proofing > AutoCorrect Options).
  2. Select the entry you want to disable and click the Delete button.

The screenshot below shows how to turn off the autocorrect of (c):

Instead of deleting, you can replace (c) with (c). For this, type (c) into the With box, and click Replace.

If you decide to return autocorrect (c) to copyright in the future, all you'll have to do is open the AutoCorrect dialog and put © in the With box again.

In a similar manner, you can turn off autocorrect for other words and characters, for example, prevent changing (R) to ®.

Tip. If you have difficulties with finding the entry of interest in the auto-correct list, type the word in the Replace box and Excel will highlight the corresponding entry.

How to undo AutoCorrect in Excel

Sometimes, you may need to prevent autocorrect of a specific entry just one time. In Microsoft Word, you'd simply press Ctrl + Z to undo the change. In Excel, this deletes the entire cell value instead of reverting the correction. Is there a way to undo AutoCorrect in Excel? Yep, here's how you can do this:

  1. Type a space after the value that is auto-corrected.
  2. Without doing anything else, press Ctrl + Z to undo the correction.

For instance, to undo the autocorrect of (c) to copyright, type (c) and then type a space. Excel performs the auto-correction, and you immediately press Ctrl + Z to have (c) back:

How to add, change, and delete AutoCorrect entry

In some situations, you may want to extend the standard list of misspellings used by Excel AutoCorrect. As an example, let see how we can force Excel to replace the initials (JS) with the full name (John Smith) automatically.

  1. Click File > Options > Proofing > AutoCorrect Options.
  2. In the聽AutoCorrect dialog box, enter the text to be replaced in the Replace box, and the text to replace with in the With box.
  3. Click the Add button.
  4. Click OK twice to close both dialogs.

In this example, we are adding an entry that will automatically replace 'js' or 'JS' with 'John Smith':

If you'd like to change some entry, select it in the list, type the new text in the With box, and click the Replace button:

To delete an AutoCorrect entry (predefined or your own one), select it in the list, and click Delete.

Note. Excel shares the AutoCorrect list with some other Office applications such as Word and PowerPoint. So, any new entries that you have added in Excel will also work in other Office applications.

How to insert special symbols using AutoCorrect

To have Excel insert a tick mark, bullet point or some other special symbol for you automatically, just add it to the AutoCorrect list. Here's how:

  1. Insert a special symbol of interest in a cell (Insert tab > Symbols group > Symbols).
  2. Select the inserted symbol and press Ctrl + C to copy it.
  3. Click File > Options > Proofing > AutoCorrect Options.
  4. In the AutoCorrect dialog, do the following:
    • In the With box, type the text that you want to associate with the symbol.
    • In the Replace box, press Ctrl + V and paste the copied symbol.
  5. Click the Add button.
  6. Click OK twice.

Microsoft Excel Mac Auto Correcting Values To Decimals Worksheet

The screenshot below shows how you can create an auto-correct entry to insert a bullet point in Excel automatically:

And now, whenever you type bullet1 in a cell, it will immediately be replaced with a bullet point:

Tip. Be sure to use some unique word to name your entry. If you use a common word, you'd often need to revert auto corrections not only in Excel, but in other Office applications.

That's how you use, adjust and stop AutoCorrect in Excel. I thank you for reading and hope to see you on our blog next week!

Microsoft Excel Mac Auto Correcting Values To Decimals Calculator

You may also be interested in: