Excel Custom Formats
Introduction
The Custom Format feature, as its name implies, is all about formatting. It also means that you can create your own formats. One thing we need to remember before we start discussing this powerful (and fun) feature is that custom formats will always involve some numbers. So, a custom format cannot format text with italic or bold (Note the first tab of the Format Cells dialog box: Number)
The easiest way to open the Format Cells dialog box in Excel is to press Ctrl+1 (not the 1 in the numeric pad). The Category list shows the standards formats this command carries. Notice the last entry in that list: Custom. Select this category to create you’re own custom formats.
Let’s start with a cool example
Decades ago someone asked me if it was possible to add the suffix “Km” to a number. He travelled a lot for work and wanted to use the format 1,200 Km to keep track of the mileage using Excel. When he entered a value this way, the entry became text (aligned on the left) and there was no way to sum the distances travelled. Let see the solution to his conundrum.
Follow along in a new workbook
Type Mileage in cell A1 and five numbers ranging from 700 to 1150 in cell A2:A6. Select the five amounts and press Ctrl+1 to open the Format Cells dialog box and select the category Custom. In the Type text box replace the entry General and type the format code: #,##0″ Km” (note the space before the K) as shown below.
Hit Enter or click OK. Each value is formatted using the suffix Km preceded with a space. This is caused by the space in front of the Km. Numbers from one thousand and above have a comma as the thousand separator! Select the cell immediately below the last value and double-click AutoSUM to sum the column. The sum inherits the format. If you prefer to display one decimal, use the format: #,##0.0″ Km”.
Why is there a 0 before the decimal point?
Great question. Number formats use the # and 0 to determine if a 0 will be displayed or not. So the format code 0 display a 0 if the user do not type anything or type a 0. On the other hand the # will display a blank if the user does not type a digit or types a 0. So, this format allow a user to type .65 in a cell and Excel will display 0.65.
If the user types the number 1,000 or greater, a comma will appear as the thousand separator.
What is the difference between using 0 and # in a number format?
The pound sign and the zero affect how the digits zero and blanks are displayed. Use the # character for non-significant zeros and 0 for zeros that you want to be visible. The formatting code # will display digits other than zero and nothing if it is zero. Let see a few example using the value: 123.56.
| Format | Result |
|---|---|
| #,##0 | 123 |
| 0,000 | 0,123 |
| 0.0 | 123.5 |
| 00000.0000 | 00123.5000 |
| ###0000000 | 0000123 |
Format for purchase orders
Suppose you often need to enter purchase order numbers using a format like PO-1234. It would be tedious to type PO- for each purchase order you need to enter. In our example, assume that a PO never use more than four numeric digits.
In a blank column type PO# in row 1 and select 5 cells below. Press Ctrl+1 and select Custom. Replace the format General format with the format: “PO-“0000. (full stop here is punctuation). In the five rows below type any four numeric digits. Ensure that one PO start with 2 zeros (for example 0062). Cool isn’t it?
The wonderful news is, since we are creating a format, you can use the Format Painter to apply it to another location in your worksheet, another worksheet even another workbook!
The lesson we learn with the examples above is that if any characters in a format is constant then you can make these characters part of the format by surrounding them with double quotation marks.
Format for Mobile numbers
In Australia all mobile numbers start with the two digits: 04. Some add the prefix M: with a space after the colon. Let’s simplify the task of entering many dozens of mobile numbers every day. If the standard for a mobile number is M: 0448 567 234, use the pattern: “M: 04″00 000 000.
Increase the width of a column by about 50%. Type the title Mobile in row 1 and select a few cells below. Apply the custom format “M: 04″00 000 000 to the selection. All you need to do now is to type the remaining 8 digits (without spaces) of the phone number and the format will do the rest. For example typing 12345678 would display M: 0412 345 678. Isn’t that great?
Formatting amounts
Let’s look at formatting numbers (amounts or quantities). A typical amount format can use three sections separated by a semi-colon: Positive value; Negative value; zero. If your worksheet will contain positive amounts or zero only, your need to use two semi-colon between the positive and the zero format code. Below are a few examples:
#,##0.00;-#,##0.00;”nil” (use for positive, negative and zero amounts)
#,##0.00;;”nil” (use for positive and zero amounts)
In the first example negative amounts will be preceded by a hyphen and the value zero will be displayed using: nil.
Let us briefly review why we put a 0 before the decimal point. Remember that the formatting code 0 will display zero if a zero is typed or if it is blank. If there is a balance 50 cents. How would you want to display that value? .50 or 0.50? To ensure that a decimal of a dollar always start with a zero and two decimals, complete the formatting code using: 0.00. So, a typical format for an amount/quantity with possible decimals would be: #,##0.00.
Display a large number as a decimal of its period
It is common to display amounts in millions as a decimal of a million. For example, instead of displaying a yearly revenue like 1,543,567 it may be simpler to use 1.5 M or 1.54 Millions. You can easily do this with a custom format. Look at some examples below:
| Amount | Format | Result |
|---|---|---|
| 53,629.13 (Thousands) | 0.0,” K” | 53.6 K |
| 7,428,941 (Millions) | 0.00,,” M” | 7.43 M |
| 2,586,452,812 (Billions) | 0.0,,,” B” | 2.6 B |
| 23,764 grams | 0.0,” Kg” | 23.8 Kg |
The rule is simple. Always start with 0.0 (add decimals as required) then type the number of commas corresponding to the period of the number (Thousand = 1, Million = 2, Billion = 3). Finally, type the corresponding abbreviation for the order. Note that some are rounded to the next decimal.
Formatting dates
You likely know that a date in Excel is, in reality, a formatted number. To convince yourself select a blank cell and press Ctrl+; (today’s date) then Ctrl+Enter. The active cell now contains the current date.
Now let’s see the number behind that date. Press Ctrl+Shift+~ (the ~ is below the Esc key). This is the keyboard shortcut for Clear Number Format. The date became a number in the 40 thousands. Since any valid date in Excel is a whole number we can use the Format Cells dialog to format a date in many useful way. To redisplay the date press Ctrl+#.
Select a blank cell and press Ctrl+; and Ctrl+Enter. Press Ctrl+1 to open the Format Cells dialog box. The Date category is selected. If you scroll down in the Type list you will see other formats available for that date.
Select Custom from the Category list. On the right, the format d/mm/yyyy is selected. Note: The formatting code may be different depending on your country’s date format. For example in US it would appear as yyyy-mm-dd.
Understanding date formatting codes
Using the formatting codes d, m and y you can display dates in more interesting ways than what Excel offers by default. Lets discover what is hidden behind these three date format codes.
- Select a blank cell and type the date 7/09/2020 then press Ctrl+Enter.
- Double the width of the column containing the date.
- Press Ctrl+1 (ensure that the Number tab is selected).
- In the Category list, select Custom.
The Type textbox allows you to use various combinations of the three date formatting codes (d, m, y) to create other date formats. You can type up to four codes for days and months and two or four for years. Note that you can use these codes in any order you want.
The table below shows the result of using various number of d and m formatting codes:
| Date Format codes | Display |
|---|---|
| d | 7 |
| dd | 07 |
| ddd | Mon |
| dddd | Monday |
| m | 9 |
| mm | 09 |
| mmm | Sep |
| mmmm | September |
| yy or yyyy | 20 or 2020 |
The table below shows a few examples using the date 27-09-2020
| Format code examples | Display |
|---|---|
| dddd “the” dd-mmm-yyyy | Sunday the 27-Sep-2020 |
| mmmm yyyy | September 2020 |
| “Balance due on “ddd mmm dd yyyy | Balance due on Sun 27 Sep 2020 |
If you want to automatically add a date time stamp at the top or end of a monthly report you could use the =NOW() function in a cell and format that cell using the formatting codes: “Printed on:” ddd mmm-d-yyyy “at” h:mm. This would display for example: Printed on: Mon Sep 28 2020 at 15:30.
Note: For a time of the day you can use the codes (h, m, s) and the special suffix AM/PM. This way you can use the format: h:mm AM/PM to display the time 8:30 as 8:30 AM and 20:30 as 8:30 PM. If you prefer to see 14:15 instead of 2:15 PM omit the AM/PM suffix.
Summing Hours
Most of us would never think that there in a potential challenge (not a bug) with adding hours. In the previous section we reviewed that a date is stored as a whole number where day 1 is 1st January 1900. Hours in a day are thus stored as a decimal of a day. So, September-28-2020 9:30 AM is actually stored in Excel as: 44,102.39583 where the decimal is actually the time on that day (39.58% of a day).
A time on a clock vs a period of time
The subtitle above may seem strange at first but viewing the difference between a time on a clock and a period of time in Excel will help understand how to sum hour : minute entries.
We can use Excel to express a ‘time on a clock’ or a ‘period of time’. For example you would process the following two statements differently: “I will be there at 3:45.” and “I worked 3:45 hours on that project.”.
If you sum two cells containing the values 3:45 and 4:15 you will get: 8:00 (because 3:45 = 0.156 and 4:15 = 0.177 the total being 0.3333). And 0.3333 is essentially 1/3 of a day (8:00 hours). Now, when the sum of a column of h:mm goes beyond 24 hours something apparently odd happens! A good friend of mine actually told me that he recently faced this very situation!
In a worksheet enter the following period of times in a column: 5:45, 3:20, 4:45, 6:15, 5:40. Move below that column add all these period of times using AutoSum. Excel displays 1:45!
Amazingly, when you select the five cells (except for the Total) and look at the the far right of the Status Bar (shown below) you see: Sum: 25:45:00 which is correct. Fortunately, Excel provides a way in the Format Cells dialog box you to get the same result in your worksheet.
Select the total hours (1:45), launch the Format Cells dialog box and select the Custom category. In the Type list the format h:mm is selected producing the result discussed above.
Look further down in the Type list for the format: [h]:mm:ss. Immediately the dialog box displays 25:45:00. The answer you are after. Delete the :ss format code to see only [h]:mm and click OK.
You could also have type the format code [h]:mm yourself.
Important: While the square brackets around the hour format code allows to display a number of hours above 24, remember that this is the result of the format! If a cell formatted as shown above displays 38:45, the value in the cell is 1.61 (1 full day + 61% (14h 45m) of another day). Understanding this will greatly help is understanding the topic in the next section (trust me!)
Multiplying an hourly rate by a period of time
Armed with the understanding of how Excel stores hours discussed above, let’s see how to calculate an amount based on an hourly rate and the total hours worked.
Please reproduce the table on the right. Cell B4 contains the sum of the two cells above. In cell B7 multiply the total hours by the rate: B4*B6. Happy with the answer? How can someone work 15 hours 15 mins at $25 .00 / hr and get paid $15.89! Not fair you exclaim!
Remember that Excel stores hours as a decimal of a day. As an example, we want 4:30 (which is 0.1875 of a day) to be displayed as 4.5 hours. To scale up 0.1875 to 4.5 hours, multiply it by 24 (hours / day). So 0.1875 * 24 = 4.5 hours.
The formula for the total invoice above should be =B4*B6*24 and Excel returns the correct answer 381.25.
Format a mobile phone number
If you regularly need to enter customer’s mobile phone number in Excel you know how annoying this is. Here’s a example of an Australian mobile number you may need to type often: M: 0448 654 285
Since ‘M: 04’ is a constant we do not need to type it. Create the following format: “M: 04″00 000 000. All you will need to type in the future are the digits after the 04. In our example it would be 48654285. To format will provide the spaces.
How can we reuse our custom formats in other documents (Work in progress)
Now this is a hot question! I totally agree that once you have created a really cool custom format, you may need it in other workbooks. Microsoft didn’t make it easy to do that so it’s a bit tricky. Once you are okay with the rule, it works like a charm.
Conclusion
There are many more ways you can use Custom Formats to display numbers in Excel. I prepared a Workbook you can download containing over two dozens of cool custom formats you can learn from. Download the workbook containing the examples discussed above and much more.
Daniel from ComboProjects
