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.
Conditional Formatting is a feature that allows users to apply formatting to cell(s) automatically depending on the value of the cell or the value of a formula. This is a handy feature, making it easy to highlight certain values (“all test scores below 50% turn red”), or make particular cells easy to identify (“all the tasks assigned to Dave turn green”). It is also a powerful feature, given that conditions can be based on any Excel formula. Users that know about the feature love it, and many book chapters and articles and web pages have been written on how to do all sorts of creative things with the feature. In our research and planning for Excel 12, it became clear that there was still a lot of additional capability that we could add in this area that would benefit all sorts of users, so we set out to really improve the feature in a number of ways. Specifically, we set out to:
These goals translated in to the following work:
Over the next week or two, I am going to review this work in detail. For the rest of this post, let’s explore one of the new visualizations – data bars. As I mentioned above, we wanted to provide users with great new data visualization tools so that users could scan and quickly comprehend large quantities of information – see outliers, spot trends, compare values, etc. In the case of data bars, the specific goal was to allow a user to select a range of cells and with one click apply a conditional format that makes it easy to see the value of a cell relative to all other cells that have been selected. Say, for example, that you had this range of data, and you wanted to make it easy to spot the large numbers and small numbers:
(Click to enlarge)
If you select that range and apply data bars, the range now looks like this:
… which makes it pretty easy to see the large and small numbers (note – I have kept the sample range small for illustrative purposes … the bigger the range, the more data bars help). So what’s going on here? Excel is comparing the values in each of the selected cells, and drawing a data bar in each cell representing the value of that cell relative to the other cells in the selected range. This bar provides a clear visual cue for users, making it easy to pick out larger and smaller values in a range. By default, when you apply data bars with one click, Excel uses the highest and the lowest value in the range to draw the shortest and longest bar. You can see how this works when I change the first two cells to have higher values (19k and 15k respectively):
The bars in all the cells adjust accordingly – as with all other conditional formatting, data bars are re-applied after calculation or data refresh, so the user always sees an accurate picture of their data set.
Of course, many times you might not want to use the minimum and maximum values in a range … accordingly; we have made all of this quite configurable. Here is a shot of part of the dialog that lets you change the settings on a set of data bars (warning – not final UI, for illustrative purposes only):
(Click to see list)
Besides the colour of the data bar, for both the shortest bar and the longest bar, you can specify “lowest/highest value”, “number”, “percent”, “percentile”, or “formula”.
Before I wrap up, I wanted to make sure that I pointed out two things that are fundamentally different from other conditional formats. First, unlike other conditional formats, which evaluate to true or false on a cell-by-cell basis, this conditional format is a comparison between a set of cells. Second, data bars provide an entirely new visual effect – a bar drawn inside a cell. These two differences also feature in some of our other visualizations, so I wanted to make sure I called the differences out clearly. Next up, "colour scales".
I don't get overly excited by conditional formatting though it certainly has its uses.
A bit like Tianwei wrote, the feature would be useful if one could filter ("directly")on the conditional formats. Without that capability, the conditional formating is to me only doing part of what I need. A list of say 10,000 items and 50 of them highlighted by conditional format is not much help, IMO, if I can't readily filter to (de)select them. This is obviously not related to data bars, which are the subject of the post. So, maybe it will be addressed in future articles?
This seems like a nice feature and judging by the comments, greatly anticipated.
Just a thought.....
Place the numeric value "inside" the color bar.
sn, Step, Marcos, Helen, and everyone else – Glad you like what you see.
Anon – IGX is a separate diagramming component. Also, the idea of ISVs writing other visualizations is interesting, although in this release, they will have to rely on the Excel object model.
Chad – We didn’t address that specific scenario in our conditional formatting work this time out.
Charles – Performance is always something we test and spend a lot of time on in order to make sure it is acceptable. Specifics depend on the amount of data you are using, the machine specs, etc.
Tianwei – No, we haven’t done any work to enable referencing formats in formulas. In Excel, formatting is applied after calculation, so there would be the danger of conditional formats changing after calculation.
Andrew – Thanks for the link.
XL-Dennis – Absolutely – the new conditional formatting features will have a full VBA object model.
Rob – That is a great idea, but not one we have implemented in Excel 12. What kind of scenarios would you use that for?
Pete – You will be able to filter by formatting, conditional and otherwise, in Excel 12. That will be the subject of some posts later this fall. We have a lot to talk about this release!
Biff - thanks for the idea. With cell alignment and column width, you may be able to get the value inside the bar.
Good afternoon, Mr Gainer -
One thing I've noticed with these new conditional formats is that the "scope" of the format now matters much more. While it's always been a little tricky to tell how far your conditional number formatting extended, it now makes quite a difference if you format A1:A10 to show data bars, and then independently format A11:A20 to do the same. The numeric ranges the bars pertain to are now different for what appears to be a contiguous range. Is there any possibility we could have even a hotkey (much like Ctrl-/ for array formulas) to show the region that this particular bar format covered?
Chris - at the moment, we don't (though we have made it easier to select conditional formats from the ribbon), but that is a great idea.
Here's another approach to doing something similar in 'legacy' Excel. This technique uses the old line art characters from the original IBM PC.
One question I have about the "Real McCoy" in Excel 12: will it be possible to have bars that progress to the left, as well as bars that progress to the right? It'd be nice to be able to use this feature to do things similar to this:
Very excited . I am also hurry to see new features for pivot table. I am looking to see calculated items from a previous field item. This would be very practical for MPS: StartingInventory = EndInventory of the previous period and EndInventory is a calculated item. See examples at
This is a really cool looking feature. What about having sparklines (miniature graphs) that could either be in a cell, or quickly generate a cell-sized line graph of a column or row?
I notice that when you specify a number for the shortest/longest bars, if the actual data falls outside those limits, Excel will just draw either the shortest or longest bar. I think that could be dangerously misleading in certain situations. Perhaps arrowheads could be used to indicate when the data is off-scale. A lot of people might say "set it up using the Highest/Lowest value option". The trouble with that is when the data varies by orders of magnitude, you quickly run out of pixels to display the smaller values. Also with regards to Data Bars, I'm interested to know how you're going to handle negative values. Is the zero point going to be somewhere in the middle of the column, with negative values to the left of zero and positive values to the right ?
Also, I must congratulate you and the team for the work you've done. I have to admit I was beginning to think that >256 columns was >Microsoft but I'm glad to say you've proved me wrong. I'm just itching to get my hands on it !