Scenario

You are asked to do analysis on a survey taken by school children. You have chosen to do goals analysis and rank analysis. The data that you need is maintained in a database, so you make a request of the database team to export data into a tab$-$delimited text file. The text file is named popularkidsdata.txt and is saved in a folder on your laptop. You will transform the data from tabular view to highly presentable and useful information.

Rename popularkids.xlsx that you downloaded from Canvas as popularkids$\_$your name.xlsx and open it$.$

Read Overview worksheet in Excel

Insert a new worksheet named Data

Copy and paste the data from the file popularkidsdata.txt into the Data worksheet. The text file popularkidsdata.txt is tab$-$delimited and includes a header row.

Create a range name for each column in the Data worksheet. Range names must be used in calculations whenever applicable. The range names Scope must be Workbook.

Note: you may, optionally, import data in table format and use table$-$name$[$column$-$name$]$ references in formulas and receive full credit for steps $4$ & $5.$

$6.$ Insert a new worksheet named Goals Analysis

$7.$ Create a summary data section in Goals Analysis worksheet with the following format:

$\backslash$table$[[$Goals$,$Girl,Boy,Total$],[$Grades$],[$Popular$,,,],[$Sports$,,,],[$Total$,,,]]$

Populate the cells using formulas and range names or table references. For example, using range names, in the cell that intersects Grades & Girl, enter

$=$COUNTIFS $($Gender$,$ "Girl", Goals, "Grades"$)$

Calculate totals on right and bottom using SUM function

Insert two clustered column charts and three pie charts to transform the visual display of your data analysis. Use your own preferred chart titles, styles, colors, labels, etc. When you complete this step your Goals Analysis worksheet may look as follows: