CyberNotes
Tutorial Thursday




For some weird reason, I’ve been a Microsoft Excel addict for as long as I can remember. When I was younger I remember being told by one of my teachers that Excel is one of the most powerful applications available, but most people will never touch it with a ten foot pole.

For over 7 years I’ve been programming Visual Basic for Applications (VBA) in Excel, which is essentially a way to extend Excel to do everything your heart desires. With it you can add more formulas, and create buttons that will execute queries on the data located in your spreadsheet. VBA is one of Excel’s best features in my opinion.

What I’m going to cover today is how to highlight cells based on their values in Excel 2007. This method is extremely simple and doesn’t use an ounce of VBA, despite an abundance of sites out there saying that VBA is required to do such a task. It’s all about Conditional Formatting!

Tip: This tutorial was created for Excel 2007, but the general principles will also work in older versions of Excel. The free OpenOffice.org Suite even has a Conditional Formatting option located under the Format menu, but it’s pretty basic compared to the one in Excel 2007.

–Preparation–

Cell Highlighting Starter

The values in column “C” and “D” are both formulas that I created to calculate the age of the person, and then how many days are left until their next birthday. That way I can prepare gifts or cards for any upcoming birthdays well in advance. The only problem is that when the list gets large it can be hard to see what birthdays are coming up.

I’m not going to walk through what all of the formulas mean, but to make it easier to follow along with our example, here’s what the content of the second row looks like:

  • A2: John
  • B2: 11/19/1984
  • C2: =FLOOR(YEARFRAC(TODAY(),B2),1)
  • D2: =DAYS360(TODAY(),DATE(YEAR(B2)+C2+1,MONTH(B2),DAY(B2)))

What I want to do with the data is highlight anyone with a birthday that is coming up within two weeks. To do cell highlighting, a lot of sites talk about how to use the Visual Basic for Applications (VBA), but that’s way more work than needed. It’s actually really easy to do …

–Instructions–

Note: Most of the screenshots below are thumbnails, so click on them for a fullsize version.

  1. The first thing that you need to do is fill in a spreadsheet with some data to apply the conditional formatting. I’m using the data that I provided above for keeping track of birthdays, but you can use whatever you want.
    Cell Highlighting Step 1
  2. I want to have the birthdays highlighted that are coming up in the next two weeks, so I need to highlight the cells in column “D” for that:
    Cell Highlighting Step 2
  3. On the Home Ribbon is the Conditional Formatting option. Click on that and select the option for a New Rule.
    Cell Highlighting Step 3
  4. There are several different types of data that you can look for in the cells that you’ve highlighted. For example, you can have duplicate entries or max/min values highlighted. For us we want to choose the Format only cells that contain option, and we want to check for a cell value that is less than 14. After that is entered in press the Format button.
    Cell Highlighting Step 4
  5. Now we need to pick what the cells should look like that match the criteria, which in our case is all birthdays that occur in less than 14 days. I chose to make the background orange and also made the text bold.
    Cell Highlighting Step 5
  6. You should see a preview of what matching cells will look like. Just hit the OK button to complete the conditional formatting.
    Cell Highlighting Step 6
  7. Yay, it worked! Looks like John has a birthday coming up in 4 days!
    Cell Highlighting Step 7
  8. Anytime the values in these cells change, Excel will automatically reanalyze them to see if they meet any of the conditional formatting rules. That means that once John’s birthday passes in 4 days it will no longer be highlighted.

This is just one of the nearly unlimited uses for conditional formatting. I use this all of the time in my spreadsheets because it helps recognize when an item needs attention. Some things I’ve used this for in the past is attendance lists for parties to highlight who’s coming and who’s not, budgets to see when money is getting low, gift lists to pinpoint who I still have to buy for, and a countdown fir events (similar to the birthday example I did in this article), and much more. Get creative!