Conditional Formatting – overview of what we did, and what’s a “data bar”?
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:
- Make the feature easier. We wanted to make it much easier for users to find the feature, to add conditional formats to their work, and to remove them too.
- Make more possible without needing to write formulas. We wanted users to be able to set up conditions like “top 10%” and “duplicates” with just one click.
- Provide new “visualizations”. We wanted to provide users with new visualizations for the purposes of exploring large data sets, identifying trends and exceptions, and quickly comparing data. We also wanted our new visualizations to be useful for annotation and presentation purposes.
- Address top customer requests. Many, many users have asked for more than three conditions, better UI to be able to reorder rules, etc., so we wanted to address those requests.
- Provide a better experience in PivotTables. We saw an opportunity for conditional formatting to “do the right thing” when applied to PivotTables. For example, it should be easy to apply a conditional format to an entire level and have new values that show up inherit that format, behave sensibly when users pivot, sort, or expand/collapse, and so on.
- Provide a better experience in Tables. Tables are a new feature in Excel 12 that I will cover more in a few weeks, but suffice to say that they have structure and conditional formatting takes advantage of the structure to help the user set up useful conditional formatting rules. For example, you can create a conditional formatting rule to compare two columns in a table or, based on a condition, format the entire row if needed.
These goals translated in to the following work:
- Three new visualizations – “data bars”, “colour scales”, and “icon sets” (note – these are working names, not final names)
- New UI for adding, removing, and managing conditional formats
- New conditional formatting “rules”
- Increased “limits” (see previous posts) and other customer requests
- Some PivotTable-specific functionality
- Some Table-specific functionality
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:
(Click to enlarge)
… 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):
(Click to enlarge)
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”.
- For "lowest/highest value," Excel evaluates all of the values in the range of cells and selects the lowest value as the shortest bar and the highest value as the longest bar.
- For "number," the user enters the value that should receive the shortest and longest bars. For numbers that are more or less than that value, Excel simply draws the shortest or longest bar as is appropriate.
- For “percent”, the user enters a percent to associate with the shortest and longest bars. For example, if the values in the selected cells range from 0 to 200, then a minimum value associated with "25%" would be 50. In this example, any cells having values less than 50 would have the shortest bar drawn in the cell.
- "Percentile" differs from "percent" in that it doesn't determine percentages within the range of values. Rather, "percentile" examines the set of values contained in the cells, orders them, and uses their ordinality or position within the set of ordered values to determine their percentile. In a set of ten ordered cells, the 40th percentile would always be the fourth cell, regardless of the value contained within it. Ergo, if a user selects "percentile" and enters 40 for shortest bar, then the cell(s) at the 40th percentile and below would have the shortest bar drawn in the cell.
- Finally, "formula" allows a user to enter a formula. The formula is evaluated to determine the value used for shortest and longest bar. This is useful for developing conditional formats that aren’t easily handled by the proceeding 4 choices.
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".
