Page 174 - Excel 2013 All-in-One For Dummies

156 Using the Format Cells Dialog Box
until you see the date codes m/d/yyyy h:mm. Highlight these codes and then edit them as follows in the Type text box directly above:
mmmm dd, yyyy
The mmmm format code inserts the full name of the month in the custom format; dd inserts two digits for the day (including a leading zero, like 02 and 03); the yyyy code inserts the year. The other elements in this custom format are the space between the mmmm and dd codes and a comma and a space between the dd and yyyy codes (these being purely “punctuational” considerations in the custom format).
What if you want to do something even fancier and create a custom format that tells you something like “Today is Friday, January 11, 2013” when
you format a cell containing the NOW function? Well, you select your first custom format and add a little bit to the front of it, as follows:
“Today is” dddd, mmmm dd, yyyy
In this custom format, you’ve added two more elements: Today is and dddd. The Today is code tells Excel to enter the text between the quotation marks verbatim; the dddd code tells the program to insert the whole name of the day of the week. And you thought this was going to be a hard section!
Next, suppose that you want to create a really colorful number format — one that displays positive values in blue, negative values in red (what
else?), zero values in green, and text in cyan. Further suppose that you want commas to separate groups of thousands in the values, no decimal places to appear (whole numbers only, please), and negative values to appear inside parentheses (instead of using that tiny little minus sign at the start). Sound complex? Hah, this is a piece of cake.
Take four blank cells in a new worksheet and enter 1200 in the first cell, -8000 in the second cell, 0 in the third cell, and the text Hello There! in the fourth cell. Then select all four cells as a range (starting with the one con- taining 1200 as the first cell of the range). Open the Format Cells dialog box and select the Number tab and Number in the Category list. Then select the #,##0_);[Red](#,##0) code in the Custom category Type list box (it’s the sev- enth set down from the top of the list box) and edit it as follows:
[Blue]#,##0_);[Red](#,##0);[Green];[Cyan]
Click OK. That’s all there is to that. When you return to the worksheet, the cell with 1200 appears in blue as 1,200, the -8000 appears in red as (8,000), the 0 appears in green, and the text “Hello There!” appears in a lovely cyan.
Before you move on, you should know about a particular custom format because it can come in really handy from time to time. I’m referring to the