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".
Are you English or from one of the Commonwealth countries? I noticed you said "colour scales" not "color scales" :)
Love the feature. Will Office 12 B1 be available broadly? Or atleast freely within Microsoft? Where can I go to register for B1 within the company?
Thanks. I am a huge Excel user and fan. I really appreciate the blog and your team taking the time to post to it.
Awesome! I've been wanting improvements to Conditional Formatting for a while - very exciting! I can see uses for the data bars, but I'm really looking forward to seeing what you've done with the "regular" Conditional Formatting dialog.
You guys really seem to have outdone yourselves on this release. Looking forward to B1.
The real kicker would be that the data bar is only a working sample of a more general approach to visual widgetry.
My question is, how does this component relate to IGX?
I see a number of ISVs willing to either write such XML-controlled widgets and/or migrate arbitrary ActiveX to this. Your thoughts?
One item in conditional formatting that I've always desired is the ability to hide (or not print) rows/columns using conditional formatting. For example, if a row has all zeros in it, conditional formatting would either hide the row or it wouldn't be printed.
Office 12 looks very impressive: I guess you must have started working on it way before Office 2003 shipped.
Bars look great, but whats the performance hit like when Excel has to do the comparisons on a large column?
This looks great and will be a very useful feature. Now I'm really looking forward to the new version!
David, thanks for the detailed postings. I am getting more excited about Excel 12 every day now.
Quick question: one of the things I have longed for over the years is the ability to reference a format (colors, patterns, etc) in a formula. Case in point, you have a detailed page of all data and conditional formatted cells. Then you create a summary page where you would like to say: if the cell in detail page is formatted like (ref to a "hard formatted" cell) then retrieve the value. Would there be such a feature in Excel 12?
These new features look like they will benefit a lot of users.
I just had a shot at doing something similar for earlier Excel versions.
Not quite as good as though ;-)
Very nice and it seems that we can replace all the workarounds we now use with built-in features.
Will we also be able to fully access C/F via VBA?
Great feature ... can't wait to implement this in my Excel solutions :-)
Very nice feature indeed! The in-cell visualization may make it easy to create, say, a project Gantt chart in Excel.
What you require is conditional number formatting - a feature which does not exist (yet?)
=0 then NumberFormat = ;;;
Two weeks ago I build a VBA procedure to draw rectangles in a column of cells to compare values visually. Last week I saw my technique built into the new CF, demo'd at the MVP Summit. This is way cool, and the new Excel is going to rock.
The =0 part of the number format is between the second and third semicolons. But you knew that.