CSIT58 Task #12 Assignment: Worksheets and Charting

Due November 22. Late deadline November 29.

Reading:

• Excel Project 2 "Using Formulas" in Dozer's Quintessential Guide to Computer Literacy
• Excel Project 3 "Formatting a Worksheet" in Dozer's Quintessential Guide to Computer Literacy
• Excel Project 5 "Charts" in Dozer's Quintessential Guide to Computer Literacy

In this assignment you will complete the identity theft worksheet you started in Task 11. You will add formulas, formatting and 2 charts.

1. Open the workbook from the last assignment, Task11.
2. Click in cell B3. Click after 2008 and press the Alt+Enter key combination on your keyboard. Delete the space before the word Rank and press Enter. The row will become taller and the text will be wrapped.
3. Repeat to wrap the labels in C3 and D3.
4. Make columns B, C and D narrower so they just fit the data.
5. In cell H3, enter the column label: % Change 06 to 08
6. Click in cell H3 and click the Wrap text button in the Home tab of the ribbon. Excel 2003 users click after the e in Change and press Alt+Enter from the keyboard to wrap the text.
7. (4 points) In cell H4, enter a formula to calculate the percent change. The formula should take the number for 2008 and subtract the number for 2006, then divide the result by the 2006 number. Remember the order of operations when creating this formula. Multiply and divide are completed first (left to right), then plus and minus. You can force a different order by using parenthesis.
8. Use the fill handle to copy the formula down for the other metropolitan areas.
9. (2 points) Format the percent change as % with one decimal place.
10. In cell A14, type the label: Average
11. (3 points) In cell E14, enter the AVERAGE function to calculate the average for 2008. Do not include the column label in the cell range.
12. Use the fill handle to copy the function across the row for all years.
13. Format all of the identity theft numbers and averages to have one decimal place.
14. Merge and center the title in A1 across columns A - H.
15. Use a fill color of your choice for the title.
16. Make the font size of the title larger so it fills the area over the data nicely (or smaller if you made a very long title).
17. Adjust column widths as needed so all data displays.
18. Add at least 2 different borders of your choice.
19. Right-align the column headings and apply some other formatting to make them look attractive.
20. Next you will create a column chart of the metropolitan areas and the years. This is a little tricky because Excel treats the year column labels as part of the data. Drag to select the identity theft data for 2008 through 2006 for the top 5 states, E4:G8.
21. Click the Insert tab, then click Column and select a column chart style. In Excel 2003, click Insert, Chart and choose a column chart.
22. Click the Select Data button on the Chart Tools Design tab. Click the Edit button under Horizontal (Category) Axis titles. Drag to select the top 5 metropolitan areas and click OK. In Excel 2003, right click the chart and choose Source Data. On the Series tab, click in the Category (X) axis labels box and then drag to select the top 5 metropolitan areas. The labels should show up on the x-axis on the chart.
23. Click Series1 under Legend Entries (Series) and then click Edit. Click in cell E3 for 2008 and click OK. Repeat to add 2007 and 2006 instead of Series2 and Series3. Click OK. In Excel 2003, click on Series1 and then in the Name box. Click on the 2008 column label in the worksheet. Repeat for the other Series and years and click OK. The years should appear in the legend of the chart.
24. Move the mouse pointer into a blank area of your new chart. Hold down the left mouse button and move the chart below your data. Make the chart larger so it is easier to read.
25. Make sure the chart is still selected. Click the Design tab. Use the Chart Styles options to change the color scheme of the chart. In Excel 2003, right click over the bars and choose Format Data Series, then change 2 of the colors to yellow and red.
26. In the Chart Tools Layout tab, click Chart Title. Choose Above Chart. Type a descriptive title.  In Excel 2003, in the blank area of the chart, right click and choose Chart Options. Then choose Title.
27. Next you will make a pie chart with wedges showing the percent increase in identity thefts for the top five areas. Select the first 5 area names. Then hold down the Ctrl key on the keyboard and select the corresponding numbers in the percent change column. Both columns of data should be selected at one time. Insert a pie chart.
28. Give the chart a descriptive chart title.
29. Use the Data Labels button on the Chart Tools Layout tab to put the percent change numbers on the wedges. In Excel 2003, right click in the chart and choose Chart Options. Under the Data Labels tab, check Value.
30. Put the pie chart on a separate sheet by clicking the Move Chart button on the Chart Tools Design tab. Choose New Sheet in the dialog box. In Excel 2003, right-click the chart and choose Location.
31. Change the Sheet1 tab to a descriptive name.

Save this worksheet as Task12. Upload it to Tasks, Tests and Surveys.