|
Conditional Formatting in Excel 2007Conditional Formatting is one of the things that's changed quite a bit in Excel 2007, so here's a short tutorial to get you started using it. (If you are using an earlier version of Excel, read this tutorial instead.) 1 In Excel 2007, open a blank workbook and add the data shown here:
2. Hold down your left mouse button and drag to select cells B1:B4 (the cells with numbers in them). Then click on the Conditional Formatting button on the Home tab of the Ribbon and choose "Highlight Cells Rules", then "Greater Than":
3. In this example, let's say you want to find all the numbers in your selection which are greater than 1000. In the Greater Than dialog box, type 1000 in the first box, then select "Green Fill with Dark Green Text" from the dropdown to the right of that. Click OK.
4. See that the numbers that are more than 1000 are now green. Change the 750 beside Harry's name to 1100 and press Enter and it will become green also. 5. Now, select the range B1:B4 again and click the Conditional Formatting button and choose "Less Than". Type 1000 in the left box and use the dropdown to choose "Custom Format" from the dropdown.
6. In the Format Cells dialog box, click on the Font tab and choose Bold and Red. Then click on the Fill tab and choose Yellow. Click OK, then OK again and your data should now look like this:
7. Now, change Linda's number to 999 and press Enter and see it turn red/yellow. 8. Let's try some text formatting now. Select the range of names (A1:A4) and click the Conditional Formatting button, but this time click on "Highlight Cells Rules", then "Text that Contains". In this box, type "ar" (without the quotes) in the left box, then choose "Light Red Fill with Dark Red Text", then click OK.
9. See that both Mary and Harry are now red because both of their names have "ar" in them. 10. With the range A1:A4 still selected, click the Conditional Formatting button, then click "Highlight Cells Rules", but this time choose "More Rules". When this box comes up, choose to "Format only cells that contain" "Specific Text" "beginning with" b. Then click the Format button and choose Font, Fill, and Border colors of your choice. Then click OK, then OK again.
11. Your data should now show the formatting applied to Bill's name because it begins with B. Change Linda to Belinda and her name will now have the same formatting as Bill's. 12. Drag your mouse to select all the data (A1:B4) and return to the Conditional Formatting dropdown and choose "Clear Rules", then "Clear Rules from Selected Cells".
13. Select cells B1:B4 and try some of these to see how they work:
14. Once you are comfortable with how the different rules work, try combining some, without clearing. For example, try applying a simple "Highlight Cells Rule", then add an Icon Set or Data Bars. You can apply multiple rules to the same data selection. However, you will see that you can only apply "Highlight Cells Rules" to text ... the other rules choices are for numbers only. 15. How about using Conditional Formatting to find duplicate values? Clear the rules from cells B1:B4; then change some numbers so you have at least two that are the same, then try Conditional Formatting > Highlight Cells Rules > Duplicate Values. 16. Or, add some dates to your spreadsheet, then select those cells and try Conditional Formatting > Highlight Cells Rules > "A Date Occurring" and see the choices you have:
17. How about if you have a large amount of data and you want to quickly see which cells are blank? Click the Conditional Formatting button, then "Highlight Cells Rules", then "More Rules" and try this one (don't forget to click the Format button and choose a format):
18. Now let's try a rule based on a formula. First clear all the rules from cells B1:B4. In cell D1 enter the same name that you have in A1. 19. Select the range B1:B4 and click the Conditional Formatting button, then "Highlight Cells Rules", then "More Rules". Then enter this info exactly:
20. Click OK and you will see the number in B1 displays the format. Now, in cell D3, enter the same name as the one you have in A3 and press Enter. You will see the number in B3 now displays the format also. (Conditional formats are similar to nesting AND or OR functions into IF statements, so if you don't understand how the AND function works, read this.) As you can see, there are many ways you can use Conditional Formatting to highlight specific areas of your spreadsheet, based on changing data and conditions. Get creative and try different stuff until you get the hang of it. And, HAVE FUN!! |
|
.
