Custom Number Format: Round To Nearest Thousands
Are you looking for a way to visually round up or down your numerical values without changing the underlying cell value? While there are limitations, you can accomplish this by utilizing a Custom Number Formatting rule. The only caveat is you have to choose to either handle positive numbers or negative numbers. There is no way around this if you need to use a custom number format rule to change the cell’s appearance.
In this article, I’ll walk you through both the rule to tackle positive numbers and the rule to tackle negative numbers. Finally, I’ll show you how to modify the rule logic to handle larger numbers such as rounding numbers to the nearest millions. Enjoy!
Round To Nearest Thousands (Positive Only)
Custom Number Format:
Format Rule: [<=500]0,;#,###,”,000″
How It Works:
• [<=500]0,
The first part of the rule tests if the number is less than or equal to 500. If it is, you’ll want to simply round down to zero with the zero + comma.
• #,###,
This part of the rule slashes off the last three places of the number and essentially performs the rounding for you.
- Example 1: If your number was 123,456 —> This rule would give you the value appearance of 123
- Example 2: If your number was 123,501 —> This rule would give you the value appearance of 124
• “,000”
The final part of the rule is simply tacking on three zeroes to the rounded number. This gives the appearance that you rounded to the nearest thousands. Since we want a comma separating every three digits, we need to include that in the string we are appending.
Round To Nearest Thousands (Negative Only)
Custom Number Format:
Format Rule: [>=-500]0,;(#,###,”,000)”
How It Works:
• [>=-500]0,
The first part of the rule tests if the number is greater than or equal to -500. If it is, you’ll want to simply round up to zero with the zero + comma.
• (#,###,
This part of the rule slashes off the last three places of the number and essentially performs the rounding for you.
- Example 1: If your number was -123,456 —> This rule would give you the value appearance of -123
- Example 2: If your number was -123,501 —> This rule would give you the value appearance of -124
Since we want to use parenthesis instead of a dash, we add a “(“ symbol in front of the number. If we use the value in Example 2, the final output would look like this: (123
• “,000)”
The final part of the rule is simply tacking on three zeroes to the rounded number. This gives the appearance that you rounded to the nearest thousands. Since we want a comma separating every three digits, we need to include that in the string we are appending. The same concept with the closed-parenthesis at the end of the string.
How About Rounding To The Nearest Millions?
How would these rules look if you were wanting to round to the nearest millions? You essentially add more places into your custom number format rule. Here’s how both the positive and negative rules would look.
Round To Nearest Millions (Positive Only)
Custom Number Format:
Format Rule: [<=500000]0,,;#,###,,”,000,000″
Round To Nearest Millions (Negative Only)
Custom Number Format:
Format Rule: [>=-500000]0,,;(#,###,,”,000,000)”
Store Your Number Rules In The Ribbon!
Custom Number Format rules like the ones in this article can be a hassle to write and even more of a pain to remember how to write. That’s why I created the Excel add-in everyone has been buzzing about in 2020, the Number Formats Add-in.
This add-in will allow you to store up to 135 Custom Number Format rules in your Excel Ribbon so you can apply them with a click of the button. You can even save your most used format buttons to your Home tab.
Excel Add-in Features:
- Store up to 135 Custom Number Format Rules
- Full Button Customization (label, icon, hover-tip, location)
- Add Favorites to your Home Tab
- Apply Quick Math to your selected data
- Includes Icon Library for the Excel Ribbon
- Cycle through 3 Format Rules per button
- Excel’s Decimal, Font/Fill Color, & Border buttons included
- Apply Text Alignment with your Number Formats Rule
- No recurring subscription (isn’t that refreshing?)
- Free Updates
30-day money back guarantee
I Hope This Helped!
Hopefully, I was able to explain how you can use some unique aspects of Number Format rules to accomplish this rounding effect. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.