Contextures News

7 Ways to Round in Excel

April 18, 2017

See 7 ways to round numbers in Excel, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

7 Ways to Round in Excel

Have you ever been in a meeting, and had someone point out that your totals are wrong? It looks odd, but sometimes 2+2 = 5 in Excel, and you might be able to guess why.

total looks incorrect

The problem occurs if you use number formatting to make the numbers look rounded, when they really aren't. Really, the numbers are 2.3 and 2.3, which add up to 4.6.

numbers with decimals showing

In some cases, you might want to use a function to round the numbers, instead of just hiding the decimals. I've made a slide show that shows 7 ways to round in Excel, and you can see it, and some rounding examples, on my Excel Rounding Functions page. There are workbooks to download too, so you can follow the examples.

Hidden Hyperlink

If you do any accounting work, there are regular tasks that need to be done at month end. In one of my workbooks, I have to add a couple of entries on a worksheet, before starting the next month's entries.

Too often, I forgot to do that, and had to go back and enter them later. So, to remind myself, I added a formula at the top of the sheet, with a hyperlink link at appears on the first day of the month. Conditional formatting adds a bright colour too, so it's hard to ignore!

The hyperlink takes me to another sheet, where I can copy the items, and paste them onto the main sheet:
=IF(DAY(TODAY())=1,HYPERLINK("#MonthEnd!A1","Add MONTH END items"),"")

See more HYPERLINK examples on my website.

hyperlink appears first day of month

Your Feedback

Thanks for sending your formulas for last week's topic of using SUMIFS with blank criteria cells. You can download my sample file that shows the original formula, my revised formula, and your suggestions. The zipped workbook is in xlsx format, with no macros.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Troubleshooting - On his Engineering Excel blog, Charlie Young shows two quick ways to troubleshoot broken Excel formulas. He uses an engineering example, but the tips are useful for any formulas. (Level - All)

Power BI - Learn about the pitfalls of Power Query, in this series of articles from Gil Raviv. He previously led the Power Query team at Microsoft, so he's certainly well-qualified! (Level - Intermediate)

Also see: My Excel Products || Excel Events

Garden 2017

The canopy is up, and things are finally sprouting in our garden. I'm especially happy to see the rhubarb, but we'll need to wait a while before the harvest. The sedum and day lilies are back in abundance, and there is a little row of narcissus too. And, of course, the weeds are doing nicely too. ;-)

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

NOTE: If you have trouble with the images or links in this email, use this URL to see the online version:

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




Get weekly Excel tips from Debra






Last updated: July 21, 2017 9:39 AM