Excel 2016 Tips and Tricks

Excel 2016

Lesson 4 – 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 131