Software

How to use Excel 2010's new conditional formatting with references

Excel 2010's new referencing option makes conditional formatting more powerful than ever before!

Excel 2010's conditional formatting feature lets you reference different sheets—something you couldn't do before. In earlier versions you had to copy or link data to the same sheet. Now, you just include the reference to another sheet as you would any other reference!

To illustrate this new feature, we'll use the simple products sheet shown below. (I based this example on a subset of the Products sheet in the Northwind database, but changed it considerably for this example.) There are two regions, Northwest (shown below) and Southwest. Each region has its own sheet for tracking product sales.

Now, let's suppose you want to see where the Northwest region is outselling the Southwest. Using Excel 2010's new referencing option, it's easy to reference another sheet, as you'll see:

  1. Select the sales values in Northwest, that's D2:11.
  2. Click the Home tab.
  3. In the Styles group, click Conditional Formatting | Highlight Cells Rules | Greater Than. The resulting dialog box will display a default format.
  4. Click the RefEdit icon (circled in the above picture).
  5. Click the Southwest tab.
  6. Click cell D2 (in Southwest).
  7. Alter the absolute cell reference $D$2 to $D2—that way the row number can adjust to accommodate the entire column in Northwest. In other words, every product in Northwest will evaluate the same product in Southwest.
  8. Click the RefEdit icon.
  9. Choose an appropriate format, such as Green Fill With Dark Green Text.
  10. Click OK. According to the conditional formatting, the Northwest region is outperforming the Southwest region in four products.

This new referencing option doesn't work with grouped sheets, which makes sense. If you want to see where the Southwest is outperforming the Northwest, just repeat the same process, but start by selecting the sales values on the Southwest tab. Then, in step 5, click the Northwest tab instead of the Southwest tab.

Both Excel 2003 and 2007 will let you go through the motions of selecting a cell or range on another sheet, but when you try to commit the reference, Excel displays an error. This new conditional formatting option is easy to implement and one you'll probably find many uses for. Try it out and let us know how you like it!

About

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.

11 comments
jeboy.06
jeboy.06

Hi, Ill just want to confirm if it is possible to format one column cell and the entire row of that cell will be highlighted also?

manishdhir
manishdhir

Hi, I need someone to help me to check and if that part is not there in excel it should skip that part and perform the rest of the action. Please help.

cutietoy
cutietoy

I need to have a conditional format for dates greater than other dates, and they need to be compared by paired cells through two columns. Any suggestions?

stapleb
stapleb

That looks good and could be extremely useful to show people trying to compare data as in your example. Hadn't thought of range names as mentioned by gbentley - great idea.

dogknees
dogknees

You can refer to other sheets in your Conditional formatting in earlier versions. Just name the range you're going to refer to then use the name in the Conditional Formatting formula. The same applies to Validation. You can use a list on another sheet as long as you name it and refer to the name in the validation settings.

library assistant
library assistant

I've been doing validation that way since I discovered validation. Just didn't know you could do conditional like that. I'll give it a try. My newest favorite in Excel is vlookup. I didn't realize you could do that in 2003. I have Office 2007 on my laptop and at home, but STILL haven't been upgraded to 2007 at work, so I've been using my laptop to do my statistics. At least I have 2003 - one of the others in my department still has office 2000!

ssharkins
ssharkins

Can you offer some specific step instructions? I tried that this morning and couldn't get it to work -- still got the "can't reference another sheet" error using Southwest!D2. Using just Southwest didn't return an error, but didn't work either -- nothing happened.

dogknees
dogknees

... can be used in Validation. Assume you have an input sheet where the user selects a Staff Type, then a Pension Plan. The difficulty is that the Plans available depend on the Staff Type. Set up a table like this with the staff types across the top and the available plans listed under each one. Boss Manager Junior Plan A Plan A Plan C Plan B Plan C Plan D Plan E The boss has a choice of three, the Manager and Junior have only two. Name the body of the table(ie all except the top row) "planList" and the top row as "typeList". To validate the Staff Type cell, lets make it A1 for example, select a List type validation and enter "=typeList" as the source. So far pretty standard, note lists can be horizontal or vertical. To validate the Pension Plan cell, lets make it A2, choose a List type validation and enter the following formula. =OFFSET(planList,0,MATCH(A1,typeList,0)-1,ROWS(planList),1) This says, find the column my selection in cell A1 is within the typeList using the Match function, and subtract one (because Matchs start at 1 and Offsets start at 0) to give the column of the planList table to use. The Offset function says, give me the part if the planList that is in the appropriate column, and make it one cell wide and as tall as the lookup table might be(ROWS()). The formula may seem a bit complex if you haven't worked with match and offset before, but a bit of experimentation will get you there. You can extend this to count the number of rows in the relevant column, so you only see the real values. Hint, use count and counta instead of the Rows() construct. This is really just scrathing the surface of what is possible in a validation formulae. If you are familiar with "array-formulae" they will also work in the validation list anabling you to sort a list for display and all sorts of other options. These methods give you enormous power in providing just the right options to your users and allowing you to easily change options without needing to recode all your validation lists by hand. You can use information entered by the user to validate other inputs. You might have a list of people and an input cell for each where you can select their spouse. Use the whole list as the source for the Spouse cell. As the users adds people, the validation lists grows accordingly. The last bit of the puzzle is that you can even create your lookup formulae as "Named Ranges" and then just refer to the name in the Validation List area. So, only one copy to change to update all similar input cells making the process much faster and easier to manage.

dogknees
dogknees

Give the cell Southwest!D2 a name using "Formula tab/Defined Names/Define Name". "rngSW" for example. When you're entering your conditional formatting statement, rather than entering "Southwest!D2", enter "rngSW". In other words, make sure any reference to another sheet refers to a range name rather than a range or cell address. Regards

ssharkins
ssharkins

Woo hoo -- it really did work. Thank you for sharing that with us!

Editor's Picks