EXCEL ASSIGNMENT

Topic: PIVOT

Instructions:
● Please complete the given below assignment on this sheet
● Once done please upload this sheet into your dashboard excel assignments block(From where you are download
● Please submit before next class/session
● Scoring/Marks as per topic guidelines

DATA

Region Rep Gender Item Units Unit Cost Total

East Jones Male Pencil 95 1.99 189.05
Central Kivell Female Binder 50 19.99 999.50
Central Jardine Female Pencil 36 4.99 179.64
Central Gill Female Pen 27 19.99 539.73
West Sorvino Male Pencil 56 2.99 167.44
East Jones Male Binder 60 4.99 299.40
Central Andrews Male Pencil 75 1.99 149.25
Central Jardine Female Pencil 90 4.99 449.10
West Thompson Female Pencil 32 1.99 63.68
East Jones Male Binder 60 8.99 539.40
Central Morgan Male Pencil 90 4.99 449.10
East Howard Female Binder 29 1.99 57.71
East Parent Male Binder 81 19.99 1,619.19
East Jones Male Pencil 35 4.99 174.65
Central Smith Male Desk 2 125.00 250.00
East Jones Male Pen Set 16 15.99 255.84
Central Morgan Male Binder 28 8.99 251.72
East Jones Male Pen 64 8.99 575.36
East Parent Male Pen 15 19.99 299.85
Central Kivell Female Pen Set 96 4.99 479.04
Central Smith Male Pencil 67 1.29 86.43
East Parent Male Pen Set 74 15.99 1,183.26
Central Gill Female Binder 46 8.99 413.54
Central Smith Male Binder 87 15.00 1,305.00
East Jones Male Binder 4 4.99 19.96
West Sorvino Male Binder 7 19.99 139.93
Central Jardine Female Pen Set 50 4.99 249.50
Central Andrews Male Pencil 66 1.99 131.34
East Howard Female Pen 96 4.99 479.04
Central Gill Female Pencil 53 1.29 68.37
Central Gill Female Binder 80 8.99 719.20
Central Kivell Female Desk 5 125.00 625.00
East Jones Male Pen Set 62 4.99 309.38
Central Morgan Male Pen Set 55 12.49 686.95
Central Kivell Female Pen Set 42 23.95 1,005.90
West Sorvino Male Desk 3 275.00 825.00
Central Gill Female Pencil 7 1.29 9.03
West Sorvino Male Pen 76 1.99 151.24
West Thompson Female Binder 57 19.99 1,139.43
Central Andrews Male Pencil 14 1.29 18.06
Central Jardine Female Binder 11 4.99 54.89
Central Jardine Female Binder 94 19.99 1,879.06
Central Andrews Male Binder 28 4.99 139.72
m where you are downloaded)

1) Create pivot table based on Region , item and units 2) Create pivot table based o

Sum of Total Item Sum of Total

Region Units BindeDesk Pen Pen Set Pencil Total Result Item
Central ### 875.00 539.73 ### 1,540.32 ### Binder
2 250.00 ### Desk
5 625.00 ### Pen
7 9.03 ### Pen Set
11 ### ### Pencil
14 18.06 ### Total Result
27 539.73 ###
28 ### ###
36 179.64 ### 3) Create a pivot table based
42 ### ###
46 ### ### Sum of Total
50 ### 249.50 ### Rep
53 68.37 ### Andrews
55 686.95 ###
66 131.34 ###
67 86.43 ###
75 149.25 ###
80 ### ### Gill
87 ### ###
90 898.20 ###
94 ### ###
96 479.04 ###
East ### ### ### 363.70 ###
4 ### ### Howard
15 299.85 ###
 16 255.84 ###
29 ### ### Jardine
35 174.65 ###
60 ### ###
62 309.38 ###
64 575.36 ###
74 ### ###
81 ### ### Jones
95 189.05 ###
96 479.04 ###
West ### 825.00 151.24 231.12 ###
3 825.00 ###
7 ### ###
32 63.68 ###
56 167.44 ###
57 ### ### Kivell
76 151.24 ###
Total Result ### ### ### ### 2,135.14 ###

Morgan

Parent

Smith

Sorvino

Thompson

Total Result
Create pivot table based on Gender, Item and Total 4) Vizualise the recommended pivot charts

Gender
Female Male Total Result Items based on Gender
5,263.33 4,314.32 9,577.65 12000
625.00 1,075.00 1,700.00
1,018.77 1,026.45 2,045.22 10000
1,734.44 2,435.43 4,169.87
8000
769.82 1,365.32 2,135.14
9,411.36 ### 19,627.88 6000

4000
Create a pivot table based on Rep, Region, Units and Total
2000

Region 0
Units Central East West Total Result Binder Desk Pen Pen Set Pencil Total
Result
438.37 ###
14 18.06 ###
28 139.72 ###
66 131.34 ###
75 149.25 ###
1,749.87 ###
7 9.03 ###
27 539.73 ###
46 413.54 ###
53 68.37 ###
80 719.20 ###
536.75 ###
29 57.71 ###
96 479.04 ###
2,812.19 ###
11 54.89 ###
36 179.64 ###
50 249.50 ###
90 449.10 ###
94 1,879.06 ###
2,363.04 ###
4 19.96 ###
16 255.84 ###
35 174.65 ###
60 838.80 ###
62 309.38 ###
64 575.36 ###
95 189.05 ###
3,109.44 ###
5 625.00 ###
42 1,005.90 ###
50 999.50 ###
96 479.04 ###
1,387.77 ###
28 251.72 ###
55 686.95 ###
90 449.10 ###
3,102.30 ###
15 299.85 ###
74 1,183.26 ###
81 1,619.19 ###
1,641.43 ###
2 250.00 ###
67 86.43 ###
87 1,305.00 ###
1,283.61 ###
3 825.00 ###
7 139.93 ###
56 167.44 ###
76 151.24 ###
1,203.11 ###
32 63.68 ###
57 1,139.43 ###
### 6,002.09 2,486.72 ###
on Gender

Gender
Female
Male

et Pencil Total
Result
 EXCEL ASSIGNMENT
Topic: VISUALIZATION

Instructions:
● Please complete the given below assignment on this sheet
● Once done please upload this sheet into your dashboard excel assignments block(From where you are download
● Please submit before next class/session
● Scoring/Marks as per topic guidelines

DATA

OrderDate Region Rep Item Units Unit Cost Total

1/6/21 East Jones Pencil 95 1.99 189.05
1/23/21 Central Kivell Binder 50 19.99 999.50
2/9/21 Central Jardine Pencil 36 4.99 179.64
2,000.00
2/26/21 Central Gill Pen 27 19.99 539.73
1,800.00
3/15/21 West Sorvino Pencil 56 2.99 167.44
1,600.00
4/1/21 East Jones Binder 60 4.99 299.40
1,400.00
4/18/21 Central Andrews Pencil 75 1.99 149.25
1,200.00
5/5/21 Central Jardine Pencil 90 4.99 449.10
1,000.00
5/22/21 West ThompsonPencil 32 1.99 63.68 800.00
6/8/21 East Jones Binder 60 8.99 539.40 600.00
6/25/21 Central Morgan Pencil 90 4.99 449.10 400.00
7/12/21 East Howard Binder 29 1.99 57.71 200.00
7/29/21 East Parent Binder 81 19.99 1,619.19 -
8/15/21 East Jones Pencil 35 4.99 174.65 1 3 5 7 9 11 13 1
9/1/21 Central Smith Desk 2 125.00 250.00
9/18/21 East Jones Pen Set 16 15.99 255.84
10/5/21 Central Morgan Binder 28 8.99 251.72
10/22/21 East Jones Pen 64 8.99 575.36 2) Create column chart based on
11/8/21 East Parent Pen 15 19.99 299.85
11/25/21 Central Kivell Pen Set 96 4.99 479.04 Sum of Total
12/12/21 Central Smith Pencil 67 1.29 86.43 Row Labels
12/29/21 East Parent Pen Set 74 15.99 1,183.26 2
1/15/22 Central Gill Binder 46 8.99 413.54 3
2/1/22 Central Smith Binder 87 15.00 1,305.00 4
2/18/22 East Jones Binder 4 4.99 19.96 5
3/7/22 West Sorvino Binder 7 19.99 139.93 7
 3/24/22 Central Jardine Pen Set 50 4.99 249.50 11
4/10/22 Central Andrews Pencil 66 1.99 131.34 14
4/27/22 East Howard Pen 96 4.99 479.04 15
5/14/22 Central Gill Pencil 53 1.29 68.37 16
5/31/22 Central Gill Binder 80 8.99 719.20 27
6/17/22 Central Kivell Desk 5 125.00 625.00 28
7/4/22 East Jones Pen Set 62 4.99 309.38 29
7/21/22 Central Morgan Pen Set 55 12.49 686.95 32
8/7/22 Central Kivell Pen Set 42 23.95 1,005.90 35
8/24/22 West Sorvino Desk 3 275.00 825.00 36
9/10/22 Central Gill Pencil 7 1.29 9.03 42
9/27/22 West Sorvino Pen 76 1.99 151.24 46
10/14/22 West ThompsonBinder 57 19.99 1,139.43 50
10/31/22 Central Andrews Pencil 14 1.29 18.06 53
11/17/22 Central Jardine Binder 11 4.99 54.89 55
12/4/22 Central Jardine Binder 94 19.99 1,879.06 56
12/21/22 Central Andrews Binder 28 4.99 139.72 57
60
62
64
66
67
74
75
76
80
81
87
90
94
95
96
Grand Total
From where you are downloaded)

1) Create line chart based on "Total" column 6) Create boxplot based on "Total" and
This chart isn't available in your version of Excel.
Total Values
2,000.00
Editing this shape or saving this workbook into a di
permanently break the chart.
1,800.00
1,600.00
1,400.00
1,200.00
1,000.00
800.00
600.00
400.00
200.00
-
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43

Create column chart based on "Items" and "Units"

Column Labels
Binder Desk Pen Pen Set Pencil Grand Total
250 250
Items & Units
825 825 12000

19.96 19.96
10000
625 625
139.93 9.03 148.96 8000

6000

4000
 10000

8000
54.89 54.89
6000
18.06 18.06
300 299.85 4000
255.84 255.84
540 539.73 2000
391.44 391.44
0
57.71 57.71 2 5 14 27 32 42 53 57 64 74 80 90
63.68 63.68
174.65 174.65
179.64 179.64
1005.9 1005.9
413.54 413.54
999.5 249.5 1249
68.37 68.37
686.95 686.95
167.44 167.44
1139.43 1139.43
838.8 838.8
309.38 309.38
575 575.36
131.34 131.34
86.43 86.43
1183.26 1183.26
149.25 149.25
151 151.24
719.2 719.2
1619.19 1619.19
1305 1305
898.2 898.2
1879.06 1879.06
189.05 189.05
479 479.04 958.08
9577.65 1700 2045 4169.87 2135.14 19627.88
boxplot based on "Total" and findout the outliers 7) Create boxplot based on "Unit Cost" and findout the outliers
able in your version of Excel. This chart isn't available in your version of Excel.

saving this workbook into a different file format will Editing this shape or saving this workbook into a different file format will
he chart. permanently break the chart.

3) Create a pie chart using "Region" and "Units"

Items & Units
Sum of Total Region
Units Central East West
2 250.00
Column Labels Binder
Desk 3 825.00
Pen
Pen Set
Pencil
 Column Labels Binder
Desk
Pen 4 19.96
Pen Set 5 625.00
Pencil
7 9.03 139.93
11 54.89
14 18.06
15 299.85
32 42 53 57 64 74 80 90 96
16 255.84
27 539.73
28 391.44
29 57.71
32 63.68
35 174.65
36 179.64
42 1,005.90
46 413.54
50 1,249.00
53 68.37
55 686.95
56 167.44
57 ###
60 838.80
62 309.38
64 575.36
66 131.34
67 86.43
74 ###
75 149.25
76 151.24
80 719.20
81 ###
87 1,305.00
90 898.20
94 1,879.06
95 189.05
96 479.04 479.04
Total Result 11,139.07 ### ###
t" and findout the outliers

Excel.

nto a different file format will

and "Units"

Total Result Region & Units

250.00 2 3 4
5 7 11 2,000.00
825.00 14 15 16
1,800.00
27 28 29
32 35 36 1,600.00
42 46 50 1,400.00
53 55 56
1,200.00
 Region & Units
2 3 4
5 7 11 2,000.00
14 15 16
19.96 1,800.00
27 28 29
625.00 32 35 36 1,600.00
42 46 50 1,400.00
148.96 53 55 56
54.89 57 60 62 1,200.00

18.06 64 66 67 1,000.00
74 75 76 800.00
299.85 80 81 87
255.84 90 94 95 600.00

539.73 96 Total Result 400.00

391.44 200.00

57.71 -
0 5 10
63.68
174.65
179.64
1,005.90
413.54
1,249.00
68.37
686.95
167.44
1,139.43
838.80
309.38
575.36
131.34
86.43
1,183.26
149.25
151.24
719.20
1,619.19
1,305.00
898.20
1,879.06
189.05
958.08
19,627.88
 4) Create a scatter plot based on "Total" 5) Create a bar chart based on "Rep" and

Total
2,000.00 Andrews
1,800.00 Jardine
1,600.00 Thompson
1,400.00 Gill
Kivell
1,200.00
 Total
2,000.00 Andrews
1,800.00 Jardine
1,600.00 Thompson
1,400.00 Gill
Kivell
1,200.00
Jones
1,000.00
Gill
800.00 Howard
600.00 Jardine
400.00 Jones
200.00 Gill
Smith
-
0 5 10 15 20 25 30 35 40 45 50 Parent
Morgan
Smith
Parent
Morgan
Thompson
Andrews
Sorvino
Jardine
Jones
- 200.00 400.00 600.00
t based on "Rep" and "Total"

Chart Title
 Chart Title

0 400.00 600.00 800.00 1,000.00 1,200.00 1,400.00 1,600.00 1,800.00 2,000.00