Shelly Cashman Excel 2016 | Module 10: SAM Project 1a Tornado Center DATA ANALYSIS WITH POWER TOO GETTING STARTED   Open the file SC_EX16_10a_ FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2016 | Module 10: SAM Project 1a

Tornado Center

DATA ANALYSIS WITH POWER TOO

GETTING STARTED

 

Open the file SC_EX16_10a_ FirstLastName_1.xlsx  available for download from the SAM website.

 

Save the file as macro-enabled worksheet SC_EX16_10a_ FirstLastName _2.xlsm by changing the “1” to a “2”. o

If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

 

Support_SC_EX16_10a_2013-2015.xlsx

o

Support_SC_EX16_10a_Population.xlsx

?

With the file

SC_EX16_10a_

FirstLastName

_2.xlsm

still open, ensure that

your first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

?

To complete this project, you need to add the Power Pivot and Developer tabs to

the ribbon

and the Power View button to the Insert tab as follows:

o

From the File tab, click the Options button. In the Data section of the

Advanced tab, click the checkbox next to Enable Data Analysis add-ins:

Power Pivot, Power View, and Power Map. (Microsoft Silverlight is required

for Power View.) In the Add-ins tab, choose Manage COM add-ins and click

Go... Click the box next to Microsoft Power Map for Excel, Microsoft Power

Pivot for Excel, and Microsoft Power View for Excel, and click OK.

*Power Pivot, Power View, and Power Map are not available on Microsoft

Office Home and Business 2016 and Microsoft Office 365 Business

.

o

Right-click any tab on the ribbon, and then click Customize the Ribbon on

the shortcut menu. In the Main Tabs area of the Excel Options dialog box,

click the Developer check box, then click the OK button to close the Excel

Options dialog box and add the Developer tab to the ribbon.

PROJECT STEPS

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.

 

Shelly Cashman

Excel 2016 | Module 10: SAM Project 1a

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:

a.

Create a new query that imports data from the

Support_SC_EX16_10a_2013-2015.xlsx

workbook.

b.

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.

c.

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:

a.

Create a new query that imports data from the

Support_SC_EX16_10a_Population.xlsx

file, available for download

from the SAM website.

b.

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.

c.

Edit the table to display the data the client requested and make it more

useful as follows:

o

Use the Query Editor to remove the top

3

rows, which contain a

heading or blank cells.

o

Remove column 5, which includes a ranking calculation.

o

Use the first row as headers.

o

Sort the data in descending order by Population.

o

Keep the top

20

rows of the data.

o

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

:

a.

Add the Population by County query to the data model.

b.

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.)

c.

Use the Power Pivot for Excel window to create a PivotTable in a new

worksheet.

d.

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 2013–2015 along with the population of each county.

Build the new PivotTable to display the tornado and population data at the same

time as follows

:

 

Shelly Cashman

Excel 2016 | Module 10: SAM Project 1a

e.

Use the following fields from the Population_by_County table in the

PivotTable areas:

o

County field: Rows box

o

Population field: Values box

f.

Use the following field from the Tornadoes table in the PivotTable areas:

o

Fujita field: Values box

g.

Create a relationship using the following tables and columns:

o

Table: Tornadoes

o

Column (Foreign): County

o

Related Table: Population_by_County

o

Related Column (Primary): County

h.

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:

a.

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.)

b.

Use

Population PivotTable

as the name of the new worksheet.

Close the Power Pivot for Excel window.

c.

Build the new PivotTable to use the following fields from the

Population_by_County table in the PivotTable areas:

o

County field: Rows box

o

Population field: Values box

o

Sq Mi field: Values box

5.

To add a column showing the population per square mile, create a measure as

follows:

a.

Use

Population per Sq Mi

as the name of the new measure.

b.

Use

[Sum of Population]/[Sum of Sq Mi]

as the formula.

c.

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:

a.

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

it’s the fifth worksheet in the workbook.

 

Shelly Cashman

Excel 2016 | Module 10: SAM Project 1a

b.

Select the

Fujita

field in the Tornadoes table.

c.

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.)

d.

Resize the table to fill the left pane of the Power View area.

e.

Add the

Fujita

field in the Tornadoes table to the Filters pane.

f.

Filter the data to display populations for counties that experienced

tornadoes with an

EF-1

rating.

g.

Switch the visualization of the data to a Clustered Bar chart.

h.

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, c

reate a 3D map as

follows:

a.

Open the 3D Maps window, and i

n Scene 1, use the State field in the

Population_by_County table as a Location to focus on the state of

Nebraska.

b.

Display state name labels on the map.

c.

Add the County field in the Population_by_County table as a second

Location to indicate the 20 most populous counties in Nebraska.

d.

Add the Fujita field in the Tornadoes table as the Height to show where the

most tornadoes occurred.

e.

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.

f.

Create a text box using

Tornadoes in the Top 20 Nebraska Counties

as the text in the title field.

g.

Capture the screen showing the 3D map, and then close the 3D Maps

window.

h.

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:

a.

In cell M3, link the “Top 20 Nebraska Counties” text to cell A1 of the

Top

20 Population

worksheet in the current workbook.

b.

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:

a.

Enable all macros in the workbook, and make sure the Use Relative

References option is selected in the Developer tab.

b.

Select cell M7.

 

c.

Create a macro to be stored in this workbook using

Created_by

as the

name of the macro.

d.

Use CTRL+

m

as the shortcut key.

e.

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.

f.

Apply wrap text formatting to cell M7.

g.

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. 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.

Final Figure 1: Home Page Worksheet

Final Figure 2: Enhanced Fujita Scale Worksheet

Final Figure 3: Population pivotTable worksheet

Final Figure 4: Power view worksheet

 

Final Figure 5: Tornado PivotTable Worksheet

Final Figure 6: Tornadoes Worksheet

Final Figure 7: Top 20 Population Worksheet