Using Conditional Formatting - Exercise

Contact Us or call 1-877-932-8228
Using Conditional Formatting - Exercise

Using Conditional Formatting

Duration: 15 to 25 minutes.

In this exercise, you will practice using conditional formatting in a number of different ways.

  1. Open Conditional Formatting.xlsx from your Excel2013.2/Exercises folder.
  2. In Sheet1, in the Listing Price column, highlight the most expensive 20% of houses using yellow fill with dark yellow text.
  3. In Sheet1, in the Town column, highlight all cells containing "Fayetteville" using light red fill.
  4. In Sheet1, in the Square Feet column, use blue gradient fill to visually demonstrate the size of each house.
  5. In Sheet1, in the Bedrooms column, highlight all cells showing 4 bedrooms using a red border.
  6. In Sheet1, in the Bathrooms column, use icon indicators to draw attention to houses that have 1 or 4 bathrooms.
  7. In Sheet2, in the Date Due column, highlight all past dates in light red fill with dark red text, current dates in yellow fill with dark yellow text, and future dates in green fill with dark green text. Tip: When creating the rules, instead of entering today's actual date, enter "=today()" (do not enter the quotation marks).

Solution:

  1. In Sheet1 in the Listing Price column, highlight the most expensive 20% of houses using yellow fill with dark yellow text.
    1. Select column B.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Top/Bottom Rules, and then Top 10%...:Top 10%... Option
    3. In the Top 10% dialog box, increase the % to "20", select Yellow Fill with Dark Yellow Text, and click OK:Selected Fill Color
  2. In Sheet1, in the Town column, highlight all cells containing "Fayetteville" using light red fill.
    1. Select column C.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    3. In the Equal To dialog box, enter "Fayetteville", select Light Red Fill, and click OK:Selected Options
  3. In Sheet1, in the Square Feet column, use blue gradient fill to visually demonstrate the size of each house.
    1. Select column D.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Data Bars, and then under Gradient Fill select Blue Data Bar:Blue Data Bar Option
  4. In Sheet1, in the Bedrooms column, highlight all cells showing 4 bedrooms with a red border.
    1. Select column E.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    3. In the Equal To dialog box, enter "4", select Red Border, and click OK:Red Border Option
  5. In Sheet1, in the Bathrooms column, use icon indicators to draw attention to houses that have 1 or 4 bathrooms.
    1. Select column F.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Icon Sets, and then select one of the options under Indicators:Indicators Options
  6. In Sheet2, in the Date Due column, highlight all past dates in light red fill with dark red text, current dates in yellow fill with dark yellow text, and future dates in green fill with dark green text.
    1. Select cells B2:B9.
    2. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Less Than...:Less Than... Option
    3. In the Less Than dialog box, enter today's date, select Light Red Fill with Dark Red Text, and click OK:Dark
    4. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Equal To...:Equal To... Option
    5. In the Equal To dialog box, enter today's date, select Yellow Fill with Dark Yellow Text, and click OK:Yellow Fill with Dark Yellow Text Option
    6. On the HOME tab, in the Styles group, click Conditional Formatting, Highlight Cells Rules, and then Greater Than...:Greater Than... Option
    7. In the Greater Than dialog box, enter today's date, select Green Fill with Dark Green Text, and click OK:Green Fill with Dark Green Text Option
Next