Liberty BUSI 201 Assignment 14 Excel 2016 Skill Review 8.1 Answers Complete Solutions
Complete many different versions to get an A on your grade!
Download it for more and ace on your assignments!
Skill Review 8.1
4. Sort the sales data alphabetically by region and then by last name. a. Go to the Sales Data worksheet, and click any cell in the data set.
b. On the Data tab, in the Sort & Filter group, click the Sort button.
c. In the Sort dialog, expand the Sort by list, and select Region.
d. Click the Add Level button.
e. Expand the Then by list, and select Last Name.
f. Click OK.
STATUS
5. Add subtotals to the data to calculate the total commission earned for each sales associate. a. On the Data tab, in the Outline group, click the Subtotal button.
b. Expand the At each change in list, and select Last Name.
c. Verify that Sum is selected in the Use function box.
d. Verify that there is a check mark next to Commission Earned in the Add subtotal to box.
e. Click OK.
6. Copy the subtotal data to the Analysis worksheet.
7. Notice that when you paste the subtotaled data, Excel removes the subtotal grouping, but keeps the subtotal rows and formulas. You can use the Outline command to re‐create groups based on the subtotal formulas.
8. On the Commissions worksheet, create a new conditional formatting rule to apply an icon set to the values in the Rating column.
9. Sort the sales data so cells with the completely filled gold star icon appear first.
10. On the Sales Data Filter worksheet, delete the conditional formatting rule that applies a font format to cells where the value is above average.
11. Use the Advanced Filter feature to find sales greater than $110,000 for George Anderson or Xin Zhu.
12. The data in the Sales Data worksheet has changed since the PivotTable was created. Refresh the PivotTable to reflect the changes. a. Go to the PivotTable worksheet and click anywhere in the PivotTable.
13. Add a calculated field to determine the average commission rate for each row in the PivotTable. a. On the PivotTable Tools Analyze tab, in the Calculations group, click the Fields, Items, & Sets button.
14. Apply a Quick Style to the PivotTable.
Click Pivot Style Medium 6.
15. Use slicers to filter the PivotTable by region to show only the NW1 region.
16. Filter the PivotChart data to show only the NW1 and NW2 regions. a. Go to the PivotChart worksheet and select the chart.
17. Apply a Quick Style to the PivotChart. a. Select the PivotChart.
19. Upload and save your project file.
20. Submit project for grading.
