INDEX
TOPIC: BASIC EXCEL
[Link]
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4
5 Sheet1(Solved)
6 REVENUE Solved
7 EXPENSES Solved
8 SAVINGS Solved
9 Paste Special
10 Conditional Formatting
11 Group Ungroup
12 Text to Column
Back to Index
S No Name 10th Marks 11th Marks
1 Naman 58 63
2 Samkit 73 78
3 Nikhil 63 68
4 Aashi 98 96
5 Upasna 68 76
6 Vaibhav 74 61
7 Nishi 63 68
8 grand total 497 510
alt h o s to move/copy sheets
alt h d s to delete sheet
ctrl fn f11 to add new sheets
ctrl x cut
ctrl y redo
ctrl z undo
f2/fn f2 cell edit
ctrl d fill down
ctrl r fill right
fn f4 absolute
ctrl f find
ctrl h replace
ctrl shift l sort and filter
alt drop down to select sort and filter column
ctrl alt tab multiple sheet switch
ctrl pdup/pgdowm shift sheets
alt = auto sum
ctrl + add
ctrl - delete
add column ctrl space
add row shift space
to move row/column ctrl x then ctrl +
to select whole range shift ctrl down/up arrow
to hide/unhide row ctrl 9 / ctrl shift 9
to hide column ctrl 0
to create hyperlink ctrl k
to be in the same cell ctrl hold then enter
ctrl shift ~,1,2,3,4,5,6 to change no. format
ctrl p print preview
ctrl tab switch bw workbook
shift f2 to add comment
ctrl 1 / ctrl shift P to show format
alt drop down to see data validation options
indirect , intext for custom in data validation
% % %
contribution contributio contributi
to the 10th n of the on of the
12th Marks SUM AVERAGE SUBTRACTION total 11th total 12th total
68 189 63.0000000 58 11.7% 12.4% 15.1%
58 209 69.6666667 73 14.7% 15.3% 12.9%
68 199 66.3333333 63 12.7% 13.3% 15.1%
101 295 98.3333333 98 19.7% 18.8% 22.5%
52 196 65.3333333 68 13.7% 14.9% 11.6%
57 192 64.0000000 74 14.9% 12.0% 12.7%
45 176 58.6666667 63 12.7% 13.3% 10.0%
449 1456 485.3333333 497 1 1 100.00%
qty
price SALES 100
10 1000
12 1200
15 1500
17 1700
20 2000
idation options
data validation
150 190 220
1500 1900 2200
1800 2280 2640
2250 2850 3300
2550 3230 3740
3000 3800 4400
Back to Index
Revenue FY'17
ITEMS January February March April May June July August September
Salary 120000 5000 120000 120000 2000 1000 20000 120000 120000
Stipend 0 0 0 0 0 0 0 0 0
Rent 0 0 0 0 0 0 0 0 0
Interest 0 0 0 150000 0 10000 0 0
Dividend 0 0 0 0 0 17000 0 0 0
Pocket Money 0 0 0 0 0 0 0 0 0
Others 2000 2200 2000 2200 2000 2200 2000 2200 2000
Grand Total 122000 7200 122000 272200 4000 20200 32000 122200 122000
October November December GRAND TOTAL
120000 120000 120000 988000
0 0 0 0
0 0 0 0
0 0 0 160000
0 12000 0 29000
0 0 0 0
2200 2000 2200 25200
122200 134000 122200 1202200
Back to Index
Expense FY'17
Items January February March April May June July August
Rent 6000 6000 6000 6000 6000 6000 6000 6000
Education 22000 29000 8000 38000 84000 6000 16500 6000
College 6000 10000 2000 32000 0 0 0 0
Tuitions 6000 6000 6000 6000 6000 6000 6000 6000
[Link] 10000 13000 0 0 78000 0 10500 0 Chart
Grocery 0 3200 3500 3000 2500 2800
140000 2500 3000
Transportation 3000 3000 3000 3000 3000 3000 3000 3000
120000 15.33%
Entertainment 1200 500 1700 1200 600 1700 1200 600
100000
Mobile & Internet 2000 2000 2000 2000 80000 2000
2000 2000 2000
8.48% 8.95%
Shopping 2000 2000 2000 2000 2000 20000
6000015000 2000 7.92
6.55% 6.29%
Outings 6000 6000 6000 6000 6000 6000
40000 6000 6000
Others 7000 12000 15000 6000 9000 12000 17000 16000
20000
Grand Total 49200 63700 47200 67200 115100 59500 69200 44600
0
% contribution 1 2 3 4 5 6
to the total 6.55% 8.48% 6.29% 8.95% 15.33% 7.92% 9.22% 5.94%
Grand Total
September October November December Grand Total
6000 6000 6000 6000 72000
6000 25000 38000 6000 284500
0 2000 32000 0 84000
6000 6000 6000 6000 72000
Chart
0 Title
17000 0 0 128500
6000 4000 3000 6000
18.00% 39500
3000 3000 3000 3000
16.00% 36000
15.33%
1700 1200 600 1700
14.00% 13900
12.00%
2000 2000
9.22%
2000 9.93% 2000
10.00% 24000
8.48% 8.95% 8.42%
20007.92% 2000 2000 2000
8.00% 55000
.55% 6.29% 6.35% 6.62%
6.00%
6000 60005.94% 8000 8000 76000
15000 14000 12000 4.00%
15000 150000
47700 63200 74600 2.00%
49700 750900
0.00%
2 3 4 5 6 7 8 9 10 11 12
6.35% 8.42% 9.93% 6.62% 100.00%
Grand Total % contribution
to the total
Back to Index
January February March April May June July August
122000 7200 122000 272200 4000 20200 32000 122200
45600 -91200 32100 172800 -142200 -92800 -89900 39000
September October November December
122000 122200 134000 122200
32600 20800 22800 28800
Back to Index
S No Name 10th Marks 11th Marks 12th Marks SUM AVERAGE
1 Naman 55 60 65 180 60
2 Samkit 70 75 55 200 66.66667
3 Nikhil 60 65 65 190 63.33333
4 Aashi 95 93 98 286 95.33333
5 Upasna 65 73 49 187 62.33333
6 Vaibhav 71 58 54 183 61
7 Nishi 60 65 42 167 55.66667
8 CLASS TOTAL 476 489 428 1393 464.3333
ram-sgsits,indore
shyam,medicaps ujjain
ram
ramsgsitsindore shy
shyammedicapsujjain
8745203621 ram
9632015741 shyam
% Contribution % Contribution % Contribution
to the 10th to the 11th to the 12th
SUBTRACTION Total Total Total
55 11.55% 12.27% 15.19%
70 14.71% 15.34% 12.85%
60 12.61% 13.29% 15.19%
95 19.96% 19.02% 22.90%
65 13.66% 14.93% 11.45%
71 14.92% 11.86% 12.62%
60 12.61% 13.29% 9.81%
476 100.00% 100.00% 100.00%
ram sgsits indore
shyam medicaps ujjain
sgsits indore
ammedi capsujjain
Back to Index
Revenue FY'17
ITEMS January February March April May June July August September
Salary 120000 5000 120000 120000 2000 1000 20000 120000 120000
Stipend 0 0 0 0 0 0 0 0 0
Rent 0 0 0 0 0 0 0 0 0
Interest 0 0 0 150000 0 10000 0 0
Dividend 0 0 0 0 0 17000 0 0 0
Pocket Money 0 0 0 0 0 0 0 0 0
Others 2000 2200 2000 2200 2000 2200 2000 2200 2000
Grand Total 122000 7200 122000 272200 4000 20200 32000 122200 122000
October November December GRAND TOTAL
120000 120000 120000 988000
0 0 0 0
0 0 0 0
0 0 0 160000
0 12000 0 29000
0 0 0 0
2200 2000 2200 25200
122200 134000 122200 1202200
Back to Index
Expense FY'17
Items January February March April May June July August
Rent 6000 6000 6000 6000 6000 6000 6000 6000
Education 22000 32200 11500 41000 86500 8800 19000 9000
College 6000 10000 2000 32000 0 0 0 0
Tuitions 6000 6000 6000 6000 6000 6000 6000 6000
[Link] 10000 13000 0 0 78000 0 10500 0
Grocery 0 3200 3500 3000 2500 2800 2500 3000
Transportation 3000 3000 3000 3000 3000 3000 3000 3000
Entertainment 1200 500 1700 1200 600 1700 1200 600
Mobile & Internet 2000 2000 2000 2000 2000 2000 2000 2000
Shopping 2000 2000 2000 2000 2000 20000 15000 2000
Outings 6000 6000 6000 6000 6000 6000 6000 6000
Others 7000 12000 15000 6000 9000 12000 17000 16000
Grand Total 27200 34700 39200 29200 31100 53500 52700 38600
Outings 18000
65700
18000
18000
23000
6700
9000
3400
6000
6000
18000
34000
September October November December Grand Total
6000 6000 6000 6000 72000
12000 29000 41000 12000 324000
0 2000 32000 0 84000
6000 6000 6000 6000 72000
0 17000 0 0 128500
6000 4000 3000 6000 39500
3000 3000 3000 3000 36500
1700 1200 600 1700 13900
2000 2000 2000 2000 24000
2000 2000 2000 2000 55000
6000 6000 8000 8000 76000
15000 14000 12000 15000 150000
41700 38200 36600 43700 466400
Back to Index
January February March April May June July August
122000 7200 122000 272200 4000 20200 32000 122200
45600 -91200 35600 175800 -142200 -92800 -89900 39000
September October November December
122000 122200 134000 122200
32600 20800 22800 28800
Back to Index
500 1000
Quantity "+" 500 Qty 000's Interest Rate
15
52 Principal
63 51000
14 12000
85 55000
25 46000
97 32000
46 29000
72 19000
55 23000
67 34000
42000
56000
15%
Time Interest Amount
5
4
7
6
2
4
5
4
3
5
6
Back to Index
Format the cells as follows
Less than 550= Green
in between 550-575= Red
Greater than 575= Red+Bold+Italics
Players Goals
Lionel Messi 600
Fillipo Inzaghi 580
Edison Cavani 575
Eden Hazard 574
Zalatan Ibrahimovic 565
Cristiano Ronaldo 664 33
Robert Lewandowski 540
Sergio Aguero 537
Hugo Sanchez 536
Luis Suarez 534
Back to Index
Group in the following manner-
1. Quarter-wise
2. Product-wise
3. Half Yearly-wise
Product X Jan Feb Mar QTR 1 Apr May Jun
North 34 43 65 142 34 45 23
South 23 23 32 78 24 25 21
East 3 2 5 10 5 3 7
West 12 12 17 41 15 15 13
Total 72 80 119 271 78 88 64
Product Y Jan Feb Mar QTR 1 Apr May Jun
North 23 21 34 78 35 29 43
South 34 35 43 112 27 28 26
East 19 21 25 65 21 25 24
West 11 15 18 44 18 21 23
Total 87 92 120 299 101 103 116
Product Z Jan Feb Mar QTR 1 Apr May Jun
North 12 14 15 41 18 17 15
South 2 5 8 15 4 6 122
East 3 5 2 10 5 6 7
West 44 55 63 162 33 54 61
Total 61 79 88 228 60 83 205
grand
total 220 251 327 798 239 274 385
Shortcuts:
Grouping: Shift+Alt+Right Arrow
Ungrouping: Shift+Alt+Left Arrow
QTR 2 H1
102 244
70 148
15 25
43 84
230 501
QTR 2 H1
107 185
81 193
70 135
62 106
320 619
QTR 2 H1
50 91
132 147
18 28
148 310
348 576
898 1696
Back to Index
Convert the given text to Data Column
DELIMITED
Country,Revenue (USD billions),Capitalization (USD billions)
UK,652,852 UK 652
Spain,532,102 Spain 532
Germany,492,419 Germany 492
Dubai,145,362 Dubai 145
Spain,253,583 Spain 253
France,230,63 France 230
US,236,465 US 236
UK,321,21 UK 321
US,365,71 US 365
Dubai,623,25 Dubai 623
Canada,522,95 Canada 522
Germany,400,223 Germany 400
France,329,52.32 France 329
FIXED WIDTH
CODE
State Code, MM,YY, [Link]
TN0218105 TN 2
MP1119002 MP 11
DL0118163 DL 1
WB1020750 WB 10
JK0419653 JK 4
GJ1212333 GJ 12
GJ1119520 GJ 11
MH0920632 MH 9
MP0218208 MP 2
CG0620444 CG 6
PB0519874 PB 5
AP08154190 AP 8
852
102
419
362
583
63
465
21
71
25
95
223
52.32
18 105
19 2
18 163
20 750
19 653
12 333
19 520
20 632
18 208
20 444
19 874
15 4190
