Custom Formatting
(Custom Formatting) is of numerous types; changing fonts, adding colors, shading, borders and so on, all this comes under formatting. However there is one more type of formatting that is exclusive to Microsoft Excel only and that is number formatting. While more about number formatting is explained in the upcoming headings, it simply refers to changing the appearance of the numbers in Formatting
This chapter explains in detail the concept of number formatting. It covers the categories of number formatting, different methods of formatting numbers and some handy shortcuts as well.
What Is Number Formatting?
As just mentioned, Number Formatting is the process of modifying the appearance of numeric values. By appearance we mean comma separators, decimal points, percentage sign, and currency symbols and so on. The purpose of all this is to make the values more understandable.
Number formatting just changes the look of the cells. It does not affect the actual underlying values.
When you enter any numeric values in Excel, they are unformatted by default. They represent nothing but a simple string of numbers. There are countless ways in which you can format them, as explained in the upcoming section.
Get To Know the Main Formatting Categories
The in numerous ways of Number Formatting are classified into several categories, as briefed below.
General: Every numeric value that you enter in a cell carries a general format by default. Normally, it refers to the plain string of numerals. However if the number is too large, the general format is automatically converted into the scientific notation.
Number: This category of format is mostly used to display precise values and decimal points. The Number Format also contains the option to add comma to separate thousands. Another very interesting thing about this format is its red coloring of negative values. In other words, if you enter any negative values or values enclosed in parentheses and then apply number formatting to it, all the negative values will turn red.
Currency: As the name suggests, this format is used to display monetary values. Just like the number format, this one also contains the decimal, thousand separator and negative value-red color option.
Accounting: It is similar to the currency format. The only difference between the two is that the Accounting format lines up the currency symbols and decimal points in a column.
Date: This format turns the underlying values into the default date format. The best thing about this format is that it allows you to select your native date format; as shown in Figure 1.1
Figure 1.1 - Selecting the Location and Regional Date Format
Time: This format is used to represent time values. Just like the Date format, this one offers options to select your country based recognized date format
Fraction: It allows you to select from nine different fraction based formats.
Percentage: If this format is applied before entering a value, it will put a percentage sign in front of any value you enter in the selected cells. It also contains the option to determine decimal places.
It is highly recommended that you apply the percentage formatting before entering the values. If done afterwards, it will multiply all the underlying values with 100 and then put a percentage sign with them. For example, if you enter 80 in a cell and then apply Percentage formatting to it, it will change the value to 8000%. On the other hand, if you would have changed the formatting beforehand, 80 would have become 80% and nothing more!
Scientific: Applying this format will change the value into scientific notations. For example if you enter 500,000 and then apply this formatting, it will convert the value into exponential form (5.00E+0.5)
Text: This format is used to display the numeric values as text. Excel’s numeric values are precise up to only 15 digits. If a value contains more digits, the numerals after the 15th digit are replaced by zeros. So if you want to enter a numeric value containing more than 15 digits that need to stay as it is, apply Text formatting to the appropriate cell and then enter the value in it.
This format is mostly used to enter credit card numbers and other such values.
Special: This category contains four additional formats, including Zip Code, Zip Code +4, Phone Number, and Social Security Number.
The four formats are only available for some particular locations. If it does not appear in your location, just change the location to English (United States) and all the four formats will appear in the dialog box, as shown in Figure 1.2
Figure 1.2 - Four Types of Special Formats
Custom: This option allows you to create your own number formats that are not available in any other category.
When a cell is in edit mode, it displays the value in the unformatted state. Same is with formula bar. It shows the value in is unformatted form. However this rule does not apply to date, time and percentage format. Once you apply any of the three mentioned formats, the resulting value will appear this way in the edit mode as well as the formula bar.
Different Ways of Number Formatting
Now that you know the different types of formatting that you can apply on numeric values, let’s see how you can actually perform Number Formatting in Excel.
Make sure you select a cell or range of cells before applying any type of formatting to it. The formatting that you apply works on the selected cell(s) only. It is not applied on all the cells in the worksheet, unless you select the entire worksheet and then apply the desired formatting style.
Excel’s Smart Auto Number Formatting
There are several ways of doing number formatting, the easiest one of which is by using Excel’s built-in Auto Number Formatting feature. When you enter any value, Excel tries to identify its nature and formats accordingly. For example, if you enter 15.8 and then hit the % key on your keyboard, Excel will automatically change it into percentage formatting. Likewise if you precede a value with a dollar sign ($), Excel will convert it into the currency format and add commas and decimal points to it.
If you are entering the numeric values and yet your values are not automatically turning into the percentage format, select Home > Options > Advanced. Locate the checkbox labeled as Enable Automatic Percent Entry and put a tick mark in it.
Handy Shortcuts for Number Formatting
Shortcuts always make ones work easy and fun. Like every other feature, there are number formatting keyboard shortcuts as well.
The most commonly used shortcuts for Number Formatting are as follows.
|
Keyboard
Shortcut |
Description |
|
Ctrl ⊕ Shift ⊕ ~ |
Apply the
General Format |
|
Ctrl ⊕ Shift ⊕ $ |
Apply the
Currency Format with two decimal places |
|
Ctrl ⊕ Shift ⊕ % |
Apply the
Percentage Format |
|
Ctrl ⊕ Shift ⊕ ! |
Add two decimal
places, commas and minus sign (-) for negative values |
Table 1.1 - Common Keyboard Shortcuts for Number Formatting
However these are not the only ones.
Number Formatting Via Ribbon
The Home Tab in the Ribbon also contains the frequently used number formatting commands. You will find them in the Number group of the Home tab, as shown in the image below.
This drop-down list contains 11 number formats. However, you already know these all are not the type of formats available in Ms-Excel. So if you could not find your required number format here, click More Number Formats located at the bottom of this drop- drown list.
Just beneath this drop-down list, there are icons for the five most commonly used number formats, as shown in Figure 1.4
Starting from the left in the above image, the icons are for currency, percentage, thousand separator, increase decimal and decrease decimal. If you want to apply any of the mentioned formats, just select the underlying cell(s) and click the respective icon. You can apply formatting before as well as after entering the values. However the former is highly recommended in case of percentage format.
Exploring the Comprehensive Format Cells Dialog Box
The drop-down list in the ribbon and the shortcuts do not contain all the Number Formatting options Excel has to offer. The Format Cells dialog box is right place for it.
4 ways to pop-up the Format Cells Dialog box, as detailed below.
- Right click the selected cell(s) and then select Format Cells.
- Press Ctrl + 1 on your keyboard
- Click the small downward diagonal arrow located at the bottom right corner of the Number group in the Home tab.
- Open the Number Format drop-down list in the Number group in the Home tab, then click More Number Formats located at the bottom of the list.
Make sure before opening the Format Cells dialog box through any of the ways first you have to select the range of cells or cell in which you want to apply the formatting.
In the dialog box, on the left hand side all the formatting categories are listed. Click a category to open its sub-options. At the bottom of the dialog box is the brief description of the format category that you select.
The value beneath Sample shows how your selected value will look like after applying the particular formatting. You can scroll up and down and check different formats till you find the one you are looking for. Click OK once you are done selecting the desired format.
Custom Number Formatting
The number formats of Excel are very comprehensive and elaborate. You will find a built- in format of almost every type you need. These are also pretty easy to use. So try to find the one you are looking for in the already available ones. In case if you are unable to find one, you can always create your own format.
Custom number formats are saved only in the workbook in which you create them. To copy your custom formats to any other workbook, copy the cell carrying the particular custom format and then paste it to the appropriate cell in the other workbook.
Learning the Custom Format Language
Custom number formatting is a bit complex. It has its own set of codes and symbols that you need to understand before you try making a format of your own.
0(zero)
Known as the Digit Placeholder, this symbol makes sure that the specified numbers of digits are displayed in the underlying value. Now there could be either of the two scenarios.
- If a numeric value has lesser digits than the number of zeros specified in the format, then this digital placeholder displays the insignificant zeros. For example, if you create a format 0.00 and the value is 0.58, it will be displayed as 0.58. On the other hand if the format is 0.0000 and the value is still 0.58, then it will be displayed as 0.5800.
- Conversely, if a numeric value has more digits than the number of zeros specified in the format, it rounds of the value. For example if the format is 0.00 and the value is 0.587, it will be displayed as 0.59. Likewise if the format is 0.0 and the value is 0.587, then the value will be displayed as 0.6
In short, the purpose of the 0 digit placeholder is to equate the number of digits in a value to the number of zeros in the format.
?
Also known as the Digit Placeholder, this symbol hides insignificant values and displays only the main value. Moreover, this placeholder ensures that the values are aligned on the decimal points. For example, if there are three values (1.25, 1.2 and 1.229) and all three are formatted 0.???, then they will be aligned as 1.2, 1.25 and 1.229 on the worksheet.
You can use this digit placeholder for fractions that have varying numbers of digits. This symbol will not only eliminate the extra insignificant zeros in individual values but will also align them in terms of their decimal points.
#
This is another Digit Placeholder. It works almost like the 0 (zero) placeholder except that is does not display the insignificant zeros if the value has lesser digits than the number of # specified in the format. You can also put thousand separators in this symbol. For example, #,### means that Excel should put a comma after every third digit to the left side of the decimal point.
. (Decimal Point)
It works just like a normal decimal point. You can place it in your customized format to determine how many digits you want to the left and right of the decimal point.
%
Known as the percentage indicator, this symbol multiples the underlying value by 100 and place a % sign next to it.
, (comma)
The basic usage of this symbol is to place thousand separators in the numeric values. However you can also use comma to round a value. If you place one comma at the end of a format, it will round of the underlying value and will display it in thousands. Likewise, if you place two commas at the end of a format, it will round up the value to the nearest million.
$ - + ()space
Add any of these characters in your formula to display them in the value. The character will be placed at the same position where you entered it in your formula.
\
Just like the previous symbols, a backslash helps you to display characters in the value. For example, if a cell is formatted with ###\P, the value in the formatted cell will contain a P with it. The backslash is not displayed in the cell.
Do not use a backslash to display any of these characters: $ - + () space. Just put them as it is in the formula to make them appear in the pertinent cell.
“Text”
It works just like backslash except for the fact that it displays all the text within the quotation marks rather than just one character. For example if you want to display USD in a cell after a numeric value, you can format it with ###“USD”. You can also perform the same function with a backslash but in that case you would have to put a backslash before each character, for example, ###\U\S\D
/
This Fraction Format symbol is used to display a number in a fraction format.
Just like the decimal places precision, you can also determine the accuracy of a fraction value. For example, if 0.415 is formatted with # ?/?, it will be displayed as 2/5. However if you format the same value with #???/??? then it will be displayed as 83/200
[color]
It displays the characters in the specified color.
[Color n]
It displays the data entries in n color where n is the number of color in Excel’s color palette. It could be anywhere in between 0 - 56.
* (asterisk)
This is the repetition indicator. It repeats the next character in the format as many times as required to fill the column width. You can put only one asterisk per format.
_ (underscore)
This symbol is used for alignment purposes. It puts space equal to the width of the next character in the format.
@
This is a text placeholder. If a cell contains a text value and is formatted with this symbol, it will display the text in the position where @ stands in the format. For example, if a cell contains Paul and is formatted with "My Name is”@, it displays as My Name is Paul in the underlying cell. Text placeholder is very helpful in cases when you need to display the same text in every cell with just one word difference. You can put the same text in the format and the different words in the formatted cells.
E- E+ e- e+ (Exponents)
If a format contains either of these symbols, it will display the value in the scientific notation format. The number of digit placeholders at the right side of E or e determines the minimum digits in the notation. E- and e- displays a minus sign before negative exponential values, whereas E+ and e+ recognizes the nature of the exponent and displays a plus or minus accordingly.
Codes- Creating Date and Time Custom Formats
You can create custom formats for date and time values as well. But for that you need to learn a different set of codes.
|
D |
Display the day number without leading zeros (1-31) |
|
Dd |
Display the day number with leading zeros (01-31) |
|
Ddd |
Display the day of the week as an abbreviation (Sun-Sat) |
|
Dddd |
Display the full name of the day (Sunday - Saturday) |
|
M |
Display the month number without leading zeros (1-31) |
|
Mm |
Display the month number with leading zeros (01-31) |
|
Mmm |
Display the name of the month in abbreviated form (Jan -Dec) |
|
Mmmm |
Display the full name of the month (January - December) |
|
Mmmmm |
Display the first letter of the month’s name (J - D) |
|
Yy |
Display the last two digits of the year (00 - 99) |
|
Yyyy |
Display the full four-digit year number (0000 - 9999) |
|
H |
Display the hour without leading zeros (0-23) |
|
Hh |
Display the hour with leading zeros (00 - 23) |
|
M |
Display the minute without leading zeros (0 - 59) |
|
Mm |
Display the minute with leading zeros (00 - 59) |
|
S |
Display second without leading zeros (0 - 59) |
|
Ss |
Display second with leading zeros (00 - 59) |
|
s.0 |
Display second and tenths of a second without leading zero |
|
s.00 |
Display second and hundredths of a second without leading zero |
|
ss.0 |
Display second and tenths of a second with leading zero |
|
ss.00 |
Display second and hundredths of a second with leading zero |
|
[ ] |
Display the absolute elapsed time; that is, it display hours greater
than 24 and minutes or second greater then 60 |
|
AM/PM or am/pm |
Display time in AM/PM or am/pm respectively. If this is not specified
in the formula, Excel display the time using the 24-hour clock |
|
A/P or a/p |
Display time in A/P or a/p respectively |
Table 1.2 - Codes for Creating Date and Time Custom Formats
Creating Custom Format
Now that you know the language of custom formatting, let’s see how you can actually make your own number format. There are two ways of creating customer formats; you can do so by using the built-in number formats or using the 4-part custom format concept.
Using Existing Formats
The easiest way of creating a custom format is to build on the built-in Excel formats.
- 1. First, type a numeric value in an empty cell. For example, 0.259
- 2. Apply the appropriate number format on it. By appropriate we mean, the one that resembles the custom format you want to create. In this example, the fraction format is applied. This changed the value to 1/4.
- 3. Select this cell and then open the Format Cells dialog box.
- 4. Select the Custom category. Here you will see that format you selected is highlighted in the list of formats. The highlighted format will also appear in the Type text box located just right above the list of formats, as shown in Figure 1.6
- 5. Edit the format as desired, in the Type text box. You can use the custom format symbols explained in the previous section to modify the format. In this example, we added two more digits placeholders to the format. As you can see in Figure 1.7, putting placeholders increased the precision of the fraction value and now it appears as 125/861 in the underlying cell.
Figure 1.7 - Creating a Custom Format Using an Available Format
Click Ok to confirm. This won’t affect the original format. The format that you create will appear at the bottom of the list.
To delete a custom format: Select the format and hit the Delete button located at the bottom right outside the formats list. Remember,
4-Part Custom Format
If you are unable to find a resembling number format to customize, you can create one of your own. A custom format can have up to 4 sub-formats. In other words, you can make four different formats and then combine them in all one single format.
The 4-part custom format is meant to differentiate four different types of values; positive, negative, zero and text values. You can specify as many as four formats; just make sure to separate the sub-formats with semicolons.
Here are a few points that you need to remember while creating a 4-part custom format.
- If you specify any one format, Excel will apply that to all the values within the selected range.
- If your custom format contains two parts, the first applies to the positive values and zeros, and the second to the negative values.
- If you specify three formats within your custom format, the first applies to the positive values, the second to the negative values and the third to the zeros.
- If you custom format contains four parts, it is applied and interpreted in the following sequence: Positive values; Negative values; Zero values; Text value. In other words, the first format applies to the positive values, the second to the negative values and so on.
If your custom format contains less than four parts, it will have no effect on the text values.
Table 1.3 Some few examples of custom formatting. The first column contains a few different types of values, the second column contains a custom format and third one shows how the original values will be displayed in the cell after applied the respective custom formats.
|
Original Cell Entry |
Custom Format |
Formatted Value |
|
5500 |
#,##0 “USD” |
5,500 USD |
|
987.569 |
_($(#,##0.00) |
$(987.57) |
|
9876543215 |
#,##0 |
9,876,543,215 |
|
856 |
“Extension:“_### |
Extension: 856 |
|
8009985436 |
(###) ###-#### |
(800) 998-5436 |
|
42163956 |
####-##-### |
421-63-956 |
|
3/31/2014 |
mmmm-d, yyyy |
March-31, 2014 |
Table 1.3 - Examples of Custom Formatting
Applications of Custom Number Format
Custom format is a lot more than just a few examples illustrated in Table 1.3 While you need to practice making different formats to get the hang of codes for custom formatting, here some few more uses along with custom formatting examples.
Creating a Null Format
You can create a null format to hide the values in a worksheet. All you need to do is type a “;” semicolon for that part of the custom format.
Be sure, just like other custom format, the null format is also simplified in the below order:
Positive(+ve) value format; Negative(-ve) value format; Zeros (0) format; Text value format
For example
If you want to hide (-ve) negative values, (0) zeros and text from a selected range of cells, select the relevant range and format it with (###;;;)
Three semicolons mean that in the selected range it hides the last three types of values. Likewise, if you type two semicolons “;;” in the starting zeros (;;0) and text values(;;”Nos”), it will hide the (+ve) positive and (-ve) negative values within the selected range of cells.
To hide all the values, format with three semicoloun (;;;)
The null format hides only the values of cells. The entries are still visible in formula bar.
Colorful Custom Format
You can create a custom format to give different colors to different types of values.
For example,
if you want to apply a red colored mobile/telephone number, format to a string of numeric values, modify the cell with the following custom format:
[Red](###) ###-####
Likewise, if you have a full mixed-up values spreadsheet and need to differentiate (+ve) positive, (-ve) negative, (0) zero and text values, select the entire worksheet and change the following custom format box:
[Red]; [Green]; [Blue]; [Yellow]
The above custom format displays all the (+ve) positive values in red color, (-ve) negative values in green color, (0) zeros in blue color and text values in yellow color.
You do not necessarily have to use the same colors in your custom format. You can select from the following colors:
● Yellow
● Black
● Cyan
● Magenta
● Green
● Blue
● Red
● White.
You can also specify the number of the color [1-56].
If you specify a color or color number that is not in Excel’s built-in range of colors, it produces the specified color by mixing up some built-in shades. Such colors are called dithered. These colors are good for shading the cells but done work very well for coloring the values and lines.
Creating Custom Formats To Scale Values
You can create custom formats to scale large values. For example, suppose you are working with large numbers such as 1,500,000,000. If you want, you can display these numbers in thousands (1,500 in this example). The underlying value would still remain 1,500,000,000 and the same would be used in calculations. The formatting only makes it look shorter on the worksheet, hence taking up less of the column width.
Here are a few custom formats to scale down numeric values.
#,###,
It removes from the display, the last three digits to the left of the decimal place. For example, 987654 displayed as 987
#,###.00,
It removes from the display, the last three digits to the left of the decimal place and rounded to two decimal places. For example, 987654 displayed as 987.65
#,###,,
The above format displays the value in millions with no decimal places. For example, 987654321 displayed as 987.
#,###.00,,
The above format displays the value in millions with no decimal places and rounded to two decimal places. For example, 987654321 displayed as 987.65
#,###,,M
This format also displays the value in millions however it also puts an M with the value. For example, 987654321 displayed as 987M
0”.”00
This format removes from the display, the last two digits to the left of the decimal place and rounded to two decimal places. For example, 9876 displayed as 98.76.
Just like you can create custom formats to scale down values, you can do the same to scale up as well. Here are a few custom formats to scale up numeric values.
#”,000”
This format displays three more zeros with the value. For example, 9 displayed as 9,000
#”,000,000”
This format displays six more zeros with the value. For example, 9 displayed as 9,000,000
