Excel 4 Ur Business

Overview

• How can I highlight monthly stock returns so that every good month is highlighted in one color and every bad month is highlighted in another?
• Given quarterly corporate revenues, how can I highlight quarters in which revenues increased over the previous quarter in one color and quarters in which revenues decreased from the previous quarter in another?
• Given a list of dates, how can I highlight the weekend dates in a specific color?
• Our basketball coach has given each player a rating between 1 and 10 for her ability to play guard, forward, or center. Can I set up a spreadsheet that highlights the ability of each player to play the position to which she’s assigned?

Conditional formatting lets you specify formatting for a cell range depending on the contents of the cell range. For example, given exam scores for students, you can use conditional formatting to highlight in red the names of students who have a final average of at least 90. For any cell, conditional formatting allows you to create up to three conditions and define formatting for each condition. Basically, when you set up conditions to format a range of cells, Excel checks each cell in the range to see whether any of the conditions you created (such as exam score > 90) is satisfied. The format you link to that condition is applied to all cells in the range that satisfy the condition. If the content of the cell does not satisfy any of the conditions, the formatting of the cell is unchanged.

How can I highlight monthly stock returns so that every good month is highlighted in one color and every bad month is highlighted in another?

The file SandP.xls, shown in the following figure, contains monthly values and returns on the Standard and Poor’s stock index. Suppose that you want to highlight in purple each month in which the S&P index went up more than 3 percent and highlight in red each month in which the S&P went down more than 3 percent.

In this example, I begin by moving to cell C10 (the first month containing an S&P return) and select all monthly returns by pressing Ctrl+Shift+Down Arrow. Next I choose Conditional Formatting from the Format menu. In the Conditional Formatting dialog box, shown in the following figure, I select Cell Value Is and then Greater Than and then fill in .03. These settings mean that the formatting we select will be applied to all cells in the range that contain a value greater than .03.

Now click the Format button, and then select purple (or whatever color or other formatting you’d like) in the Format Cells dialog box, shown in the following figurue.

Notice that the lists for fonts and font size aren’t available, so your choice of formatting can’t change the font or font size. The Patterns tab provides the option to shade cells in a color you choose, while the Borders tab lets you create a border for cells that satisfy your conditional criteria. After clicking OK in the Format Cells dialog box, you’re returned to the Conditional Formatting dialog box, which now appears as it’s shown in the following figure.

We could stop here, click OK, and months meeting our condition would be highlighted in purple. To define a second condition (in a fashion similar to the first), click Add and then specify that all months in which the S&P return is less than -3 percent will be colored red. The Conditional Formatting dialog box should now appear as it’s shown in the following figure.

When you now click OK, all months with an S&P return that’s greater than 3 percent (see cell C23, for example) are displayed in purple, and all months with an S&P return less than -3 percent (see cell C18) are displayed in red. Cells in which the monthly returns don’t meet either of our conditions maintain their original formatting.

By the way, to delete conditional formatting (or any format) applied to a range of cells, simply select the range of cells and choose Edit, Clear Formats. To select all the cells in a spreadsheet to which conditional formatting applies, press F5 to open the Go To dialog box. In the dialog box, click the Special button, select Conditional Formats, and then click OK.

Given quarterly corporate revenues, how can I highlight quarters in which revenues increased over the previous quarter in one color and quarters in which revenues decreased from the previous quarter in another?

The file ToysrusFormat.xls contains quarterly revenues (in millions) for Toys ‘R’ Us during the years 1997-2002. (See in the following figure.) We’d like to highlight quarters in which revenues increased over the previous quarter in one color and highlight quarters in which revenues decreased over the previous quarter in another.

The Formula Is option in the Conditional Formatting dialog box enables you to use a formula to define conditions that Excel checks before it applies formatting to a cell. We’ll use this option in this example, but before we work with the Formula Is option, let’s look at how Excel evaluates some logical functions.

What happens when we type a formula such as =B3<2 in cell B4? If the value in B3 is a number smaller than 2, Excel returns the value True in cell B4; otherwise, Excel returns False. You can refer to the file LogicalExamples.xls, shown inthe following figure, for other examples like this. As you can see in the following figure, you can also use combinations of AND, OR, and NOT in formulas.

• In cell B6, the formula =OR(B3<3, C3>5) returns the value True if either of the conditions B3<3 or C3>5 is true. Since the value of C3 is greater than 5, Excel returns True.
• In cell B7, the formula =AND(B3=3,C3>5) returns True if B3=3 and C3>5. Because B3 is not equal to 3, Excel returns False. In cell B8, however, the formula =AND(B3>3,C3>5) returns True because B3>3 and C3>5 are both true.
• In cell B9, the formula =NOT(B3<2) returns True because B3<2 would return False, and Not False becomes True.

Now let’s look at how to use the Formula Is feature to create a conditional format in a range of cells. Begin by selecting the range of cells to which the conditional format should be applied. Open the Conditional Formatting dialog box, select Formula Is, and then enter the formula that defines the condition (it must start with an equals sign). The formula should be relative to the cell that’s selected in the range. Click Format, and then enter the formatting you want. Click OK. After clicking OK in the Conditional Formatting dialog box, your formula and formatting are copied to the whole cell range. The format will be applied to any cell in the selected range that satisfies the condition defined in the formula.

Returning to the file ToysrusFormat.xls, let’s focus on highlighting in purple-the quarters in which revenues increase. Basically, what we want to do is select the range E5:E25 (there is no prior quarter to which we can compare the revenue figure in cell E4) and then instruct Excel that if a cell’s value is larger than the cell above it, highlight the cell in purple. The following figure shows how to fill in the Conditional Formatting dialog box.

If you enter =E5>E4 by pointing to the appropriate cells, be sure you remove the $ signs from the formula in the Conditional Formatting dialog box or the formula won’t be copied. The formula in this example ensures that cell E5 is colored purple if and only if sales in that quarter exceed the previous quarter. After clicking OK, you’ll find that all quarters in which revenue increased are colored purple. Notice that in cell E6, for example, the formula was copied in the usual way, as =E6>E5.

To add the condition for formatting cells in which revenue decreased, select the range E5:E25 again, open the Conditional Formatting dialog box, and then select Formula Is. Enter the formula =E5<E4 and change the color to red. The Conditional Formatting dialog box will now appear as it’s shown in the following figure.

Given a list of dates, how can I highlight the weekend dates in a specific color?

The file WeekendFormatting.xls (see the following figure) contains several dates. We want to highlight all Saturdays and Sundays in red. To do this, I first copied the formula WEEKDAY(C6,2) from cell D6 to D7:D69. Choosing Type = 2 for the WEEKDAY function returns a 1 for each Monday, 2 for each Tuesday, and so on, so that the function returns 6 for each Saturday and 7 for each Sunday.

I now select the range D6:D69 and then choose Format, Conditional Formatting.-After selecting Formula Is, I fill in the dialog box as shown in the following figure.

After clicking OK, each date having its weekday equal to 6 (for Saturday) or 7 (for Sunday) is colored red.

Our basketball coach has given each player a rating between 1 and 10 for her ability to play guard, forward, or center. Can I set up a spreadsheet that highlights the ability of each player to play the position to which she’s assigned?

The file Basketball.xls, shown in the following figure, contains ratings given to 20 players for each position and the position (1 = guard, 2 = forward, 3 = center) played by each player. We would like to display in bold type the rating for each player for the position to which she’s assigned.

Begin by selecting the range C3:E22, which contains the players’ ratings. Choose Format, Conditional Formatting and then fill in the dialog box as shown the following figure.

The formula =$A3=C$1 compares the player’s assigned position to the column heading (1, 2, or 3) in row 1. If the player’s assigned position equals 1 (guard), her rating in column C, which is her guard rating, appears in bold. Similarly, if the player’s assigned position equals 2, the rating in column D, her forward rating, appears in bold. Finally, if the assigned position equals 3, the rating in column E appears in bold.

Explore posts in the same categories: MS Excel Tune Up