Tornado Center

DATA ANALYSIS WITH POWER TOOLS and Creating Macros

1. You work as a support specialist for the Tornado Center, a group of researchers at the University of Nebraska who provide statistics and other information to businesses and citizens in Nebraska. Your supervisor, Karen Chao, has asked you to gather data on Nebraska tornadoes from 2013 to 2015 and then provide statistical information to a client interested in business development. To do so, you need to import data from various sources and use the Excel power tools.

Start by collecting tornado data from another Excel workbook, which includes the Enhanced Fujita scale, a way to rate tornadoes according to the amount of damage they cause. Use the Get & Transform tools to create a query and load data from this workbook into a new table as follows:

1. 1. Create a new query that imports data from the Support_SC_EX16_10a_2013-2015.xlsx workbook.
   2. Load the data from the Tornadoes worksheet to a table in a new worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook, using Tornadoes as the name of the new worksheet.
   3. Because all of the data is for tornadoes in Nebraska, use the Query Editor to remove the State column, and then load the transformed data into the worksheet.
2. The client wants to know the population of the twenty most populous counties in Nebraska. To provide this information, create another query and load data from another Excel workbook into a new table as follows:
   1. Create a new query that imports data from the Support_SC_EX16_10a_Population.xlsx file, available for download from the SAM website.
   2. Load the data from the Population by County worksheet to a table in a new worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook, using Top 20 Population as the name of the new worksheet.
   3. Edit the table to display the data the client requested and make it more useful as follows:
      • Use the Query Editor to remove the top 3 rows, which contain a heading or blank cells.
      • Remove column 5, which includes a ranking calculation.
      • Use the first row as headers.
      • Sort the data in descending order by Population.
      • Keep the top 20 rows of the data.
      • Load the transformed data into the worksheet.
3. Add the two queries you created to the data model so you can use them with Power Pivot as follows:
   1. Add the Population by County query to the data model.
   2. Add the Tornadoes query on the Tornadoes worksheet to the data model. (Hint: Do not close the Power Pivot for Excel window after performing this substep.)
   3. Use the Power Pivot for Excel window to create a PivotTable in a new worksheet.
   4. Use Tornado PivotTable as the name of the new worksheet.

The client wants to know which of the twenty most populated counties experienced tornadoes in 20132015 along with the population of each county. Build the new PivotTable to display the tornado and population data at the same time as follows:

5. Use the following fields from the Population_by_County table in the PivotTable areas:
   • County field: Rows box
   • Population field: Values box

1. 6. Use the following field from the Tornadoes table in the PivotTable areas:

• • Fujita field: Values box

1. 7. Create a relationship using the following tables and columns:

• • Table: Tornadoes
  • Column (Foreign): County
  • Related Table: Population_by_County
  • Related Column (Primary): County

3. 8. Use County as the column heading in cell B3, use Population as the column heading in cell C3, and use Tornadoes as the column heading in cell D3. (Hint: The (blank) entry is for tornadoes that occurred in counties not among the 20 most populated counties.)
4. The client wants to know how concentrated the population is in each of the 20 most populous counties. Create another PivotTable that shows the population per square mile in these counties as follows:
   1. Use the Power Pivot for Excel window to create a second PivotTable in a new worksheet. (Hint: If necessary, use the Manage button in the Data Model group on the Power Pivot tab to open the Power Pivot for Excel window.)
   2. Use Population PivotTable as the name of the new worksheet.

Close the Power Pivot for Excel window.

1. 3. Build the new PivotTable to use the following fields from the Population_by_County table in the PivotTable areas:

4. • County field: Rows box
   • Population field: Values box
   • Sq Mi field: Values box
5. To add a column showing the population per square mile, create a measure as follows:
   1. Use Population per Sq Mi as the name of the new measure.
   2. Use [Sum of Population]/[Sum of Sq Mi] as the formula.
   3. Choose Number as the category and Whole Number as the format.
6. To provide another visual representation of the tornado data for the top 20 most populated Nebraska counties, create a Power View report as follows:
   1. Insert a Power View report on a new worksheet, using Power View as the name of the new worksheet. Move the newly created worksheet so that its the fifth worksheet in the workbook. (Hint: If you cannot move the worksheet itself, move the other worksheets.)
   2. Select the Fujita field in the Tornadoes table.
   3. Select the County and Population fields (in that order) in the Population_by_County table. (Hint: If a message appears indicating you may need relationships between tables, close the message. You already created the relationships.)
   4. Resize the table to fill the left pane of the Power View area.
   5. Add the Fujita field in the Tornadoes table to the Filters pane.
   6. Filter the data to display populations for counties that experienced tornadoes with an EF-1 rating.
   7. Switch the visualization of the data to a Clustered Bar chart.
   8. Use County Population and Tornado Rating as the chart title.
7. Switch to the Top 20 Population worksheet. To develop a map showing tornado data for the 20 most populated counties in Nebraska, create a 3D map as follows:
   1. Open the 3D Maps window, and in Scene 1, use the State field in the Population_by_County table as a Location to focus on the state of Nebraska.
   2. Display state name labels on the map.
   3. Add the County field in the Population_by_County table as a second Location to indicate the 20 most populous counties in Nebraska.
   4. Add the Fujita field in the Tornadoes table as the Height to show where the most tornadoes occurred.
   5. Close the field list, remove the legend, and zoom in six times to display the Nebraska city names on the map, and then tilt the map up four times.
   6. Create a text box using Tornadoes in the Top 20 Nebraska Counties as the text in the title field.
   7. Capture the screen showing the 3D map, and then close the 3D Maps window.
   8. Paste the map in cell A1 of the Home Page worksheet in the SC_EX16_10a_FirstLastName_2.xlsx workbook.
8. Add hyperlinks to the Home Page worksheet as follows to improve navigation in the workbook:
   1. In cell M3, link the Top 20 Nebraska Counties text to cell A1 of the Top 20 Population worksheet in the current workbook.
   2. In cell M4, link the Nebraska Tornadoes by County text to cell A1 of the Tornadoes worksheet in the current workbook.
9. Your supervisor, Karen Chao, wants to include a note on two worksheets that the Tornado Center created this workbook. Create and record a macro to automate this task as follows:
   1. Enable all macros in the workbook, and make sure the Use Relative References option is selected in the Developer tab.
   2. Select cell M7.
   3. Create a macro to be stored in this workbook using Created_by as the name of the macro.
   4. Use CTRL+m as the shortcut key.
   5. Begin recording the macro, and enter the following text in cell M7:

This workbook was created by the Tornado Center at the University of Nebraska.

9. 6. Apply wrap text formatting to cell M7.
   7. Stop recording the macro.
10. Go to the Enhanced Fujita Scale worksheet. In cell E3, run the Created_by macro.

Your workbook should look like the Final Figures on the following pages. Depending on your version of Office, the order of the worksheets may be different. With the exception of the worksheet mentioned in step 6, this will not affect your grading. Save your changes (as a macro-enabled workbook), close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.