OfficeTalk: Conditional Formatting: Adding Customized Data Bars to Excel 2007

Article
05/23/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Data bars give you an opportunity to create visual effects in your data that can help you see how the value of a cell compares with other cells. Learn how to implement and manipulate data bars programmatically through the user interface. (7 printed pages)

Frank Rice, Microsoft Corporation

February 2007

Applies to: 2007 Microsoft Office Suites, Microsoft Office Excel 2007

Contents

Overview
Easily Visualize Your Data
Adding Data Bars from the UI
How Does It Work?
Working with Data Bars Programmatically
Adding Data Bars by Customizing the Ribbon
To Summarize
All About Conditional Formatting in Excel 2007

Overview

In a previous column, I discussed some of the enhancements made to conditional formatting in Microsoft Office Excel 2007. Three new visualizations are in the list of enhancements: data bars, color scales, and icon sets. In this column, I focus on data bars. In future columns, I explore color scales and icon sets.

Easily Visualize Your Data

Data bars enable you to quickly comprehend large quantities of information; for example, you can see outliers, spot trends, and compare values. Data bars enable you to select a range of cells and, with one click, apply a conditional format that makes it easier to see the value of a cell relative to all other cells that you selected. For example, say that you have the range of data shown in Figure 1, and you want to make it easy to spot the large numbers and small numbers.

Figure 1. The range of data before formatting

If you select that range, and apply data bars, the range now looks like Figure 2.

Figure 2. The range of data after formatting

Now, with a single glance, you can see which values are the largest and smallest, and how each value compares to its neighbor.

Adding Data Bars from the UI

To see the advantage of data bars for yourself:

Open a blank worksheet in Excel 2007.
Type a range of four or five numbers into column A.
Highlight the range and, on the Home tab, in the Styles group, click the down arrow next to Conditional Formatting.
Point to Data Bars and choose one of the styles in the gallery.

Data bars are inserted into each cell of your data, giving you a visual representation of the value of each number in comparison with the other values.

How Does It Work?

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 easier 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 in Figure 3 how this works if you change the first two cells to higher values (19,000 and 15,000, respectively).

Figure 3. Changes cause the bars to recalculate

The bars in all the cells adjust accordingly—as with all other conditional formatting, data bars are reapplied after calculation or data refresh so you always see an accurate picture of the data set.

Of course, many times you might not want to use the minimum and maximum values in a range. As you might suspect, Excel makes this easy to configure by using the New Formatting Rule dialog box, as shown in Figure 4.

Figure 4. The New Formatting Rule dialog box

Besides the color of the data bar, for both the shortest bar and the longest bar you can specify Lowest (Highest) Value, Number, Percent, Formula, and Percentile (see Figure 5).

Figure 5. Options for the type of formatting rule

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, you type the values that should receive the shortest and longest bars. For numbers that are higher or lower than that value, Excel simply draws the shortest or longest bar, as is appropriate.
For Percent, you type 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 that have values less than 50 would have the shortest bar drawn in the cell.
Formula allows you to specify a formula. The formula is evaluated to determine the value used for the shortest and longest bar. This is useful for developing conditional formats that are not easily handled by any of these choices.
Finally, Percentile differs from Percent in that it does not determine percentages within the range of values. Rather, Percentile examines the set of values contained in the cells, sets their order, and uses their ordinal value or position within the set of ordered values to determine their percentile. In a set of ten ordered cells, the fortieth percentile would always be the fourth cell, regardless of the value contained within it. Thus if you select Percentile and enter 40 for the shortest bar, then the cells at the fortieth percentile and below have the shortest bar drawn in the cell.

Working with Data Bars Programmatically

In addition to working with conditional formatting from the user interface, you can access the new conditional formatting features by using Microsoft Visual Basic for Applications (VBA). The DataBar object is contained in the FormatConditions collections object, which is a child of the Range object. You can create a data bar formatting rule by using either the Add method or the AddDatabar method of the FormatConditions collection. You use the MinPoint and MaxPoint properties of the Databar object to set the values of the shortest bar and longest bar of a range of data. These properties return a ConditionValue object, in which you can specify how to evaluate the thresholds.

Adding Data Bars by Customizing the Ribbon

In the following example, you add a button to a custom tab on the Ribbon in Excel 2007. When you click the button, the VBA procedure adds a range of data to the worksheet and then applies a data bar to the range. If you read my previous column about conditional formatting, the procedure to customize the Ribbon is the same; you add the button by modifying the Excel 2007 Open XML Formats file.

Note

You can also get the same result by using a COM add-in project to modify the Ribbon. For more information about customizing the Ribbon, see the three-part series of articles that start with Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).

To summarize the process:

Create an XML file containing the custom Ribbon code and save the file in a folder on the Microsoft Windows desktop.
Create a workbook and add a VBA subroutine that inserts the data and sets the behavior of the data bars.
Save the workbook as a macro-enabled file with the extension .xlsm.
Open the file as a compressed (zipped) folder.
Add the folder containing the XML file to the zipped folder.
Modify the relationship file in the folder to point to the XML file.
Remove the .zip extension from the folder name and open the file in Excel.
On the custom tab created from the XML file, click the button. The data is inserted and the data bars are added.

Creating the XML Customization File

In this section, you create the XML file that adds a tab, a group, and a button to the existing Ribbon:

On the Windows desktop, create a folder named customUI.

Using any text editor, such as Notepad, create the customization file by inserting the following XML:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="CustomTab" label="Data Bars Demo">
            <group id="SimpleControl" label="Conditional Format">
               <button id="Button1" imageMso="Chart3DBarChart" size="large"
                  label="Add Data Bars"
                  onAction="ThisWorkbook.CreateDataBarCF"/>
            </group>
          </tab>
      </tabs>
   </ribbon>
</customUI>

Save the file as customUI.xml in the customUI folder.

Creating the Macro-Enabled Workbook

In this section, you create the macro-enabled workbook containing the VBA code:

Start Excel 2007 and open a blank workbook. Keep the default name.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.

Note

If the Developer tab does not appear in your workbook, do the following:
1. On the File menu (the large Microsoft Office button), click Excel Options.
2. On the Popular tab, select the Show Developer tab in the Ribbon option.
3. Click OK to close the dialog box.
In the Project pane, double-click the ThisWorkbook node.

In the code window, insert the following procedure:

Sub CreateDataBarCF()

Dim cfDataBar As Databar

'Create a range of data with some extreme values.
With ActiveSheet
   .Range("D1") = 1
   .Range("D2") = 45
   .Range("D3") = 50
   .Range("D2:D3").AutoFill Destination:=Range("D2:D8")
   .Range("D9") = 500
End With

Range("D1:D9").Select

'Create a data bar with default behavior.
Set cfDataBar = Selection.FormatConditions.AddDatabar
MsgBox "Because of the extreme values, the middle bars are very similar"

'The MinPoint and MaxPoint properties return a ConditionValue object
'that you can use to change the threshold parameters.
cfDataBar.MinPoint.Modify newtype:=xlConditionValuePercentile, _
   newvalue:=5
cfDataBar.MaxPoint.Modify newtype:=xlConditionValuePercentile, _
   newvalue:=75

End Sub

Quickly looking at the procedure, first, the range of data is assigned to cells in the worksheet and the range is selected. Then, data bars are added to the range. Notice that, because there is an extremely low value and an extremely high value in the range, the middle values have data bars that are of similar length. To disambiguate the middle values, the sample code uses the ConditionValue object to change how the thresholds are evaluated to "percentiles."

Preparing the Office Open XML Formats File

Now, add the XML customization file to the workbook file and modify the relationships part to point to the XML file:

In Windows Explorer, add the file extension, .zip, to the workbook file name, and then double-click the file to open it as a compressed (zipped) folder.
Add the customization file to the container by dragging the customUI folder from the Windows desktop to the compressed folder.
Next, drag the _rels folder from the compressed folder to the Windows desktop.
Open the folder and then open the .rels file in a text editor.

Between the final <Relationship> tag and the closing </Relationships> tag, add the following line:

<Relationship Id="customUIRelID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

Save the .rels file.
Drag the .rels file from the Windows desktop to the _rels folder in the compressed folder, replacing the existing file.

Testing the Solution

Finally, open the file in Excel and test the process:

In Windows Explorer, remove the .zip extension from the container file name.
Open the file in Excel 2007.
Click the Data Bars Demo tab.
Next, click the Add Data Bars button. A data range is inserted into the worksheet and data bars are added to the range, as shown in Figure 6.

Figure 6. Data bars have been added to the data range
Click OK to close the dialog box.
Expand column D. Notice, as seen in Figure 7, that the data bars have changed to give you a better delineation of the data.

Figure 7. The data bars have been expanded to give a better view of the data

As stated earlier, the values in the middle of the range are similar and, because of the extreme values of the boundary data, they are difficult to distinguish from one another. By setting the threshold parameters to "Percentile", the data bars are assigned to each value based on its position in the range rather than its numeric value.

To Summarize

Data bars make it easy to see at a glance how data values relate to each other. You have also seen how easy it is to add data bars to your data by using the conditional formatting built into Excel 2007 and how to add additional conditional formatting programmatically. Changing the thresholds and how the data is evaluated is also a snap.

In future columns, I will discuss other aspects of the new conditional formatting enhancements in Excel 2007, including how to add other visualizations such as color scales and icon sets, and some of the new rules that you can apply to your data.

All About Conditional Formatting in Excel 2007

For more in this series about conditional formatting in Excel 2007, see the following resources: