Excel 2016 Data and List Management
Excel 2016
Lesson 2 – Creating/Revising PivotTables
E XERCISE
C REATING /R EVISING P IVOT T ABLES
T ASK
Create and revise PivotTable reports.
1. Open EXPIVOT1.XLSX and display the World worksheet, if necessary. 2. Create a PivotTable report from the data range A4:G67. Place the PivotTable report in a new worksheet. 3. Create the following layout:
Field
Area
Date Sold
Report Filter
Product
Row Labels
Inv Num
Row Labels
Sales Rep
Column Labels
Total Income
Values
4. Use the Date Sold list to display only the sales for 02/06/2006 . 5. Display the World worksheet and change the number in cell F9 to 25 . 6. Display the Sheet1 worksheet and refresh the PivotTable report.
7. Change the Date Sold list to display all dates. 8. Move the Inv Num field to the Report Filter area. 9. Delete the Date Sold field from the Report Filter area.
10. Display the number of orders for each sales representative by changing the summary function for the data area to Count . ( Hint: Display the Value Field Settings for the data area.) Then, return the summary function to Sum . 11. Add the Qty Sold field below the Product field in the Row Labels area. 12. Hide Ernest Feldgus and Janice Faraco in the Sales Rep field. 13. Create a separate PivotTable report for each Inv Num item in the page area. ( Hint: Use the Show Report Filter Pages option from the Analyze button in the PivotTable group on the Analyze tab.) 14. Select the 3325 sheet and apply the Pivot Style Dark 4 style. 15. Publish the 3325 worksheet to the student data folder as a web page named wsgpivot and have the published web page open in your browser.
OFFICEPRO, Inc.
Page 65
Made with FlippingBook - professional solution for displaying marketing and sales documents online