Start with the Excel data file.

Make a copy of the data file on a new sheet $($right$-$click the sheet tab $>$ Move or Copy $>$ Create copy$)$ and rename this new sheet WIP $($for work$-$in$-$progr

Prepare$/$clean the data file $($on the WIP sheet$)$ to make it ready for analysis. Please try to use Excel features$/$functions$/$commands with minimal typing.

Report title, report footer $($at the bottom$),$ extra rows with no data $--$ Should be removed.

Cell Background Format $($merged cells$)--$ Should be reformatted$/$unmerged $($Home Tab $>$ Alignment Group $>$ Merge & Center $>$ Unmerge Cells$)$

Rename the column Background Format $--$ should be removed $($Home Tab $>$ Editing Group $>$ Clear $>$ Clear Format$)$

Rename the column Data to Public Transit

Split data in the Region column into Region Name and States. Do not include the word Metro or Metro Area in the final data set.

Find and Replace Chicago with IL

Rearrange the columns in this order: Region Code, year, Region Name, States, Public Transit

Create a copy of the cleaned data into a new sheet. Use a meaningful sheet name for this new sheet. Apply Excel Table to the data set $($Insert Tab $>$ Table$)$ an PublictransitData

Create a metadata sheet for this data set with appropriate description.

Hint: Video on Metadata is located toward the end of the video on Join $($on the $3\mathrm{.}3$ Using Excel for Data Wrangling page$)$

Create a chart $($or PivotChart$)$ from this clean data set comparing the total number of people using public transit in four different metro areas over the years $(i.$ each year for all y $-$ Gary Division $-$ Bloomington

Indianapolis$-$Carmel$-$Anderson

Louisville$-$Jefferson County

Save the Excel file and submit it to this assignment.