Problems

Using the data in the file Sandp.xlsx, use conditional formatting in the following situations:

1. Format in bold each month in which the value of the S&P index increased, and underline each month in which the value of the S&P index decreased.

2. Highlight in green each month in which the S&P index changed by a maximum of 2 percent.

3. Highlight the largest S&P index value in red, and the smallest in purple.

4. Using the data in the file Toysrusformat.xlsx, highlight in red all quarters for which revenue has increased over at least the last two quarters. Highlight all fourth-quarter revenues in blue, and first-quarter revenues in red.

5. The file Test.xlsx contains exam scores for students. The top 10 students receive an A, the next 20 students receive a B, and all other students receive a C. Highlight the A grades in red, the B grades in green, and the C grades in blue. Hint: The function LARGE(D4:D63,10) gives you the tenth highest grade on the test.

6. In the file Weekendformatting.xlsx, highlight all weekdays in red. Highlight in blue all days that occur in the first 10 days of the month.

7. Suppose each worker in Microsoft’s finance department has been assigned to one of four groups. The supervisor of each group has rated each worker on a 0–10 scale, and each worker has rated his satisfaction with each of the four groups. (See the file Satis-super.xlsx.) Based on the group to which each worker is assigned, highlight the supervisor rating and the worker satisfaction rating for each worker.

8. The file Varianceanalysis.xlsx contains monthly profit forecasts and monthly actual sales. The sales variance for a month equals:

   

   Highlight in red all months with a favorable variance of at least 20 percent, and highlight in green all months with an unfavorable variance of more than 20 percent.

9. For our drug cost example from Chapter 20, “The OFFSET Function,” format the worksheet so that all Phase 1 costs are displayed in red, all Phase 2 costs are displayed in green, and all Phase 3 costs are displayed in purple.

10. The file Names.xlsx contains a list of names. Highlight all duplicates in green, and all names containing Ja in red.

11. The file Duedates.xlsx contains due dates for various invoices. Highlight in red all invoices due by the end of the next month.

12. In the file Historicalinvest.xlsx, set up conditional formatting with three icons so that 10 percent of returns have an up arrow, 10 percent have a down arrow, and 80 percent have a horizontal arrow.

13. The file Nbasalaries.xlsx contains salaries of NBA players in millions of dollars. Set up data bars to summarize this data. Players making less than $1 million should have the shortest data bar, and players making more than $15 million should have largest data bar.

14. Set up a three-color scale to summarize the NBA salary data. Change the color of the bottom 10 percent of all salaries to green, and the top 10 percent to red.

15. Use five icons to summarize the NBA player data. Create break points for icons at $3 million, $6 million, $9 million, and $12 million.

16. The file Fractiondefective.xlsx contains the percentage of defective units produced daily. A day’s production is considered acceptable if 2 percent or fewer items produced are defective. Highlight all acceptable days with a green flag. In another worksheet, highlight all acceptable days with a red flag. Hint: Use the Stop If True option to make sure that no icon occurs in any cell containing a number less than 2.

17. How could you set things up so no icons are shown for players who had salaries between $7 million and $8 million? Hint: Use the Stop If True option.

18. Summarize global warming data with a three-color scale. Lower temperatures should be blue, intermediate temperatures should be yellow, and higher temperatures should be red.

19. Suppose you are saving for your child’s college fund. You are going to contribute the same amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For annual investment returns ranging from 4 percent to 15 percent, and number of years investing varying from 5–15 years, determine your required annual contribution. Suppose you are able to save $10,000 per year. Use conditional formatting to highlight for each annual return rate the minimum number of years needed to accumulate $100,000.

20. The file Amazon.xlsx contains quarterly revenues for Amazon.com. Use conditional formatting to ensure that sales during each quarter are highlighted in a different color.

21. Set up conditional formatting which colors the cell range A1:H8 like a checkerboard, with alternating white and black coloring. Hint: The ROW() function gives the row number of a cell, and the COLUMN() function gives the column number of a cell.