Office challenge: How do you change the shape of a clip art image?

If you know how to change the shape of a clip art image, take part in this week's Office challenge. You'll also learn the answer to last week's question on hiding duplicate values in Excel.

Most clip art is square or rectangular, which is okay most of the time. You have to be satisfied with the shape because you can't change it – or can you? I ran into this last month while working on a PowerPoint presentation, but the solution I used works in most Office applications, not just in PowerPoint. How would you change the shape of a clip art image?
Last week, we asked…

How do you hide duplicate values in the same column without filtering the entire row? Pjroutledge (Peter) suggested conditional formatting, which is exactly the tool  I used. The condition is a formula that compares the content of two cells. The format trick is to match the font property to the cell background, rendering the data invisible when the conditional formula equals True. Duplicate values are still there, you just can't see them.

Let's look at a quick example. Suppose you wanted to hide all but the first occurrence of each city in a spreadsheet of address data. To do so, you could do the following:

  1. First, sort the list by the column in question. In this case, you'd sort by the City column. The values must be sorted, so don't skip this step.

  1. Select the range that contains the values (that's F2:F92 in the example spreadsheet).
  2. Choose Conditional Formatting from the Format menu.
  3. From the Condition 1 control's drop-down list, choose Formula Is.
  4. Enter the formula =F2=F1, where F1 is the cell directly above the first cell in in the data range. I know it doesn't seem to make sense, but you must reference the cell just above the first cell in the data range or this will not work as expected.
  5. Select the font color that matches the cell's color. In this case, that's white. Be sure to manually select white from the Color control's palette, even if the current color appears to be white already.
  6. Click OK to return to the Conditional Formatting dialog box.

  1. Click OK to return to the sheet.

With the cells still selected, you can still see the duplicate values — they are white in a highlighted cell.

Once you unselect the cells, duplicate values disappear. They're still there, you just can't see them, because the font and cell colors are the same.

In Excel 2007, the process is similar, but the steps are a bit different:

  1. Repeat steps 1 and 2 above.
  2. Click the Home tab and then click Conditional Formatting.
  3. Click New Rule.
  4. Select Use A Formula To Determine Which Cells To Format.
  5. Repeat steps 5 through 8 above.

If you sort the spreadsheet by another column, the conditional format still works. It won't hide every single occurrence but the first, as it does in this example. Instead, it hides every single occurrence but the first within contiguous blocks of the duplicates. In fact, that's what the example does too — it hides all occurrences but the first because all duplicates are in the same contiguous block.

What formatting task has you stumped? Perhaps we can help. Or feel free to stump your fellow readers by presenting your own formatting challenge.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks