Strategy: Excel offers the Select Objects tool, that allows you to select all images in a rectangular area. Use, Home, Find & Select, Select Objects.

Using the mouse, start highlighting above and to the left of the first cell that contains a picture. Drag down and to the right to encompass all the cells that contain pictures. When you release the mouse button, all pictures that are completely contained in the rectangle will be selected. At this point, press the Delete key.

Gotcha: You have to remember to turn off Select Objects mode, or you will no longer be able to select any cells. It is very annoying to try to select cells and have the mouse not respond to clicking. To exit Select

Objects mode, you simply press the Esc key.

PREVENT LONG TEXT FROM SPILLING

Problem: I have a column with long comments. When the adjacent column is blank, the text from the comment spills over into the adjacent cell.

Figure 1222 The long text spills over when C is blank.

Strategy: Ironically, the solution is to turn on Wrap Text in column B. This will not be what you are wanting to do, as each row will get really tall to accommodate the long comment.

Choose Home, Format, Row Height. Set everything back to 12.75.

4

Figure 1223 After wrap text, set the row height back to 12.75.

The result will be normal height rows that will not spill into adjacent blank cells.

Figure 1224 The long text won’t spill after turning on wrap text.

Problem: My manager wants me to show two values in one cell, as shown here.

Figure 1225 One cell shows 33 & 44.

Strategy: This formatting requires a little bit of cleverness.

1. Type 33, then Alt+Enter, about 9 spaces, then 44, then enter. 2. Select the cell, then Ctrl+One to access Format Cells.

3. On the Border tab, use the diagonal border.

4. You need to adjust the column width or the number of spaces in the cell to get it all lined up. If you add too many spaces, the row height will increase by 50%.

Gotcha: You won’t be able to do any math on numbers stored this way.

Abettermethodwouldbetostorethelowandhighincolumns.Then,usetheformula=A2&CHAR(10)&REPT(“ “,14)&B2 in cell D2. Turn on Wrap Text. Add the diagonal border.

Figure 1226 A & B can be totaled.

FOR EACH CELL IN COLUMN A, HAVE THREE ROWS IN COLUMN B

Problem: For each cell in column A, I want to have three rows in columns B and C, as here. I also want to be able to perform calculations with the values in column C.

Figure 1227 You can’t easily calculate using numbers in column C.

Strategy: You might be tempted to use the Alt+Enter trick to enter three lines of data in columns B and C. However, this will not work well in column C. Although the numbers are displayed fine, there is no way to have the numbers in C calculate automatically.

A better option is to merge cells A1:A3 into a single cell. You can then let the data in B fill B1:B3. Here’s how:

1. Enter a value in A1. Leave cells A2:A3 blank. Select cells A1:A3. 2. Select Home, Merge & Center dropdown. Choose Merge Cells.

4.Creative use of the Borders setting around each group will further enhance the illusion of three rows for each value in column A.

4

Figure 1231 Align to the top of the cell.

5. If you have several rows that need this formatting, use Format Painter mode to copy the formatting.

Select cells A1:A3. Double-click the Format Painter icon in the Home ribbon tab. The double-click will put you in Format Painter mode. You can now click in A4, then A7, then A10. Each click will copy the format from A1:A3 to the clicked cell. When you are finished, you can either click the For- mat Painter icon or press Esc to exit Format Painter mode.

SHOW RESULTS AS FRACTIONS

Problem: I work in an industry that reports values in fractions. Stockbrokers used to deal in increments of 1/8, and tire engineers still measure tread depth in increments of 1/32 inch.

Strategy: There are number formats for fractions. When you press Ctrl+1 to display the Format Cells dialog, you will see that there are nine standard fraction formats available in the Number tab of the Format Cells dialog box.

Figure 1232 Built-in fraction formats.

When you choose a fraction format, Excel finds the closest fraction.

Figure 1233 0.548679 is about 5/9 or 45/82.

Beyond the seven shown above, Excel offers standard formats for 10ths and 100ths. Unfortunately, there is not a standard format for 32ths.

You can create a custom numeric format to handle 32ths: 1. Select the standard format for 16ths.

2. In the Category list on the Number tab of the Format Cells dialog, scroll down and select Custom.

The custom number format code for 16ths is # ??/16. From this, you can deduce that # ??/32 might be a valid number format.

3. Click in the Type box and change the 16 to 32. The Sample area will immediately confirm that you have hit upon the correct format for 32ths.

Figure 1235 Display numbers as 32ths.

Figure 1234 Adapt this format for any fraction.

BETTER SCIENTIFIC NOTATION

Problem: I have to enter values for milliamps. For the 35 mA in row 2, you type 35E-3. For the 150 mA in row 3, you type 150E-3. In both cases, Excel changes what you typed to the values shown in column C.