You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
In this blog post I’ll walk through some of the new Conditional Formatting features in Excel 2010 such as:
In Excel 2010, you can create conditional formatting with references to a different sheet on the workbook. This means that you no longer need to copy or link your data to the same sheet when using conditional formatting.
For Example, let us look at the monthly sales data for a bookstore chain. The bookstore organizes their sales data as follows:
We will apply conditional formatting so that we can easily determine if the department at the local branch is doing better or worse as compared to the other branches in the company. Let us use the San Francisco branch as our example. We will apply a green background fill for the departments if they are performing better than average.
1. Start on the sheet for San Francisco location and select the cells in the sales column to which you want to apply conditional formatting. Go to the Home Tab-> Conditional formatting -> Highlight Cell Rules -> Greater Than.
2. Click on the (RefEdit icon) and simply navigate to the sheet whose value you want to compare against. In our case, we will navigate to the company wide worksheet and click on the average for the first row in the department column.
3. Now here is a neat little tip – references in Conditional Formatting follow all the rules of absolute and relative references. The Cell References are relative to the active cell in the “Applies To” range (in this case the top-left cell in the range).
Let us modify the reference to be relative to the rows by deleting “$” symbol before the row number.
The result is that each department on the “San Francisco” sheet being compared to the same type of department on the “Company” sheet.
4. Click OK and you have a Conditional Formatting Rule that is dependent on other sheets in the workbook.
Have you ever used ranged based conditional formatting (such as icon sets, color scales and data bars) only to find that one of your formulas have evaluated to an error? In Excel 2007, errors in the range would lead to conditional formatting not being applied to all the cells in the range. Users told us that this was frustrating.
In Excel 2010, conditional formatting silently ignores the errors and continues applying conditional formatting to the rest of the cells in the range.
For this release we’ve also improved the performance of formatting in Excel 2010. For conditional formatting we store dependencies of the formulas used so we don’t need to reevaluate the entire conditional format as often. We’ve also made banding in the table and pivot table styles faster, so this will impact things like refreshing a pivot table or scrolling with a large table on your sheet. For all the above cases, we also cache the format of the cell so if you scroll off the screen and come back, or switch sheets and come back, we’ll remember the cell formatting and can display faster.
In our next Excel 2010 post, we’ll look at a new object model feature for conditional formatting.
Comments: (11) Collapse
I'm not sure if it's possible but I never managed to do that. Let's assume I have a pivot table with some financial data from 5 years (years in columns, items - ie company departments - in rows). With this pivot table I want conditional formatting to compare results for each department through all 5 years (compare values from 5 columns within one row, not values for one row within all rows).
Sam, out of curiousity, what ever happened to the "Compare Columns" rule that made a brief appearance in Excel 2007 beta but was canned in the final release because of bugs that couldn't be fixed in time? Why has this option not returned in Excel 2010, given that by the time Excel 2010 ships, you'd have had three years to fix the problems?
Hi
I never saw the Compare columns in 2007B but it sounds similar to a feature I've been looking for where conditional formatting can be applied to say, a list of names in column A based on some formula in column X.
"Colin: I don't remember that option (I was new to the team around that time)...Was it doing something that couldn't be done through a cf formula rule?"
These were Dave Gainer's posts. Here's the link to the original discussion. Scroll down to the section titled "More Conditional Formatting Goodness"
blogs.msdn.com/.../tables-part-2-stickiness-structured-selection-and-more.aspx
Like the other cell highlighting rules, the option was designed to avoid having to use formulas...the discoverability thing.
I can't locate the follow-up post that explained why the option was being dropped for the Excel 2007 release.
Will Excel 2010 contain a simple way con conditionally format entire rows based on the contents of a cell in that row? I've been looking for a function like that since Excel 97.
Sorry, ended up removing a comment, I'll repost here:
IgorM: Amit should be replying to your question shortly.
Colin: I don't remember that option (I was new to the team around that time)...Was it doing something that couldn't be done through a cf formula rule?
Steve: Can you do this with a formula rule today and a relative ref? Like select a1:a10 and new rule -> =X1 where column x returns true or false? Or are you talking about something else?
Colin part 2: Yeah the quick turn on for CFs is nice w/o having to build the formula rule, unfortunately we just didn't spend time there this release.
Noah: see Colin's link, something like that was in the beta as well but didn't make the final release. iirc there are some design complications around c/p partially highlighted rows, and losing formatting, and the fact that it only worked with tables. we should look at this as well as colin's cf, and other easy to set presets (ideas?), in the future.
IgorM: unfortunately, there isn't a really good way of doing thing. We are looking at this for future releases.
Currently, the recommended way is to create separate rules that apply to each row (format painter is a great way to accomplish this). This works the best if the number of rows will remain constant on data refresh. Thanks for giving us your feedback.
Just saw this. I like the improvements.
One question. In 2007 when I have a conditional format applied to a range, and I cut/copy from the middle of that range, the conditionally formatted range becomes split. With multiple copy/cuts these ranges can spawn out of control, eating up a tremendous amount of resources. Has this been fixed?
I'm sure that's fairly confusing. Let me illustrate:
If my initial range (conditionally formatted) is this: =$A$1:$A$10, and I cut from say A5 and paste it at A12, then my conditionally formatted range becomes: =$A$1:$A$4,$A$6:$A$10,$A$12. Imagine this on 10,000 rows with twenty or thirty cuts and you can see why it becomes a problem.
A comment for the previous post on data bars, can't post there because its closed.
You can put the text in one cell and the data bars in another cell in 2007, which helps solve one of the discussed problems.
Suppose A1:A10 contain your values
In cell B1 just enter =A1 and copy down.
Set data bars on for column B and turn On the Show bar only feature.
Regarding BEL8490's comment in the Icon Set Improvement blog regarding creating a legend. How about providing a tool tip like in the PivotTable area where you tell us the where every cell gets its value (column and row label fields) and have those in a condtional formatting range to provide backup equivalent to a legend. The problem of course is that you can apply an unlimited set of condtions but since there appear to be fewer options for the icon sets and possibly data bars, this might be helpful.
I'm new to 2010, came from 2003. I have a spreadsheet where I WAS able to apply up to 3 rules per cell, ex: if the value in the cell was below 100 highlight it green and above in red. I am unable to see in 2010 how to do that. All I've found so far is to apply 1 rule. How do you apply more than 1 rule to a cell in 2010?