MS Office Formulas

Excel in a Nutshell

Microsoft Excel has been referred to as "the best application ever written for Windows." You may or may not agree with that statement, but you can't deny that Excel is one of the oldest Windows products and has undergone many re- incarnations and face-lifts over the years. Cosmetically, the current version-Excel 2007-barely even resembles the original version. However, many of Excel's key elements have remained intact over the years, with significant enhancements, of course.
This chapter presents a concise overview of the features available in the more recent versions of Excel, with specific emphasis on Excel 2007. It sets the stage for the subsequent chapters and provides an overview for those who may have let their Excel skill get rusty.

If you're an old hand at Excel, you may want to read only the section on the Excel user interface and ignore or briefly skim the rest of the chapter.

The History of Excel

You probably weren't expecting a history lesson when you bought this book, but you may find this information interesting. At the very least, this section provides fodder for the next office trivia match.
Spreadsheets comprise a huge business, but most of us tend to take this software for granted. In the pre-spreadsheet days, people relied on clumsy mainframes or calculators and spent hours doing what now takes minutes.

It Started with VisiCalc

Dan Bricklin and Bob Frankston conjured up VisiCalc, the world's first electronic spread- sheet, back in the late 1970s when personal computers were unheard of in the office environment. They wrote VisiCalc for the Apple II computer, an interesting machine that seems like a toy by today's standards. VisiCalc caught on quickly, and many forward-looking companies purchased the Apple II for the sole purpose of developing their budgets with VisiCalc. Consequently, VisiCalc is often credited for much of Apple II's initial success.

Then Came Lotus

When the IBM PC arrived on the scene in 1982, thus legitimizing personal computers, VisiCorp wasted no time porting VisiCalc to this new hardware environment. Envious of VisiCalc's success, a small group of computer enthusiasts at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathon Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. Released in January 1983, Lotus Development Corporation's 1-2-3 proved an instant success. Despite its $495 price tag (yes, people really paid that much for a single program), it quickly outsold VisiCalc and rocketed to the top of the sales charts, where it remained for many years. Lotus 1-2-3 was, perhaps, the most popular application ever.

Microsoft Enters the Picture

Most people don't realize that Microsoft's experience with spreadsheets extends back to the early 1980s. In 1982, Microsoft released its first spreadsheet-MultiPlan. Designed for computers running the CP/M operating system, the product was subsequently ported to several other platforms, including Apple II, Apple III, XENIX, and MS-DOS. MultiPlan essentially ignored existing software user-interface standards. Difficult to learn and use, it never earned much of a following in the United States. Not surprisingly, Lotus 1-2-3 pretty much left MultiPlan in the dust.
Excel partly evolved from MultiPlan, first surfacing in 1985 on the Macintosh. Like all Mac applications, Excel was a graphics-based program (unlike the character-based MultiPlan). In November 1987, Microsoft released the first version of Excel for Windows (labeled Excel 2 to correspond with the Macintosh version). Excel didn't catch on right away, but as Windows gained popularity, so did Excel. Lotus eventually released a Windows version of 1-2-3, and Excel had additional competition from Quattro Pro-originally a DOS program developed by Borland International, then sold to Novell, and then sold again to Corel (its current owner).

Excel Versions

Excel 2007 is actually Excel 12 in disguise. You may think that this name represents the twelfth version of Excel. Think again. Microsoft may be a successful company, but its version-naming techniques can prove quite confusing. As you'll see, Excel 2007 actually represents the tenth Windows version of Excel. In the following sections, I briefly describe the major Windows versions of Excel.


The original version of Excel for Windows, Excel 2 first appeared in late 1987. It was labeled Version 2 to correspond to the Macintosh version (the original Excel). Because Windows wasn't in widespread use at the time, this version included a runtime version of Windows-a special version with just enough features to run Excel and nothing else. This version appears quite crude by today's standards.
 The original Excel 2 for Windows. Excel has come a long way since its original version. (Photo courtesy of Microsoft Corporation)


At the end of 1990, Microsoft released Excel 3 for Windows. This version offered a significant improvement in both appearance and features. It included toolbars, drawing capabilities, worksheet outlining, add-in support, 3-D charts, workgroup editing, and lots more.


Excel 4 hit the streets in the spring of 1992. This version made quite an impact on the marketplace as Windows increased in popularity. It boasted lots of new features and usability enhancements that made it easier for beginners to get up to speed quickly.


In early 1994, Excel 5 appeared on the scene. This version introduced tons of new features, including multisheet workbooks and the new Visual Basic for Applications (VBA) macro language. Like its predecessor, Excel 5 took top honors in just about every spreadsheet comparison published in the trade magazines.


Excel 95 (also known as Excel 7) shipped in the summer of 1995. On the surface, it resembled Excel 5 (this version included only a few major new features). However, Excel 95 proved to be significant because it presented the first version to use more advanced 32-bit code. Excel 95 and Excel 5 use the same file format.


Excel 97 (also known as Excel 8) probably offered the most significant upgrade ever. The toolbars and menus took on a great new look, online help moved a dramatic step forward, and the number of rows available in a worksheet quadrupled. And if you're a macro developer, you may have noticed that Excel's programming environment (VBA) moved up several notches on the scale. Excel 97 also introduced a new file format.

EXCEL 2000

Excel 2000 (also known as Excel 9) was released in June of 1999. Excel 2000 offered several minor enhancements, but the most significant advancement was the ability to use HTML as an alternative file format. Excel 2000 still supported the standard binary file format, of course, which is compatible with Excel 97.

EXCEL 2002

Excel 2002 (also known as Excel 10) was released in June of 2001 and is part of Microsoft Office XP. This version offered several new features, most of which are fairly minor and were designed to appeal to novice users. Perhaps the most significant new feature was the capability to save your work when Excel crashes and also recover corrupt workbook files that you may have abandoned long ago. Excel 2002 also added background formula error checking and a new formula-debugging tool.

EXCEL 2003

Excel 2003 (also known as Excel 11) was released in the fall of 2003. This version had very few new features. Perhaps the most significant new feature was the ability to import and export XML files and map the data to specific cells in a worksheet. It also introduced the concept of the List, a specially designated range of cells. Both of these features would prove to be precursors to future enhancements.

EXCEL 2007

Excel 2007 (also known as Excel 12) was released in early 2007. Its official name is Microsoft Office Excel 2007. This latest Excel release represents the most significant change since Excel 97, including a change to Excel's default file format. The new format is XML based although a binary format is still available. Another major change is the Ribbon, a new type of user interface that replaces the Excel menu and toolbar system. In addition to these two major changes, Microsoft has enhanced the List concept introduced in Excel 2003 (a List is now known as a Table), improved the look of charts, significantly increased the number of rows and columns, and added some new worksheet functions. For more, see the sidebar, "What's New in Excel 2007?".

XML (extensible Markup Language) stores data in a structured text format. The new file formats are actually compressed folders that contain several different XML files. The default format's file extension is .xlsx. There's also a macro-enabled format with the extension .xlsm, a new binary format with the extension .xlsb, and all the legacy formats that you're used to.

Microsoft Office Excel  2007 Change formula recalculation, iteration, or precision

To use formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) efficiently, there are three important considerations that you need to understand:

Calculation     is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. To avoid unnecessary calculations, Microsoft Office Excel automatically recalculates formulas only when the cells that the formula depends on have changed. This is the default behavior when you first open a workbook and when you are editing a workbook. However, you can control when and how Excel recalculates formulas.

Iteration     is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. Circular references can iterate indefinitely. However, you can control the the maximum number of iterations and the amount of acceptable change.

Precision     is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.
What do you want to do?


Change when a worksheet or workbook recalculates

As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.
  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Formulas category.
  1. Do one of the following:
    • To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.
      1.  Tip   Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
  • To recalculate all dependent formulas—except data tables—every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.
  1.  Tip   Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic Except for Data Tables.
  • To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calculate Now under Calculation Options in the Calculation group on the Formulas tab), in the Calculation options section, under Workbook Calculation, click Manual.
  1.  Note   When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing Recalculate workbook before saving may improve the save time.
  2.  Tip   Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Manual.
  • To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.
  • To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, on the Formulas tab, in the Calculation group, click the Calculate Sheet button.
Changing any of the options affects all open workbooks.
 Note   If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.

Recalculate a worksheet or workbook manually by using keyboard shortcuts

Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation.

Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.
Recalculate all formulas in all open workbooks, regardless of whether they have changed since last time or not.
Recheck dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since last time or not.


Change the number of times Excel iterates a formula

  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Formulas category.
  1. In the Calculation options section, select the Enable iterative calculation check box.
  2. To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet.
  3. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet.
 Note   Solver and Goal Seek are part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. Both commands use iteration in a controlled way to obtain desired results. You can use Solver when you need to find the optimum value for a particular cell by adjusting the values of several cells or when you want to apply specific limitations to one or more of the values in the calculation. You can use Goal Seek when you know the desired result of a single formula but not the input value the formula needs to determine the result.

Change the precision of calculations in a workbook

Before you change the precision of calculations, keep in mind the following important points:

By default, Excel calculates stored, not displayed, values     The displayed and printed value depends on how you choose to format and display the stored value. For example, a cell that displays a date as "6/22/2008" also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to "22-Jun-2008"), but changing the display of a value on a worksheet does not change the stored value.

Use caution when changing the precision of calculations     When a formula performs calculations, Excel usually uses the values stored in cells referenced by the formula. For example, if two cells each contain the value 10.005 and the cells are formatted to display values in currency format, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Excel adds the stored values 10.005 and 10.005, not the displayed values.

When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. If you later choose to calculate with full precision, the original underlying values cannot be restored.
  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Advanced category.
  1. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.


Change the number of processors used to calculate formulas

A computer can have more than one processor (it contains multiple physical processors) or can be hyperthreaded (it contains multiple logical processors). On these computers, you can improve or control the time it takes to recalculate workbooks that contain many formulas by setting the number of processors to use for recalculation. In many cases, portions of a recalculation workload can be performed simultaneously. Splitting this workload across multiple processors can reduce the overall time it takes complete the recalculation.
  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Advanced category.
  1. To enable or disable the use of multiple processors during calculation, in the Formulas section, select or clear the Enable multi-threaded calculation check box.
    •  Note   This check box is enabled by default and all processors are used during calculation. The number of processors on your computer is automatically detected and displayed next to the Use all processors on this computer option.
  2. Optionally, if you select Enable multi-threaded calculation, you can control the number of processors to use on your computer. For example, you might want to limit the number of processors used during recalculation if you have other programs running on your computer that require dedicated processing time.

    • ShowHow to control the number of processors
  • Under Number of calculation threads, click Manual. Enter the number of processes to use.
 Note   The maximum number is 1024.


Learn about calculating workbooks that were created in an earlier version of Excel

To ensure that older workbooks are calculated correctly, Excel behaves differently when you first open an old workbook than when you open a workbook created in the current version.

When you open a workbook created in the current version, Excel recalculates only the formulas that depend on cells that have changed.

When you use open a workbook that was created in a earlier version of Excel, all of the formulas in the workbook — those that depend on cells that have changed and those that do not — are recalculated. This ensures that the workbook is fully optimized for the current Excel version.

Because complete recalculation can take longer than partial recalculation, opening a workbook that was not previously saved in the current Excel version can take longer than usual. Once you save the workbook in the current version, it will open faster.

Microsoft Office Excel  2007 Creating Megaformulas

This chapter describes a useful technique that lets you combine several formulas into a single formula-what I call a megaformula. This technique can eliminate intermediate formulas and may even speed up recalculation. The downside, as you'll see, is that the resulting formula is virtually incomprehensible and may be impossible to edit.

What Is a Megaformula?


Often, a worksheet may require intermediate formulas to produce a desired result. In other words, a formula may depend on other formulas, which in turn depend on other formulas. After you get all these formulas working correctly, you often can eliminate the intermediate formulas and create a single (and more complex) formula. For lack of a better term, I call such a formula a megaformula.
What are the advantages of employing megaformulas? They use fewer cells (less clutter), and recalculation may be faster. And, you can impress people in the know with your formula-building abilities. The disadvantages? The formula probably will be impossible to decipher or modify, even by the person who created it.

I used the techniques described in this chapter to create many of the complex formulas presented elsewhere in this book

Microsoft Office Excel  2007 Conditional Formatting and Data Validation

This chapter explores two very useful Excel features: conditional formatting and data validation. You may not think these features have much to do with formulas. As you'll see, though, when you toss formulas into the mix, conditional formatting and data validation can perform some amazing feats.
Conditional Formatting
Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells. For example, you can set things up such that all negative values in a range have a light yellow background color. When you enter or change a value in the range, Excel examines the value and evaluates the conditional formatting rules for the cell. If the value is negative, the background is shaded. If not, no formatting is applied.

Conditional formatting has improved significantly in Excel 2007 and is now even more useful for visualizing numeric data. In some cases, you may be able to use conditional formatting in lieu of using a chart.

Conditional formatting is a useful way to quickly identifying erroneous cell entries or cells of a particular type. You can use a format (such as bright red cell shading) to make particular cells easy to identify.
  • Greater than 10: Values greater than 10 are highlighted with a different background color. This rule is just one of many numeric value related rules that you can apply.
  • Above average: Values that are higher than the average value are highlighted.
  • Duplicate values: Values that appear more than one time are highlighted.
  • Words that contain X: If the cell contains the letter X (upper- or lowercase), the cell is highlighted.
  • Data bars: Each cell displays a horizontal bar, proportional to its value.
  • Color Scale: The background color varies, depending on the value of the cells. You can choose from several different color scales or create your own.
  • Icon Set: This is one of many icon sets, which display a small graphic in the cell. The graphic varies, depending on the cell value.
  • Icon Set: This is another icon set.
  • Custom Rule: The rule for this checkerboard pattern is based on a formula:

Specifying Conditional Formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Styles Conditional Formatting drop-down to specify a rule. The choices are
  • Highlight Cell Rules: Examples rules include highlighting cells that are greater than a particular value, are between two values, contain specific text string, or are duplicated.
  • Top Bottom Rules: Examples include highlighting the top ten items, the items in the bottom 20 percent, or items that are above average.
  • Data Bars: This applies graphic bars directly in the cells, proportional to the cell's value.
  • Color Scales: This applies background color, proportional to the cell's value.
  • Icon Sets: This displays icons directly in the cells. The icons depend on the cell's value.
  • New Rule: This enables you to specify other conditional formatting rules, including rules based on a logical formula.
  • Clear Rules: This deletes all the conditional formatting rules from the selected cells.
  • Manage Rules: This displays the Conditional Formatting Rules Manager dialog box, in which you create new conditional formatting rules, edit rules, or delete rules.
Excel 2007 Improvements
If you've used conditional formatting in a previous version of Excel, you'll find lots of improvements in Excel 2007, and the feature is now much easier to use. Many conditional formatting rules that previously required a formula are now built in. Improvements include the following:
  • In the past, it was far too easy to accidentally wipe out conditional formatting by copying and pasting a range of cells to cells that contain conditional formatting. This problem has been corrected in Excel 2007.
  • Excel 2007 includes conditional formatting visualizations based on a range of data. These visualizations include data bars, color scales, and icon sets.
  • You're no longer limited to three conditional formatting rules per cell. In fact, you can specify any number of rules.
  • In the past, if more than one conditional formatting rule evaluated to true, only the first conditional format was applied. In Excel 2007, all the format rules are applied. For example, assume that you have a cell with two rules: One rule makes the cell's contents italic, and another rules makes the background color green. If both conditions are true, both formats are applied. When conflicts arise, (for example, red background versus green background), the first rule is used.
  • Excel 2007 allows number formatting to result from conditional formatting.
  • In previous versions, a conditional formatting formula could not reference cells in a different worksheet. Excel 2007 removes that restriction.
When you select a conditional formatting rule, Excel displays a dialog box that's specific to that rule. These dialog boxes have one thing in a common: a drop-down list with common formatting suggestions. shows the dialog box that appears when you choose Home Styles Conditional Formatting Highlight Cells Rules Between. This particular rule applies the formatting if the value in the cell falls between two specified values. In this case, you enter the two values (or enter cell references) and then use the drop-down control to choose the type of formatting to display if the condition is met.

The formatting suggestions in the drop-down control are just a few of thousands of different formatting combinations. In most cases, none of Excel's suggestions are what you want, so you choose the Custom Format option to display the Format Cells dialog box. You can specify the format in any or all of the four tabs: Number, Font, Border, and Fill.

The Format Cells dialog box used for conditional formatting is a modified version of the standard Format Cells dialog box. It doesn't have the Number, Alignment, and Protection tabs; and, some of the Font formatting options are disabled. The dialog box also includes a Clear button that clears any formatting already selected.

For do-it-yourself types, Excel provides the New Formatting Rule dialog box. Access this dialog box by choosing Home Styles Conditional Formatting New Rules.

The New Formatting Rule dialog box lets you re-create all the conditional format rules available via the Ribbon as well as create new rules.
First, select a general rule type from the list at the top of the dialog box. The bottom part of the dialog box varies, depending on your selection at the top. After you specify the rule, click the Format button to specify the type of formatting to apply if the condition is met. An exception is the first rule type, which doesn't have a Format button. (It uses graphics rather than cell formatting.)
Following is a summary of the rule types:
  • Format All Cells Based on Their Values: Use this rule type to create rules that display data bars, color scales, or icon sets.
  • Format Only Cells That Contain: Use this rule type to create rules that format cells based on mathematical comparisons (greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, or not between). You can also create rules based on text, dates, blanks, nonblanks, and errors. This rule type is very similar to how conditional formatting was set up in previous versions of Excel.
  • Format Only Top or Bottom Ranked Values: Use this rule type to create rules that involve identifying cells in the top n, top n percent, bottom n, and bottom n percent.
  • Format Only Values That Are Above or Below Average: Use this rule type to create rules that identify cells that are above average, below average, or within a specified standard deviation from the average.
  • Format Only Unique or Duplicate Values: Use this rule type to create rules that format unique or duplicate values in a range.
  • Use a Formula to Determine Which Cells to Format: Use this rule type to create rules based on a logical formula. See "Creating Formula-Based Rules," later in this chapter.
Conditional Formats That Use Graphics
This section describes the three conditional formatting options that are new to Excel 2007: data bars, color scales, and icon sets. These types of conditional formatting can be useful for visualizing the values in a range.
The data bars conditional format displays horizontal bars directly in the cell. Length of the bar is based on the value of the cell, relative to the other values in the range.
shows a simple example of data bars. It's a list of customers and sales amounts. I applied data bar conditional formatting to the values in column B. You can tell at a glance where the higher values are.

On the CD 
The examples in the section are available on the companion CD-ROM. The workbook is named  data bars examples.xlsx.

The differences between the bar lengths become more prominent when you increase the column width.

Excel provides quick access to six data bar colors via the Home Styles Conditional Formatting Data Bars command. For additional choices, click the More Rules option, which displays the New Formatting Rule dialog box. Use this dialog box to
  • Show the bar only (hide the numbers).
  • Adjust how the bars relate to the values (use the Type and Value controls).
  • Change the color of the bars.

Data bars are always displayed as a color gradient (from dark to light), and you can't change the display style. Also, the colors used are not theme colors. If you apply a new document theme, the data bar colors do not change.

If you make adjustments in this dialog box, you can use the Preview button to see the formats before you commit to them by clicking OK.

You may notice something odd about the data bars in Contrary to what you may expect, a cell with a zero value displays a data bar. Data bar conditional formatting always displays a bar for every cell, even for zero values. The smallest value in the range always has a bar length equal to ten percent of the cell's width. Unfortunately, Excel provides no direct way to modify the minimum percent setting. However, if you're familiar with VBA, you can use a statement like the following to set the minimum display width for a range that uses conditional formatting data bars:
Range("B2:B123").FormatConditions(1).PercentMin = 1
After executing this statement, the minimum value in the range will display a bar length equal to one percent of the cell's width-and zero value cells will not display a data bar.

Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. shows a three-column table of data, with data bars applied in the third column. The third column of the table contains references to the values in the second column. The conditional formatting in the third column uses the Show Bars Only option.
shows an actual bar chart created from the same data. The bar chart takes about the same amount of time to create and is a lot more flexible. But for a quick-and- dirty chart, data bars are a good option-especially when you need to create several such charts.

The color scale conditional formatting option varies the background color of a cell based on the cell's value, relative to other cells in the range.
shows a range of cells that use color scale conditional formatting. It depicts the number of employees on each day of the year. This is a three-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient.

On the CD 
This workbook, named  daily staffing level.xlsx, is available on the companion CD-ROM.

Excel provides four two-color scale presets and four three-color scales presets, which you can apply to the selected range by choosing Home Styles Conditional Formatting Color Scales.
To customize the colors and other options, choose Home Styles Conditional Formatting Color Scales More Rules. This command displays the New Formatting Rule dialog box/
It's important to understand that color scale conditional formatting uses a gradient. For example, if you format a range with a two-color scale, you will get a lot more than two colors because you'll get colors with the gradient between the two specified colors.
shows an extreme example that uses color scale conditional formatting on a range of 10,000 cells (100 rows x 100 columns). The worksheet is zoomed down to 20% to display a very smooth three-color gradient. The range contains formulas like this one, in cell C5:
in column A and row 1 range from 0 to 4.0, in increments of 0.04.

The result, when viewed on your, screen is stunning. (It loses a lot when converted to grayscale.)

On the CD 
This workbook, named  extreme color scale.xlsx, is available on the companion CD-ROM.

You can't hide the cell contents when using a color scale rule, so I formatted the cells using this custom number format:

Yet another conditional formatting option is to display an icon in the cell. The icon displayed depends on the value of the cells.
To assign an icon set to a range, select the cells and choose Home Styles Conditional Formatting Icon Sets. Excel provides 17 icon sets to choose from. Note: You can't create your set of icons. The number of icons in the sets ranges from 3–5.
shows a simple example that uses the icon set named Three Symbols (Uncircled). The symbols graphically depict the status of each project, based on the value in column C.

On the CD 
All the icon set examples in this section are available on the companion CD-ROM. The workbook is named  icon set examples.xlsx.

By default, the symbols are assigned using percentiles. For a three-symbol set, the items are grouped into three percentiles. For a four-symbol set, they're grouped into four percentiles. And for a five-symbol set, the items are grouped into five percentiles.
If you would like more control over how the icons are assigned, choose Home Styles Conditional Formatting Icon Sets More Rules to display the New Formatting Rule dialog box. shows how to modify the icon set rules such that only projects that are 100% completed get check mark icons. Projects that are 0% completed get an X icon. All other projects get an exclamation point icon.
shows a table that contains two test scores for each student. The Change column contains a formula that calculates the difference between the two tests. The Trend column uses an icon set to display the trend graphically.

This example uses the icon set named 3 Arrows, and I customized the rule:
  • Up Arrow: When value is >=5
  • Level Arrow: When value <5 and >= -5
  • Down Arrow: When value is >=5
In other words, difference of five points or less in either direction is considered an even trend. An improvement of more than five points is considered a positive trend, and a decline of more than five points is considered a negative trend.

The Trend column contains a formula that references the Change column. I used the Show Icon Only option in the Trend column, which also centers the icon in the column.

In some cases, you may want to display only one icon from an icon set. Excel doesn't provide this option directly, but displaying a single icon is possible if you use two rules. shows a range of values. Only the values greater than or equal to 80 display an icon.

Here's how to set up an icon set such that only values greater than or equal to 80 display an icon:
  1. Select the cells, choose Home Styles Conditional Formatting Icon Sets, and select any icon set. Keep in mind that only the last icon of the set will be used.
  2. With the range selected, choose Home Styles Conditional Formatting Manage Rules. Excel displays its Conditional Formatting Rules Manager dialog box.
  3. Click Edit Rule to display the Edit Formatting Rule dialog box.
  4. Change the first icon setting to When Value Is >= 80 and specify Number as the Type. Leave the other icon settings as they are, and then click OK to return to the Conditional Formatting Rules Manager.
  5. Click New Rule and then choose this rule type: Format Only Cells That Contain.
  6. In the bottom section of the dialog box, specify Cell Value Less Than 80 and then click OK to return to the Conditional Formatting Rules Manager. The range now has two rules.
  7. Place a check mark next to Stop If True for the first rule. Click OK.

The first rule checks whether the value is less than 80. If so, rule checking stops, and no conditional formatting is applied. If the value is greater than or equal to 80, the second rule kicks in. This rule indicates that values greater than or equal to 80 are displayed with an icon.
Working with Conditional Formats
This section describes some additional information about conditional formatting that you may find useful.
The Conditional Formatting Rules Manager dialog box is useful for checking, editing, deleting, and adding conditional formats. Access this dialog box by choosing Home Styles Conditional Formatting Manage Rules.
You can specify as many rules as you like by clicking the New Rule button. As you can see in, cells can even use data bars, color scales, and icon sets all at the same time-although I can't think of a good reason to do so.

Conditional formatting information is stored with a cell much like how standard formatting information is stored with a cell. As a result, when you copy a cell that contains conditional formatting, you also copy the conditional formatting.

To copy only the formatting (including conditional formatting), use the Paste Special dialog box and select the Formats option.

Inserting rows or columns within a range that contains conditional formatting causes the new cells to have the same conditional formatting.
When you press Delete to delete the contents of a cell, you do not delete the conditional formatting for the cell (if any). To remove all conditional formats (as well as all other cell formatting), select the cells and choose Home Editing Clear Clear Formats. Or, choose Home Editing Clear Clear All to delete the cell contents and the conditional formatting.
To remove only conditional formatting (and leave the other formatting intact), use Home Styles Conditional Formatting Clear Rules.
Excel's Find And Replace dialog box includes a feature that allows you to search your worksheet to locate cells that contain specific formatting. This feature does not locate cells that contain formatting resulting from conditional formatting.
Just by looking at a cell, you can't tell whether it contains conditional formatting. You can, however, use Excel's Go To dialog box to select such cells.
  1. Choose Home Editing Find & Select Go To Special.
  2. In the Go To Special dialog box, select the Conditional Formats option.
  3. To select all cells on the worksheet containing conditional formatting, select the All option. To select only the cells that contain the same conditional formatting as the active cell, select the Same option.
  4. Click OK. Excel selects the cells for you.
Creating Formula-Based Rules
Excel's conditional formatting feature is versatile, but sometimes it's just not quite versatile enough. Fortunately, you can extend its versatility by writing conditional formatting formulas.
The examples later in this section describe how to create conditional formatting formulas for the following:
  • To identify text entries
  • To identify dates that fall on a weekend
  • To format cells that are in odd-numbered rows or columns (for dynamic alternate row or columns shading)
  • To format groups of rows (for example, shading every group of two rows)
  • To display a sum only when all precedent cells contain values
  • To identify text cells that begin with the same first letter as a letter in a cell
  • To identify cells that contain a value that meets a criterion entered in a cell
Some of these formulas may be useful to you. If not, they may inspire you to create other conditional formatting formulas.
To specify conditional formatting based on a formula, select the cells and then choose Home Styles Conditional Formatting New Rule. This command displays the New Formatting Rule dialog box. Click the rule type labeled Use A Formula To Determine Which Cells To Format, and you'll be able to specify the formula.
You can type the formula directly into the box, or you can enter a reference to an existing formula. As with normal Excel formulas, the formula you enter here must begin with an equal sign (=).

The formula must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, the condition is satisfied, and the conditional formatting is applied. If the formula evaluates to FALSE, the conditional formatting is not applied.

If the formula that you enter into the Conditional Formatting dialog box contains a cell reference, that reference is considered a relative reference, based on the upper-left cell in the selected range.
For example, suppose that you want to set up a conditional formatting condition that applies shading to cells in range A1:B10 only if the cell contains text. None of Excel's conditional formatting options can do this task, so you need to create a formula that will return TRUE if the cell contains text and FALSE otherwise. Follow these steps:
  1. Select the range A1:B10 and ensure that cell A1 is the active cell.
  2. Choose Home Styles Conditional Formatting New Rule to display the New Formatting Rule dialog box.
  3. Click the rule type labeled Use a Formula to Determine Which Cells to Format.
  4. Enter the following formula in the Formula box:
5.  =ISTEXT(A1)
  1. Click the Format button to display the Format Cells dialog box.
  2. In the Format Cells dialog box, click the Fill tab and specify the cell shading that will be applied if the formula returns TRUE.
  3. Click OK to return to the New Formatting Rule dialog box.

In the New Formatting Rule dialog box, click the Preview button to make sure that the formula is working correctly and to see a preview of your selected formatting.
  1. If the preview looks correct, click OK to close the New Formatting Rule dialog box.
Notice that the formula entered in Step 4 contains a relative reference to the upper-left cell in the selected range.
Generally, when entering a conditional formatting formula for a range of cells, you'll use a reference to the active cell, which is normally the upper-left cell in the selected range. One exception is when you need to refer to a specific cell. For example, suppose that you select range A1:B10, and you want to apply formatting to all cells in the range that exceed the value in cell C1. Enter this conditional formatting formula:
In this case, the reference to cell C1 is an absolute reference: It will not be adjusted for the cells in the selected range. In other words, the conditional formatting formula for cell A2 looks like this:
The relative cell reference is adjusted, but the absolute cell reference is not.
If you enter a conditional formatting formula that uses one or more references to other sheets, Excel responds with an error message. If you need to refer to a cell on a different sheet, you must create a reference to that cell on the sheet that contains the conditional formatting. For example, if your conditional formatting formula needs to refer to cell A1 on Sheet3, you can insert the following formula into a cell on the active sheet:
Then use a reference to that cell in your conditional formatting formula.

Another option is to create a name for the cell (by using Formulas Defined Names Define Name). After defining the name, you can use the name in place of the cell reference in your conditional formatting formula. If you use this technique, the named cell can be in any worksheet in the workbook.

Each of these examples uses a formula entered directly into the New Formatting Rule dialog box, after selecting the rule type labeled Use a Formula to Determine Which Cells to Format. You decide the type of formatting that you apply conditionally.

On the CD 
The companion CD-ROM contains all the examples in this section. The file is named  conditional formatting formulas.xlsx.

Identifying Weekend Days
Excel provides a number of conditional formatting rules that deal with dates, but it doesn't let you identify dates that fall on a weekend. Use this formula to identify weekend dates:
This formula assumes that a range is selected and also that cell A1 is the active cell.
Identifying Cells Containing More Than One Word
You also can use conditional formatting with text. For example, you can use the following conditional formatting formula to apply formatting to cells that contain more than one word:
This formula assumes that the selected range begins in cell A1. The formula works by counting the space characters in the cell (using the TRIM function to strip out multiple spaces). If the count is greater than 0, the formula returns TRUE, and the conditional formatting is applied.
Displaying Alternate-Row Shading
The conditional formatting formula that follows was applied to the range A1:D18, to apply shading to alternate rows:

Alternate row shading can make your spreadsheets easier to read. If you add or delete rows within the conditional formatting area, the shading is updated automatically.
This formula uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). For cells in even-numbered rows, the MOD function returns 0, and cells in that row are formatted.
For alternate shading of columns, use the COLUMN function instead of the ROW function.
Creating Checkerboard Shading
The following formula is a variation on the example in the preceding section. It applies formatting to alternate rows and columns, creating a checkerboard effect.
Shading Groups of Rows
Here's another rows shading variation. The following formula shades alternate groups of rows. It produces four rows of shaded rows, followed by four rows of unshaded rows, followed by four more shaded rows, and so on.

For different sized groups, change the 4 to some other value. For example, use this formula to shade alternate groups of two rows:
Displaying a Total Only When All Values Are Entered
shows a range with a formula that uses the SUM function in cell C6. Conditional formatting is used to hide the sum if any of the four cells above is blank. The conditional formatting formula for cell C6 (and cell C5, which contains a label) is
This formula returns TRUE only if C2:C5 contains no empty cells.

Identifing Text Cells That Begin with a Certain Letter
The worksheet contains a list of names in the range A5:G32. Cell A1 contains a letter of the alphabet. A conditional formatting formula highlights the names that begin with the letter in cell A1.

The conditional formatting formula for the range A5:G32 is
Identifying Cells That Meet a Numeric Criteria
The example in this section is similar to the previous example, but it involves values. The range A5:P32 uses the following conditional formatting formula:
This formula takes advantage of the fact that the COUNTIF function can handle criteria that are entered in a cell. shows the worksheet when cell A1 contains the text >90.

Figure 19-23: Cells that meet the criteria entered in cell A1 are highlighted.
Excel's conditional formatting feature is very versatile. If it's not versatile enough, you can create your own formulas to define the conditions (as I explained in the previous sections). And if custom formulas still aren't versatile enough, you can create custom VBA function and use those in a conditional formatting formula.
This section provides three examples of VBA functions used in conditional formatting formulas.

On the CD 
The companion CD-ROM contains all the examples in this section. The file is named conditional formatting with VBA function.xlsm.
Identifying Formula Cells
Oddly, Excel does not have a function that determines whether a cell contains a formula. When Excel lacks a feature, you often can overcome the limitation by using VBA. The following custom VBA function uses the VBA HasFormula property. The function, which is entered into a VBA module, returns TRUE if the cell (specified as its argument) contains a formula; otherwise, it returns FALSE.
Function ISFORMULACELL(cell) As Boolean
    ISFORMULACELL = cell.HasFormula
End Function
After you enter this function into a VBA module, you can use the function in your worksheet formulas. For example, the following formula returns TRUE if cell A1 contains a formula:
And you also can use this function in a conditional formatting formula.

Another way to identify formula cells is to use the Home Editing Find & Select Go To Special command, which displays the Go To Special dialog box. Choose the Formulas option and click OK to select all cells that contain a formula.

Identifying Date Cells
Excel also lacks a function to determine whether a cell contains a date. The following VBA function, which uses the VBA IsDate function, overcomes this limitation. The custom HASDATE function returns TRUE if the cell contains a date.
Function HASDATE(cell) As Boolean
    HASDATE = IsDate(cell)
End Function
The following conditional formatting formula applies formatting to cell A1 if it contains a date and the month is June:
The following conditional formatting formula applies formatting to cell A1 if it contains a date and the date falls on a weekend:
Identifying Invalid Data
You might have a situation in which the data entered must adhere to some very specific rules, and you'd like to apply special formatting if the data entered is not valid. You might have part numbers that consist of seven characters: four uppercase alphabetic characters, followed by a hyphen, and then a two-digit number-for example, ADSS-09 or DYUU-43.
You can write a conditional formatting formula to determine whether part numbers adhere to this structure, but the formula is very complex. The following formula, for example, returns TRUE only if the value in A1 meets the part number rules specified:
For a simpler approach, write a custom VBA worksheet function. The VBA Like operator makes this sort of comparison relatively easy. The following VBA function procedure returns TRUE if its argument does not correspond to the part number rules outlined previously:
Function INVALIDPART(n) As Boolean
    If n Like "[A-Z][A-Z][A-Z][A-Z]-##" Then
       INVALIDPART = True
    End If
End Function
After defining this function in a VBA module, you can enter the following conditional formatting formula to apply special formatting if cell A1 contains an invalid part number:
shows a range that uses the custom INVALIDPART function in a conditional formatting formula. Cells that contain invalid part numbers have a colored background.
In many cases, you can simply take advantage of Excel's data validation feature, which is described next.