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.
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:
- 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.
- Select the range that contains the values (that's F2:F92 in the example spreadsheet).
- Choose Conditional Formatting from the Format menu.
- From the Condition 1 control's drop-down list, choose Formula Is.
- 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.
- 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.
- Click OK to return to the Conditional Formatting dialog box.
- Click OK to return to the sheet.
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:
- Repeat steps 1 and 2 above.
- Click the Home tab and then click Conditional Formatting.
- Click New Rule.
- Select Use A Formula To Determine Which Cells To Format.
- 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.
