Excel Custom Formats
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.
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 in 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” 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 total inherits the format. If you want 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 a number. On the other hand the # will display a blank if the user does not type a digit. Based on this rule, this format allow a user to type .65 in a cell and Excel will display 0.65.
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 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, a PO never use more than four numeric digits.
In a blank column type PO# in row 1 and select 5 cells under. While the five cells are selected press Ctrl+1 and select Custom. Replace the format General format with the format: “PO-“0000. (full stop is punctuation). In the rows below the heading PO# type any four numeric digits. Ensure that one PO start with 2 zeros and another one ends with a zero. 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 of 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 digit 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?
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:
In the first example negative amounts will be preceded by a minus and the value zero will be displayed using: nil.
Let us briefly discuss why we put a 0 before the decimal point. Remember that the formatting code 0 will display zero if a zero is typed or there is a 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 the examples below:
|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. Notice in the examples above that some are rounded to the next decimal.
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 8/10/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 type 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 for years. Note that you can use these codes in any order you want.
Suppose today is Monday the 7/09/2020, the table below show the result with various number of d and m formatting codes:
|Date Format codes||Display|
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 may also use the format: h:mm AM/PM. This will display the time 8:30 as 8:30 AM and 20:30 as 8:30 PM.
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 of that day. Here the decimal is 39.58% of a day).
A time on a clock vs a period of time
The subtitle above may seem strange but viewing the difference between a time on a clock and a period of time in Excel will help understand how to sum hour minutes entries. We want Excel to use a time on a clock as 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). However when the sum of a column of h:mm goes beyond 24 hours you could be puzzled! A good friend of mine actually told me 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 you see: Sum: 25:45:00 which is correct as shown below. Fortunately, Excel provides a way in the Format Cells dialog box you to do the same.
Select the total hours (1:45) and 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 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.
Warning: While the square brackets around the hour format code which 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% 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!
Once you know how to deal with it you will be fine. 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.