The Ultimate Excel Number Formats Guide

Overview

Number formats are an important, but often overlooked part of Excel.  They are much more flexible than most people realize, letting you set very specific formats that provide clarity and polish to your workbooks.  While Excel provides several basic, pre-set number formats, custom number formats let you include placeholders, symbols, text strings, and even colors, without changing the underlying number values.  In this article, we’ll discuss how they work, several examples, some keyboard shortcuts, and some macros that make number formats super-easy to use.

For a downloadable workbook with all the information in this article, as well as 50 number format examples you can copy and paste from, get our Ultimate Excel Number Shortcuts Guide Workbook.

How do you use number formats?

Number formats determine how cell values are displayed, without affecting the underlying values.  This includes dates and times, which are just numbers formatted to display as dates or times. There are three primary ways to update cell number formats:

• Use the pre-set number formats 🖼️ available in the Home ribbon tab – number formats section 🖼️ or the format cells box 🖼️.
• Set a custom number format using the format cells box 🖼️.
• Use macros 🖼️ to set precise number formats – either pre-set or custom formats.

There are several aspects to number formats that are worth understanding.  These are covered below.

The Pieces

Behind a number format is a code that determines how numbers will be displayed (i.e. “$_(#,##0.00_);_($(#,##0.00);_(-_);@”).  I know at first it seems quite cryptic, but we’ll break it down below.  There are four pieces to the number format code (separated by semi-colons) that determine how the format is displayed – positive numbers, negative numbers, zero, and text.  Only the first piece for positive numbers is required; if others are omitted, they follow the positive number piece (text values display as entered). If a blank format is entered in the segment space (between semi-colons), it displays as blank and hides the value.  To hide values from being displayed, but retain the underlying numbers, enter only semi-colons (i.e. “;;;”) as the number format code.

The TEXT function

The TEXT function is extremely useful for displaying a formatted number (or date/time) within a string of text.  Most of the number formats you can set for cell values can also be used with the TEXT function.  Here’s an example:

Using this formula: =”Taxes are due on “&TEXT(A14,”Dddd, Mmmm d, yyyy”)&” this year.”

Would display this in Excel: Taxes are due on Saturday, April 15, 2023 this year.

Placeholders

Several characters perform special actions when used within number formats:

• – 0 – Displays insignificant zeroes if a number has fewer digits than there are zeroes in the format.
• – # – Similar to zero but does not display extra zeroes when the raw value has fewer digits than the format code.
• – ? – Similar to zero but adds a space for insignificant zeroes so that the decimal points are aligned in the column.
• – . – [Period] Displays the decimal point in a number. Leave out to not display any decimals.
• “” – [Double quotes] Displays the literal text between the double quotes (i.e. ” weeks”).
• \ – [Backslash] Displays the single character following it as a literal character.
• @ – [At symbol] Displays the value entered in the cell as text.
• _ – [Underscore] Displays a single blank space according to the width of the following character (not displayed).
• * – [Asterisk] Displays the following literal character as many times as required to fill the width of the cell.

The placeholders above can help with the alignment, especially if you’re using parentheses (i.e. “_(” or “_)”).  If displaying currency, you can use the asterisk to align it to the left (i.e. “$*”).  See the examples below for how to use these special characters.

Rounding

Use comma(s) after other characters to round to thousands, millions, etc. (i.e. “#,##0,”).  Consider adding text to indicate the rounding if not specified elsewhere like the header (i.e. “100k”, “100m”).

Colors

Colors can be separately specified for each code piece, which can be very helpful for separately displaying positive and negative numbers.  56 colors can be specified with codes (i.e. “[color 14]” for turquoise).  8 colors can be specified by name:

• black
• white
• green
• red
• blue
• magenta
• yellow
• cyan

Conditionals

Basic conditionals are supported for numeric comparisons, which is a great way to show colors conditionally.  Enter the comparison within the first three pieces of the number format code (i.e. “[>=500]”).

Dates and Times

Dates in Excel are positive whole numbers since January 1, 1900 but usually displayed in some type of date format.  Time in Excel is a decimal portion of a whole number (so each second is 0.0000116).  See the date and time character codes and examples in the screenshots below for how to display specific date and time components.

Date Characters

Date Examples

Time Characters

Time Examples

Examples

Keyboard shortcuts

To make it easy to set common number formats, there are several shortcuts you can use:

• Ctrl+1 – Launch the format cells box
• Ctrl+Shift+~ – General format
• Ctrl+Shift+1 – Number format (commas, two decimals)
• Ctrl+Shift+2 – Time format
• Ctrl+Shift+3 – Date format
• Ctrl+Shift+4 – Currency format
• Ctrl+Shift+5 – Percent format
• Ctrl+Shift+6 – Scientific format

Macros That Help

The XLEV8 Excel Add-in contains several macros that help you work more efficiently with number formats with shortcuts and bulk automation:

• Toggle Number Format – this lets you toggle through your five favorite number formats, for any selected cells.
• Numer Format Picker – this displays a picklist of common number formats, as well as your five favorite formats, that will be applied to any selected cells.
• Number Format Shortcut – this lets you apply a number format code (defaulting to the text in your clipboard), that will be applied to any selected cells.
• Bulk Format Cells – this lets you set number formats and other cell formats to multiple sheets/ranges in one bulk step.

Summary

If you work in Excel files with a lot of numbers (and almost every Excel file has a lot of numbers!), formatting those numbers optimally can be a huge help for analyzing them and communicating them clearly and effectively.  The details above, including the examples, shortcuts, and macros can help you apply them with ease and impress your colleagues!

What are your favorite number formats or tips and tricks when you use them? Let us know in the comments below!