bta3o1 – productivity software – unit 2:
TRANSCRIPT
BTA3O1 – Productivity Software – Unit 2: Spreadsheet – Part 6: Conditional Formatting– Page 1 of 4
MS EXCEL 2003
CONDITIONAL FORMATTING
CONDITIONAL FORMATTING
Sometimes you want special formatting for a cell if it meets certain conditions. For example, you might want all cells with a negative number or less than a certain number to be bold and red. Conditional formatting does it for you automatically.
A maximum of three conditions can be applied to a cell.
Ex. 01 Conditional formatting based on a cell value
In this first example, the condition which determines the cell formatting is simply a value in the cell.
1. Create the simple worksheet illustrated to the right.
2. Highlight B2:E7.
3. Select Format / Conditional Formatting. The Conditional Formatting dialog box opens.
4. In the Condition 1 box, make sure Cell Value Is is selected.
5. In the middle condition box, select less than.
6. In the third box, enter 50.
7. Click on the Format button.
8. Select the Font tab.
9. Set the font to bold red.
10. Click on OK.
11. Check to see that all cells in the range B2:E37with a value of less than 50 are now bold and red.
12. As a test, change some of the numbers in the list.
13. Save as Excel6_ex1.xls.
14. Keep this sheet for Ex. 03 below.
BTA3O1 Information and Communication Technology: The Digital Environment- Productivity Software
Unit 2: Spreadsheet – Part 6: Conditional Formatting – Page 1 of 4
BTA3O1 – Productivity Software – Unit 2: Spreadsheet – Part 6: Conditional Formatting– Page 2 of 4
MS EXCEL 2003
CONDITIONAL FORMATTING
Ex. 02 Conditional formatting based on a cell value
This second example is only slightly different from the example above. Here the condition is a simple value but in a cell different from the one being formatted.
1. Create the worksheet illustrated to the right.
2. Save as Excel6_ex2.xls.
3. Highlight A3:A8.
4. Select Format / Conditional Formatting.
5. In the Condition 1 box, make sure Cell Value Is selected.
6. In the middle condition box, select greater than.
7. In the third box, enter =C3. (Note: The equal sign is important.)
8. Press F4 to make C3 an absolute cell reference.
9. Click on the Format button.
10. Select the Font tab.
11. Set the font to a colour of your choice.
12. Click on OK.
13. Check to see that the cells have been formatted correctly.
14. As a test, change some of the numbers in the list.
Ex. 03 Conditional formatting with two conditions, based on a formula
In this exercise we’ll use the same data we used in Ex. 01 above (Excel6_ex1.xls), but without any formatting. Remove the formatting from Excel6_ex1.xls or do a Copy / Paste Special: Values from Excel6_ex1.xls to another worksheet.
In this exercise we want to highlight the more important values in B2:E7 in bold green and the less important values in bold red. The more important values will be those 30 percent or more of the total; the less important values will be those 20 percent or less of the total.
1. Highlight B2:E7.
2. Select Format / Conditional Formatting.
3. In the Condition 1 box, make sure Cell Value Is is selected.
4. In the middle condition box, select greater than or equal to.
5. In the third box, enter the formula =$F2*0.3. (Note: The equal sign is important because $F2*0.3 is a formula.)
6. Click on the Format button.
7. Select the Font tab.
8. Set the font to bold green.
9. Click on OK.
BTA3O1 Information and Communication Technology: The Digital Environment- Productivity Software
Unit 2: Spreadsheet – Part 6: Conditional Formatting – Page 2 of 4
BTA3O1 – Productivity Software – Unit 2: Spreadsheet – Part 6: Conditional Formatting– Page 3 of 4
MS EXCEL 2003
CONDITIONAL FORMATTING
10. Click on the Add button to add a second condition.
11. Set the second condition as below:
Cell Value Is: Less than or equal to Formula: =$F2*0.2 Formats: Bold, red font
12. Check to see that the conditional formatting works correctly.
Note: If more than one condition is true for a cell, Excel applies only the formats of the first true condition, ignoring the others.
Ex. 04 Conditional formatting to highlight upcoming dates
To emphasize work that needs to be done soon in our office because its deadline is approaching, we will highlight all future dates that are within three days of today (Dec. 20) by colouring them bold red. We will also colour dates that are already past grey.
1. Create a worksheet as illustrated to the right.
2. Save as Excel6_ex4.xls.
3. Highlight C2:C8.
4. Select Format / Conditional Formatting.
5. Condition 1 is Cell value is less than =$B$1, and the format is font colour grey.
6. Add a second condition: cell value is between =$B$1 and =$B$1+3, and the format is font colour red bold.
7. Check to see that the cells have been formatted correctly.
8. As a test, change some of the dates in the list.
Note: Normally today’s date in B1 would not be entered manually; it would be automatically entered using the date function =today().
Ex. 05 Practice
1. Create a worksheet that will highlight overdue dates by comparing a list of various dates with a cell holding today’s date. Use the function =today().
BTA3O1 Information and Communication Technology: The Digital Environment- Productivity Software
Unit 2: Spreadsheet – Part 6: Conditional Formatting – Page 3 of 4
BTA3O1 – Productivity Software – Unit 2: Spreadsheet – Part 6: Conditional Formatting– Page 4 of 4
MS EXCEL 2003
CONDITIONAL FORMATTING
Ex. 6 Practice
1. Create a worksheet as illustrated to the right.
2. Save as Excel6_ex6.xls.
3. Use conditional formatting to colour the second mark of students who improved their mark. Hint: Condition will be “Formula Is”.
4. As a test, change some of the cell values.
Ex. 7 Conditional Formatting with Text (Single Word)
1. Create a worksheet as illustrated to the right.
2. Save as Excel6_ex7.xls.
3. Use conditional formatting so that cells containing the word TRUE have a light green background while cells containing FALSE have a light red background. The first condition will be Formula Is - =A1=TRUE. The second condition will be - =A1=FALSE.
Ex. 7 Conditional Formatting with Text (Multiple Words)
When conditional formatting is used to highlight a cell containing text of more than one word, quotation marks must surround the text in the formula. For example, in the illustration below, the condition in A1 will be Formula Is =A1=”SEE LAB INSTRUCTOR FOR ASSISTANCE”
1. Create a worksheet as illustrated to the right.
2. Use conditional formatting to colour green the words “SEE LAB INSTRUCTOR FOR ASSISTANCE” and red “MAKE APPOINTMENT WITH PROFESSOR”.
BTA3O1 Information and Communication Technology: The Digital Environment- Productivity Software
Unit 2: Spreadsheet – Part 6: Conditional Formatting – Page 4 of 4