Shelly Cashman Excel 2016 | Module 10: SAM Mac Project 1b Hennepin Consultants DATA ANALYSIS WITH POWER TOOLS AND CREATING MACROS GETTING STARTED ? Open the file SC_EX16_Mac_10b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Shelly Cashman Excel 2016 | Module 10: SAM Mac Project 1b

Hennepin Consultants

DATA ANALYSIS WITH POWER TOOLS AND CREATING MACROS

GETTING STARTED

?

Open the file

SC_EX16_Mac_10b_

FirstLastName

_1.xlsx

, available for

download from the SAM website.

?

Save the file as macro-enabled worksheet

SC_EX16_Mac_10b_

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:

o

Support_SC_EX16_Mac_10b_Households.txt

o

Support_SC_EX16_Mac_10b_MN_Population.txt

o

Support_SC_EX16_Mac_10b_Map.png

?

With the file

SC_EX16_Mac_10b_

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 Developer tab to the ribbon

as

follows:

o

Click the More Commands... option in the dropdown in the Quick Access

Toolbar. In the Main Tabs area of the Ribbon & Toolbar dialog box, click the

Developer check box, then click the Save button to close the Ribbon and

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

PROJECT STEPS

1.

You work as an assistant to Tim Jensen, the owner of Hennepin Consulting,

which performs market research for businesses who want to move to Minnesota.

Tim has asked you to gather data on Minnesota demographics from 2002 to

2019 and then provide statistical information to a client. To do so, you need to

import data from various sources and use the Excel power tools.

Start by collecting population data from another file, which lists the Minnesota

population.

a.

With the

County Population

worksheet as the active worksheet, create a

new query that imports data from the

Support_SC_EX16_Mac_10b_Households.txt

file in a new worksheet in

the

SC_EX16_Mac_10b_FirstLastName_2.xlsm workbook. 

b.

Start loading the data at row 1, and separate the columns using tab

delimiters.

c.

Because all of the data is for Minnesota, skip the State column, and then

load the transformed data into the new worksheet using

Households

as

the name of the new worksheet.

d.

Format the imported data as a table with headers using the

Table Style

Dark 9

table style. (

Hint

: Depending on your version of Office, the table

style may be written as Orange, Table Style Dark 9. If an alert comes up,

press “Yes” to convert the range to a table.)

e.

For the Persons per Household data, set the decimal places to two places.

2.

The client wants to know the twenty most populous counties in Minnesota. To

provide this information,

create another query as follows:

a.

Create a new query that imports data from the

Support_SC_EX16_Mac_10b_MN_Population.txt

file in a new

worksheet in the

SC_EX16_Mac_10b_FirstLastName_2.xlsm workbook

.

b.

Start loading the data at row 3, and separate the columns using tab

delimiters.

c.

Skip column 6, which includes a record count, and then load the

transformed data into the new worksheet using

Top 20 Counties

as the

name of the new worksheet.

d.

Format the imported data as a table with headers using the

Table Style

Dark 9

table style. (

Hint

: Depending on your version of Office, the table

style may be written as Orange, Table Style Dark 9. If an alert comes up,

press “Yes” to convert the range to a table.)

e.

To make the data the client requested more useful, sort the data in

descending order by Population, and then delete all rows of data except

the header row and the top 20 rows of data.

3.

The client wants to know the number of people per household in the twenty

most populated counties along with each’s population. Build a new PivotTable as

follows

:

a.

Rename the

Sheet4

worksheet using

Household PivotTable

as the name

of the worksheet.

b.

Use the following fields in the PivotTable areas:

o

County Name field: Rows box

o

Population field: Values box

o

Persons Per Household (PPH) field: Values box (Add this below the

Population field.)

c.

Summarize the Count of Population and Count of Persons Per Household

(PPH) fields using the Sum function.

d.

Use

County

Population

as the column heading in cell B3, and use

Persons Per Household

as the column heading in cell C3. (

Hint

: The

blank entry is for data for counties other than those with the top 20

population.)

e.

Format the Persons Per Household data using the

Number

format with

2

decimal places and no thousands separator.

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.

Using the table in the Top 20 Counties worksheet, create a second

PivotTable in a new worksheet, using

Population PivotTable

as the name

of the new worksheet.

b.

Build the new PivotTable using the following fields in the PivotTable areas:

o

County Name 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 calculated

column as follows:

a.

Divide the Population column by the Sq Mi column as the formula.

b.

Use

Population per Sq Mi

as the custom name of the calculated column.

c.

Format the field using the

Number

number format with

0

decimal places.

6.

To provide a visual representation of the data for the top 20 most populated

Minnesota counties, create a chart as follows:

a.

In the

County Population

worksheet, add the County Name field to the

Rows area and the Population field to the Values area of the PivotTable.

Summarize the Count of Population field using the Sum function.

b.

Filter the data to only display population for the counties Anoka, Dakota,

Hennepin, Ramsey, and Washington.

c.

Insert a

Clustered Bar

chart based on the data in the PivotTable. Move

the clustered bar chart to a new worksheet, using

CP Chart

as the name

of the worksheet.

d.

Format the chart using the Style 3 chart style.

e.

Switch the column and row data so that the legend shows the county

names. (

Hint

: Depending on your version of Office, your chart may appear

with field buttons. If so, hide the field buttons and continue.)

f.

Remove the vertical axis label from the chart.

g.

Add a primary horizontal axis title using

Population Count

as the title.

h.

Use

Population by County

as the chart title.

7.

Switch to the

Home Page

worksheet. To show the household data for the 20

most populated counties in Minnesota, display

a 3D map as follows:

a.

Insert the map image

Support_SC_EX16

_Mac_

10b_Map.png

in cell A3

of the

Home Page

worksheet in the

SC_EX16_Mac_10b_FirstLastName_2.xlsm workbook.

b.

Resize and reposition the image so that the upper-left corner is within cell

A3 and the lower-right corner is within cell J30.

c.

Insert a Textbox using

Households in the Top 20 Minnesota Counties

as the text.

d.

Format the textbox using the

Moderate Effect - Orange, Accent 2

shape style, and change the font size to

28 pts

.

e.

Resize and reposition the textbox so that the upper-left corner is within

cell C25 and the lower-right corner is within cell J30.

8.

Add hyperlinks to the

Home Page

worksheet as follows to improve navigation in

the workbook:

a.

In cell A32, link the “Top 20 Minnesota Counties - Population” text to cell

A1 of the

Top 20 Counties

worksheet in the current workbook.

b.

In cell A33, link the “Minnesota Households” text to cell A1 of the

Households

worksheet in the current workbook.

9.

Tim wants to include a note on two worksheets that you created in this

workbook. While still on the

Home Page

worksheet, create and record a macro

to automate this task as follows:

a.

Enable all macros in the workbook.

b.

Select cell A34.

c.

Create a macro to be stored in this workbook using

Created_by

as the

name of the macro.

d.

Use Option+Cmd+

j

as the shortcut key.

e.

Begin recording the macro, and apply wrap text formatting to cell A34.

f.

Enter the following text in cell A34:

This workbook was created by Hennepin Consultants.

g.

Stop recording the macro.

10.

Go to the

Top 20 Counties

worksheet. In cell E23, run the Created_by macro.

Your workbook should look like the Final Figures below. Depending on your version of

Office, your final figures may look slightly different, but this should 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.

Final Figure 1: Home Page Worksheet

Final Figure 2: Population PivotTable Worksheet

Final Figure 3: Top 20 Counties Worksheet

Final Figure 4: Households Worksheet

Final Figure 5: Household PivotTable Worksheet

Final Figure 6: CP Chart Worksheet

Final Figure 7: County Population Worksheet