To assess your understanding of the material covered in the chapter, please complete the following assignments.

Although Excel is primarily used in business and scientific applications, you will find it useful in other areas of study as well. In these exercises we will use Excel to create charts using historical, health, and social justice data.

Charting Historical Data (Comprehensive Review)

Download Data File: PR4 Data

Excel is an excellent tool for helping to display historical data. In this exercise we will be examining ways to display information on minimum wage data and life expectancy.

Task1 – National Minimum Wage in the US – 1960-2014

Since the beginning of the previous century, the United States has set a minimum wage, in order to set a “floor” beneath which wages cannot fall. Most states have set their own minimum wages, but none are lower than the national minimum wage. To learn more about the national minimum wage, look here: https://en.wikipedia.org/wiki/Minimum_wage_in_the_United_States

1. Open the file named PR4 Data and then Save As PR4 Historical Data.
2. On the Minimum Wage worksheet, select the range B4:C60
3. Select the Insert tab, then the Recommended Chart tool in the Charts group.
   Recommended Charts allows users to first see how selected data would be represented on a variety of chart types before committing to a particular type of chart. Being able to see your data as it would look in a variety of charts helps you select the kind of chart that best matches your date.
   It does a particularly good job when you want to use dates or years as labels. Sometimes, Excel gets confused and thinks that dates are part of the data – instead of labels.
4. Select the first Line chart. Press OK.
5. Your line chart is embedded in the Minimum Wage worksheet.
6. Make sure that the upper left corner of your chart is in the upper left corner of E4. The lower right corner is in the lower right corner of M20.
7. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.
8. Type the following in front of the first letter in the chart title: US National
9. While your chart is still selected, select the Design tab. You will find the Chart Styles group in the middle of the ribbon. If necessary, press the More button to see the available styles.
10. Float your cursor over the available styles so you can see how they will affect your chart. Select Style 4.
11. The years across the X (category) axis are a little hard to read.
    Select the labels. When you have them selected, you will see a box surrounding the list of years.
    On the Home tab, in the Alignment Group, select the Orientation tool. Select Angle Counter Clockwise.
12. Prepare the Minimum Wage worksheet for printing by changing the scaling to Fit Sheet on One Page.

Task2 – Oregon: Projected Life Expectancy at Birth

In the past 40 years, between 1970 and 2010, life expectancy for Oregon men improved by 8.7 years and for women by 5.5 years. Oregon’s life expectancy has remained slightly higher than the U.S. average. The life expectancy will continue to improve for both men and women. However, the gain for men has been outpacing the gain for women. Consequently, the difference between men’s and women’s life expectancies has continued to shrink.

https://www.oregon.gov/das/OEA/Documents/OR_pop_trend2019.pdf

1. On the Life Expectancy sheet, select A5:B11
2. Press F11
3. This creates a column chart, and puts it on a separate sheet.
4. Double click the chart sheet tab, change the name to Men.
5. Take a good look at this chart. It is not what we were expecting. Excel has made a mistake and charted the Birth Year information as though it was data, instead of using it to label the bottom (category) axis. That needs to be fixed. We need to tell Excel that the data in Column A are labels, not values.
6. With the chart selected, go to the Design tab, and select the Select Data tool.
   This opens the Select Data Source dialog box. The box at the top tells us the range we selected, which looks fine.

The Legend Entries need to be corrected to fix the issue with the data series (columns).  Also, the Horizontal Axis Labels are just a series of default numbers. They need to be the range that contains the years.

7. In the Legend Entries, click in the small box in front of Birth Year to remove the check mark.  This will remove the Birth Year as a data series on the chart.
8. In the Horizontal (Category) Axis Labels box, press the Edit button. This will open the Axis Labels dialog box. Press the Select range button.
9. Navigate to the Life Expectancy tab and select A6:A11. After “=’Life Expectancy’!$A$6:$A$11” is displayed in the box, press the Select Range button. Press OK.
   Press OK
10. Change the chart title so that it reads: Life Expectancy for Oregon Men.
11. Remove the Legend from the bottom of the chart by right-clicking on it and selecting Delete.
12. Return to the Life Expectancy tab, select A5:All , C5:C11 (Select the first range, hold down the CTRL key, select the next to select both noncontiguous ranges at the same time.
13. Repeat steps 2-11 above to create a matching chart for Life Expectancy for Oregon Women.
14. Return to the Life Expectancy tab, select A5:D11
15. Use the Recommended Charts tool to create a simple line chart.
16. Change the Chart Title to Oregon: Projected Life Expectancy at Birth.
17. The green line across the bottom of the chart represents the difference between men’s and women’s life expectancy. It is not very helpful as it is.
    Right click on the green line to open the pop up menu. Select Format Data Series. In the Format Data Series pane, under the Series Options tab, select the radio button in front of Secondary Axis.
18. Close the Format Data Series pane.
19. Add a text box that explains the Difference calculation. While the chart is still selected:
    1. On the Insert tab, on the right side of the ribbon, find Text. Select the Text Box tool.
    2. When you select it, your cursor will turn into cross hair (thin black plus sign)
    3. Click once in the lower left corner of your chart. This creates a text box.
    4. Type the following into the text box: Difference = Female life expectancy minus male.
       Move or resize the text box if you wish.
20. Move or resize your chart so that it no longer is on top of the spreadsheet data, and so it covers the text that starts in A19.
21. Use the Chart Styles tools to change your chart to something a bit more dramatic.
22. Preview the Life Expectancy worksheet in Print Preview and make any necessary changes.
23. Save the PR4 Historical Data workbook.
24. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR4 Historical Data  workbook as directed by your instructor.

Attribution

“4.5 Chapter Practice” by Noreen Brown, Portland Community College is licensed under CC BY 4.0