Assignment 3 Excel Shortcut and Tricks by Teachoo.xlsx

	C	D	E	F	G	H	I	M	N	O
1
2
3
4
5	Q1A	Followng is QuantIty Sold by Salesman A to E
6
7		Salesman Name	Quantity Sold					Shortcuts for Copy Paste
8		A	50000					Copy	Ctrl C
9		B	40000					Paste	Ctrl V
10		C	25000
11		D	20000
12		E	40000
13
14
15
16		Suppose Rate is Rs 10 per piece for all
17		Calculate Total Sales Amount						Useful Excel Shortcuts
18		Also Calclate Commission @ 5% of Sales and Net Sales						For Drag Down		Ctrl D
19								For Drag Right		Ctrl R
20		Salesman Name	Quantity Sold	Rate	Sales Amt	Commission @ 5%	Net Sales
21		A	50000	10	500000	25000	475000
22		B	40000	10	400000	20000	380000	How to Calcuate Total
23		C	25000	10	250000	12500	237500	There are 4 Methods
24		D	20000	10	200000	10000	190000	=Sum(Select Area)
25		E	40000	10	400000	20000	380000	=B2 +B3+B4
26		Total	175000		1750000	87500	1662500	Alt = Enter
27								∑𝐴𝑢𝑡𝑜Sum
28
29
30	Q1B	Copy Paste and Make Commission Sheet in following format
31
32		Salesman Name	Commission
33		A	25000					In case of Formulas,we use Paste Special
34		B	20000					Shortcuts for Copy Paste Special
35		C	12500					Copy	Ctrl C
36		D	10000					Paste Special	Ctrl Alt V -> Values
37		E	20000						(Ctrl Alt V V)
38		Total	87500
39
40
41
42	Q1C	What is total Sales and Commission earned by B
43
44		Try by Copy Paste/Special				Try by links		Link is better than copy paste/special paste
45
46		Salesman Name	B			Salesman Name	B	Link will update the value when it changes
47		Sales Made	400000			Sales Made	400000
48		Commission	20000			Commission	20000
49		Net Sales	380000			Net Sales	380000
50
51
52
53
54
55
56
57
58	Q1D	Supose Manager Asks to make Net Sales sheet in following format						to Make Rows into Columns or Vie versa
59								We use Transpose
60		Salesman Name	A	B	C	D	E	Ctrl Alt V --->Transpose
61		Net Sales	475000	380000	237500	190000	380000	(Ctrl Alt V E)
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100