Open the salesdata.xlsx file

Select cell A$1,$ and insert a pivot table. $($Insert$,$ Pivot Table, and press OK$)$

In the pivot table fields task pane on the right, check the boxes for Region and Total Sales. The pivot table should display the sum of total sales by region.

Modify your pivot table on Sheet $1$ to answer the following question:

What are the total sales for each quarter by region? $($drag the quarter field in the field list on the right to the columns area under the field list$)$

Select the pivot table on Sheet $1.$ Insert a $3-$D clustered column pivot chart. $($Analyze$,$ pivot chart$)$

On the pivot chart, filter the Southwest region only. Note that the filtering is also applied to the pivot table. $($click on down arrow next to region in lower left corner, choose Southwest only$)$

Select Sheet $1,$ and select the pivot chart. Change the design to Chart Style $8.($Design tab$)$

Double click on any column in the chart. Select the State field in the show detail dialog box, and press OK$.$ Move the pivot chart next to the pivot table, and observe the data for each state. Filter for the region Southwest for the states of New Mexico and Texas only.

Select the Data worksheet, and select cell A$1.$ Insert the recommended pivot table called Sum of Total Sales by Region. $($Insert$,$ Recommended Pivot Table, second choice on left$)$

Modify the second pivot table to include the Products field. The pivot table should display total sales for products in each region. $($on Sheet $2,$ check the box for products in the field list on the right$)$

Modify your second pivot table $($on Sheet $2)$ to answer this question:

What are the total sales for each region by product? $($drag the product field from the rows area to the columns area under the field list on the right$)$

Format the values as currency $($right$-$click on any number in the pivot table, value field settings, number format, currency$)$

Sort the grand totals in ascending order. $($right$-$click on any number in Column K$,$ sort$)$

Create an additional pivot chart for the pivot table on Sheet $2.($click on pivot table, analyze, pivot chart$)$ Choose the $3-$D clustered column, which is the fourth subtype at the top of the window. Move the pivot chart next to the pivot table. Filter the Southwest region only.

Tips: On the first pivot table, the quarter field should be in the column area, the region and state should be in the row area, and the values should contain only the sum of total sales.

For the second pivot table, the product field should be in the column area, the region should be the rows field, and the sum of total sales should be the only field in the values area.

Save and close your file, and upload it using the Blackboard link for grading.