268	POWER EXCEL WITH MR EXCEL
	DOUBLE SPACE YOUR DATA SET

Problem: My manager wants me to add a blank row after every row of the data.

Figure 687 Double space the report!

Strategy: Excel MVP Bob Umlas showed me this trick, and it has become one of my favorites. Search YouTube for Learn Excel 467 to see Bob demo the trick.

Bob adds a new column with numbers 1, 2, 3, and so on. He then copies this range of numbers below the itself. When you sort by the new column, your report is instantly double-spaced! Follow these steps:

1. In the blank column to the right of your data, enter the heading Sort.

2. Fill the column with a sequence of 1, 2, 3, etc. One method is to type a 1 in the first cell, select the cell, and Ctrl+drag the fill handle to the end of the data set.

3. Press Ctrl+C to copy the selected numbers in the new column to the Clipboard.

4. Select the first blank cell beneath your new column. Press Ctrl+V to paste a duplicate set of numbers.

Figure 688 Copy the numbers below.

5. Select one cell in the new column. Click the AZ button on the Data tab. Excel sorts by the new col- umn. Because every number occurs twice—once in the original report and once below the report— blank rows are sorted up into your data.

Figure 689 Sort by C, and the data is double-spaced.

6. Delete the Sort column.

Additional Details: To triple space your data, you can paste two copies of the numbers below your data.

Additional Details: Check out Bob Umlas’s book More Excel Outside the Box (available at Amazon). It is filled with tricks like this one.

PART 3: WRANGLING DATA		269
	USE FIND TO FIND AN ASTERISK

Problem: My largest customer is Wal*Mart. When I use Find or Find and Replace to search for Wal*Mart,

Excel also finds Wallingsmart. I know this happens because Excel sees * as a wildcard character. What if

I really want to search for an asterisk?

Figure 690 The asterisk is a wildcard.

Strategy: You can use three wildcard characters in the Find and Replace dialog: *, ?, and ~.

If you include an *, Excel will search for any number of characters where the asterisk is located. For ex- ample, searching for Wal*mart will find Wal*mart and also Walton Williams is smart.

If you include a ?, Excel will search for any one character. For example, searching for ?arl will find both

Carl and Karl.

To force Excel to search for an asterisk, tilde, or a question mark, you can precede the wildcard with a tilde

(~). When you search for Wal~*mart, Excel will only find Wal*mart. If you search for Who~? Excel will only find Who? and not Whom. When you search for “Alt+~~”, Excel will find “Alt+~”.

3

Figure 691 Use ~* to really find an asterisk.

Additional Details: To change all the multiplication formulas to division formulas, you can have Excel change all ~* to /.

Figure 692 Change multiply to divide.

Gotcha: Changing a formula of =5*3 to =5/3 will work fine. Changing a math exercise sheet with 5*3 to be- come 5/3 might change your values to May 3rd. Use caution when changing asterisk to slashes within text.

270	POWER EXCEL WITH MR EXCEL
	USE AN AMPERSAND IN A HEADER

Problem: I added the custom header Profit & Loss Report to my report.

Figure 693 Include an ampersand in the header.

However, the actual header appears as Profit Loss Report. The ampersand is missing.

Figure 694 Excel leaves out the &.

Strategy: The ampersand is a special character in the custom header and footer field. To print an amper- sand in the header, you have to type && in the Header dialog box.

Figure 695 Specify && while editing the header.

Having two ampersands will give you the desired heading Profit & Loss.

Figure 696 Excel will print the && as a single &.

HIDE ZEROS & OTHER CUSTOM NUMBER FORMATTING TRICKS

Problem: I don’t want zeros to appear in my document.

Strategy: Excel’s custom number formatting codes have an amazing array of options that not many people know about. You can specify multiple formats within one custom number code. Each format is separated by semi-colons. Read on for details.

To assign a custom number format, you select the range and press Ctrl+One. On the Format dialog, you select the Number tab and then select Custom from the Category list. Finally, you type any valid custom number format in the Type box.

You’ve probably run into some custom number formats, such as these: ●● #,##0 will display numbers with thousands separators.

●● $#,##0.00 will display two decimal places and a currency symbol.

PART 3: WRANGLING DATA		271
●●	#,##0,K will display numbers in thousands.
●● mm/dd/yyyy will display a date as 02/17/2014.
●●	[h]:mm will display hours in excess of 24 hours.
●●	[blue]0 will display a number in blue text.
●●	[color12]0 will use color index 12, often olive.

In these simple formats, there is only one format being used. If you enter two formats separated by a semi- colon, the first format is used for positive and zero value, and the second format is used for negative values.

For example, [blue]0;[red]-0 will display negative numbers in red and other numbers in blue.

If you enter three formats separated by semicolons, the first format is for positive, the second format is for negative, and the third format is for zero. For example, [blue]0;[red]-0;[green]0 will display 0 cells in green text.

To show a plus sign before the positive numbers, use +0;-0;0.

If you type a second semicolon and leave out the final formatting code, Excel will suppress the display of zero values. For example, 0;-0; will show positive and negative numbers but hide zeros. Note that the final semicolon is a subtle but important difference from using 0;0. This figure shows the custom number format to hide zeros.

Figure 697 The zero in C2 is not displayed.

If you specify a fourth number format, it is used for text values.

To hide all values in a cell, you can use ;;; as the custom number format. 3

Additional Details: The custom number formats were written long before Microsoft started using con- ditional formatting. You can change the formatting based on meeting certain criteria. For example, the following code would display numbers above 10,000 in thousands and other numbers normally:

[<10000]#,##0;[>=10000]#,##0,K,.

In many cases in which you might use concatenation to join text and a number, you could use a custom number format instead. Here, cell B8 contains a SUM function, yet the result is displayed with a payment message.

Figure 698 This SUM function produces a message.

Further, the message changes, depending on whether the balance is positive, negative, or zero. Below, the three cells show the message for each state. You control the messages by using three zones in the custom number format. Note that in the negative zone, there is no minus sign in the number format, so Excel displays the number as positive. In the zero zone, there are no numeric characters at all, so Excel displays the No Balance Due message.