Gift Card Emporium, the medium$-$sized e$-$tailer where you work, is interested in learning more about their gift card sales. Your manager has asked you to work with a subset of the sales data to see what kind of information can be gained from analysis. The data you are presented is in good shape but needs some cleansing and organizing before it can be used appropriately. Your task is to prepare this dataset and perform analyses to show your manager what they could expect if the decision to analyze the full dataset was made. No IF functions may be used for Part $2$ except for Step $2$ and if needed in Step $5.$

Complete the following:

$1.$ Open the workbook named GiftCard.xlsx and save it as GiftCardAnalysis$($your last name$).$xlsx$.$

$2.$ Before we can conduct the analysis, the data needs to be prepared. In the EntryFormatted column, if the Entry Number is odd, format the number as $$XXXX$-$XXXX$.$ If Entry Number is even, format it as $$XX$-$XXX$-$XXX$.$ Use the same formula for all rows.

a$.$ The Entry Number data is stored as text. Do not manually change this to number as you would then lose the leading $0$ on some of the data.

b$.$ This means that you will need to see if a value is odd by first converting the last digit to a number. You do this with ISODD$($VALUE$($RIGHT$($XXXX$,1)))$ where XXXX is your cell reference. The RIGHT$($XXXX$,1)$ gets the last digit, then VALUE$()$ turns the text into a number, then ISODD$()$ provides true or false if that number is odd or not.

$3.$ In the Name column, use a formula to display the First Name and Last Name together, in that order. Include a space between the first and last names.

$4.$ The Address column has extra spaces in the values. In the Add$\_$Fixed column, use a formula to remove these extra spaces.

$5.$ The Phone is currently in a format that Gift Card Emporium cannot use. In the FixedPhone column, use a formula to reformat these phones to $($XXX$)$ XXXXXXX$($no quotes$).$ For example, a phone shown as $999-898-5765$ should be shown as

$(999)8985765($no quotes$).$ Use the MID function to retrieve the numbers between the two dashes.

$6.$ Your manager looked at your new phone format and decided that thy want it a little different. In the ActualPhone column, use the TEXT function to take the data from the Phone column and format it$.$ If the original phone number does not have an area code, the result should be formatted as $$XXX$-$XXXX$.$ Example: $999-5555.$ If the phone number does have an area code, the result should be formatted as $($XXX$)$ XXX$-$XXXX$($no quotes$).$ Example: $(999)555-5555.$

$7.$ The Date is also currently in a format that Gift Card Emporium cannot use. In the FixedDate column, use a formula to reformat these dates to $$MM$/$DD$/$YYYY$.$ For example, a date shown as $20200102,$ which is January $2$nd$,2020,$ should be shown as $01/02/2020($no quotes$).$ A single formula is needed $($breaking it down into parts in new columns and recombining with concatenate is not the answer$).$ Format the resulting cells as a short date $$ do not use the DATE function in your formula.

$8.$ In the PivotTable worksheet, create a PivotTable that shows the summed amounts by date and state. State should be the rows, date the columns. Format the amount as currency and $0$ decimal places. Sort the PivotTable descending by the grand total so that the state with the highest total amount is at the top.

$9.$ Add the Zip Code to the table under the State. This makes the PivotTable huge, so we need a better way to see specific data without the excess. Insert a slicer by state and place it in cell I$2.$ Select Texas from the slicer to display the data for Texas only.

$10.$ In the Map worksheet, copy the State column from the Gift Certificate Sales worksheet into Column A$.$ Remove the duplicate values and sort alphabetically. These should fit in the marked cells.

$11.$ In the Total column, use a formula to sum the total sales per state. Use relative references so that you can copy the formula down the list of states. Format the resulting totals as currency, zero decimals.

$12.$ Create a Map based on the State and Total data. Place the Map in cell D$2.$ Change the colors of the map to Monochromatic Palette $1.$ Title the chart $$Total Sales by State$.$ Show the data labels and expand the chart to be large enough to display all the labels within their respective states.

$13.$ Save and Close the Gift Card Analysis workbook.