Excel Homework on PowerPivot
|
Requirements |
Explanations |
| Open excel file Excel HW Mar2021.xls
First setup Vlookup functions in propriate tables |
Except report 4, you need to link related table with proper keys by using Vlookup function, read through below requirement and explanation carefully |
| Report 1: Create PivotTable in a new sheet showing how many orders under each employee, then insert a PivotChart (Column chart) based on this PivotTable | show the count of orders ID by employee names, also show data labels on top of each column in Chart |
| Report 2: Create PivotTable in the same sheet as report 1 showing how many orders are shipped/new/closed (status name), then insert a PivotChart (Bar chart) based on the PivotTable | Show count of Order ID by Status Name, Show data label outside of each bar |
| Report 3: Create PivotTable in the same sheet as report 1&2 showing who’s our biggest supplier, then insert a PivotChart (Pie chart) based on this PivotTable | Show count of purchase order ID by supplier’s company name, show percentage as data label for the pie |
| Report 4: Create PivotTable in the same sheet as report 1, 2&3 showing sum of shipping fee by each state/province from Order table, then insert a PivotChart (area chart) based on this PivotTable | show Data Tables below the area chart |
| Add Employee names as a Slicer | controls report 1, 2 and 4 at same time |
| Add Order Date as a timeline | controls report 1, 2 and 4 at same time |
Please finish this exercise and send the excel file (PowerPivot report) back to [email protected] before end of April 4th
PLEASE write your student number in the email