Question

a. 9. Switch to the Discounted Price worksheet. Because Anita wants this PivotTable to focus on customers, reorder the fields in the Rows area so the PivotTable displays data first by Customer and then by Order Number. 10. Anita is interested in the performance of the Midwest region. Filter the PivotTable to display orders from the Midwest in June and August, which were the most popular months for spice pack orders, as follows: Use the Region slicer in the range 13:K15 to filter the Pivottable and display Midwest orders only. b. Add a second slicer based on the Date field. Move the Date slicer below the Region slicer so that its upper-left corner is in cell 116 and the lower-right corner is in cell K28. d. Use the Date slicer to filter the PivotTable and display June and August orders only. 11. Switch to the Product Pricing worksheet. For each product and customer, Anita wants to compare the unit price with the average discounted price, starting with products that have the most orders. To provide this information, modify the Product Pricing Pivot Table as follows: Sort the Pivot Table based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom. b. Add the Discounted Price field to the bottom of the Values area of the Pivot Table. c. Change the Value Field Settings for the Sum of Discounted Price values to determine the C. a. dnri

Question 9 and 10

Answer 1

ANSWER:

• I have provided the properly explained stepwise process in text and image format so you can easily implement the process.
• I have provided the Final image so you can easily cross-check for the correct output of the process.
• Have a nice and healthy day!!

PROCESS IMAGE

• INPUT/DATA IMAGE (SAMPLE)

1 А B С D E Customer Order Number Region Date Amount 2 SP1 500 Midwest 01-05-2020 100 3 SP2 501 Northeast 01-06-2020 200 4 SP3 502 Northwest 01-07-2020 500 5 SP4 503 Southeast 01-08-2020 300 6 SP5 504 Southwest 01-09-2020 1000 7 SP6 505 Midwest 01-05-2020 400 8 SP7 506 Northeast 01-06-2020 786.6666667 9 SP8 507 Northwest 01-07-2020 892.3809524 10 SP9 508 Southeast 01-08-2020 998.0952381 11 SP10 509 Southwest 01-09-2020 1103.809524 12 SP1 510 Midwest 01-05-2020 1209.52381 13 SP2 511 Northeast 01-06-2020 1315.238095 14 SP3 512 Northwest 01-07-2020 1420.952381 15 SP4 513 Southeast 01-08-2020 1526.666667 16 SP5 514 Southwest 01-09-2020 1632.380952 17 SP6 515 Midwest 01-05-2020 1738.095238 18 SP7 516 Northeast 01-06-2020 1843.809524 19 SP8 517 Northwest 01-07-2020 1949.52381 20 SP9 518 Southeast 01-08-2020 2055.238095 21 SP10 519 Southwest 01-09-2020 2160.952381

• ADDING MONTH COLUMN TO SOLVE QUESTION 10

С D E F G Region Date Amount MONTH Midwest 01-05-2020 100 =TEXT(D2,"mmmm") Northeast 01-06-2020 200 Northwest 01-07-2020 500 Southeast 01-08-2020 300 Southwest 01-09-2020 1000 Midwest 01-05-2020 400 Northeast 01-06-2020 786.6666667 Northwest 01-07-2020 892.3809524 Southeast 01-08-2020 998.0952381 Southwest 01-09-2020 1103.809524 Midwest 01-05-2020 1209.52381 Northeast 01-06-2020 1315.238095 Northwest 01-07-2020 1420.952381 Southeast 01-08-2020 1526.666667 Southwest 01-09-2020 1632.380952 Midwest 01-05-2020 1738.095238 Northeast 01-06-2020 1843.809524 Northwest 01-07-2020 1949.52381 Southeast 01-08-2020 2055.238095 Southwest 01-09-2020 2160.952381

A B С D E F 1 Customer Order Number Region Date Amount MONTH 2 SP1 500 Midwest 01-05-2020 100 May 3 SP2 501 Northeast 01-06-2020 200 June 4 SP3 502 Northwest 01-07-2020 500 July 5 SP4 503 Southeast 01-08-2020 300 August 6 SP5 504 Southwest 01-09-2020 1000 September 7 SP6 505 Midwest 01-05-2020 400 May 8 SP7 506 Northeast 01-06-2020 786.6666667 June 9 SP8 507 Northwest 01-07-2020 892.3809524 July 10 SP9 508 Southeast 01-08-2020 998.0952381 August 11 SP10 509 Southwest 01-09-2020 1103.809524 September 12 SP1 510 Midwest 01-05-2020 1209.52381 May 13 SP2 511 Northeast 01-06-2020 1315.238095 June 14 SP3 512 Northwest 01-07-2020 1420.952381 July 15 SP4 513 Southeast 01-08-2020 1526.666667 August 16 SP5 514 Southwest 01-09-2020 1632.380952 September 17 SP6 515 Midwest 01-05-2020 1738.095238 May 18 SP7 516 Northeast 01-06-2020 1843.809524 June 19 SP8 517 Northwest 01-07-2020 1949.52381 July 20 SP9 518 Southeast 01-08-2020 2055.238095 August 21 SP10 519 Southwest 01-09-2020 2160.952381 September 22

• CREATING PIVOT TABLE ON DATA, Select Complete table -> INSERT -> PivotTable

FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Store My Apps PivotTable Recommended Table Pictures Online Shapes SmartArt Screenshot Pivot Tables Pictures Tables illustrations E11 х 1103.80952380952 А B D E 1 Customer Order Number Region Date Amount MONTH 2 SP1 500 Midwest 01-05-2020 100 May 3 SP2 501 Northeast 01-06-2020 200 June 4 SP3 502 Northwest 01-07-2020 500 July 5 SP4 503 Southeast 01-08-2020 300 August 6 SP5 504 Southwest 01-09-2020 1000 September 7 SP6 505 Midwest 01-05-2020 400 May 8 SP7 506 Northeast 01-06-2020 786.6666667 June 9 SP8 507 Northwest 01-07-2020 892.3809524 July 10 SP9 508 Southeast 01-08-2020 998.0952381 August 11 SP10 509 Southwest 01-09-2020 1103.809524 September 12 SP1 510 Midwest 01-05-2020 1209.52381 May 13 SP2 511 Northeast 01-06-2020 1315.238095 June 14 SP3 512 Northwest 01-07-2020 1420.952381 July 15 SP4 513 Southeast 01-08-2020 1526.666667 August 16 SP5 514 Southwest 01-09-2020 1632.380952 September 17 SP6 515 Midwest 01-05-2020 1738.095238 May 18 SPZ 516 Northeast 01-06-2020 1843.809524 June 19 SP8 517 Northwest 01-07-2020 1949.52381 July 20 SP9 518 Southeast 01-08-2020 2055.238095 August 21 SP10 519 Southwest 01-09-2020 2160.952381 September 22

D к ? X А B с E F G H 1 Customer Order Number Region Date Amount MONTH 2 SP1 500 Midwest 01-05-2020 100 May 3 SP2 501 Northeast 01-06-2020 200 June 4 SP3 502 Northwest 01-07-2020 500 July 5 SP4 503 Southeast 01-08-2020 300 August 6 SP5 504 Southwest 01-09-2020 1000 September 7 SP6 505 Midwest 01-05-2020 400 May 8 SP7 506 Northeast 01-06-20207 Create Pivot Table 9 SP8 507 Northwest 01-07-2020 8 10 SP9 508 Southeast 01-08-2020 9 Choose the data that you want to analyze 11 SP10 509 Southwest 01-09-2020 1 Select a table or range 12 SP1 510 Midwest 01-05-2020 Table/Range: Sheet1!$A$1:$F$211 13 SP2 511 Northeast 01-06-2020 1. Use an external data source 14 SP3 512 Northwest 01-07-2020 1 15 SP4 513 Southeast 01-08-2020 11 Choose connection 16 SP5 514 Southwest 01-09-2020 1 Connection name: 17 SP6 515 Midwest 01-05-2020 1 choose where you want the PivotTable report to be placed 18 SP7 516 Northeast 01-06-2020 1 New Worksheet 19 SP8 517 Northwest 01-07-2020 Existing Worksheet 20 SP9 518 Southeast 01-08-2020 21 Location: 21 SP10 519 Southwest 01-09-2020 2 22 Choose whether you want to analyze multiple tables 23 Add this data to the Data Model 24 OK 25 Cancel

• INSERTING CUSTOMER FIRST and THEN ORDER NUMBER SECOND IN ROWS FIELD, INSERT AMOUNT COLUMN IN VALUES FIELD, TO GET THE DESIRED OUTPUT

A B с D E 1 Pivot Table Fields N Choose fields to add to report: 7 Customer Order Number Region Date Amount MONTH MORE TABLES. 2 3 Row Labels 4 SP1 5 500 6 510 SP10 8 509 9 519 10 SP2 11 501 12 511 13 SP3 14 502 15 512 16 O SP4 17 503 18 513 19 SP5 20 504 21 514 22 SP6 23 505 24 515 25 SP7 26 506 27 516 28 Sum of Amount 1309.52381 100 1209.52381 3264.761905 1103.809524 2160.952381 1515.238095 200 1315.238095 1920.952381 500 1420.952381 1826.666667 300 1526.666667 2632.380952 1000 1632.380952 2138.095238 400 1738.095238 2630.47619 786.6666667 1843.809524 Drag fields between areas below: FILTERS COLUMNS ROWS Σ VALUES Sum of Amount Customer Order Number OSDA 2911 90762

• CREATING SLICER, BY CLICKING ANY CELL ON PIVOT TABLE THEN, INSERT-> SLICER

FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ANALYZE DESIGN 11 PivotChart Store Pivot Table Recommended Table Illustrations My Apps Recommended Pivot Tables Charts Tables Apps B15 X fx 1420.95238095238 Line Column Win/ Slicer Timeline Loss Sparklines Filters Charts B - с D E F G H K 500 Sum of Amount 1309.52381 100 1209.52381 3264.761905 1103.809524 2160.952381 1515.238095 200 A 1 2. 3 Row Labels 4 SP1 5 6 510 7 e SP10 8 509 9 519 10 SP2 11 501 12 511 13 SP3 14 502 15 512 16 ESP4 17 503 18 513 19 SP5 20 504 21 514 22 SP6 23 505 24 515 25 SP7 26 27 1315.238095 1920.952381 500 1420.952381 1826.666667 300 1526.666667 2632.380952 1000 1632.380952 2138.095238 400 1738.095238 2630.47619 786.6666667 506 516 1042 QO0524

ח A B с D E H к L M 1 Insert Slicers ? x Sum of Amount 1309.52381 100 1209.52381 3264.761905 1103.809524 2160.952381 1515.238095 Customer Order Number Region Date Amount MONTH 200 2 3 Row Labels 4 SP1 5 500 6 510 7 ESP10 8 509 9 519 10 SP2 11 501 12 511 13 SP3 14 502 15 512 16 SP4 17 503 18 513 19 SP5 20 504 21 22 ESP 23 505 24 515 25 SP7 26 506 27 516 1315.238095 1920.952381 500 1420.952381 1826.666667) 300 1526.666667 2632.380952 1000 1632.380952 2138.095238 400 1738.095238 2630.47619 786.6666667 1843.809524 514 OK Cancel

• FINALLY, SELECT MULTIPLE OPTIONS IN SLICER BY CTRL+CLICK (IN MY CASE THERE IS NO DATA FOR MIDWEST IN JUNE, JULY AND AUGUST)

A B с D E F G H K 1 2 3 Row Labels - Sum of Amount 4. Grand Total Region 5 Northeast 6 7 Northwest 8 Southeast 9 Midwest Southwest 10 11 12 13 14 15 16 17 18 19 MONTH May June 20 21 July 22 23 24 August September 25 26 27 28