The Comprehensive Guide to Excel Custom Number Formats
At Excel Efficiency, we spend a lot of time explaining how to speed up formatting in Microsoft Excel.
For example, you can read this shortcut roundup to get up to speed on the best formatting shortcuts.
And there are some formatting hints in our list of ways to Idiot-Proof Your Spreadsheets, too.
These shortcuts and tips cover the pre-defined number formats in Excel – which are probably sufficient for around 95% of your work.
But if the default formatting options in Excel don’t quite allow you to present your work in just the right way, there is a solution.
- Introducing the Custom Number Format in Excel
- Display numbers as units (or add any text to the start or end of a cell)
- Make zeroes appear as blanks
- Add leading zeroes to values
- Display values in thousands or millions
- Fill up cells with a specific, repeated character
- Transform a date into its weekday
- Specify alternate formatting for negative, zero and text values (including colors)
- Add an indent (blank space) to the left or right
- Display numbers as phone or fax numbers
- Full list of Custom number format characters
Introducing the Custom Number Format in Excel
The Custom number format is a flexible formatting option that allows you to create your own specific formatting types.
How to apply a Custom number format
To take advantage of all the good stuff in this guide, you’ll need to find where this magical Custom number format option is.
First up, hit Ctrl + 1 to open up the Format Cells dialog box.
Next, in the Number tab, you should see “Custom” in the list of categories on the left:
Whoa – there are a lot of crazy looking characters in that box on the right!
It’s ok. They all refer to specific ways that you can format your data in Excel, and by the time you’re finished reading this post, you’ll understand what all of those characters mean.
The important thing to realise is that you can create your own completely unique Custom number format instead.
Why use the Custom number format at all
But before we dive into the examples, why even bother with the Custom number format anyway?
The main benefit is that you can alter the appearance of your data without changing the actual values themselves.
If you’re in any kind of analytics role where the integrity of your data is important, then this is a great trade-off between good presentation and preserving your raw data.
It also means that if you’re sharing your work with other people, then the formatting can easily be reset or modified.
If you really want to change your data, you consider creating an additional column, using the TEXT() function to change your original values. All of the tricks in this guide can be used with TEXT().
However, if you update your data via the TEXT() function you won’t be able to perform any calculations on the data, since it’s now in Text format.
What you will learn
The rest of this post will get you up to speed with a wide range of situations where the Custom number format can handle.
I’ve scoured the Excel website community to find a lot of these helpful tips, and you can visit their sites directly to find out more as you read through each section.
And as an added bonus, at the end of this post is a detailed table listing each of the characters that can be inserted into any Custom number format (#, 0, @, etc) and explains how each character works.
Display numbers as units (or add any text to the start or end of a cell)
Let’s start off this list with a fairly simple example.
If you need to present your data in a certain unit of measurement (say, “feet” or “seconds”), you can format your data to display some text after the number in the cell.
Enter the below line into the Type: section of the Custom number format dialog box:
Here’s how it looks:
Explanation: The hashtag symbol (#) indicates a number, and the text inside the quotes is the text that appears afterwards. The space between the # and ” symbols is important as well – Excel will add the space (or multiple spaces if you like!) into the format.
When you look at each of the values of both columns in the formula bar, you will notice that only the value appears. However, the cells on the right are displayed with the “ft” text at the end because we told Excel to format it in that way.
Add decimal places to the number
Notice the third value, where the value 12.4 is only displayed as “12 ft”? We’ve lost the decimal place because we didn’t include it in the number format. To fix this, try this format instead:
Explanation: With Custom number formats, the number 0 indicates a number just like the # symbol, but it means that the digit will always appear, even if the number doesn’t require it. This way, the numbers line up consistently. If we chose to use the format #.# “ft”, then the first line would show up as 14. ft which wouldn’t appear consistent (and is mathematically wrong!).
You can even add text to the start of the cell in the same way – either of the below will work (change Text to any text string you like):
For numbers: “Text” #
For text: “Text” @
Make zeroes appear as blanks
(Source: Mike’s Macros)
Alright, let’s make things a bit more complicated now.
You have a list of numbers, some of which are positive, some are negative and some are zero.
But you don’t care about the lines with zeroes in them – you want them to appear blank.
Enter this into the Type: section of the Custom number format dialog box:
As you can see, the negative values appear with brackets around them.
Explanation: Custom number formats in Excel can have up to four sections that are separated by semi-colons (;). In order, they refer to positive values, negative values, zero values and text. So the first section is “0” which simply represents a number, and the second section “(0)” formats negative values as a number with brackets around them.
The trick with the zeroes appearing blank is because there is a second semi-colon at the end of the second section. This means we’ve specified a third section of the custom format, but since no extra characters are listed, Excel displays nothing (ie: the cell appears blank).
If you want to present negative values as they are normally, you can try this number format instead:
Now, if you want to have zeroes always appear as blank, you can disable this Excel option in the Advanced tab:
Tweak your Excel options exactly how you like them, with this 5-Step Excel Option Healthcheck.
Add leading zeroes to values
(Source: Excel Campus)
We’ve already talked about the difference between including # and 0 in your Custom number format.
Both mean that you’re displaying a number, but the “0” means it has to be displayed.
If you add extra “0” symbols to the number format, extra digits will be added to the formatting.
If you want to add leading zeroes to your numbers so that five digits will always be displayed, enter this as your Custom number format:
You can see that the smaller numbers have zeroes added to the start. This is because we’ve required five digits to be included in the formatting.
This can work with decimal places as well – if you want a specific number of decimals you can have them, but Excel won’t add any extra.
Display values in thousands or millions
(Source: Excel Easy)
If you are dealing with very large numbers, you can easily shrink them down into values that are easier to understand.
Just add a comma after the number to divide it by 1000.
Let’s see an example:
Every additional comma will further divide the value by a factor of 1000, so for examle “0,,” will divide the number by one million. The “thousand” is just extra text that is placed after the number – you could put any text you like there.
You should keep in mind that the comma has an alternate use in Custom number formats.
If the comma is surrounded by a number placeholder (either 0 or #) on either side, then the number will be displayed with the thousands separator. Let’s see an example of that:
Fill up cells with a specific, repeated character
(Source: Excel Easy)
I’ll admit, this won’t be a very common option for most Excel users, but you’ll never know when you need this.
I’ve seen some internal templates at previous companies I worked at, which had these kinds of cells:
And you can tell that someone has held down the underscore key to fill out the rest of the white space for each cell.
This is fine – to an extent – until you need to rearrange your template or resize some columns.
Then these cells will look ugly and unfinished.
So if you want to fill up any blank space in the cell with a specific character, this Custom number format will help you:
Explanation: The “@” symbol is a placeholder for text, and the symbol after the asterisk is what the white space will be filled with.
If you resize the column in Excel, you can see that the underscores shrink accordingly:
Definitely handy for if you’re creating user forms.
Transform a date into its weekday
(Source: Extend Office)
If you want to display the weekday of a given date, you can utilise the Custom number formatting in Excel to display that.
By default, dates will appear in a “01/05/2015” or “01-May-2015” format so you can always select the “Long Date” formatting option in the Excel Ribbon to easily display the weekday. If this is enough for you, continue on to the next section. If not, read on!
To display only the weekday of a given date, use either of the below Custom number formats:
ddd (eg: Sat)
dddd (eg: Saturday)
It’s not very common to display only the weekday – you’re sacrificing a lot of date/time information.
One common use of this is to format dates in PivotTables with their weekday values.
This way, you can improve the value of your PivotTable by grouping data based on their weekday. Give it a try!
Want a crash course in learning PivotTables? Try the 7 Minute PivotTable Tutorial.
Full List of Date and Time Codes for Custom Number Formats
If you want to combine multiple date/time elements together into your own unique Excel Custom number format, refer to the below table.
|Years||yyyy||Displays the year as a four-digit number.||1995|
|Years||yy||Displays the year as a two-digit number.||95|
|Months||m||Displays the month as a number without a leading zero.||7 (for July)|
|Months||mm||Displays the month with a leading zero.||07 (for July)|
|Months||mmm||Displays the month as text, as an abbreviation.||Jul|
|Months||mmmm||Displays the month as text.||July|
|Months||mmmmm||Displays the month as a single character||J|
|Days||d||Displays the day as a number, without a leading zero.||4|
|Days||dd||Displays the day as a number, with a leading zero.||04|
|Days||ddd||Displays the day as a day of the week, as an abbreviation.||Fri|
|Days||dddd||Displays the day as a day of the week, without abbreviation||Friday|
|Hours||h||Displays the hour without a leading zero.||6|
|Hours||hh||Displays the hour with a leading zero.||06|
|Hours||[h]||Displays elapsed time in hours (to be used when the time value exceeds 24 hours).||32|
|Minutes||m||Displays the minute without a leading zero.||3|
|Minutes||mm||Displays the minute with a leading zero.||03|
|Minutes||[m]||Displays elapsed time in minutes (to be used when the time value exceeds 60 minutes).||105|
|Seconds||s||Displays the second without a leading zero.||9|
|Seconds||ss||with a leading zero.||09|
|Seconds||[s]||Displays elapsed time in seconds (to be used when the time value exceeds 60 seconds).||74|
|Converts to 12-hour time. Displays either AM/am/A/a or PM/pm/P/p depending on the time of day.||4:14 PM|
From the Excel help page here.
Better and faster than using complicated IF() functions after using the WEEKDAY() function!
Some jargon about reviewing data and wanting to understand the distribution of dates.
Specify alternate formatting for negative, zero and text values (including colors)
(Source: Excel Tactics)
Earlier in the post, we spoke briefly about setting different values when a value is negative or zero.
To reiterate, you can have up to four sections of the Custom number format, and they need to be entered in this order:
- Positive values
- Negative values
- Zero values
If any sections aren’t specified in your Custom number format, they take on the formatting of the first section.
Here’s an example of a fairly complex number format using all four sections:
Explanation: I’ve specified which colors should apply to each section of the number format – note that the color needs to be at the start, otherwise it won’t work. For zero values, I’ve also chosen to display the text “NULL”. And for the text section, I can include the word “General” (without quotes) to display the General number format type.
The full list of colors that can be used are:
Add an indent (blank space) to the left or right
(Source: MBA Excel)
You may want to include a small buffer or indent at the start or end of your data.
Adding these indents mean that values might align better, if you want positive or negative values to appear differently.
The code to add an indent or blank space is an underscore (_), followed by any character. The width of that additional character will be the size of the indent.
Try this custom number format to add a blank space for only positive values:
Explanation: The “_(” code creates a blank space equal to the width of the “(” symbol. Same thing for the “_)” code, which is placed after the number. With the above number format, positive values will have “invisible” parentheses at the start and end, while negative values will actually display the parentheses. As a result, the numbers appear aligned, regardless of whether the cells are left or right aligned.
You can use any character after the underscore. If you want a larger indent or blank space, consider a character like ‘m’ or ‘w’.
Display numbers as phone or fax numbers
(Source: Microsoft Office help page)
If you’ve ever downloaded a raw data dump of contact information (for example, your customers or suppliers) then there’s a good chance that you receive the phone numbers in a standard number format, without any dashes, spaces or any other markup.
Below are some of the common number formats you may want to use:
US/Canada: (###) ###-####
UK (London): (###) #### ####
Australia: (##) #### ####
(The values above are all randomly generated so hopefully none of them are real North American phone numbers. Please don’t call these numbers!)
As an alternative, you can use the “Special” category in the Format Cells dialog box instead. For US phone numbers, the default phone number format is listed there under the English (U.S.) locale).
Full list of Custom number format characters
Below is the full list of special characters that you can include in the Custom number format field:
|0||Placeholder for digits (numbers). Will display leading zeroes if there are fewer digits than '0' characters.||123||00000||00123|
|#||Placeholder for digits (numbers). Does not add any leading zeroes.||123||#####||123|
|?||Placeholder for digits (numbers). Adds blanks spaces for unnecessary zeroes at the beginning or end of the value, so that decimal points always appear in the same space in the column.||12.3456|
|. (period)||Placeholder for the decimal place in a number.||123.456||#.#||123.5|
|_||Adds a blank space, to the width of the following character.|
eg: '_m' will create a blank space with the width of the letter 'm'.
|* (asterisk)||Repeats the character after the asterisk, to fill up the blank space in the cell.|
eg: '*-', when placed after a format, will add dashes after the end of the cell's value.
|[color]||Formats the cell in a specific color. Must be placed at the start of the number format.|
[Black], [Green], [White], [Blue], [Magenta], [Yellow], [Cyan], [Red]
|%||Displays the value as a percentage||0.27||#%||27%|
|, (comma)||Displays the thousands separator in a number, when enclosed on both sides by a digit placeholder. When placed after a digit placeholder (0 or # or ?), it scales the value by a factor of 1000.|
|@||Placeholder for text.|
|/||Placeholder for fractions.||1.23||# ??/??|
|E||Placeholder for scientific notation. Requires a '+' symbol after, and a digit placeholder before and after.||1000234567||#E+#|
And there you have it – a complete guide of Custom number formats in Microsoft Excel.
With the above examples and reference tables, you should be able to format your data in any way you like.
And remember – any of the codes that are listed in this post can be applied in the TEXT() function, if you want to update your values.
It’s hard to think of any formatting requirements that aren’t listed here – but if you have any, feel free to comment below and we can help come up with a solution!