Article by Michael Diamond.
Thank you, Michael, for all the effort you’ve put in to write this detailed article on custom formatting in Excel. We appreciate you sharing this with us and our online Excel community.
Typing data in Excel can have the same result as typing a letter on a typewriter – what you see is what you get – commonly referred to as “WYSIWYG”. Unlike an old Remington typewriter, Excel can chameleon a cell by magically transforming its color and appearance by changing its custom & number formatting.
An Excel cell can be “dressed up” by using different formatting instruments: typeface (fonts), borders, cell fills, alignments, number formatting etc. This article focuses on number formatting techniques since they’re used in everything that happens in Excel and the most under-utilized formatting tool.
Number formats are comprised of positive/negative numbers, zero values and text strings. Excel can’t determine in advance if your cell entry is a number, a zero or a text entry. So, Excel has compensated for this unknown by creating a flexible number format structure. This structure (often called numeric code or a number format) is comprised of 4 parts – segment 1; segment 2; segment 3; segment 4. Each of these sections (i.e. “segments”) are separated by semicolons. This “segmented” structure allows the user to control a cell’s “character format” appearance. The structure looks like this:
Positive numbers; Negative numbers; Zero; Text
- Segment 1: formats positive numbers (values)
- Segment 2: formats negative numbers (values)
- Segment 3: formats 0 (zero) entries
- Segment 4: formats text entries (letters or letter/number combinations)
Cell values are “customized” by using a formatting symbol(s) in either 1, 2, 3, or all 4 format – segments.
Real-Life Example
| Figure 1 – Format Cells Dialog Box |
One spreadsheet in my current assignment requires Employee ID’s contain at least 5 digits. However, some employees have been with the company for over 20 years and their ID’s have 3 or 4 digits. To conform to the 5-digit rule, we must add leading zeros to their ID’s. One method used is to type an apostrophe into the cell and then insert the required leading zeros. Another approach is to insert a helper column and use the Text function (A2=Text(B2,”00000”) to add the necessary zeros. The apostrophe method’s quite laborious and the Text function requires both spreadsheet awareness and maintenance. What was my solution to avoid all this work, eye strain and headache? Simply launch the Format Dialog box (Ctrl 1), select the Custom category and type 5 zeros (see Figure 1) into the “Type:” text box and apply the new format!
What is Custom Formatting?
This article’s goal is to define what it means to “format” and to “customize a cell”?
To start with, as we all know, organisms (like the human body), groups (like the military) and professions (like plumbers and carpenters) are controlled by codes, rules, ethics, laws, etc.:
- Genetic traits are determined by our inherited genetic code
- Military conduct is subject to the Uniform code of Military Justice (UCMJ)
- Building construction is governed by city/county building regulations
Number formats, unlike border, font or interior fill formats, are magically controlled by the specific numeric schema as just discussed. This number format mechanism works because the structure requires system-dedicated symbols and/or characters to be strung together in specific sequences (sounds like a genetic code, doesn’t it?). Custom codes are either comprised of a single character or a string of single/multiple characters.
On the Home ribbon you’ll see the Font, Alignment and Number menus (highlighted in yellow).
Figure 1 – Format Control Groups
You’ve used the format icons shown on the Font, Alignment and Number menus someplace else, haven’t you?
I’m sure you’ve launched the Format Cells Dialog box and used these tools, represented as icons on the Home ribbon, to format charts, tables and lists, right? Interesting you accessed them from the Format Cells dialog box too, but they appear in reverse order from the Home Ribbon?
Figure 2 – Format Cells Dialog Box (Number, Alignment, and Font Tabs)
Watch it Live!: Refer to Figure 2.
All format icons and text boxes appearing on the Font, Alignment and Number menus are “connected” to worksheet cells. Some menu devices, like toggles, turn a format on or off (e.g. Bold, Underline, Alignments, Comma separators (“,”), etc.). Other icons, when selected, display their own icons — like font/fill colors, currency formats, font size icons, etc. Press a toggle or icon and Poof! The format appears! Selecting any of these menu items allows you not only to change the current cell format but also to see how that format is currently being applied to your selection – watch them do their duty – live!
But, the font, font size and number format textboxes (“Calibri”, “11” & “General” in Figure 3 above) behave differently. These items allow you to not only change the current format but they also “mirror” the currently selected format without having to press a toggle/icon button or activating a dropdown. Glance at them now and again and you’ll instantly know the current Font, Font Size and Number Format being applied to your selection, without moving your mouse or tapping the keyboard, hands free!
Each menu item on the Font, Alignment and Number menus do behave like cell-attribute “microscopes”. They allow you to observe all the “living” custom formats on your worksheet, albeit one cell at a time.
Format Cells Dialog Box
The Format Cells dialog box is launched by one of the following several methods:
- Press Ctrl + 1
- Right-click on a cell or press Shift + F10 then select Format Cells from the list
- Home (tab) à Cells (group) à Format (button) à Format Cells
- The Find and Replace Dialog box (press the Options button, then press the Format button)
- The last item in Number Format dropdown list (located in the Number group of the Home ribbon) has an option called “More Number Formats”. Selecting this opens the Format Cells dialog box.
Figure 3 – “More Number Formats” Option
- “Font”, “Alignment” & “Number” menus. Press the Dialog Box Launcher (downward pointing arrow icon in the right corner of many button groups – highlighted in yellow) and the Format Cell dialog box is launched. The active tab depends upon which menu arrow is pushed (see Figure 4).
Figure 4 – Dialog Box Launchers
Whereas the Number and Alignment menus appear on their own tabs by the same name, the Font menu is sprinkled across 3 different tabs (i.e. Font, Border & Fill headings– Figure 5) due to the multitude of formatting elements available for each formatting instrument.
Figure 5 – Font, Border, and Fill Tabs
Leveraging Custom Number Formatting
Of all formatting tools available in Excel, the “Number formatting” tool can be the most complex to understand but one of the most powerful –and rewarding –to use.
So, how does Excel apply “Custom Formatting” as we’ve been discussing in this article?
To “customize”, according to the Merriam‑Webster dictionary, means to build, fit or alter something according to individual (i.e. “cell”) specifications. To “format” is defined as to organize or to prepare something in a certain arrangement, layout or configuration (like tables, charts, reports & lists).
Merge these verbs into a single noun (i.e. “Custom Format”) and Excel comes up with this definition:
A “Custom Format” is any unique combination of items (typefaces, borders, font sizes, colors, alignment, numeric representation, conditional formatting formats, etc.) that alters the appearance of the cell’s data without altering the original entry (with a couple of exceptions).
In other words, any number or text sitting in a cell is always ”formatted“ (i.e. customized). A cell, by default, is always “dressed up” in some fashion. Otherwise, we couldn’t create those amazing dashboards, charts, tables and lists. The type of “fashion attire” required for our reports depends upon the individual reporting requirements. After all, Excel isn’t one of those “prehistoric” Remington typewriters!
Appearances in Excel are deceiving
Excel, as we all know, is often referred to as an “electronic spreadsheet”. It was designed to take the place of the green accounting ledger paper consisting of a space to write comments followed by little green “squares (cells)” where a single number or letter was printed using either a pen or a pencil.
Excel’s “spreadsheet” appearance, unfortunately, is an electronic illusion. Remember how I compared Excel to a typewriter? Well, maybe Excel should be called the “new age” typewriter. Excel takes “WYSIWYG” to mesmerizing heights!
Excel appears on your computer screen as the “squares” of the accounting ledger (Note: each cell can hold 255 characters as opposed to 1 character). While the data entries seem to be made on a “ledger (spreadsheet)” they’re really typed into the Formula Bar (the white text bar sitting above the electronic “ledger”). Data gets “dressed-up” on the spreadsheet – not the Formula bar.
Excel processes data entry visually in 3 steps (from the floor up not the roof down)
- data is entered in the Formula Bar, but it appears to be entered in a spreadsheet cell
- a numeric format is applied to the entry (either a number or the default General format)
- your computer screen displays the formatted, customized data entry.
The Formula Bar entry remains unaltered, but the worksheet value appearance is “dressed up” (formatted) in some fashion. This “dressed up” view is both the “bird’s eye view” and the location where your data is “massaged/modified” format wise. Conversely, your data in the Formula Bar just sits there as entered.
Type 1000 into any cell. Excel internally sees 1000.00 not 1000 – 1000 followed by decimal values! Excel does this because of the way it is programmed to store dates and times.
If your cell contains a time format, like “h:mm:ss”, something visually odd happens in Excel. You enter 1000 in the Formula Bar, but it is somehow replaced with “9/26/1902 12:00:00 AM”. Then, because a time format is selected, 1000 in the spreadsheet view changes to “0:00:00”. Remember, appearances in Excel can be deceiving – which is why it isn’t a “WYSIWYG” typewriter!
And didn’t I just say original cell entries always remain unchanged?
So why does the time/date format appear to replace 1000 in the Formula Bar with “9/26/1902 12:00:00 AM”? This switch is conjured up, as just mentioned, by Excel’s internal date/time programming.
Though you see a Date and Time value in the Formula Bar, the original value – 1000- still lies underneath this date/time string. Otherwise, Excel wouldn’t show that date and time value.
Ah, appearances in Excel are deceptive, don’t you agree? Date and Time formats, along with the “birds eye view” exemplify this axiom. This is the only time the Formula Bar will not display the original value.
Date and Time formatted value are the only formats which seem to violate the Formula Bar original entry rule, but they don’t – they sit on top of your data, they don’t replace it. Mystifying, isn’t it?
Here’s why date/time formats do this.
The beginning of time in Excel was arbitrarily chosen to be 12 AM, January 1, 1900. Excel stores dates and time values as decimal serial numbers. The day component is expressed by the whole number before the decimal and the time component expressed by numbers after the decimal. A single second is equal to approximately 0.00001157407 seconds (1 day divided by 86,400 seconds per day).
In other words, Excel’s programming recognizes numbers before the decimal as dates and values (“0.00” in this scenario) after the decimal as time – the ‘decimal” portion of a 24-hour day. For example.125 would represent 3:00:00 AM in the morning. Or .78 translates to 6:43.12 PM.
So, when 1000 was typed into the Formula Bar, Excel saw 1000.00 as 1000 full days after the ‘beginning of time” without any time elapsing. 1000, as a value, still sits in the Formula Bar but is hidden underneath the “9/26/1902 12:00:00 AM” value. Excel can’t show a formatted value if the value is absent from a cell!
If you change the format to “m/d” – a date format- the Formula Bar would now show “9/26/1900” and your spreadsheet would display “9/26” because the year symbols (“yyyy”) weren’t included in the date only format. Change the date format to a currency format (sans zeros). 1000 now is visible in the Formula Bar unfettered-where it sat all along-but the “bird’s eye view” has now morphed into “$1000”!
Figure 6 – Value Appearing in Formula Bar versus Cell
“New” custom formats (both in appearance and number formatting) are created when a cell’s appearance is customized so that’s its active format is “unique” from any other cells in the entire file.
Alert: Excel counts Custom formats (e.g. fill, borders, fonts, etc.) and Conditional Formatting formats as entirely separate and distinct formats though the same formatting can be applied to a cell using either method.
They are considered distinct formats because the manually added format is performed on the “fly” (i.e. you decide when and how to change the cells format). However, Conditional Formatting is dependent upon true/false conditions (rules) that control when the format is activated. More memory is required to run Conditional Formats than it does to manually apply custom formats because of the true/false trigger logic.
Be aware that adding different fills, borders, fonts, conditional formats, etc. to your file will increase the file’s size and can cause file “bloating”. Too many custom formats in a file will also eventually slow down Excel’s calculation time as well.
In summary, both custom & Conditional formats both will consume a lot of computer resources. Number formats, on the other hand, normally consume much less.
Excel 2016 is the first version, unlike previous Excel versions, that doesn’t have a custom format limit and won’t freeze or “crash” like previous versions. Earlier Excel versions do have a custom format limit – between 4,000 to 64,000 custom formats – depending upon the Excel version. You can find this limit for your Excel version on the web. It is a good idea to review that documentation for your version to understand your Excel version limitations, if any.
So, if your files are calculating or updating slowly, reducing the number of custom formats and number of conditional formats might help speed them up – assuming you don’t have numerous array formulas lurking elsewhere on your spreadsheets (but that’s entirely different bedtime story).
All unformatted cells, by default, apply the “General” format. Translation: the cell entry appears as typed (“WYSIWYG”).To change the cell “clothes“ use the number formats provided in the “Number” submenu (on the Home Ribbon) or the Categories on Number tab in the Format Cells tool (dialog box).
The Number Formatting Example: Code and Symbol Tables
Number formats are comprised of different symbol combinations, like #,?, 0, [Red], $, €, h, m, s , d, etc. These “numeric code” characters (symbols) are Excel’s “scripting language” which is used to cast its cell transformation magic to format the charts, tables, and lists that you see in the “bird’s eye view”.
The number categories in each workbook are all “housed” on the Format Cells à Custom category list box (Figure 1 – Format Cells Dialog Box, for example). Each new workbook comes with 36 number formats. New number formats can be added to the existing list and remain there until they are manually deleted. These formats can only be removed by pressing the Delete key. To Delete a format, select the format and press the Delete button in the Format Cells dialog box (see Figure 7 – Deleting a Custom Number Format)
Figure 7 – Deleting a Custom Number Format
All number formats listed the in “default list” are displayed in the “Type:” text box only when the “Custom” option is chosen from the Category list.
The currently selected number format code, in Figure 8 above, is highlighted from the “default” list box and the “Type:” text box (Figure 8 -the yellow highlighted section) displays the code selection. What happens, though, if a format is #37 in a list of 36 formats? Translation – this format is missing from the default list.
Suppose you’ve used the “Currency” category to format a cell and the format currency you’ve chosen is #37 (i.e. its missing from the default list). The format will show in the “Type:” field but nothing will be highlighted in the list. So how does Excel add your “new” Currency format to the existing format list?
First, the new currency format from the Currency category is displayed in the “Type:” field. Nothing is highlighted in the default formats list box because this currency format is “missing” from Excel (well, not really but that’s another bedtime story too). Pressing the Enter key automatically appends the new format to the bottom of the list box and the new currency format is highlighted in the default list.
The “Sample” field sits above the Type: field (Figure 8 – Sample of Active Cell with Formatting Applied). Its function is to provide you with a snapshot of how the Type: field number code “impacts” the active cell on the worksheet. Flip to the spreadsheet and note how it appears like the Sample field (see in Figure 9).
In Figure 8, note that cell A1 isn’t formatted. Cell A1 and Sample text box don’t match. Why is this?
In this example, the accounting number format hasn’t been applied yet because the “OK” button hasn’t been pushed, activating the format. Otherwise, A1 would show “$1,000,000,00”. Pressing the “OK” button applies the format and closes the dialog box. Try it!
Figure 8 – Sample of Active Cell with Formatting Applied
Refer to Figure 8 Again:
The following 3 things happen in the Custom Category when 1000000 is entered in cell A1.
- 10000000 is displayed in the Formula bar (not shown in Figure 9).
- The “chosen” number format is shown in the “Type:” field
(Figure 8, highlighted in yellow just below the “Sample text box) - The “Sample” text box displays the formatted value displayed as you will see on the spreadsheet.
In summary: 1) Excel displays the unaltered data in the Formula Bar; 2) the active number format is revealed in the “Type:” field’; and 3) the final “bird’s eye view” value is presented in the ‘Sample” field.
Hiding Cell Contents
Here is a little trick to show how to use custom formats to hide cell entries. This approach hides the spreadsheet value but, unlike other approaches, displays the value in the Formula Bar when it becomes the active cell.
Most users either hide a cell’s contents by hiding the row/column it sits in or will change the font color to match the background color. But, the same effect can be achieved by using 3 semicolons as the custom number format (“;;;”). The cell’s value is always visible in the Formula Bar since formats don’t normally affect the Formula Bar appearance but the Sample text box (the “bird’s eye view”) will display nothing since numeric “code” doesn’t appear in any segment (i.e. Excel doesn’t know how to format the cells without any formatting symbols – > Result: the cell appears bereft of any entry). Also note that the semicolon approach uses less memory.
The Excel File Accompanying this Article
Every new Excel workbook contains 36 different default number formats.
The downloadable Excel workbook that comes with this article allows you to interact with these default number formats as they interact with Excel. You can view the number format code from a spreadsheet cell instead of the “Type:” field. You can also interact with the number format icons formats listed on the Home Ribbon in the same way (i.e. on a spreadsheet instead of the Custom Category).
The demo file illustrates how a number format “code” transforms raw data into formatted values viewable from the “bird’s eye view” perspective.
The workbook consists of 2 sheets:
- “36 Default Custom Formats” – This sheet contains the default number formats that come with every new Excel workbook. Each Category example (set up as a 3-column table) is listed in the order that it appears on the Number tab list box on the Format Cells dialog box (Figure 10 “Deleting a Custom Number Format” or Figure 11 “Sample of Active Cell with Formatting applied”) . This sheet only uses one value and applies each default number format to it. Using the same value in every Number category effectively illustrates how the formatting codes and segments work together to magically change the “WYSIWYG” entry into a “butterfly” format.
- “Home Ribbon Formats” – This sheet walks you through the number formats that drive the number formatting text and icons listed on the Number menu on the Home ribbon (Figure 3 – “More Number Formats” Option). The table structure follows the same as the first sheet.
Each sheet contains a table for each number format category being placed under the microscope. The category table consists of the following three columns:
- Column 1 (value): Starting below the header, each row contains a numeric or text value which is formatted by the number format appearing in the default list
- Column 2 (number format): Highlighted in yellow. This cell displays the actual number format code “running” in the value column. The “active” format in Column 1 “appears” courtesy of the “ShowFormat” function which is entered in every cell in Column 2. The “=ShowFormat” function is the vehicle running the Get.Cell macro (as discussed earlier in this article). Together, they both create the cellular “magic”-allowing the active number format to appear on your spreadsheet
- Column 3 (format symbols): The cells in this column contain all the numeric formatting symbols (code) shown in numeric format column (Column 2). The symbols are hardcoded and will not update because these symbols are based on the original entries demo workbook
The “Home Ribbon Formats” worksheet follows the same structure as the “36 Default Custom Formats” tab but it allows you to investigate which number formats are driving these icons.
36 Default Custom Formats
The first 17 rows on this page define some of Excel’s number format symbols and how they’re used in any numeric code string. The 36 default formats that come with every new workbook are both listed and mapped to the correct Category starting on row 19.
Figure 10 – “36 Default Custom Formats” Worksheet
I’ve split the screen on row 20. This allows the user to scroll down through the 36 default custom formats while keeping both definition table and the custom format headers visible on row 19.
Note how these various format symbols are used in various combinations as they apply to an individual format (e.g. 0. #, m, h. etc.).
The “36 Default Custom Formats” sheet (as discussed previously) are segregated into small, individual tables that list the custom number categories (and the their numeric formats) in the order in which they appear in the Format Cells dialog box (the snapshot picture in the top right-hand corner of the sheet).
Each of the Category tables follows this format (as previously outlined above)
- The first column contains a sample numeric or text value
- The second column contains a custom function that allows you to see the cell formatting applied to the first column in the cell. This way you don’t need to go to the custom formatting dialog box to view the formatting. If you change the number or formatting in the first column, go to the cell in the second column and press enter to update the formatting. To find out more about this macro see the Get.Cell Excel 4 Macro explanation below.
- The third column displays the format symbols appearing in column 2. If you change the format in the column 1 new symbols might appear in column 2 that are not in column 3 because column 3 refers to the codes that are contained in the original format in column 1.
If a format uses one segment, for example, then this number format is applied to the other three. If the first 2 segments (i.e. positive and negative) both contain a numeric “code” then the positive number format (the 1st segment) will be “adopted” by the zero and text segments. The first segment to contain a format code is the format adopted by the omitted segments when multiple segments contain a “code”.
The Accounting formats, however, make use of all four segments (Figure 8 – Sample of Active Cell with Formatting Applied). Each segment, in this code type, contains some sort of numeric code and each number or text entry uses the code applied to its format segment, it can’t “clone” another segments format.
The Get.Cell Excel 4 Macro
The demo file with this article uses the Get.Cell macro (the macro code driving the “ShowFormat” function) to display the underlying spreadsheet number format. The Get.Cell macro displays a number format in a cell, so we don’t have to launch the Format Cells dialog box to view the current cell format.
The Get.Cell macro is a carryover from earlier versions of Excel . Excel 4 macros were the first macros used but replaced in later versions by VBA (Visual Basic for Applications). They are still available in Excel but not as widely used.
A drawback to Excel 4 macros is that they must be created in the Name Manager. Another downside is that Get.Cell doesn’t dynamically update unless the worksheet is recalculated (e.g. updating a value, adding another calculation, etc.) Changing a number format doesn’t trigger worksheet recalculation but changing the cell value after number format is changed will cause Get.Cell to update.
All macro files, like this one, must be saved using either the “xlsb” (Binary) or “xlsm” (macro enabled) file extension for macros to run.
Exploring how this macro works isn’t part of this article but here are a few links to articles and videos explaining how the Get.Cell macro works for those who wish to learn more.
Try Creating Your Own Custom Formats
As mentioned earlier in this article, the accompanying Excel file allows you to see how the same number can be formatted differently by applying different number formats.
You can also experiment by creating your own formats.
If you do this, observe how different symbol combinations affect the values appearance in the “bird’s eye view”.
When you do create a new format most often you’ll have to update the Get.Cell macro manually at times. This caveat was mentioned earlier in the article in the The Get.Cell Excel 4 Macro section.
I’ve referred to the ShowFormat function and how it is used by the Get.Cell macro but I haven’t walked you through you how it is created in the Name Manager.
The “ShowFormat” formula in a Tables second column must be created in the Name Manger, as just mentioned. I named the function “ShowFormat” on the “fly” but I could have called it something else as well.
The syntax is:
=Get.Cell(format type (number), formatted cell(column/row)or
=Get.Cell(7, A1) will show the number format of cell A1The Get.Cell macro can display other cell attributes (e.g Font Size, Border type, alignment, font color, number format, etc). There are over 30 different attributes available in this function.
For example, Get.Cell can display the font size and font name. Whereas 7 is the argument for number format, 18 is the font size argument & the argument number for the font name is 19. You should find a list of all the available cell attributes in one of the articles or videos I have provided in the Get.Cell section.
Please refer Get.Cell links videos and articles to learn more.
Figure 11 – The ShowFormat and Get.Cells function created in the Name Manager
Of course, you can create your own VBA macro to display a cell format using the NumberFormat function but Get.Cell it is a pre-programmed macro and ends up using less memory resources than the typical VBA macro you would create.
By working with the file, I hope that you can get a good sense of what custom number formatting is and how it can be used. The following articles will go into each format category in greater detail and hopefully provide you with possible uses that you can apply to your everyday work.
Practice Workbook
Feel free to Download the Workbook HERE.
Thank you Michael for sharing.
Michael is a Senior Financial and Commissions Analyst. He has been in this role since 2000.
Michael has been using Excel since 1997 (Excel 5) and has spent the last several years studying the finer points of the application (this is where the spider web analogy came into play).
I first met Michael as a student in one of my courses. What I found impressive is that when he sets out to learn something, he learns from as many resources as he can find. Even if he thinks he knows the content he still jumps in and discovers functionalities he didn’t know before. He refers to these as “golden nuggets”. A phrase I’ve taken over from him.
Michael also loves creating music. Check out his account on SoundCloud.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
