Unit 5 Practice

Start Assignment

• DueSundayby11:59pmPoints10Submittinga file uploadAvailableSep 13 at 12am - Sep 19 at 11:59pm7 days

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)

Excel is an excellent tool for helping to display historical data. In this exercise, we will be examining ways to display information on minimum mage 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(Links to an external site.)

When saving files for submission, include your last name in the file name.

1. Open the file namedPR4 Datain the Data Files and then Save AsPR4 Historical Data.
2. On theMinimum Wageworksheet, select the rangeB4:C60
3. Select theInserttab, then theRecommended Charttool in theChartsgroup.
4. 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.
5. 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.
6. Select the firstLine chart. PressOK.
7. Your line chart is embedded in theMinimum Wageworksheet.
8. Make sure that the upper left corner of your chart is in the upper left corner ofE4. The lower right corner is in the lower right corner ofM20.
9. Adjusting the chart title: Click thechart titleonce. 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.
10. Type the following in front of the first letter in the chart title:US National
11. While your chart is still selected, select theDesigntab. You will find theChart Stylesgroup in the middle of the ribbon. If necessary, press theMorebutton to see the available styles.
12. Float your cursor over the available styles so you can see how they will affect your chart. SelectStyle 4.
13. The years across theX(category) axis are a little hard to read.
14. Select the labels. When you have them selected, you will see a box surrounding the list of years.
15. On theHometab, in theAlignmentGroup, select theOrientationtool. SelectAngle Counter Clockwise.
16. Prepare theMinimum Wageworksheet for printing by changing the scaling toFit 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_trend2012.pd

Actions

1. fOn theLife Expectancysheet, selectA5:B11
2. Press theFnkey andF11.
3. This creates a column chart and puts it on a separate sheet.
4. Double click the chart sheet tab, change the name toMen.
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 theDesigntab, and select theSelect Datatool.
7. This opens the Select Data Source dialog box. The box at the top tells us the range we selected, which looks fine.

TheLegend Entriesneed to be corrected to fix the issue with the data series (columns). Also, theHorizontal Axis Labelsare just a series of default numbers. They need to be the range that contains the years.

1. In theLegend Entries, click in the small box in front ofBirth Yearto remove the checkmark. This will remove the Birth Year as a data series on the chart.
2. In theHorizontal (Category) Axis Labelsbox, press theEditbutton. This will open theAxis Labelsdialog box. Press theSelectrange button.
3. Navigate to theLife Expectancytab and selectA6:A11. After "='Life Expectancy'!$A$6:$A$11" is displayed in the box, press theSelect Rangebutton. PressOK.
4. PressOK
5. Change the chart title so that it reads:Life Expectancy for Oregon Men.
6. Remove the Legend from the bottom of the chart by right-clicking on it and selecting Delete.
7. Return to theLife Expectancytab, selectA5:All,C5:C11(Select the first range, hold down the Ctrl key, select the next to select both non-contiguous ranges at the same time.
8. Repeat steps 2-11 above to create a matching chart forLife Expectancy for Oregon Women.
9. Return to theLife Expectancytab, selectA5:D11
10. Use theRecommended Chartstool to create a simple line chart.
11. Change the Chart Title toOregon: Projected Life Expectancy at Birth.
12. 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.
13. Right-click on the green line to open the popup menu. SelectFormat Data Series. In theFormat Data Seriespane, under theSeries Optionstab, select the radio button in front of Secondary Axis.
14. Close theFormat Data Seriespane.
15. Add a text box that explains the Difference calculation. While the chart is still selected:
16. On theInserttab, on the right side of the ribbon, findText. Select theText Boxtool.
17. When you select it, your cursor will turn into crosshair (thin black plus sign)
18. Click once in the lower-left corner of your chart. This creates a text box.
19. Type the following into the text box:Difference = Female life expectancy minus male.
20. Move or resize the text box if you wish.
21. 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.
22. Use theChart Styles tools to change your chart to something a bit more dramatic.
23. Preview theLife Expectancyworksheet in Print Preview and make any necessary changes.
24. Save thePR4 Historical Dataworkbook.
25. Submit thePR4 Historical Data workbookas directed by your instructor.