CI2000: Week 5 Assignment

Objectives

Directions

Opening a .csv file

1. Download the .csv file from the assignment directions

Opening and Saving the the .csv file

2.     Open the file with Excel.

3.     From the File tab and select Save As.

4.     Use the “Save as Type” pull-down (just below the “File Name” field) to change the file format to Excel Workbook.

5.     Name the file with your name.

Select all data

6. Use Control A to select all data cells.

*Note: An alternative is to click in the data and let Excel determine the range of data. Do not use the Select All light grey triangle at the top left of A and above row 1. This will select the entire sheet, not just your data.

Format as table

7. The Format as Table command is in the Styles group on Home Tab. *Note: Make sure the box “My table has headers” is checked.

Filter table

8. Click on the pull-down arrow in B1, click Select All to deselect, then click on TWO states to select.

Filter table

9.     Use City Name filter to select 1 or 2 cities in each state.

10.  Click Select All to deselect, then select at least one city in each state.

Filter table

11.  Scroll over to column M to find the “Measured” value.

12.  Use the pull-down, uncheck Select All, and then choose 1 or 2 unhealthy behaviors measures.

Integrating Excel findings in Word

13.  Start a new Word document. Think about the different data values in column J across the cities you chose. Write a few sentences about what you found, including the similarities or differences between cities.

14.  Save the Word document with your name and Assignment wk5.

Why Pivot Tables?

15. In Excel, the data is not arranged in a way to easily make a chart. Copying and pasting the data into a new arrangement is messy. For this reason, you will need to use a Pivot Table.

Inserting a Pivot Table

16.  Click the Insert tab in the ribbon.

17.  Click on the “Recommended Pivot Tables” and select the first thumbnail.

18.  Click “Okay” in the dialog box. The pivot table will be on a new sheet.

Pivot Tables:

Removing Row and Values Fields

19. At the bottom right corner, you will see four boxes that are titled: “Filters,” “Columns,” “Rows,” and “Values.” In the Rows box, click on the pulldown arrow for “StateAbbr” and select “Remove Field.” Do the same for the “Sum of Data_Value” in the “Values” box.

Pivot Tables: Using Fields

20.  Within the “PivotTable Fields” box, select “CityName” and drag it to the Columns field.

21.  Then select “Measure” from the scrolling field list and drag it to the Rows box.

22.  Drag “Data Value” from the field list to the “Values” field.

Filtering the CityNames

23.  Drag the “CityName” from the columns field to the Filters field.

24.  On cell B1, click on the pull-down arrow.

25.  Click the box “Select Multiple Items,” de-select “All,” and then check the same cities you previously selected on the other Excel worksheet.

26.  Drag the “CityName” field from Filters back to the Columns field. You should see the names of the cities you selected in a column. Notice the pull-down arrows in B3 that allow you to change the selection of cities or measures to display.

Adding a Pivot Chart

27.  Add another city to your table and chart.

28.  Click in the pivot table, then on “Analyze” in the Pivot Table Tools in the ribbon.

29.  Click on Pivot Chart in the Tools group. Click “Okay” in the chart selection dialog box.

Copy the chart

30. Right-click on a white space in the chart and select copy.

Integration: Linking the chart from Excel to your Word document

31.  In your Word document, right click, view "Paste Options," and select the “Keep Source Formatting & Link” option.

32.  Paste the table below the paragraph you wrote.

Integration: Excel to Word Live Updates

33. In Excel, use the CityName filter pulldown on the chart to select an additional city.

Integration: Excel to Word Live Updates

34. Notice how the Word document has immediately updated the change you made in Excel.

*Note: Your table should look similar to (not the same as) the one below.

35. Submit your Word document as the assignment.

among adults smoking among physical activity             adults aged                than 7 hours aged >=18   adults aged                among adults            >=18 Years                  among adults

                Years                 >=18 Years           aged >=18                                             aged >=18

                                                                  Years                                                       Years