Conditional Formatting Is Better Than Your Faulty Memory
If there are any typos in this blog post, blame my maple syrup related injury. Sunday morning, I tried to clean a few drips from the syrup jug, and ended up with a puncture wound. Those crystallized bits are razor sharp, so don’t put your delicate typing fingers anywhere near them. Maybe it’s just a Canadian hazard!
Bravely carrying on, I was updating some files, and keeping track of the updates in Excel. On a sheet named All_Files, I have a list of all the files, and the number of downloads for each file.
On the Files_Updated sheet, I have a list of files that have been updated.
Mark Duplicate Entries With Conditional Formatting
When looking at the full list of files, I’d like a quick way to identify the files that have been updated. The actual list is pretty long, and my faulty memory only works for the first few updates. After that, I can’t really remember which ones have been done.
I could add a new column, with a COUNTIF formula to count the number of times each file appears in the Update list. Instead, I’ll use conditional formatting to colour the rows for files that have been updated.
Just like data validation, conditional formatting complains if you try to refer to cells on another worksheet. So, I’ll name the range on the Files_Updated sheet, and refer to the named range. For some reason, Excel is okay with references to named ranges on another sheet.
Name the Range
To name the range:
- On the Files_Updated, select column A
- Click in the Name box, and type a one word name for the range – UpdateA in this example.
- Press the Enter key, to complete the naming.
Add the Conditional Formatting
Next, add the conditional formatting to the list of all files.
- On the All_Files sheet, select the cells that contain the file names and download quantities.
- On the Ribbon, click the Home tab
- Click Conditional Formatting, then click New Rule.
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format
- In the formula box, enter a COUNTIF formula, referring to the named range on the Updates sheet, and to the active cell on the All_Files sheet. Use an absolute reference to the column, $A. In this example, the formula is:
- Click the Format button, and select the formatting you want for the highlighting.
- Click OK, twice, to close the dialog boxes.
The rows for the files that have been updated are now highlighted.
You can quickly see which files are done, and concentrate on the files that still need to be updated.
Watch the Video
To see the steps in action, you can watch the following short video.