{"id":425,"date":"2008-09-16T13:32:31","date_gmt":"2008-09-16T12:32:31","guid":{"rendered":"http:\/\/simoncpage.co.uk\/blog\/?p=425"},"modified":"2008-10-19T14:40:34","modified_gmt":"2008-10-19T13:40:34","slug":"excel-custom-and-conditional-number-formatting","status":"publish","type":"post","link":"https:\/\/simoncpage.co.uk\/blog\/2008\/09\/excel-custom-and-conditional-number-formatting\/","title":{"rendered":"Excel Custom and Conditional Number Formatting"},"content":{"rendered":"<p>Finding Excel&#8217;s custom number formatting confusing? Below is a good reference\u00a0for some of the more popular examples\u00a0of formatting codes\u00a0for numbers and text\u00a0data\u00a0in Excel.<\/p>\n<h2>Sample Custom &amp; Conditional\u00a0Number Formats<\/h2>\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<colgroup span=\"1\">\n<col span=\"1\" width=\"224\"><\/col>\n<\/colgroup>\n<colgroup span=\"1\">\n<col span=\"1\" width=\"242\"><\/col>\n<\/colgroup>\n<colgroup span=\"1\">\n<col span=\"2\" width=\"139\"><\/col>\n<\/colgroup>\n<tbody>\n<tr height=\"30\">\n<td height=\"30\"><strong>Format Code<\/strong><\/td>\n<td><strong>Description<\/strong><\/td>\n<td width=\"139\"><strong>Data with General Format<\/strong><\/td>\n<td width=\"139\"><strong>Data with Custom Number Format<\/strong><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">00000<\/td>\n<td>Always displays 5 digits. Pads with leading<\/td>\n<td>983<\/td>\n<td>00983<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>zeros if the number contains fewer than 5 digits.<\/td>\n<td>23589<\/td>\n<td>23589<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This custom format is very useful when you<\/td>\n<td>9856<\/td>\n<td>09856<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>work with zip codes.<\/td>\n<td>85632<\/td>\n<td>85632<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td width=\"139\"><\/td>\n<td width=\"139\"><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0;-0;;@<\/td>\n<td>Suppresses zeros in cells.<\/td>\n<td>98<\/td>\n<td>98<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format displays positive values (0) and<\/td>\n<td>0<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>negative values (-0), hides zero values, and<\/td>\n<td>-9<\/td>\n<td>-9<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>displays text (@).<\/td>\n<td>0<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>hello<\/td>\n<td>hello<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">;;;@<\/td>\n<td>Suppresses numbers in cells.<\/td>\n<td>98<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format hides positive, negative, and zero<\/td>\n<td>0<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>values, and displays only text (@).<\/td>\n<td>-9<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>0<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>hello<\/td>\n<td>hello<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">[Black]General<\/td>\n<td>Suppresses errors in cells.<\/td>\n<td>0.333333333<\/td>\n<td>0.333333333<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format hides or displays positive,<\/td>\n<td>#DIV\/0!<\/td>\n<td>#DIV\/0!<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>negative, zero, and text values as black<\/td>\n<td>#N\/A<\/td>\n<td>#N\/A<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>characters in the General format. The cells<\/td>\n<td>1.666666667<\/td>\n<td>1.666666667<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The other\u00a0values appear because the number format<\/td>\n<td>0.5<\/td>\n<td>0.5<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>color overrides the font color for the cell.<\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">#.???<\/td>\n<td>Lines numbers up with the decimal.<\/td>\n<td>3.256<\/td>\n<td>3.256<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The ? code leaves a space for<\/td>\n<td>5.2<\/td>\n<td>5.2<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>insignificant zeros but does not<\/td>\n<td>9.652<\/td>\n<td>9.652<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>display them.<\/td>\n<td>98.2568<\/td>\n<td>98.257<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">#,<\/td>\n<td>Displays numbers in thousands.<\/td>\n<td>4058.34<\/td>\n<td>4<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The comma is the thousands<\/td>\n<td>52865<\/td>\n<td>53<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>placeholder. If you wish to display<\/td>\n<td>236<\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>the numbers in millions, use the<\/td>\n<td>5502235623<\/td>\n<td>5502236<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>format #,, instead.<\/td>\n<td>999555<\/td>\n<td>1000<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">#,###,, &#8220;M&#8221;<\/td>\n<td>Displays numbers in millions.<\/td>\n<td>32654236<\/td>\n<td>33 M<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The comma is the thousands<\/td>\n<td>4563258963<\/td>\n<td>4,563 M<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>placeholder. The letter &#8220;M&#8221; is displayed<\/td>\n<td>1.2357E+12<\/td>\n<td>1,235,699 M<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>after each number.<\/td>\n<td>22333666<\/td>\n<td>22 M<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>12345678<\/td>\n<td>12 M<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0.00,,<\/td>\n<td>Represents numbers in millions.<\/td>\n<td>1000000<\/td>\n<td>1.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This number format displays numbers<\/td>\n<td>12000000<\/td>\n<td>12.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>so that 1 represents one million.<\/td>\n<td>12200000<\/td>\n<td>12.20<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>120000<\/td>\n<td>0.12<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0;[Red]&#8221;Error!&#8221;;0;[Red]&#8221;Error!&#8221;<\/td>\n<td>Displays &#8220;Error!&#8221; in red for negative numbers<\/td>\n<td>10<\/td>\n<td>10<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>and text.<\/td>\n<td>hello<\/td>\n<td><span style=\"color: #ff0000;\">Error!<\/span><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format may be useful to alert users<\/td>\n<td>-10<\/td>\n<td><span style=\"color: #ff0000;\">Error!<\/span><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>that they entered invalid text in a cell.<\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0.0\u00b0<\/td>\n<td>Displays numbers with the degree symbol.<\/td>\n<td>32.63<\/td>\n<td>32.6\u00b0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The degree symbol uses the character<\/td>\n<td>63.258<\/td>\n<td>63.3\u00b0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>map code ALT+0176.<\/td>\n<td>96.75<\/td>\n<td>96.8\u00b0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>-5.36<\/td>\n<td>-5.4\u00b0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0.00 \u00a3<\/td>\n<td>Displays numbers with the British pounds<\/td>\n<td>100<\/td>\n<td>100.00 \u00a3<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>symbol.<\/td>\n<td>67.63<\/td>\n<td>67.63 \u00a3<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The pound symbol uses the character<\/td>\n<td>0<\/td>\n<td>0.00 \u00a3<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>map code ALT+0163.<\/td>\n<td>9.63<\/td>\n<td>9.63 \u00a3<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0.0_-;0.0-<\/td>\n<td>Displays the negative sign on the right side<\/td>\n<td>-5<\/td>\n<td>5.0-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>of the number.<\/td>\n<td>-40.3<\/td>\n<td>40.3-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format also pads space at the right<\/td>\n<td>50<\/td>\n<td>50.0<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>of a postive number so that the decimals line up.<\/td>\n<td>-10.99<\/td>\n<td>11.0-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">@<\/td>\n<td>Displays 5 spaces and then the text to<\/td>\n<td>this<\/td>\n<td>this<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>give the appearance of a tab (or indent).<\/td>\n<td>is<\/td>\n<td>is<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">(5 spaces, then @)<\/td>\n<td><\/td>\n<td>a<\/td>\n<td>a<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>test<\/td>\n<td>test<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">@*-<\/td>\n<td>Shows text leaders.<\/td>\n<td>Apples<\/td>\n<td>Apples &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>In a number format, the asterisk (*)<\/td>\n<td>Oranges<\/td>\n<td>Oranges &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>causes Microsoft Excel to repeat the next<\/td>\n<td>Bananas<\/td>\n<td>Bananas &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>character until the width of the column<\/td>\n<td>Pears<\/td>\n<td>Pears &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>is filled. Text leaders are commonly used in<\/td>\n<td>Peaches<\/td>\n<td>Peaches &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>tables of contents.<\/td>\n<td>Plums<\/td>\n<td>Plums &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>Grapes<\/td>\n<td>Grapes &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0 &#8220;dollars and&#8221; .00 &#8220;cents&#8221;<\/td>\n<td>Displays a currency value with words.<\/td>\n<td>20.36<\/td>\n<td>20 dollars and .36 cents<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This format displays the whole number<\/td>\n<td>2.55<\/td>\n<td>2 dollars and .55 cents<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>portion of the number followed by the<\/td>\n<td>45.36<\/td>\n<td>45 dollars and .36 cents<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>words &#8220;dollars and,&#8221; followed by the fractional<\/td>\n<td>69<\/td>\n<td>69 dollars and .00 cents<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>portion of the number and the word &#8220;cents.&#8221;<\/td>\n<td>36.25<\/td>\n<td>36 dollars and .25 cents<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">0&#8243;.&#8221;00<\/td>\n<td>Displays a value in hundreds.<\/td>\n<td>2300<\/td>\n<td>23.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>The comma is used only for scaling<\/td>\n<td>23<\/td>\n<td>0.23<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>numbers in multiples of one thousand. Use<\/td>\n<td>400<\/td>\n<td>4.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>a number format with a decimal character<\/td>\n<td>5000<\/td>\n<td>50.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>between the placeholders to display a<\/td>\n<td>-90<\/td>\n<td>-0.90<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>number as a multiple of 100 or 10 (0&#8243;.&#8221;0).<\/td>\n<td>0<\/td>\n<td>0.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">[&gt;9999999](000)000-0000;000-0000<\/td>\n<td>Displays a telephone number with or without an<\/td>\n<td>7045556325<\/td>\n<td>(704)555-6325<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>area code.<\/td>\n<td>9106325689<\/td>\n<td>(910)632-5689<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>If the number is greater than 9,999,999, this code<\/td>\n<td>8896523<\/td>\n<td>889-6523<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>displays the number with an area code<\/td>\n<td>5362563<\/td>\n<td>536-2563<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>((000)000-0000); otherwise the number appears<\/td>\n<td>2065896325<\/td>\n<td>(206)589-6325<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>without the area code (000-0000).<\/td>\n<td>3369856<\/td>\n<td>336-9856<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">[&lt;1].00\u00a2;$0.00_\u00a2<\/td>\n<td>Shows currency values in dollars or cents.<\/td>\n<td>1.25<\/td>\n<td>$1.25<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>This code displays values less than 1 in<\/td>\n<td>3<\/td>\n<td>$3.00<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>cents notation (.00\u00a2), and display values<\/td>\n<td>0.35<\/td>\n<td>.35\u00a2<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>greater than or equal to 1 in dollars, and<\/td>\n<td>0.95<\/td>\n<td>.95\u00a2<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>leaves a space on the right so that the<\/td>\n<td>22.36<\/td>\n<td>$22.36<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>decimals line up ($0.00_\u00a2).<\/td>\n<td>0.75<\/td>\n<td>.75\u00a2<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">[&lt;=2]&#8221;Low&#8221;* 0;[&gt;=4]&#8221;High&#8221;* 0;&#8221;Average&#8221;* 0<\/td>\n<td>Using &#8220;If, ElseIf, Else&#8221; in a number format:<\/td>\n<td>1<\/td>\n<td>Low\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>If the value is &lt;=2, display the word &#8220;low&#8221; with the value,<\/td>\n<td>2<\/td>\n<td>Low\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>Else If the value is &gt;=4, display the word &#8220;high&#8221; with the value,<\/td>\n<td>3<\/td>\n<td>Average\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>Else display the word &#8220;Average&#8221; with\u00a0value.<\/td>\n<td>4<\/td>\n<td>High\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>5<\/td>\n<td>High\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\">[Red][&lt;=2]0;[Green][&gt;=4]0;[Black]0<\/td>\n<td>Using &#8220;If, ElseIf, Else&#8221; in a number format:<\/td>\n<td>1<\/td>\n<td><span style=\"color: #ff0000;\">1<\/span><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>If the value is &lt;=2, display the value\u00a0with red text,<\/td>\n<td>2<\/td>\n<td><span style=\"color: #ff0000;\">2<\/span><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>Else If the value is &gt;=4, display the value\u00a0with green text,<\/td>\n<td>3<\/td>\n<td>3<\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td>Else display the value with black text.<\/td>\n<td>4<\/td>\n<td><span style=\"color: #339966;\">4<\/span><\/td>\n<\/tr>\n<tr height=\"20\">\n<td height=\"20\"><\/td>\n<td><\/td>\n<td>5<\/td>\n<td><span style=\"color: #339966;\">5<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"<p>Finding Excel&#8217;s custom number formatting confusing? Below is a good reference\u00a0for some of the more popular examples\u00a0of formatting codes\u00a0for numbers and text\u00a0data\u00a0in Excel. Sample Custom &amp; Conditional\u00a0Number Formats Format Code Description Data with General Format Data with Custom Number Format 00000 Always displays 5 digits. Pads with leading 983 00983 zeros if the number contains [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[26],"tags":[76,63,79,78,77,54],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/425"}],"collection":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=425"}],"version-history":[{"count":0,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/425\/revisions"}],"wp:attachment":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=425"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=425"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=425"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}