Using Conditional Number Formats On Your Web Page

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.

Wade Tai
Microsoft Corporation

Created: November 1999

Applies to: Microsoft Excel 2000

Summary: This tip describes how to implement custom number formats when you publish your Microsoft Excel workbook or worksheet to a Web page. It describes how to use the NumberFormat property to specify conditional font color shading. (3 printed pages)

In Microsoft® Excel, you can use the NumberFormat property to apply conditional number formats to cells in a spreadsheet. A conditional number format uses custom number format codes that describe how you want to display a number. You can specify up to three conditions of format codes. The conditions are separated by semicolons. A common use for conditional number formats is the emphasis of numbers and patterns of numbers in different color shadings that depend on cell values. This type of simple conditional formatting is often referred to as "traffic-light" formatting.

When you publish the spreadsheet or workbook to a Web page, you can also use the NumberFormat property to conditionally format numbers displayed in the PivotList and Spreadsheet components. Unlike Excel, you can specify up to four conditions of format codes; however, the data cannot be exported to Excel if you use the fourth condition.

To set the color for a section of the format, specify the English name of the color in square brackets in the section. You must use the English name even if you are using a non-English-language version of Excel. You can also specify the color index by using the syntax Color n where n is the index. The color code must be the first item in the section, and only the first 8 colors in the palette are supported. The following table lists the colors and the equivalent color index.

To set number formats that will be applied only if the number meets a condition you specify, enclose the condition in square brackets; the condition consists of a comparison operator and a value. You can choose from the comparison operators listed in the following table.

For example, the following script formats numbers less than 10 in a red font with two digits to the right of the decimal, numbers less than or equal to 50 in a yellow font with one digit to the right of the decimal, numbers less than 400 as green integers, and numbers greater than or equal to 400 as magenta integers.

<script language=VBScript><!--
Sub FormatCells()
   Set oInvSS = NumFmt_20284_WebCalc
   oInvSS.Range("B2:B6").NumberFormat = _
                "[Red][<10]#0.00;[Yellow]" _
                "[<=50]#0.0;[Green][<400]" _
                "##0;[Magenta][>=400]###0"
end sub

--></script>

Note that for the PivotList component, any formatting conditions you apply to your totals are also applied to subtotal and grand total rows and columns when they are visible. For PivotList fields, the conditional format color is not applied when a field is in the column or row axis.

To see the code at work

1. Open a worksheet in Excel. Enter a variety of numbers in the cells.

2. From the File menu, click Save as Web Page. On the Save As dialog box, click the Publish button.

3. On the Publish as Web Page dialog box, under the Viewing options section, click the Add Interactivity with check box and choose Spreadsheet functionality.

4. At the bottom of that dialog box, click the check box for Open published web page in browser, and then click Publish.

   Your browser should now display a Spreadsheet component with the numbers you entered in the worksheet—but in black and white. To see the conditional formatting, do the following steps.

5. Select Source from the View menu in the browser, and save the resulting Notepad file to your hard drive (C:\Documents and Settings\ is the default location).

6. Paste the script from above into the source code file, at the bottom, after the </div> tag.

7. Search the file (CTRL+F) for <object to find the object ID that was assigned to the Spreadsheet component. In the code you pasted at the bottom, replace the text NumFmt_20284_WebCalc with the object ID.

8. To make the code actually run, replace the name of the VBScript function (Sub FormatCells()) with Sub Window_onload(). Save the file; switch to the browser window; press F5 to refresh the screen; and watch the numbers change color.