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