Save popularkids.xlsx (on your M: drive) from Blackboard

Read Overview worksheet

In a browser, open the URL given in cell A1 of the Overview worksheet

Highlight the data on the Web page and Copy

Paste the data into a text file (Notepad) and save it

Insert a new worksheet RawData

Populate worksheet with data from file: Data > From Text (Delimited > Tab > Finished)

Copy RawData to a new worksheet named Data

Add range names for each column: Gender, Grade, Age, Race, UrbanRural, School, Goals, Grades, Sports, Looks, Money

Insert a new worksheet RangeNames to document the names that you have defined (Formulas > Use in Formula > Paste Names)

Access:

Open Access 2013 and create an empty database called popularkids

Import the Data Excel worksheet into a table called data External Data > Excel

Choose Import the source data into a new table in the current database Browse to your Excel file and click OK

Choose Data worksheet to import and click Next>

Select box First Row Contains Column Headings and click Next

General format is OK for now, click Next>

Let Access add primary key;Next>

Import to Table: Data; Finish; Close

Explore Access:

Home tab Sort & Filter Advanced > Filter by Form > Toggle Filter

Insert a new worksheet GoalsAnalysis to analyze the Goals data by gender

The yellow formula represents the number of respondents of that gender that chose the goal as the most important to them. HINT: Lookup the COUNTIFS function in Excel help for these formulas. The green and blue represent formulas that count the total numbers. Note that there are 478 respondents.

Now you can define a pie chart and 2 clustered column charts as shown below to visualize the answer. Link to data: http://lib.stat.cmu.edu/DASL/Datafiles/PopularKids.html