Question

Maureen decided to create a pivot table in a new worksheet using bracelet pivot table as

4. Total Row should automatically total the values in the 2020 field.) Maureen decides to create a PivotTable to allow her to better manipulate and filter the Braceletsales table data. Create a PivotTable based on the Braceletsales table in a new worksheet using Bracelet PivotTable as the worksheet name. Update the PivotTable as described below so that it matches Final Figure 1: Add the Material field and the Product ID field (in that order) to the Rows area. (Hint: The order of the materials should be Gold, Silver, Copper, as shown in Final Figure 1. Sort the Pivottable manually by dragging or by using the Move command if necessary.) b Add the 2018, 2019, and 2020 fields (in that order) to the Values area. Update the Sum of 2018 field in the values area to display the name 2018 Sales with the Accounting number format with o decimal places and $ as the symbol. C I Focus 0 (40) ENG

Answer 1

Solution

4)

First select the table

Go to Insert tab

"Table"

"Pivot Table"

File Design Tell me... Sign in Share Home Insert Page Layout Formulas Data Review View L- IST Tables Illustrations Add- Recommended ins Charts PivotChart Tex 3D Sparklines Filters Links Мар" Tours Charts fx Product ID Pivot Table Recommended Table Pivot Tables Tables 1 12 Product ID 3 BGPS-194 4 BGGB-147 5 BGGB-104 6 BSPS-142 7 BCPS-117 Type Bracelet Bracelet Bracelet Bracelet Bracelet Pracalot Forge and Polish Jewelry Sales Records - 2018 - 2020 Material Embellishment Gold Precious Stones Gold Glass Beads Gold Glass Beads Silver Precious Stones Copper Precious Stones Sihir Doar 2018 $420 $1,180 $890 $1,280 $800 20 2019 $530 $920 $480 $1,740 $590 An 2020 $1,6 $1,3 $1,9 $1,7 $3 lo DDD 14A $12

then

x Create Pivot Table Choose the data that you want to analyze Select a table or range Table/Range: Bracelet Sales O Use an external data source Choose Connection... Connection name: Use this workbook's Data Model Choose where you want the PivotTable report to be placed New Worksheet Existing Worksheet Location: Choose whether you want to analyze multiple tables Add this data to the Data Model OK Cancel

A3 A B с E F H 1 J 1 PivotTable Fields X 2 Choose fields to add to report: 8 3 4 PivotTable2 Search 5 6 7 8 To build a report, choose fields from the Pivot Table Field List 9 LIIDID Product ID Type Material Embellishment 2018 2019 2020 10 11 12 ==== 13 ==== Drag fields between areas below: 14 15 Y FILTERS II COLUMNS 16 17 18 19 ROWS VALUES 20 21 22 23 Bracelet Necklace Sheet2 UPDATE Earri ... + Defer Layout Update

a)

Active Field Filter Data Tools A3 x Row Labels А B с D "" F H - 1 Pivot Table Fields 2 Choose fields to add to report: 8 Search 3 Row Labels 4 Copper 5 BCPP-182 6 BCPS-117 7 BCPS-132 8 Gold 9 BGGB-104 10 BGGB-117 11 BGGB-147 12 BGPS-194 13 Silver 14 BSPP-144 15 BSPS-136 16 BSPS-142 17 Grand Total 18 19 POPODDI Product ID Type Material Embellishment 2018 2019 2020 Drag fields between areas below: Y FILTERS II COLUMNS ROWS Σ VALUES 20 21 Material Product ID 22 23 UPDATE Sheet2 Bracelet Necklace Earri ... Defer Layout Update + Ready B - + 100%

now move the field

Gold

Silver

Copper

manually drag the field to arrange it

Choose fields to add to report: Search 2 3 Row Labels 4 eGold 5 BGGB-104 6 BGGB-117 7 BGGB-147 8 BGPS-194 esilver 10 BSPP-144 9 Product ID Type Material Embellishment 2018 2019 2020 Drag fields between areas below: 11 BSPS-136 12 BSPS-142 13 e Copper 14 BCPP-182 15 BCPS-117 16 BCPS-132 17 Grand Total 18 Y FILTERS III COLUMNS 19 ROWS Σ VALUES 20 Material 21 Product ID 22 23

---

b)

Move 2018,2019 & 2020 to VALUES area

File Home Insert Page Layout Formulas Data Review View General Calibri Σ AP 11 C % Analyze Design Tell me... Conditional Formatting Insert Format as Table Delete Cell Styles Format - Styles Cells Sign in 2 Share Ayo Sort & Find & Filter - Select Editing Paste Β Ι Ο 4.0 .00 .00 0 Clipboard F Font Alignment Number B9 X for 1590 A C D E F G H | Pivot Table Fields Choose fields to add to report: Search o 5 7 1 2 3 Row Labels Sum of 2018 Sum of 2019 Sum of 2020 4 eGold 2610 3710 6400 BGGB-104 890 480 1990 BGGB-117 120 1780 1490 BGGB-147 1180 920 1300 8 BGPS-194 420 530 1620 9 Silver 1590 4010 4390 10 BSPP-144 240 470 1250 11 BSPS-136 1800 1380 12 BSPS-142 1280 1740 1760 13 Copper 3740 3350 3370 14 BCPP-182 1910 1200 1380 15 BCPS-117 800 590 340 16 BCPS-132 1030 1560 1650 17 Grand Total 7940 11070 14160 18 Type Material Embellishment 2018 2019 2020 70 Drag fields between areas below: Y FILTERS II COLUMNS Σ Values 19 20 ROWS Σ VALUES Sum of 2018 Sum of 2019 Material Product ID 21 22 23

c)

File Home Insert Page Layout Formulas Data Review Sign in Share 2- Calibri Σ- Α. View Analyze Design Tell me General - Conditional Formatting Insert % Formatas Table * Delete - * Cell Styles Format Number Styles Cells Paste BIU A. Sort & Find & Filter - Select Editing Clipboard Font Alignment B9 1590 E F G H 1 PivotTable Fields Choose fields to add to report Search 1780 c D 1 2 3 Row Labels Sum of 2018 Sum of 2019 Sum of 2020 4 Gold 2610 3710 6000 5 BGGB-104 890 1990 BGGB-117 120 1490 7 BGGB-147 1180 920 1300 - BGPS-194 420 530 1620 9 Silver 1590 4010 10 BSPP-144 240 470 1250 BSPS-136 1800 1380 12 BSPS-142 1280 1740 1760 13 Copper 3740 3350 3370 BCPP-182 1910 1200 1380 15 BCPS-117 800 590 340 16 BCPS-132 1030 1560 1650 17 Grand Total 7940 11070 14160 18 19 20 21 22 23 Sheet2 Bracelet Necklace Earn Material По Embellis 2018 2019 Move Down 2020 Move to Beginning Move to End Drag fields Y Move to Report Filter - Move to Row Labels Y FILTERS Move to Column Labels 2Move to Valves X Remove Field ROWS Value Field Settings Material Sum of 2018 Product ID Sum of 2019 UPDATE Deler Layout Update • .

Value Field Settings ? X Source Name: 2018 Custom Name: 2018 Sales Summarize Values By Show Values As Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum Count Average Max Min Product Number Format OK Cancel

Select "Number Format"

Format Cells х Number Category: General Number Currency Accounting Date Time Percentage Fraction Scientific Text Special Custom Sample $1,590 Decimal places: 0 Symbol: S English (United States Accounting formats line up the currency symbols and decimal points in a column. OK Cancel

5 6 2 3 Row Labels 2018 Sales Sum of 2019 Sum of 2020 4 Gold $ 2,610 3710 6400 BGGB-104 $ 890 480 1990 6 BGGB-117 $ 120 1780 1490 7 BGGB-147 $ 1,180 920 1300 8 BGPS-194 $ 420 530 1620 9 Silver $ 1,590 4010 4390 10 BSPP-144 $ 240 470 1250 11 BSPS-136 $ 70 1800 1380 BSPS-142 $ 1,280 1740 1760 13 Copper $ 3,740 3350 3370 14 BCPP-182 $ 1,910 1200 1380 15 BCPS-117 $ 800 590 340 16 BCPS-132 $ 1,030 1560 1650 17 Grand Total $ 7,940 11070 14160 18 N 19

---

all the best