Welcome to Blaisdell's Little Corner of the Web

Home Browsers

MsOffice 97 & 2000

Site Search Windows

Freeware | Freeware From A-Z | Security | Virus Information

Office Index II | Office Index I |                                                    | This site was Updated on  02/28/2006 |

Welcome to page 2 of Excel with Excel

Index

  1. CONDITIONAL FORMATTING - A SIMPLE ANALYTICAL TOOL
  2. USING FORMULAS AS CONDITIONS - ADVANCED CONDITIONAL FORMATTING
  3. EXPAND CONDTIONAL FORMATTING WITH FUNCTIONS
  4. COPYING CONDITIONAL FORMATS
  5. CONDITIONAL FORMATTING - NOT JUST A PRETTY FACE NOTE - Conditional Formatting is available in Excel 97 and 2000; it is not available in earlier versions. You select the cells that you want to apply the conditional formatting to and choose Format, Conditional Formatting
  6. You can also create a condition that uses a formula as part of the condition
  7. Excel 97 Security Update: REGISTER.ID 
    Summary
    The Microsoft Excel REGISTER.ID Security Update eliminates the vulnerability that could be exploited using the worksheet tools of Excel 97 to do malicious acts, such as deleting and overwriting files without the user's input or verification. This update protects against harmful code that could be executed from a worksheet by disabling the REGISTER.ID function.  
    Patch Availability
  8. Open Excel empty, without creating the default new workbook called Book1
  9. Here is a bare-bones macro you can use to create a new Invoice Template so that cells contain formulas which remain hidden until the user clicks on the cell or gets a result after having entered data in a relevant row or column.
  10. EXPLORING EXCEL GOAL SEEK & GOAL SEEK - ANOTHER EXAMPLE
  11. ONE MORE USEFUL CONDITIONAL FORMAT
  12. Making a template in Excel 97
  13. CREATING RANDOM NUMBERS IN EXCEL
  14. CONDITIONAL SUMS IN EXCEL

Excel Page 3

Go to Index Go to Start Page Go to Excel with Excel Main index Go to Secondary Index

CONDITIONAL SUMS IN EXCEL

Have you ever wanted to generate a total based on just part of data in list? The SUMIF function looks at a list and totals numerical data that meets conditions (criteria) you specify. The criteria can be a text string, a number, or a comparison (for example, >1500 or <=50%).

REMEMBER: A list consists of one row of labels at the top of the list, and may have a column of labels in the far left column (usually column A). There are no empty (blank) rows or columns in a list.

The syntax for SUMIF is:

=SUMIF(range,criteria,sum_range)

Suppose you have a list of hours spent on consulting for the month of June which contains 4 columns of information: Company Name; Date; Consultant Name; and Hours. You can use the SUMIF function to calculate the total hours for a particular company. The range argument would be the cell references for the column of company names (do not include the column heading or title). The criteria argument is the name of the particular company whose hours you want to total. The sum_range argument is the column of hours (again, do not include the column heading or title).

To use this function, select the cell where you want the sum to appear. To make it easy to see the result, select a cell above the list, or a cell on the same row as the column headings or titles.

To provide more flexibility, you can replace the company name in the criteria with a cell reference, such as cell G2. This way you don't have to edit the formula for each new company whose hours you want to sum. You would simply type the name of the next company in the cell (G2) and the total would automatically be calculated for that company.

NOTE: If you want to count, rather than total, the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. The syntax for the COUNTIF function is

=COUNTIF(range,criteria)

The range argument is the group of cell references you want to count. The criteria are the condition(s) that defines which cells will be counted.

5. USING THE SOLVER A number of people have asked us to explain how to use the Solver in Excel... well, that is easier said than done <grin>. In order to full appreciate the Solvers' capabilities it is necessary to use an example that is slightly more complex than Goal Seek (discussed in WOW 5.37), though abbreviated here due to the nature of our electronic newsletter.

The Solver resembles the Goal Seek command. Both commands backtrack from a formula to a precedent value. A precedent value is a value on which a formula is directly (or indirectly) based. In Goal Seek, the precedent value is the By Changing Cell option. But here is where the Solver differs from the Goal Seek command -- you can specify only one precedent in Goal Seek, whereas the Solver can accommodate many precedents.

In addition, the Solver enables you to set conditions, or constraints, on the solution. That is, you use the Solver to specify that certain values and formulas must satisfy your conditions before a solution is judged to be acceptable. So if you only want a precedent to be altered within a given range of values, you can set that constraint with the Solver.

Working with the Solver is not as easy to explain or illustrate as Goal Seek. Therefore we'll be presenting aspects of the Solver in a series of articles in WOW over the next few weeks. Here's Part 1.

INSTALLING THE SOLVER The Solver is an add-in feature supplied with Microsoft Office. This means you must install it specifically before you can use it. The easiest way to do so is to choose Tools, Add-Ins from the Excel menu. If a check mark appears next to Solver Add-In, then it is already installed and you can close the dialog box. If there is no check mark, place one there and click OK. A warning message appears, asking whether you'd like to install the add-in. You may need access Office CD or installation files to install the Solver add-in. After the Solver is installed, you will find the Solver command on the Tools menu.

OUR SOLVER SCENARIO Suppose that you are a book publisher who is considering the publication of a new book on a suite of software applications named Millennium '02. The shelf life of such books is fairly short because the book product line must keep current with new software releases, which come out every 18-24 months. Given the short shelf life, your decision is an important one - is it worth publishing a book which will have such as short shelf life?

You will incur significant costs in development, printing and binding, marketing, and getting the book through the distribution channel. So you sit down with your Excel worksheet and start to input some timelines, costs, and revenue projections. After a few minutes, you come up with a few rough estimates (due to width constraints of this newsletter I'm only showing 2 quarters but would normally show all 4 quarters):

A B C D

1 Quarter 1 2 2 Revenue 545 529 3 Quarterly % 4 Costs of Revenue 5 Research 60 0 6 Advertising 58 38 9.0% 7 Production 291 193 45.0% 8 Salaries 90 60 14.0% 9 Commission 16 10 2.5% 10 Total Costs 515 301 11 12 Rev Less Costs 30 228 13 Taxes 10.2 43.5 34.0% 14 Net Income 19.8 184.5 15 16 Cum. Net Income 204.3

The eventual goal is to optimize the cumulative net income at the end of the second quarter.

Your estimates are probably educated guesses. Some of them are based on solid empirical information, such as the costs associated with salaries and taxes. Some are based on nothing more quantifiable than your own current sense of the market. And some blend real data, such as sales of earlier books, with guesses -- for example, how aggressively the software manufacturer will market the new software release. Using these data sources, you arrive at the estimates. Your publishing firm will incur many costs that are not shown, of course, but the ones listed are those that are most relevant to the business decision.

SPECIFYING THE TARGET CELL The revenues and costs flow through the model to result in a cumulative net income of 204.3 in cell C16 (Quarter 2). Your first reaction on seeing this figure is that it is an inadequate return at that point on the costs you will incur (515 + 301). You believe that to make going ahead with the book worthwhile, that the value in Quarter 2 must be closer to 300.

You decide to make cell C16 the Solver's Target Cell. When you choose Tools, Solver, the Solver Parameters dialog box appears. And although the Solver can handle many Changing Cells, it solves for just one Target Cell.

If you selected the Target Cell (C16 in our example) prior to starting the Solver, its address appears in the Set Target Cell reference edit box. Otherwise, highlight whatever address appears there and click in cell C16.

As with the Goal Seek command, the target cell that you specify for the Solver must contain a formula. Otherwise, if that cell contains a value, the Solver could change the values of other cells repeatedly and the target's value would never change.

In this example, you are seeking to maximize a particular value, so choose the Max option button in the Solver Parameters dialog box.

NOTE: Each of the choices you make is capable of causing the Solver to fail, and one of the most important choices concerns setting the goal for the Target Cell. If you were (perversely or accidentally) to set the target value for the cumulative net income to a minimum, you would make it impossible for the Solver to reach a solution. Any one of several outcomes is possible:

>> The Solver might return a message that it could not find a feasible solution.

>> You might see a message that an internal error had occurred, or that the Solver ran out of memory.

>> The Solver might exceed the number of permissible iterations.

>> Exceptionally, the Solver might find a minimum value that's legal but completely ridiculous -- for example, a negative 184,538,962.

So, before you specify a Target Cell goal for the Solver to find, make sure that the formulas on the worksheet support the goal you specify.

SPECIFYING THE CHANGING CELLS In our example, many cells are precedent to the result in cell C16, the final cumulative net income. The percentages in D6:D9, however, are the real drivers. The revenues are dependent on the percentages of the Advertising, Salaries, Production, and Commissions. For example, the revenue expected for the first quarter is 545, based on the formula

=500*(1+3*D9)

A revenue base of 500 is expected, plus an additional 45 as a result of the threefold increase in commissions offered to the sales reps for advance sales.

Likewise, the costs are based on a percentage of the revenue. So, the percentage you use for these costs, are controllable. It's possible for you to make decisions about the commission rates, for example, that will raise or lower the total quarterly costs. Because they actually are under your control, the percentages that drive these costs are good candidates for Changing Cells.

By Changing Cells, the Solver means values that it can adjust to reach the goal that you specify for the Target Cell. Just because the Solver can change them, however, doesn't mean that it necessarily will do so. Several factors determine whether the Solver will change the value of a particular cell as it searches for a solution. For example, you might establish a constraint that establishes very narrow bounds on the values a Changing Cell can take on (we'll discuss constraints in the next WOW issue). The Solver might not change that cell's value if doing so has a negligible impact on the Target Cell value. Or it might be that some other Changing Cell has such a strong relationship to the Target Cell's value, that the Solver reaches its target before it has even gotten around to changing some other cell by more than a very small amount.

The point is that it occasionally takes some experimenting before you reach the right mix of values, formulas, and constraints. If the Solver makes a huge change in one Changing Cell and leaves another completely alone -- and if this isn't what you want -- you'll have to make some adjustments to the constraints, to the formulas, or both.

One cost, that of market research, is a constant value. Your market research firm charges a fixed fee of 60. Although you could use the Solver to alter this value in pursuit of a higher net income, to do so would be irrelevant. In the Solver, you would have control over that value, but in reality you do not -- so it is a poor candidate for Changing Cells.

Although you can't control that cost, it doesn't mean that it doesn't belong in the model, however. It has an effect on the Target Cell both in reality and in the model, and so it remains on the worksheet as an actual cost.

Both the controllable and the uncontrollable costs, along with taxes, are subtracted from the revenue figures to reach eventual net income figures. The cumulative net incomes are the running total of the net incomes.

You decide that you want Solver to modify the quarterly percentages that determine the controllable costs and revenues. You note, though, that even if each of those costs dropped moderately, you would still not have reached your goal of 300 cumulative net income flow at the end of the second quarter. You can't reduce the costs too far or no resources would be available to produce the book.

In the Solver dialog box, click the By Changing Cells reference edit box and highlight cells D6:D9 to identify the quarterly percentages.

Instead of specifying the Changing Cells yourself, you could leave the By Changing Cells reference edit box empty and choose the Guess button. If you do, the Solver does its best to determine which cells are the precedents to the Target Cell. In the process of doing its best, the Solver can set up a situation in which too many cells, or the wrong cells, are identified as Changing Cells. For example it might attempt to change the cell containing the market research cost. As discussed previously, the market research should not enter the analysis because it really isn't under your control. When you can't change a cost in reality, why allow the Solver to change them in the worksheet model?

CAUTION -- Occasionally, and with a very simple model, you might find it convenient to use the Guess button. But be aware that when you do, you relinquish control of the model, and Solver's best guess for the Changing Cells might well be different from what you intend.

By specifying the percentages used in D6:D9, you completely specify all the other controllable costs: advertising, production, salaries, and commissions, and you indirectly specify the revenue values as well.

To arrange for the Solver to change, for instance, the advertising costs in B6:C6 would be redundant. Suppose that you identified the advertising costs in B6:C6 as Changing Cells, in addition to the cost percentages in D6:D9. Apart from the redundancy and inefficiency of doing so, you do not want the Solver to alter B6:C6. Those cells contain formulas. When the Solver changes a cell, it changes its value. If a cell starts out with a formula, the Solver replaces the formula with the value that helps get to the target. Then you've lost your formulas.

In the next issue of WOW we'll continue our discussion of the Solver including: specifying constraints, using the solution, and understanding the Solvers reports.

 


CREATING RANDOM NUMBERS IN EXCEL

If you need to automatically create a series of six random numbers. Each of the six numbers must fall between 1 and 99. You can do this easily in Excel.

It's very easy to create random numbers in Excel. Go to cell A1 and enter

=randbetween(1,99)

Click cell A1, then use the mouse to grab the handle at the lower right corner of the cell. When the mouse pointer turns to a crosshair, drag to the right to F1.

When you need a new set of random numbers, press F9.


Making a template in Excel 97

You're familiar with the concept of templates in Microsoft Word, but you've never used them in Excel.

Excel templates make sense for any spreadsheet you regularly create. They can contain all the attributes of a spreadsheet, including (but not limited to) a specific number of sheets, specific sheet names, text, numbers, formulas, formatting, styles, and macros. (Unlike a Word template, though, once an Excel workbook has been created based on a template, that's the end of the relationship.)

To make an Excel template, first create a new book, then enter the desired boilerplate content and/or formatting. Select File and choose Save As, then in the "Save as type" list choose "Template (*.xlt)." This switches focus to the Templates folder, but change to the Xlstart folder (usually C:\Windows\Application Data\ Microsoft\Excel\Xlstart) and save it with a name like "Template1.xlt" just for testing purposes. Now, close Template1.xlt, select File, choose New, and note that an entry now exists called Template1. Select it, click on OK, and you have a new workbook based on Template1.xlt.


ONE MORE USEFUL CONDITIONAL FORMAT,

In a previous condition example I discussed a feature in Excel called "Conditional Formatting". This feature allows you to format cells in different ways based on conditions they must meet. For example if a cell value is greater than 100 you can format the number to appear in blue. Reader Kari K. sent me a very handy condition you can use with dates. The condition checks to see if the date in the cell is today's date. If it is, Conditional Formatting changes the color of the cell background. This creates a "dynamic" format; whatever date it is today will be the colored cell. This useful format makes it very easy to quickly locate the current date in a worksheet. Here's what you need to do to use this conditional format:

1. Select the first cell in the list that you want to format. Let's use cell D12 for this example.

2. Choose Format, Conditional Formatting; the Conditional Formatting dialog box appears.

3. In the first drop-down box choose Formula Is. When you change the type of condition to Formula Is, the dialog box changes. The Formula Is type provides a single box to enter the formula.

4. Enter the formula in the text box to the right of the condition type drop-down. If the cell D12, the formula will be:

= D12 = TODAY()

TODAY is a function in Excel that determines the current date. So the formula reads "is the date in the active cell (D12) equal to today's date?"

5. Then click the Format button. The Format Cells dialog box appears. In this dialog box you select the font style, font color, underlining, borders, shading, or patterns you want to apply when the condition is met. If you want the cell background to appear in a different color, you would click the Pattern tab and choose a color.

6. Click OK to close the Format Cells dialog box. If you want to add another condition, click Add. Remember, you can specify up to three conditions.

7. Choose OK to accept the conditions


EXPLORING EXCEL GOAL SEEK
GOAL SEEK - ANOTHER EXAMPLE

The fundamental approach taken by any spreadsheet application is to apply input values to formulas: You supply an input value and a formula, and the application supplies the result of the formula. Goal Seek turns this sequence around. By using Goal Seek, you specify the formula and its result, and Excel supplies the input value.

You can manually predict the goal by entering the necessary information into a worksheet, such as:

A B 1 Salary 30,000 2 Tax Rate 18% 3 Take Home Pay =B1-(B1*B2)

Cell B1 contains the 30,000 value that you currently earn each year; B2 contains the 18% tax rate you pay, and B3 contains a formula that calculates your salary less taxes, your Take Home Pay.

The result of this calculation is 24,600. Suppose you have a goal of earning 29,000 as your Take Home Pay. What salary would you need to reach this goal?

To perform Goal Seek manually, you would try out different values in cell B1, the Salary figure, until the Take Home Pay result until you close in on the 29,000 value you seek in cell B3. For example, you could change cell B1 to 35,000; the resulting Take Home Pay would be 28,700. Changing cell B1 to 37,000 you would get 30,340. The salary you need to reach your goal is somewhere between 35,000 and 37,000. This is the essence of goal seeking.

However, it's a lot faster and usually more accurate to use Excel's Goal Seek tool, instead of guessing the values. To use the Goal Seek command, select the cell containing the formula and choose Tools, Goal Seek. The Goal Seek dialog box appears. There are the three parts of the Goal Seek command:

-- Set Cell. Select the cell that is to show the target or goal. This cell must contain a formula. In our example, this is cell B3, the Take Home Pay formula.

-- To Value. Type the goal, target, or end result that you want to achieve, for example 29,000.

-- By Changing Cell. Select the cell containing the value that you want to adjust to reach the goal you've specified. The Salary, cell B1, is the value that will be changed.

The Goal Seek dialog box requires three entries. Goal Seek then iterates through different values in cell B1 until cell B3 contains the value of 29,000.

When Goal Seek has determined an answer, it will temporarily change your worksheet. Another dialog box appears indicating it found a solution. You have two choices at this point:

If you just wanted to see what the goal would be, select Cancel to revert back to the original values in the worksheet.

To keep the changes made to the worksheet by Goal Seek, click OK.

GOAL SEEK - ANOTHER EXAMPLE

Suppose that you have $1,000, and you wonder how long it would take to turn it into $1,200 at a 6% compound annual interest rate. You can enter the necessary information into a worksheet, such as:

A B 1 Starting Principle 1,000 2 Annual Interest Rate 6% 3 Goal =B1*(1+B2)^B4 4 Years to Goal 3.5

Cell B1 contains the 1,000 value that you have at present; B2 contains the 6% interest rate, and B4 contains your guess as to the length of time needed to earn 200 in interest. Cell B3 contains the formula:

=B1*(1+B2)^B4

This formula returns the result of earning 6% on an original investment of 1,000 for as many years as are entered in cell B4. The ^ symbol (sometimes call the "carrot" symbol) is an operator used in formulas much like the * (asterisk) for multiplication and the / (forward slash) for division. The ^ symbol is used for exponentiation. For instance 2^2 is equivalent to 2*2 (which equals 4) and 2^3 is equivalent to 2*2*2 (which equals 8). This is often read as 2 to the 3rd power.

For some formulas, Goal Seek might encounter difficulty in reaching the solution that you specify. For example, if you were to specify -1200, instead of 1200, Goal Seek would be unable to reach a solution.

TIP - The default number of iterations for Goal Seek is 100. The default maximum change per iteration is 0.0001. Sometimes Goal Seek needs additional iterations or a smaller maximum change number to solve the problem. You can change these defaults by choosing Tools, Options, and selecting the Calculation tab. Increase the value shown in the Maximum Iterations edit box or lower the value shown in the Maximum Change edit box


Here is a bare-bones macro you can use to create a new Invoice Template so that cells contain formulas which remain hidden until the user clicks on the cell or gets a result after having entered data in a relevant row or column.

To enter the macro, run Excel and press F11 to open the VBA editor. Choose Insert, Module and enter the following code:

Sub Macro1()
Range("A1").Select
If Range("A1") Then
x = 0 'black text
Else
x = 2 'white text
End If
Range("A7").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = x
End With
End Sub

Press Ctrl-S to save the workbook and the macro along with it. Press Alt-Q to get back to your worksheet. Click A7, then type

=sum (a1:a5)

and press Enter. Now press Alt-F8 and double-click your macro. The macro will turn the formula white, making it invisible. If you move to A1 and enter a number, then run the macro again, A7 will appear in standard black type.
Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


Open Excel empty, without creating the default new workbook called Book1.

Select Start, Run and type "excel.exe /e" (without the quotes).

Alternately, you can create a Desktop shortcut. First, right-click on your open Desktop and choose New, Shortcut. Click on the Browse button and locate Excel.exe (typically C:\Program Files\Microsoft Office\Office\Excel.exe). Then add " /e" (without the quotes) to the right of the existing quoted command text in the "Command line" field. Click on Next, enter a name for the Shortcut (say, "Excel Empty"), and click on Finish.

Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


You can also create a condition that uses a formula as part of the condition

Those formulas can use functions. A simple example presented in the WOWMM article used the Weekday function to determine if consulting work was performed on a weekend (so the clients can be charged extra and the consultants given a bonus <grin>).

A more elaborate use of functions would evaluate the numbers in a column and highlight the lowest and highest numbers (such as the best and worst sales for week). Naturally you use the MAX and MIN functions to examine the current cell against the range of cells.

Let's say that the current cell is B2, and the range is B2:B8. The conditions would be:

Condition1 Formula is =B2=MAX($B$2:$B$8) Condition2 Formula is =B2=MIN($B$2:$B$8)

Suppose your list is not always the same range of cells. This week it is B2:B8, next week it is B2:B200, the following week B2:B54. You can make your Conditional Formatting more dynamic (and flexible) by using the OFFSET and COUNT functions to determine the range.

The OFFSET function can use one cell as the starting point (the reference argument), and return another range or cell, offset from the reference cell. The other arguments specify how far (rows and columns) to offset from the first cell and the dimensions (height and width) of the range or cell to return. The OFFSET syntax is

=OFFSET(reference,rows,cols,height,width)

The second argument of the OFFSET function is rows; that is how many rows down should OFFSET look for the desired data. To make the number of rows returned depend on the number of values in a range of cells, use the COUNT function. COUNT returns the number of cells in a range that contain values (numbers or dates). If you need to count values and text, use the COUNTA function.

The syntax for COUNT is

=COUNT(value1, value2,...)

where each value represents a range of cell references. Or you can specify a range such as B2:B65536)

For example, this formula:

=OFFSET($B$2,COUNT($B$2:$B$65536),0,1,1))

would (starting from cell B2), count the number of rows down that contain data. Since you only want the data in the active column the cols argument is zero (0). Since you only want to return a number to one cell, the height and width arguments are one (1).

So that Condition1 changes from

=B2=MAX($B$2:$B$8)

and substitutes the OFFSET function for the second part of the range ($B$8). The new Condition1 becomes:

=B2=MAX($B$2:OFFSET($B$2,COUNT($B$2:$B$65536),0,1,1))

and Condition2 becomes

=B2=MIN($B$2:OFFSET($B$2,COUNT($B$2:$B$65536),0,1,1))

Next week well resume our discussions of analytical tools in Excel, when we talk about Scenarios and GoalSeek.

Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


CONDITIONAL FORMATTING - A SIMPLE ANALYTICAL TOOL

The features in Excel that you can use to analyze data is conditional formating. Although considered more of a formatting tool rather than an analytical tool, Conditional Formatting makes it easy to format a cell, based on the data in the cell. In order for Excel to know how to format the cell, you must specify the conditions for each format. You can use Conditional Formatting to flag cells in your worksheet. The formatting makes those cells stand out from the rest of the cells in the worksheet. This makes it easy to peruse a worksheet to see which cells have the special format displayed.

  NOTE - Conditional Formatting is available in Excel 97 and 2000; it is not available in earlier versions.

Let's say you have a worksheet that calculates the difference between sales in 1999 and 2000. You may want negative numbers (you sold less in 2000 than in 1999) to be formatted to stand out from the other numbers by displaying a light gray shading behind the number. Likewise, if the
difference is greater than 100,000 you may want to have those numbers appear bold. Conditional Formatting can be applied to a cell that contains a value, a formula, or text. This powerful formatting feature changes the visual appearance of the data or cell and draws attention to data that is in some
way out of the ordinary. Conditional Formatting can apply up to three different formats to data in a cell (such as bold or italic), or to the cell itself (such as borders or shading).

To use Conditional Formatting, follow these steps:

  1. Select the cell (or cells) you want to format.

  2. Choose Format, Conditional Formatting, which displays will prompt. The Conditional Formatting dialog box appears.

  3. Make sure the Cell Value Is option is selected in the first drop-down box.

  4. Choose one of the eight conditional operators such as Less Than, Between, or Greater Than Or Equal To.

  5. In the box (or boxes) to the right of the conditional operators, type in values or choose cell references.

  6. To select a format when the condition is met, choose the Format button in the dialog box. Select the font style, font color, underlining, borders, shading, or patterns that you want to apply, then select OK.

  7. If desired, choose the Add button to specify another condition and repeat steps 3-6. Up to 3 three conditions may be used.

  8. Choose OK to accept the conditions.

If conditional formatting is added to a cell that has a formula, when the formula is copied, the conditional formatting is copied also.
Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


USING FORMULAS AS CONDITIONS - ADVANCED CONDITIONAL FORMATTING

With Conditional Formatting, you decide what the conditions are and how the data in your worksheet will appear that meets those conditions. In the previous section, we showed how to use the basic conditions as they apply to cell values. For example if the value in a cell is greater than a specified amount, change the appearance. This type of condition is triggered by the actual value in a cell. However, you can also create a condition that uses a formula.

But what if you want the appearance of something else (like a label) to change if the value is above a certain amount. Suppose you have a worksheet tracks the number of units each production plant produces each month. You want the name of the plant to be formatted in Bold when the total plant production reaches 75,000 units. Because the condition relies on the values in other cells, a simple formula is required. As you enter the units produced by each plant every month, you want the Conditional Formatting to evaluate whether the total units produced by a plant meet or exceed 75,000. To accomplish this type of Conditional Formatting, follow these steps:

1. Start by selecting the first cell you want changed as a result of the condition; in this case it would be the cell containing the name of the plant.

2. Choose Format, Conditional Formatting. The Conditional Formatting dialog box appears.

3. The first drop-down box is used to indicate the type of condition. In this example, you need to change the type to Formula Is. When you change the type of condition to Formula Is, the dialog box changes. The Formula Is type provides a single box to enter the formula.

4. Enter the formula in the text box to the right of the condition type drop-down. As with all Excel formulas, start with an equal sign (=). Then enter the condition you are looking for. In this example, the condition is

= F4 >= 75000

where F4 is the cell containing the name of the first plant.

NOTE - When you need a cell reference in your formula, you can type it in, or select the cell with your mouse. When you use the mouse, an absolute cell reference is automatically displayed. Press the F4 key three times to change this to a relative cell reference.

5. Then click Format. The Format Cells dialog box appears, where you select the font style, font color, underlining, borders, shading, or patterns you want to apply when the condition is met.

6. Click OK to close the Format Cells dialog box. If you want to add another condition, click Add. Remember, you can specify up to three conditions.

7. Once all the conditions and associated formats are specified, click OK.

Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


EXPAND CONDTIONAL FORMATTING WITH FUNCTIONS

You can also use functions in formulas for Conditional Formatting. Suppose you have a worksheet that lists information regarding consulting work performed in June. You can use Conditional Formatting to highlight any work that was performed on a weekend -- so the clients can be charged extra and the consultants given a bonus <grin>.

To evaluate whether the date is on the weekend, you use the WEEKDAY function and create two conditions. Suppose B4 contains the first date you want to evaluate. The first condition checks to see whether the date in cell B4 falls on a Sunday (where 1 is the first day of the week).

=WEEKDAY(B4)=1

The second condition checks to see whether the date falls on a Saturday (where 7 is the last day of the week).

=WEEKDAY(B4)=7

You need two conditions if you want to format the results differently. If the formats are going to be the same, use one condition, such as:

=OR(WEEKDAY(B4)=1,WEEKDAY(B4)=7)

OR is a Logical function in Excel where the criteria (in this case, the conditions) are separated by commas.

TIP - It's a good idea to spend some time up front determining each condition you want to check and the formatting to be applied if the condition is met.

Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index


COPYING CONDITIONAL FORMATS

There are two ways to copy the Conditional Formatting to other cells. While you can use the Format Painter button on the Standard toolbar, this tool copies all cell formatting.

To copy just the conditional formats, select the cell that currently has the Conditional Formatting and all the cells to which you want to apply the formatting. Then choose Format, Conditional Formatting. When the dialog box appears, simply click OK. The Conditional Formatting is applied to the selected cells; the formula adjusts for each cell in the range.

TIP - When the values in the worksheet don't match the conditions, the formatting is suppressed. If you want to determine which cells in a worksheet have Conditional Formatting, choose Edit, Go To and click the Special button. In the Go To Special dialog box, choose Conditional Formats. Below the Data Validation option, choose All and then click OK. If you want to find only those cells with a particular conditional format, you must select a cell in the worksheet that has the conditional formatting you are looking for. Then in the Go To Special dialog box, after you select Conditional Format, click the Same option below Data Validation.

If you want to flag data entry errors, you can use Conditional Formatting. However, a more powerful option would be to use Excel's Data Validation feature. Data Validation can display error messages if an invalid entry is made.

Go to Excel with Excel Main index
Go to Secondary Index
Go to this page Index

Entertainment Government Technology About Email Me