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.

FileListAll01

On the Files_Updated sheet, I have a list of files that have been updated.

FileUpdates01

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:

  1. On the Files_Updated, select column A
  2. Click in the Name box, and type a one word name for the range – UpdateA in this example.
  3. Press the Enter key, to complete the naming.

FilesUpdateName

Add the Conditional Formatting

Next, add the conditional formatting to the list of all files.

  1. On the All_Files sheet, select the cells that contain the file names and download quantities.
  2. On the Ribbon, click the Home tab
  3. Click Conditional Formatting, then click New Rule.
    CondFormatNewRule
  4. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format
  5. 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:
    =COUNTIF(UpdateA,$A2)

    CondFormatUpA
  6. Click the Format button, and select the formatting you want for the highlighting.
  7. Click OK, twice, to close the dialog boxes.

The rows for the files that have been updated are now highlighted.

CondFormatUpGreen

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.

____________________

You may also like...

9 Responses

  1. JP says:

    It might be limited to Canada. After all, as Weird Al Yankovic says, maple syrup and snow are the major exports of Canada. ;-)

  2. Thanks, JP! I wish we could export more of that snow. Let me know if you need any this winter.

  3. Ron says:

    I am trying conditional format a range of three cells where the FONT of the highest value in the range will be in Red follow by Blue and Green. I know it can be done using cell fill but, with a large spread sheet, it will look like dog’s breakfast. Any help would be appreciated. Also can this be done across worksheets (i.e. three different worksheet in the same file)

  4. Ron, use formulas for the conditional formatting. For example,

    For the highest value: =C2=MAX($C2:$E2)
    For the lowest: =C2=MIN($C2:$E2)
    For the second highest: =C2=LARGE($C2:$E2,2)

    For each condition, go to Format, and on the Font tab, select the colour that you want.

  5. Ron says:

    Thanks for the help Debra.

  6. Mike B says:

    I have a very large spreadsheet that has about 50 columns and over 200 records that I have to maintain. This information has the potential to change on a daily basis. I however am not the one who is actually changing or updating this official document. Each week I have to download a new copy and document any changes from the previous week.
    Is there any way that I can use conditional formating to highlight information that was updated on the newly downloaded sheet, using the previous weeks version as a reference?

    FYI I am using Excel 2007. If I cant use excel is there any other 07 Programs that I can use?

    If you can answer my questions can you please Cc me as well on my home e-mail account: balsleyma@yahoo.com

    Thanks

  7. venkateh says:

    hey guys can any one tell me, If do change any thing in excel sheet the same thing should change on another excel file for example: we entered employees details in one excel sheet that details should enter in another excel file without entering data manually.

  1. September 5, 2012

    […] […]

  2. February 2, 2013

    […] You need to use Named Ranges – see Conditional Formatting Is Better Than Your Faulty Memory | Contextures Blog […]

Leave a Reply

Your email address will not be published. Required fields are marked *