Conditional formatting meets the ribbon
Now that I have reviewed much of the new conditional formatting functionality we have added in Excel 12, let’s review how we have made it easier to find and use these features. One of our objectives was to showcase the feature in a more prominent place in the UI, so we put the entry point for conditional formatting on the first tab in the new Excel ribbon. (If you haven’t read The New Microsoft Office User Interface Overview, you might want to give it a quick read now to familiarize yourself with terms like "tab" and "gallery". Also, standard disclaimer – all visuals and names are works in progress and subject to change before we ship the product). The first tab is named the "Sheet" tab. Here is a picture of the Sheet tab in current builds:
(Click to enlarge)
The Sheet tab is the place where the most commonly-used Excel commands are located. You can see that conditional formatting is given a nice large, attractive button that will hopefully be something users want to explore. Indeed, one of the many great features of the ribbon is that it makes all the Office applications browsable and much easier to explore. If you select the conditional formatting button, you are presented with a gallery that contains five categories of conditional formats and a few other commands:
Then, as you select each item in the gallery, you see a fly-out that lists the specific formats that can be applied using that gallery. The fly-out from the “Highlight Cells Rules” item allows you to create value-based rules like Greater Than, Less Than, Between, Equal To, Text that Contains, etc. (Pardon the yellow dot – missing artwork.):
(Click to enlarge)
Each entry point on the “Highlight Cells Rules” item launches what we call “Quick Launch Dialogs” that are focused on the rule you are trying to create. Before we explore the rest of the conditional formatting gallery, let's take a look at an example of how all of this works. Say you had a range of numbers, and you wanted to highlight the cells that were less than 150:
(Click to enlarge)
You would simply select the range and then select “Less Than” from the “Highlight Cells Rules” list. Excel would show you the "Quick Launch Dialog" that lets you set the value and the formatting you want to see:
As you enter the value for the condition or change the resultant format from Red Fill to something else, you would see the formatting applied to the cells on the spreadsheet instantly without needing to press OK to commit the changes. This “live preview” allows you to ensure that your condition is set correctly before finalizing the rule. For example, if you type in 150, the appropriate cells are immediately highlighted:
(Click to enlarge)
You could then change the format to “Red Text” (or set any other format) and again the values update immediately.
(Click to enlarge)
Once you are happy with the results, you simply press OK, and the conditional format is committed. Note that you can pick from a list of pre-defined formats (we have tried to provide many common formats) or set your own format. Also note that there is a “More Rules …” command. This will bring up a more detailed conditional formatting dialog that allows you to access all conditional formatting rules as well as tweak various settings on rules you are adding.
Before I continue on through the rest of the gallery, I just want to reflect how quick and easy that was – select the range, and then with a few clicks, users will be able to set a wide variety of powerful conditions on their data.
Moving on down through the gallery, the fly-out from the “Top/Bottom Rules” item allows you to create value-based rules like Top n Items, Top n %, etc. Again, these items launch “Quick Launch Dialogs” to help you complete conditions, and you can create any rule (i.e. one standard deviation above) and tweak settings (i.e. make that three standard deviations above) from the “More Rules…” command.
(Click to enlarge)
The fly-out from Data Bars allows you to create data bars of your color choice with a single click – in this case (as is the case for Colour Scales and Icon Sets), there is no “Quick Launch Dialog” because Excel does not need any other information from the user to apply the conditional format – these are truly one-click conditional formats. Just select the cells you want to compare, click on the colour of data bars you want, and you are done. By default, when you apply data bars with one click via the ribbon, Excel uses the highest and the lowest value in the range to draw the shortest and longest bar. You can further tweak the color of the data bar or the way Excel computes the values for the shortest and longest bar by selecting “More Options…” from the fly-out.
(Click to enlarge)
The fly-out from Colour Scales allows you to create a 2-colour or 3-colour color scale rule using default colours we have provided. By default, when you apply colour scales with one click, Excel uses the lowest, highest, and midpoint values in the range to determine the colour scales. You can change the colours or the way Excel determines the values for the highest, lowest, or midpoint values by selecting “More Options…” from the fly-out.
(Click to enlarge)
By now, you can likely see the pattern. The fly-out from Icon Sets allows you to create a conditional formatting rule with 3-icon, 4-icon, or 5-icon icon sets of your choice. The most commonly used icons are exposed here; by default, Excel 12 uses percentiles to calculate the break between icon sets … for example, in the three-icon case, Excel 12 sets 33% and 67% as the break between the three sets of icons. You can change the icons or the way Excel determines the values for the icon sets, as well as pick from further icon sets, by selecting “More Options…” from the fly-out.
(Click to enlarge)
The final entries in the gallery are “Clear Rules” and “Conditional Formatting Rules Legend”. The Clear Rules fly-out has options to clear all conditional formatting rules on the selected range, or the entire sheet, or the active Table, or the active Pivot Table. The goal here is to make it easy to remove conditional formats from a sheet.
“Conditional Formatting Rules Legend” will be described in further detail in my next post. This is the place for users to do advanced conditional formatting and manage the rules in the workbook (add new rules, edit rules, reorder rules, and more).
In sum, we have tried to make conditional formatting easy to find, fast and simple to apply, and straightforward to remove – no more than a few clicks in most cases. Next time up, a discussion of some of the more advanced features and limits.
