Conditional Formatting



Copyright@1997 by David Hager

Chapter Outline – Conditional Formatting

Overview

How It Works

Using Cell Values to Format Data

Combining Old and New Conditional Formatting

More About Operators

Data Protection

Viewing Differences in Data

Refining the Process with a Spinner Control

Using a Formula to Format Data

Viewing Data Above and Below the Average

Conditional Formatting as a Guide to Data Entry

Spotting Duplicate Data Entry

Formatting for Data Quality

Using Excel’s Macro Functions for Formatting

Creating an Autoformat that Appears Like Magic

Modifying Hyperlink Formats

Viewing Sequential Data by Direction

Formatting Dates and Merged Cells in a Schedule

Locating the Last Action with Conditional Formatting

Using Conditional Formatting to Validate a Sorted Column

Finding Cells that have Conditional Formats

Data Filtering and Conditional Formatting

Viewing Maximum and Minimum Values in a Filtered List

Applying Custom Functions to Filtered Lists

Format-Filtering a Data List

Using a Complex Formula for Numeric Fields

Multi-Sheet Data Comparison by Conditional Formatting

Summary

OVERVIEW

Well, it’s finally here. For many years Excel users have been asking for the ability to create an effect whereby a worksheet cell is formatted based on the value in a cell or the result of a formula. With the release of the latest version of Excel, this spreadsheet dream has become a reality. The new conditional formatting feature of Excel 97 now allows the user to greatly enhance their ability to scan a spreadsheet visually and easily obtain information about their data. Versions of Excel prior to Excel 97 (or Excel 8) used a form of conditional formatting that would change the font color of cell entries based on their value. For example, selecting Format, Cells, Number from the menu and typing a custom format of [Blue]General; [Red]”-“General;[Green]General in the edit box will format a range of cells so that positive values are blue, negative values are red and zero values are green. This form of formatting is still is available in Excel 97, but its new conditional formatting feature allows for a myriad of cell formatting options.

HOW IT WORKS

Below are the general guidelines to use when setting up a conditional format.

1. Select the range of cells to be formatted.

Note: The range selection to be conditionally formatting can be non-contiguous (more than one range selected). In order to make multiple selections, press Shift and F8 after making the first selection. Then, highlight the other range(s) to be added. The selection can be extended to other worksheets within a workbook by grouping the sheets before the selection is made. This can be done by holding down the Ctrl key while clicking the sheet tabs for the worksheets to be grouped.

2. Select Format, Conditional Formatting from the main menu to bring up the conditional formatting dialog box. If more than one format is needed, click the Add>> button to expand the dialog box to add condition 2 (or again for condition 3).

Conditional Formatting Dialog Box

3. Based on your formatting requirements, there are four basic approaches that are available to be used in the conditional formatting process. The four identical examples in Figure CCF illustrate the possibilities in this regard.

Note: By design, the application of conditional formats works on a hierarchical structure. A total of three conditional formats are allowed. If a cell has conditional formats 1 & 2, and both of the conditions are TRUE, only the format associated with condition 1 will be applied to the cell. The same is true for conditional formats 2 & 3.

Figure CCF

In each case, the object is to format all cell values in the range to have a red font if =AVEDEV($A$2:$A$20)*2 with Format Font blue

Formula Is: =ABS(AVERAGE($A$2:$A$20)-A2)>=AVEDEV($A$2:$A$20) with Format Font magenta

These formulas return TRUE if a data point in the table is greater or less than the average of the data by an amount determined by the part of the formula to the right of the >= operator. This example illustrates that the order of formulas used in conditional formatting is in many cases critical to the success of the model. It is important that the formula that formats values that are three absolute deviations from the mean be used with the first conditional format, so that values that might fit the criteria for 2 or 1 absolute deviation(s) are formatted correctly.

VALIDATING WITH CONDITIONAL FORMATTING

In many cases it is important that data entry be restricted in some way. This is a common feature of most database products. Now, in Excel97, by using conditional formatting along with data validation (which is also a new feature in Excel97) it is possible to restrict data entry in some quite useful ways. For example, when entering advertiser information in a record table, there may be a need to make sure that only the name of the advertisers found in the approved list of advertisers is a valid entry. There are two other basic features that are being used for this model. One is that the master list should not be stored on the same worksheet as the worksheet used for data entry. The other feature is that the master list must be dynamic, so those new advertisers can be added without any modification to the formulas used in the conditional format. All of this is accomplished by the formulas shown below, used in a conditional formatting framework.

Define lrange =OFFSET('CF8'!$E$2,,,COUNTA('CF8'!$E:$E)-1,1)

Note: Formulas that include sheet references that are not on the sheet where they are being used cannot be used directly as a conditional format formula. However, they can be used when they are used as a defined name formula, as demonstrated in this example.

The formula that is defined as lrange returns a worksheet range located on sheet CF8 that starts at cell E2 and includes all of the cells below E2 that contain entries. Then, if a new advertiser is added to the next empty cell in column E of that sheet, lrange automatically includes this new entry. The conditional formatting formula incorporates lrange and it is applied to all of the cells in column A.

Formula Is: =AND(A1"",ISNA(MATCH(A1,lrange,0))) with Format solid border outline.

The data validation feature also uses a formula to act as the trigger for displaying a message box to inform the user that the entry was not found in the list, as well as providing a dropdown box of the advertiser list to aid in the insertion of list items. The cells in column A were set with these data validation conditions.

Settings: Allow: list , Source: =lrange

Input Message: None

Error Alert: Style: Information

Validation Message Box

The error box message is shown in Fig. CF7. Each time a cell is highlighted in column A, the dropdown box appears with the list of items from the dynamic list range. If an item is selected from the dropdown, the conditional format is not applied, since the item is from the list. However, if a new item is typed into the cell, the error message appears to inform the user that the item needs to be added to the master list. The conditional format (which is this case is a dark outline around the cell) then reminds the user that the item is not yet in the list. When the item is added to the list, the outline format disappears.

USING EXCEL’S MACRO FUNCTIONS FOR FORMATTING

Besides being able to use worksheet functions and VBA custom functions in conditional formats, it turns out that there is a wealth of xlm (the old Excel macro language) information functions buried in the worksheet interface that are ideal for a numbers of neat formatting tricks. However, these functions cannot be entered directly into a worksheet cell (or the conditional formatting dialog box) as part of a formula. Instead, they must be given defined names through Insert, Name, Define in the main menu in order to be used. The name that you want to use for the formula goes in the Names in workbook box, and the formula is typed in the Refers to box. This same technique can be used on “normal” formulas, examples of which are shown throughout this chapter.

Caution: The use of xlm information functions in defined name formulas is undocumented. Thus, there is no guarantee that future versions of Excel will support this feature. It is also important to note that these functions are non-volatile, which means that they may not recalculate as expected. In cases where the xlm function returns a value, it can be made volatile by adding “+NOW()-NOW()” to the end of the defined name formula. Also, note that cells that have conditional formats that use xlm functions cannot be copied /

pasted to another worksheet.

As an example of just how useful these forgotten functions can be, the following technique allows for the conditional formatting of all cells containing formulas on a worksheet, as well as the ability to differentiate between normal formulas and array formulas.

Define aformula as: =GET.CELL(49,'CF9'!A1)

Define fformula as: =GET.CELL(48,'CF9'!A1)

1) Formula Is: =aformula with Format Font Red & Bold

2) Formula Is: =fformula with Format Font Black & Bold

The formula =aformula is used as the first conditional format , since it will return TRUE for both an array formula and a normal formula. In the example shown in Figure CF9, the cells A5, A9, A15, D5 and D9 contain regular SUM formulas for the values above each of those cells, whereas the cell D15 contains the array formula =SUM((D10:D14 1 Then

.Borders.LineStyle = xlContinuous

.Borders.Weight = xlThin

.Interior.ColorIndex = cInd

.HorizontalAlignment = xlCenter

End If

End With

Next

End Sub

The initialization process is done by running InitFE01, which creates an instance of the Application object named fObj1.formatEvent1. Then, the formatting procedure will run automatically each time a change is made in any worksheet of all open workbooks.

This process serves as a general way to apply custom formatting and it leaves open the option of using conditional formatting for other purposes. However, one distinct advantage that the in-cell conditional formatting provides is that when an entry in a cell is deleted, the underlying format appears. As pointed out earlier in this section, this format does not have to be the default cell format. This feature would be difficult to accomplish without in-cell conditional formatting.

MODIFYING HYPERLINK FORMATS

One of the new features of Excel97 is the HYPERLINK function. When this function is used in a worksheet cell, a hyperlink is created as defined in the formula that allows the user to click on it to go to the linked file. The format of these hyperlinks is a font of Arial 7.5, blue and underlined. The hyperlinks that can be created in Excel97 allow the user to “click and go” to web pages, files that are accessed through file transfer protocol (FTP) on the Internet and local files (on the desktop or LAN). Through the use of conditional formatting, formats can be made that differentiate these 3 types of hyperlinks.

=HYPERLINK(" report.xls", "Click for report") for web page

=HYPERLINK("", “Get Microsoft file”) for ftp file

=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5) for local file

Define GlobalIsWebPage as:

=NOT(ISERROR(FIND("http:",GET.CELL(6,globref))))

Define GlobalIsFTP as:

=NOT(ISERROR(FIND("ftp:",GET.CELL(6,globref))))

Formula Is: = GlobalIsWebPage with Format

Formula Is: = GlobalIsFTP with Format

There is a type of doubleclicking goto feature that has been in Excel since version 4. This involves the doubleclicking of a cell that contains a linked formula. When this is done, the first reference that appears in the formula will be opened or activated. In order to use this feature, select Tools, Options, Edit from the menu and uncheck the “Edit directly in cell” box. A way to format cells containing linked formulas is shown below. This method assumes that the symbol “!” is performing a linking role in the formula.

Define GlobalIsLinked as: =AND(ISERROR(FIND("HYPERLINK",GET.CELL(6,globref))),NOT(ISERROR(FIND("!",GET.CELL(6,globref)))))

Formula Is: =GlobalIsLinked with Format

GlobalIsLinked returns TRUE if the cell formula has an exclamation point symbol and the formula does not contain the word “HYPERLINK”.

VIEWING SEQUENTIAL DATA BY DIRECTION

The conditional formatting technique utilized in Fig. CF12 is a prime example of how simple formulas can be used to create a powerful visual effect. The data list represents the collection of values for a stock market index. The charting of this data allows the user to see the intervals where this market is going up or down. With the application of the following conditional format, the actual data can be viewed from this perspective as well. The following conditions were applied to the cells in column A, excluding A1.

1) Formula Is: =A2>A1 Format Patterns Green Cell Shading

2) Formula Is: =A2TODAY()-WEEKDAY(TODAY()),A2=A$2:INDEX(A:A,COUNTA(A:A))) ,AND(A$1:INDEX(A:A,COUNTA(A:A)-1)=A$2:INDEX(A:A,COUNTA(A:A)))

and

AND(A$1:INDEX(A:A,COUNTA(A:A)-1) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download