Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Custom Number Formats

| | Information Helpful? Why Not Donate.

Also see Custom Formats With Symbols | Convert a Numeric Value into English Words | Convert a Currency or Value into English Words

Got any Excel Questions? Free Excel Help

It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section.

It is also very important to understand that the formatting of a cells value does not effect its underlying true value. To show this we can type any number into cell A1, then go to Format>Cells-Number-Custom and, using any format as a Starting point, type in "Hello", with the quotation marks, now click OK. Now, while the cell displays the word Hello, it's true value can be seen by selecting the cell and looking in the Formula bar, or by pressing F2. If you were to reference this cell in a formula, e.g. =A1+20 the result cell would also take on the custom format. If we were to reference cell A1 and many other cells that have any standard Excel format, e.g. =SUM(A1:A10) our result cell would still take on the custom format of cell A1. This is an educated guess by Excel that you want the result cell formatted the same as the referenced cell(s). If the referenced cells contain more than one type of format, any custom format will take precedence. So, you must always remember that Excel uses a cells true value and not it's displayed value. This can catch out the unwary if you are calculating cells that are formatted for no, or few, decimal places. For example, enter 1.4 in A1 and 1.4 in A2, format both these cells to show zero decimal places and then place =A1+A2 and the result is 3. Excel does have an option called Precision as displayed, found under Tools>Options-Calculation, but you should be aware that this option will permanently change stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. In other words, once it's been checked and given the OK, there is no turning back.

The default format for any cell is "General". If we enter a number into a cell, Excel will often guess the number format that is most appropriate. For example, if you type in 10%, Excel will format the cell as a percentage. Most often Excel gets this correct, but sometimes we need to change this. One point I will make on this is, when using Format>Cells, resist the temptation of forcing a left, right or centre horizontal format! By default numbers are right aligned and text left aligned. If you leave this alone, you can tell at a glance whether a cell is text or numeric, as in the case of my earlier example where cell A1 appears to hold text, when in fact, it holds a number.

Ok, getting back to the all important Sections that a formatted cell contains. Within these Sections we are able to use Formatting Codes. It is these codes that force Excel to make our data appear how we would like. Let's use a simple Example. Suppose you would like any negative number to appear inside parenthesis, and all numbers, positive, negative or zero, to show two decimal places. The Custom Format we could use is: 0.00_ ;(-0.00). If you also wanted negatives to be red, use: 0.00_ ;[Red](-0.00) Note the use of the square brackets in the Section for negative numbers. This is the Formatting Code that tells Excel to make the number red.

There are many different Formatting Codes that can be used within Sections of a Custom Format. The tables below show these. The Table is

Formatting Codes