Five Awesome Excel Number Format Tricks

by | Oct 15, 2025 | Efficiency, Excel Tips | 0 comments

Overview

I’m guessing your Excel files have a lot of numbers in them.  I can’t think of hardly any that don’t!  For most of my career, I just used the standard number formats with maybe a few custom ones – mainly for dates and displaying currency in a certain way.  But one time a colleague showed me one of these tricks, and I was blown away.  So I read everything I could on number formats, and that led to creating these five tricks and then some.  If you use Excel frequently, you’ll definitely want to check these out to make your files pop and make them super-clear to anyone who uses them.

In this article, we’ll walk through five number format tricks that give you a lot of flexibility in how to display numbers and beyond – without messing up your formulas!  Make sure to grab a copy of the example workbook that accompanies this article using the box below!  And if you want to learn even more about number formats, check out this comprehensive template that includes everything you want to know plus 50 great examples!

Why use custom number formats

The built-in number formats are helpful, but they can leave you wanting more.  More color.  More whitespace.  More simplicity.  And that’s where custom number formats come in.

You can get very specific with how you display numbers, without affecting the underlying values used in formulas!  By using the tricks below, you’ll make numbers stand out and easy to understand.  You’ll remove unnecessary clutter.  And you’ll be able to add context where it’s needed.  Along with other formatting options (fonts, borders, fill colors, etc.), that gives you a lot of power.  And combining them with conditional formatting gives you even more power to make your files amazingly dynamic!

The number format tricks

1. Colors

Colors are a great way to make certain numbers stand out. The most common example of this is displaying positive and negative numbers in different colors – negative as red and positive as black or green

Colors are defined within the number format code components (positive, negative, zero, text – separated by semi-colons). Simply include the color code within brackets (i.e. “[color 3]” for red). Alternatively, you can define 8 colors by name within brackets (i.e. “[red]”): black, white, green, red, blue, magenta, yellow, cyan.

See the examples below where the number format codes are used to add colors for positive and negative numbers with this number format code:

[color 10]$_(#,##0.00_);[color 3]_($(#,##0.00);_(-_);@

2. Symbols
Symbols are another great way to make numbers stand out or to add some context. Arrows are among the most common example of this. Note that you can combine these different tricks, such as symbols and colors. I frequently use arrows and colors like in the screenshot below for variance analysis. To display a symbol, simply include it in the number format code in the position you’d like it to appear – usually before the number and any other formatting you want to apply (currency, commas, spacing, etc.). Make sure to include the symbols in the appropriate number format code component (i.e. up arrow for positive). See the examples below where the number format codes are used to add symbols and colors for positive and negative numbers with this number format code: [color 10]▲0.0%;[color 3]▼0.0%;-
3. Conditions
Conditions are another great way to apply very specific number formats to your cells. As opposed to applying formatting to positive, negative, and zero values, you can apply them to values that meet certain conditions – up to two specific conditions plus an “else” condition. This is often useful for specifying thresholds or targeting certain values (trick #5 below includes one of these). Conditions are specified within the number format code in brackets with logic similar to IF statements (i.e. “[>1000]”). You can specific two of these, separated by a semi-colon. The third component (after the second semi-colon) is a catch-all else condition for numeric values that don’t fit the first two conditions. See the examples below where the number format codes are used to add colors for values specified within the conditions with this number format code: [>1000][color 10]#,##0;[<-1000][color 3]-#,##0;#,##0;
4. Rounding
Rounding is a super-effective way to reduce clutter in your numbers and make them easier to understand. While people often accomplish rounding with formulas, sometimes this can lead to issues with related calculations. Including rounding within the number formats allows the raw values to remain unrounded since the rounding is only for display purposes. Whether you round to a certain number of digits, include zeroes as placeholders, or use abbreviations, make sure it’s obvious to users how the numbers are rounded so they don’t make incorrect assumptions about them. I like to put rounding in the column headers when it’s applicable. Rounding is defined within the number format code components by adding a comma to the number placeholders (i.e. “#,###,”). That last comma is what rounds to the thousands place. To round to a higher level, simply add more commas (i.e. “#,###,,” to round to millions). See the examples below where the number format codes are used to apply rounding in a few different ways using number format codes like this one: #,###,"k";-#,###,"k";0
5. Phrases
Phrases are another helpful way to add context to your numbers – without affecting the underlying values. Common examples of this are adding a symbol for degrees, “ID: ” to identifying values, or units of measurement. An example I’ve often used is adding the number of weeks (like in the screenshot below) due to working with fiscal calendars where periods are defined as 4 weeks/5-weeks/4-weeks or something similar. To add text to your numbers in this way, simply include it within double quotes in the applicable number format code component. The text can be different based on defining conditions or the positive/negative/zero/text components. See the examples below where the number format codes are used to add text with a number format code like this one: [=1]"1 week";#" weeks"

Bonus: instantly apply all your favorite number formats

By now, you probably agree that number formats are a lot more powerful than you ever thought.  Those number format codes can be tricky though, so I highly recommend building a library of the ones you use most so you don’t have to re-type them out every time. And if you want to make it super-easy to apply them to your cells, I built a couple of tools exactly for this.  The first one lets you set up a library of reusable number formats in one bulk step, and the other tool uses that library to give you a dynamic search box!  These make it super-easy to reuse all the different number formats you might want to use.  To learn more, check them out here and take a quick peek with the screenshot video here, or within the video below.

Video

Summary

Knowing how number formats work can be a really valuable skill, especially if you constantly work with a sea of numbers like most accountants and finance folks.  Your files will really stand out if you use the tricks above, and if you use those bonus Excel tools, you can be super-efficient at it!  Make it easy for users to understand the numbers by adding colors, symbols, and rounding easily and dynamically.  And make sure to save those custom number format codes you work so hard on in a reusable library!

Have you done some other cool stuff with number formats?  Let us know in the comments below!

Submit a Comment

Your email address will not be published. Required fields are marked *

Don't miss great tips, tricks, news, and events!

  • Get our 105 Excel Tips e-book free!
  • Get monthly insights and news
  • Valuable time-saving best practices
  • Unlock exclusive resources

Almost there! We just need to confirm the email address is yours. Please check your email for a confirmation message.

We won’t pass your details on to anyone else. By clicking the sign up button, you agree to our Terms of Use and Privacy Policy.