Formatting

Aung
Computer

Aung Computer

Aung
Computer

Aung
Computer
AutoFail 1 Sunday jan 1 Sunday
Customlist 2 Monday feb 2 Monday
Hide Column 3 Tuesday mar 3 Tuesday
4 Wednesdayapr 4 Wednesday
5 Thursday may 5 Thursday
6 Friday jun 6 Friday
7 Saturday jul 7 Saturday
8 Sunday aug 8 Sunday
9 Monday sep 9 Monday
10 Tuesday oct 10 Tuesday
11 Wednesdaynov 11 Wednesday
12 Thursday dec 12 Thursday
13 Friday jan 13 Friday
14 Saturday feb 14 Saturday
15 Sunday mar 15 Sunday
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
jan
feb
mar
 1 Aung-1 Yangon Yangon Mandalay
2 Aung-2 Mandalay Mandalay
3 Aung-3 Naypyitaw Naypyitaw
4 Aung-4 Pathein Pathein
5 Aung-5 Yangon
6 Aung-6 Mandalay
7 Aung-7 Naypyitaw
8 Aung-8 Pathein
9 Aung-9 Yangon
10 Aung-10 Mandalay
11 Aung-11 Naypyitaw
12 Aung-12 Pathein
13 Aung-13 Yangon
14 Aung-14
15 Aung-15
NaypyitawPathein Yangon Mandalay
 Add-G
Name In Formula
Borders
40 50 90 Item
100 200 300 Apple
Orange
300 200 100 Mango
1800 600 1200 Total

40 30 1200
1000 300 300000

Salary Day One Day

500000 30 16666.67
150000 30 5000
Price
300
500
300
1100
 Sum
Autosum
Max
Aung Computer
Min Item Price Quantity Amount
Average Microsoft Word 6000 4 24000
Excel 6000 6 36000
PowerPoint 6000 8 48000
Pagemaker 7000 10 70000
Photoshop 7000 5 35000
Total 213000
Max 70000
Min 24000
Average 42600
 Aung Computer
Cell Style Order ID Product Category Amount
Merge Cell 1 Carrots Vegetables 4270
StrikeThrough 2 Broccoli Vegetables 8239
SuperScript 3 Banana Fruit 617
Subscript 4 Banana Fruit 8384
5 Beans Vegetables 2626
6 Orange Fruit 3610
7 Broccoli Vegetables 9062
8 Banana Fruit 6906
9 Apple Fruit 2417

Computer
training
Centre
omputer

Date Country
42375 United States
42376 United Kingdom
42377 United States
42379 Canada
42379 Germany
42380 United States
42380 Australia
42385 New Zealand
42385 France
If
And
If,And
Students Mark Pass Or Fail Dis
Aung Aung 83 Pass D
Lwin Naing 67 Pass
Win Oo 84 Pass D
Pwint Wai 32 Fail
Soe Soe 26 Fail
Moe Moe 59 Pass
Min Ko 49 Pass
Chit Chit 95 Pass D
Aye Aye 41 Pass
Aung Aung 33 Fail

Students Myan Eng Maths Phy Chem Bio

Aung Aung 71 83 49 34 24 64
Moe Moe 60 67 95 61 100 67
Soe Soe 90 84 41 95 76 45
Win Win 88 32 63 80 61 44
Myint Myint 73 26 31 26 27 47
Kyaw Kyaw 58 59 50 41 74 70
 5 No
200 100 Yes

12 12 1

100 50 Yes

Pass Or Fail Total

Fail 325
Pass 450
Pass 431
Fail 368
Fail 230
Pass 352
 Transpose
Flash Fail
Move Copy
Name Salary
Thu Rosy 9
Ctrl+A Khine 15
CtrLl+C Wai Mon 32
Ctrl+Alt+V Hnin Pwint 5
Su 10
Than Than 68
Ei Ei 125

Name Thu Rosy Khine Wai Mon Hnin Pwint

Salary 9 15 32 5
Designation Executive Executive Manager Executive

Name Salary
Thu Rosy 9
Khine 15
Wai Mon 32
Hnin Pwint 5
Su 10
Than Than 68
Ei Ei 125

First Name Last Name Full Name

Aung Thu Rosy Aung Thu Rosy
Win Khine Win Khine
Soe Wai Mon Soe Wai Mon
Chit Hnin Pwint Chit Hnin Pwint
Khin Su Khin Su
Thi Dar Lwin Thi Dar Lwin
Chit Ko Ko Chit Ko Ko
Lwin Mar Lwin Mar
Ei Chit Ei Chit
Than Than Khin Than Than Khin
 Designation
Executive
Executive
Manager
Executive
Executive
Branch Manager
CEO

Su Than Than Ei Ei
10 68 125
Executive Branch Manager CEO

Designation
Executive
Executive
Manager
Executive
Executive
Branch Manager
CEO
 Result
Upper aung computer AUNG COMPUTER
Lower AUNG COMPUTER aung computer
Proper aUnG coMPutEr Aung Computer
Exact Aung Computer Aung Computer 1
Len Computer Compter 0
Okey Okey 1
Computer 8
Microsoft Excel 15
 Text To Column Text To Column
Remove Duplicate Aung Kyaw Lwin Aung
Concatenate Soe Yu Mon Soe
Khin Hnin Yu Khin
Win Thu Zar Win
Pa Pa Khin Pa
Lwin Mar Lwin
Min Thank Min
Thu Thu Khin Thu

Remove Duplicate
Desktop
Laptop
Keyboard
Muose
Monitor
Printer
Kyaw Lwin Aung Kyaw Lwin
Yu Mon Soe Yu Mon
Hnin Yu Khin Hnin Yu
Thu Zar Win Thu Zar
Pa Khin Pa Pa Khin
Mar Lwin Mar
Thank Min Thank
Thu Khin Thu Thu Khin
Order ID Product Category Amount Date Country
1 Carrots Vegetables 4270 42375 United States
2 Broccoli Vegetables 8239 42376 United Kingdom
3 Banana Fruit 617 42377 United States
4 Banana Fruit 8384 42379 Canada
5 Beans Vegetables 2626 42379 Germany
6 Orange Fruit 3610 42380 United States
7 Broccoli Vegetables 9062 42380 Australia
8 Banana Fruit 6906 42385 New Zealand
9 Apple Fruit 2417 42385 France
Insert Row
Save
Save as
PDF
 24
Conditional Formatting
Consolidate Sales Man Jan Feb Mar
Moe Moe 2000 1000 800
Soe Soe 4000 2000 1000
Win Win 6000 3000 1200
Myint Myint 8000 4000 1400
Kyaw Kyaw 10000 5000 1600
Aung Aung 12000 6000 1800
Moe Moe 14000 7000 2000
Soe Soe 16000 8000 2200
Win Win 18000 9000 2400
Myint Myint 20000 10000 2600
Kyaw Kyaw 22000 11000 2800
Win Win 24000 12000 3000
Apr May Jun
3000 4000 800
3200 5000 700
3400 6000 600
3600 7000 500
3800 8000 400
4000 9000 300
4200 10000 200
4400 11000 100
4600 12000 300
4800 13000 400
5000 14000 500
5200 15000 600
PowerPoint Book

PowerPoint Book
 4 7 9
Aung Computer Aung 10 3 12
Com 14 2 8
Left puter
Mid 4
Right 14
Small
Large
ID Name Salary Region Position
A-017 Chit Chit 150000 Yangon Marketing
A-031 Aye Aye 150000 Yangon Marketing
A-001 Aung Aung 200000 Yangon Accountant
A-002 Lwin Naing 200000 Mandalay Office
A-003 Win Oo 180000 Yangon Sale
A-004 Pwint Wai 180000 Yangon Sale
A-005 Soe Soe 150000 Mandalay Sale
A-006 Moe Moe 150000 Mandalay Marketing
A-007 Min Ko 150000 Yangon Marketing
A-008 Chit Chit 150000 Yangon Marketing
A-009 Aye Aye 150000 Yangon Marketing
A-010 Aung Aung 200000 Yangon Accountant
A-011 Lwin Naing 200000 Mandalay Office
A-012 Win Oo 180000 Yangon Sale
A-013 Pwint Wai 180000 Yangon Sale
A-014 Soe Soe 150000 Mandalay Sale
A-015 Moe Moe 150000 Mandalay Marketing
A-016 Min Ko 150000 Yangon Marketing
A-018 Aye Aye 150000 Yangon Marketing
A-019 Aung Aung 200000 Yangon Accountant
A-020 Lwin Naing 200000 Mandalay Office
A-021 Win Oo 180000 Yangon Sale
A-022 Pwint Wai 180000 Yangon Sale
A-023 Soe Soe 150000 Mandalay Sale
A-024 Moe Moe 150000 Mandalay Marketing
A-025 Min Ko 150000 Yangon Marketing
A-026 Chit Chit 150000 Yangon Marketing
A-027 Aye Aye 150000 Yangon Marketing
A-028 Moe Moe 150000 Mandalay Marketing
A-029 Min Ko 150000 Yangon Marketing
A-030 Chit Chit 150000 Yangon Marketing
A-032 Aung Aung 200000 Yangon Accountant
A-033 Lwin Naing 200000 Mandalay Office
A-034 Win Oo 180000 Yangon Sale
A-035 Pwint Wai 180000 Yangon Sale
A-036 Soe Soe 150000 Mandalay Sale
A-037 Moe Moe 150000 Mandalay Marketing
A-038 Min Ko 150000 Yangon Marketing
A-039 Chit Chit 150000 Yangon Marketing
A-040 Aye Aye 150000 Yangon Marketing
A-041 Moe Moe 150000 Mandalay Marketing
A-042 Min Ko 150000 Yangon Marketing
A-043 Chit Chit 150000 Yangon Marketing
A-044 Aye Aye 150000 Yangon Marketing
 Soft
Soft By Color
Filter
Advanced Filter NAME SALE PRODUCT QUANTITY AMOUNT
AYE AYE PRINTER 6 1500000
AYE SWE USB 6 6000
KYAW KYAW LAPTOP 5 500000
KYAW SOE TV 7 500000
LIN LIN DVD 23 60000
MIN MIN DVD 26 34000
MIN THU TV 4 400000
MOE MOE KEYBOARD 9 40000
MYAT MYAT USB 32 40000
MYINT MG LAPTOP 2 200000
PWINT PWINT CD 8 80
SOE SOE MOUSE 6 60000
THU THU CD 2 500
WIN MYINT TV 10 20000
WIN WIN MOUSE 7 25000
AYE AYE PRINTER 6 30000
AYE SWE USB 6 35000
KYAW KYAW LAPTOP 5 40000
KYAW SOE TV 7 45000
LIN LIN DVD 23 50000
MIN MIN DVD 26 55000
MIN THU TV 4 60000
MOE MOE KEYBOARD 9 65000
MYAT MYAT USB 32 70000
MYINT MG LAPTOP 2 75000
PWINT PWINT CD 8 80000
SOE SOE MOUSE 6 85000
THU THU CD 2 90000
WIN MYINT TV 10 95000
WIN WIN MOUSE 7 100000
 SALE PRODUCT
LAPTOP

NAME SALE PRODUCT QUANTITY AMOUNT

KYAW KYAW LAPTOP 5 500000
MYINT MG LAPTOP 2 200000
KYAW KYAW LAPTOP 5 40000
MYINT MG LAPTOP 2 75000

Name
Lin Lin

NAME SALE PRODUCT QUANTITY AMOUNT

LIN LIN DVD 23 60000
LIN LIN DVD 23 50000
 Count
Count A
Count Blank Fruit Amount
Countif Apples 50
Countifs Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Apples 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Apples 40

Fruit Sales Man Region

AYE SWE Apples AYE AYE YGN
Oranges Oranges AYE SWE YGN
Bananas KYAW KYAW MDY
2 Lemons KYAW SOE MDY
Apples LIN LIN NPT
Oranges MIN MIN NPT
Bananas MIN THU YGN
Lemons MOE MOE MDY
Apples MYAT MYAT MDY
Oranges MYINT MG NPT
Bananas PWINT PWINT NPT
Apples SOE SOE NPT
 Apples AYE AYE YGN
Oranges AYE SWE YGN
Bananas KYAW KYAW YGN
Lemons KYAW SOE YGN
Apples LIN LIN NPT
Oranges MIN MIN NPT
Bananas MIN THU NPT
Lemons MOE MOE MDY
Apples MYAT MYAT MDY
Oranges MYINT MG MDY
Bananas PWINT PWINT MDY
Apples SOE SOE YGN

Apples
COUNT VALUE 3
COUNTA VALUE+TEXT 17
COUNTBLANK BLANK 7

Oranges
Bananas
6
Apples

7
Apples
800
Oranges
Bananas
Lemons
Apples
Oranges
Bananas

Apples

Bananas
Apples
 TAB Key
Ctrl+A
Apples
Apples
Apples Fruit
8 Fruit

Amount
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
COUNTIF Apples Fuji COUNTIFS
SUMIF Apples Fuji SUMIFS
Fruit Type
Fruit Type
 Sum Fruit Amount
Sumif Apples 50
Sumifs Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Apples 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Lemons 40
Apples 50
Oranges 20
Bananas 60
Apples 40

Fruit Sales Man Region

AYE AYE Apples AYE AYE YGN
Apples Oranges AYE SWE YGN
Bananas KYAW KYAW MDY
Lemons KYAW SOE MDY
100 Apples LIN LIN NPT
Oranges MIN MIN NPT
Bananas MIN THU YGN
Lemons MOE MOE MDY
Apples MYAT MYAT MDY
Oranges MYINT MG NPT
Bananas PWINT PWINT NPT
Apples SOE SOE NPT
Apples AYE AYE YGN
Oranges AYE SWE YGN
Bananas KYAW KYAW YGN
Lemons KYAW SOE YGN
Apples LIN LIN NPT
Oranges MIN MIN NPT
Bananas MIN THU NPT
Lemons MOE MOE MDY
Apples MYAT MYAT MDY
Oranges MYINT MG MDY
Bananas PWINT PWINT MDY
Apples SOE SOE YGN
 1020

Lemons

160

120

Amount
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
50
20
60
40
 Data valitation

၁ မွ ၁၀၀၀ ထိ စာလုံ းကန္႔သန္႔ ရက္စြဲကန္႔သန္႔

အမည္ အသုံ းျပဳရမည့္ပမာဏ Aung 2/1/2016
အခ်ိဳမွဳန္႔ 100 Mo 1/1/2017
ဆပ္ျပာ 7000 Soe
ငရု တ္သီး
ငါးပိ
ၾကက္သြန္ျဖဴ
ၾကက္သြန္နီ
List List
Apple Keyboard Desktop
Banana Keyboard Laptop
Lemon Keyboard Keyboard
Orange Monitor Muose
Monitor
Printer
Form Create
Name Age Salary
Aung Aung 22 200000
Aye Aye 21 190000
Su Su 20 170000
Kyaw Kyaw 19 160000
Win Win 19 200000
D Sum Group

Sales Man Region Amount Sales Man

Aung Aung Ygn 2000 Kyaw Kyaw
Moe Moe Npt 3000
Soe Soe Mdy 4000 Dmax
Win Win Mdy 5000 Dcount
Myint Myint Ygn 6000
Kyaw Kyaw Npt 7000 18000
Aung Aung Mdy 8000
Moe Moe Mdy 9000
Soe Soe Ygn 10000
Win Win Npt 11000
Myint Myint Mdy 12000
Kyaw Kyaw Mdy 13000
Win Win Mdy 5000
Myint Myint Ygn 6000
Kyaw Kyaw Npt 7000
Aung Aung Mdy 8000
Moe Moe Mdy 9000
Soe Soe Ygn 10000
Win Win Npt 11000
Myint Myint Mdy 12000
Kyaw Kyaw Mdy 13000
Amount
40000 Aung Aung
Moe Moe
13000 Soe Soe
4 Win Win
Myint Myint
Kyaw Kyaw
 Datedif
Networkday
Workday

DatedIf Formula
Y YM MD
ေမြးေန႔ ေရာက္ရွိရက္ ႏွစ္ လ ရက္

2/2/1999 8/29/2021 22 6 27
2/3/1999 8/29/2021 22 6 26
2/4/1999 8/29/2021 22 6 25
2/5/1999 8/29/2021 22 6 24

အမည္ အလု ပ္၀င္ရက္ ေရာက္ရွိရက္ ႏွစ္ လ ရက္

f4
ေရာက္ရွိအခ်ိန္
05:52:18 PM Hour Minute Second Time
17 52 18 5:52 PM

ေရာက္ရွိရက္စြဲ
Day Month Year Date
8/29/2021 29 8 2021 8/29/2021
Date Region Selesman Amount
2/Mar/2019 Ygn AYE SWE 2500
3/Mar/2019 Mdy KYAW KYAW 3000
4/Mar/2019 Npt KYAW SOE 3500
5/Mar/2019 Pt LIN LIN 4000
6/Mar/2019 Ygn MIN MIN 4500
7/Mar/2019 Mdy MIN THU 5000
8/Mar/2019 Npt MOE MOE 5500
9/Mar/2019 Pt MYAT MYAT 6000
10/Mar/2019 Ygn MYINT MG 6500
11/Mar/2019 Mdy PWINT PWINT 7000
12/Mar/2019 Npt SOE SOE 7500
13/Mar/2019 Pt AYE AYE 8000
Item Price Qty Amount
Microsoft Word 6000 4 24000

Excel 6000 6 36000

PowerPoint 6000 8 48000

Pagemaker 7000 10 70000

Photoshop 7000 5 35000

Microsoft Word 6000 11 66000

Excel 6000 13 78000

PowerPoint 6000 15 90000

Pagemaker 7000 17 119000

Photoshop 7000 19 133000

Microsoft Word 6000 21 126000

Excel 6000 23 138000

PowerPoint 6000 25 150000

Pagemaker 7000 27 189000

Photoshop 7000 29 203000

Sum 447000
Subtotal 447000
Order ID Product Category Amount Date Country Page Number
1 Carrots Vegetables 4270 42375 United States Header And Footer
2 Broccoli Vegetables 8239 42376 United Kingdom Print Title
3 Banana Fruit 617 42377 United States Print Area
4 Banana Fruit 8384 42379 Canada Custom view
5 Beans Vegetables 2626 42379 Germany
6 Orange Fruit 3610 42380 United States
7 Broccoli Vegetables 9062 42380 Australia
8 Banana Fruit 6906 42385 New Zealand
9 Apple Fruit 2417 42385 France
10 Carrots Vegetables 4270 42375 United States
11 Broccoli Vegetables 8239 42376 United Kingdom
12 Banana Fruit 617 42377 United States
13 Banana Fruit 8384 42379 Canada
14 Beans Vegetables 2626 42379 Germany
15 Orange Fruit 3610 42380 United States
16 Broccoli Vegetables 9062 42380 Australia
17 Banana Fruit 6906 42385 New Zealand
18 Apple Fruit 2417 42385 France
19 Carrots Vegetables 4270 42375 United States
20 Broccoli Vegetables 8239 42376 United Kingdom
21 Banana Fruit 617 42377 United States
22 Banana Fruit 8384 42379 Canada
23 Beans Vegetables 2626 42379 Germany
24 Orange Fruit 3610 42380 United States
25 Broccoli Vegetables 9062 42380 Australia
26 Banana Fruit 6906 42385 New Zealand
27 Apple Fruit 2417 42385 France
28 Carrots Vegetables 4270 42375 United States
29 Broccoli Vegetables 8239 42376 United Kingdom
30 Banana Fruit 617 42377 United States
31 Banana Fruit 8384 42379 Canada
32 Beans Vegetables 2626 42379 Germany
33 Orange Fruit 3610 42380 United States
34 Broccoli Vegetables 9062 42380 Australia
35 Banana Fruit 6906 42385 New Zealand
36 Apple Fruit 2417 42385 France
37 Carrots Vegetables 4270 42375 United States
38 Broccoli Vegetables 8239 42376 United Kingdom
39 Banana Fruit 617 42377 United States
40 Banana Fruit 8384 42379 Canada
41 Beans Vegetables 2626 42379 Germany
42 Orange Fruit 3610 42380 United States
43 Broccoli Vegetables 9062 42380 Australia
44 Banana Fruit 6906 42385 New Zealand
45 Apple Fruit 2417 42385 France
46 Carrots Vegetables 4270 42375 United States
47 Broccoli Vegetables 8239 42376 United Kingdom
48 Banana Fruit 617 42377 United States
49 Banana Fruit 8384 42379 Canada
50 Beans Vegetables 2626 42379 Germany
51 Orange Fruit 3610 42380 United States
52 Broccoli Vegetables 9062 42380 Australia
53 Banana Fruit 6906 42385 New Zealand
54 Apple Fruit 2417 42385 France
55 Carrots Vegetables 4270 42375 United States
56 Broccoli Vegetables 8239 42376 United Kingdom
57 Banana Fruit 617 42377 United States
58 Banana Fruit 8384 42379 Canada
59 Beans Vegetables 2626 42379 Germany
60 Orange Fruit 3610 42380 United States
61 Broccoli Vegetables 9062 42380 Australia
62 Banana Fruit 6906 42385 New Zealand
63 Apple Fruit 2417 42385 France
64 Carrots Vegetables 4270 42375 United States
65 Broccoli Vegetables 8239 42376 United Kingdom
66 Banana Fruit 617 42377 United States
67 Banana Fruit 8384 42379 Canada
68 Beans Vegetables 2626 42379 Germany
69 Orange Fruit 3610 42380 United States
70 Broccoli Vegetables 9062 42380 Australia
71 Banana Fruit 6906 42385 New Zealand
72 Apple Fruit 2417 42385 France
73 Carrots Vegetables 4270 42375 United States
74 Broccoli Vegetables 8239 42376 United Kingdom
75 Banana Fruit 617 42377 United States
76 Banana Fruit 8384 42379 Canada
77 Beans Vegetables 2626 42379 Germany
78 Orange Fruit 3610 42380 United States
79 Broccoli Vegetables 9062 42380 Australia
80 Banana Fruit 6906 42385 New Zealand
81 Apple Fruit 2417 42385 France
82 Carrots Vegetables 4270 42375 United States
83 Broccoli Vegetables 8239 42376 United Kingdom
84 Banana Fruit 617 42377 United States
85 Banana Fruit 8384 42379 Canada
86 Beans Vegetables 2626 42379 Germany
Percentage
၀န္ထမ္ းမ်ား
က်ား ၃၄၉ 57%
မ ၂၅၈ 43%
ေပါင္း ၆၀၇
Discount 10%
Product Price Dis Price
ဦးထု တ္ ၄၀၀၀ ၃၆၀၀ ,=E4-(E4*$F$2)
ေဘာင္းဘီ ၇၀၀၀ ၆၃၀၀
ထီး ၄၅၀၀ ၄၀၅၀
ဖိနပ္ ၅၅၀၀ ၄၉၅၀
ပု ဆု ိး ၆၀၀၀ ၅၄၀၀
=NETWORKDAYS Sat,Sun
=NETWORKDAYS.INTL Fri အလု ပ္စရက္ ျပီးဆုံ းရက္

5/5/2020 5/20/2020
5/5/2020 5/20/2020
(ၾကားရက္) အားလပ္ရက္

11 5/12/2020
14
Work Day sun,sat
Work Day Intl Fri အလု ပ္စရက္ အလု ပ္လုပ္ရက္ေပါင္း ျပီးဆုံ းမည့္ေန႔

5/5/2020 10 5/19/2020
5/5/2020 10 5/16/2020
အားလပ္ရက္

5/13/2020
 Lookup Formula

ID Product Price ID Product Price Quantity

1001 Desktop 200000 1008 Usb 20000 3
1002 Laptop 400000
1003 Mouse 10000
1004 Keyboard 6000 ID Product Price Quantity
1005 Dvd 200 1005 Dvd 200 4
1006 Cd 100
1007 Tv 150000
1008 Usb 20000 =LOOKUP(F8,B4:B12,C4:C12)
1009 Memory 8000
Amount
60000

Amount
800
 Vlookup
Hlookup
ID Product Price ID Product Price
1001 Desktop 200000 1005 Dvd 200
1002 Laptop 400000
1003 Mouse 10000
1004 Keyboard 6000 ID Product Price
1005 Dvd 200 1008 Usb 20000
1006 Cd 100
1007 Tv 150000
1008 Usb 20000
1009 Memory 8000

ID 1001 1002 1003 ID 1002

Product Desktop Laptop Mouse Product Laptop
Price 200000 400000 10000 Price 400000

Hlookup

ID 1001 1002 1003 1004 1005 1006

Product Desktop Laptop Mouse Keyboard Dvd Cd
Price 200000 400000 10000 6000 200 100

ID 1003
Product Mouse
Price 10000
ITEM PRICE IN STOCK ITEM
MICROSOFTWORD BOOK 6000 10 PRICE
EXCEL BOOK 6000 4 IN STOCK
ADVANCED EXCEL BOOK 6000 6
POWERPOINT BOOK 6000 2
PHOTOSHOP BOOK 7000 11
PAGEMAKER BOOK 7000 55
BASIC DVD 12000 33
EXCEL DVD-1 5000 23
EXCEL DVD-2 5000 8
 BASIC DVD
12000
33

Vlookup Formula
 YANGON

ITEM JAN FEB MAR APR MAY

LAPTOP 20 20 20 20 20
MOUSE 20 20 20 20 20
DESKTOP 20 20 20 20 20
KEYBOARD 100 20 20 20 20
 MANDALAY

ITEM JAN FEB MAR APR

LAPTOP 20 20 20 20
MOUSE 20 20 20 20
DESKTOP 20 20 20 20
KEYBOARD 20 20 20 20
MAY
20
20
20
20
 NAYPYITAW

ITEM JAN FEB MAR APR

LAPTOP 200 20 20 20
MOUSE 200 20 20 20
DESKTOP 200 20 20 20
KEYBOARD 200 20 20 20
MAY
20
20
20
20
 TOTAL

ITEM JAN FEB MAR APR

LAPTOP
MOUSE
DESKTOP
KEYBOARD
TOTAL 0 0 0 0
MAY

0
Sumproduct Product Price Quantity Amount
Charts Apples 200 3 600
Oranges 250 4 1000
Bananas 100 5 500
Mango 160 6 960
Total 3060
Total 3060

Product Jan Feb Mar

Apples 200 60 80
Oranges 100 500 300
Bananas 400 300 60
Mango 60 200 700
စာသား ႏွင့္ ဂဏာန္းေရာေနသည့္အခါ တြက္နည္း LEFT
Find
အမည္ အေရအတြက္ ေစ်းႏႈန္း စု စု ေပါင္း
ေကာ္ဖီ ၁၀ ထု ပ္ ၁၀၀ #VALUE!
ဆပ္ျပာ ၂၀ ထု ပ္ ၁၅၀ #VALUE!
ဗလာစာအု ပ္ ၈ အု ပ္ ၂၀၀ #VALUE!
ခဲတံ ၁၅ ေခ်ာင္း ၁၂၀ #VALUE!
ေဘာ့ ပင္ ၅ ေခ်ာင္း ၂၅၀ #VALUE!

IF
OR ေမာင္ 5 လုံ းကု ိယ္စားျပဳ

Left ဦး ၃ လုံ းကု ိယ္စားျပဳ

အမည္ က်ား/မ က်ား-အေရအတြက္ မ-အေရအတြက္
ဦးတင္စိန္ က်ား 5 5
ဦးခင္ေမာင္ က်ား
မသင္းသင္း မ
ေမာင္စိန္ပန္း က်ား
မခင္ခင္ေအး မ
ေဒၚသန္းစိ္န ္ မ
ဦးလူ ေအး က်ား
ေမာင္သီဟထြန္း က်ား
မလင္းလင္း မ
မခင္ခင္စိန္ မ
ေမာင္စိန္လွ က်ား
3D Reference
Hyperlink

3D Reference YANGON
Hyperlink
MANDALAY

NAYPYIT
AW
 Offset
Sum offset Months Sale-1 Sale-2 Sale-3
Choose jan 200 100 300
Sum Choose feb 250 150 350
mar 300 200 400
apr 350 250 450
may 400 300 500
jun 450 350 550
jul 500 400 600
aug 550 450 650
sep 600 500 700
oct 650 550 750
nov 700 600 800
dec 750 650 850
5400
 2000

Offset 300
Sum, offset 500
Choose mar
Sum, Choose 4500

6900
4500
Name Region Salary Position Index Marketing
Moe Moe Ygn 500000 Manager Match 4
Soe Soe Mdy 300000 Office Index & Match
Win Win Npt 280000 Marketing
Myint Myint Mdy 250000 Sale
Kyaw Kyaw Ygn 200000 Sale Name Region
Aung Aung Ygn 180000 Office Win Win Npt
Salary Position
280000 Marketing
 BOOK STORE

Nested If ITEM Qty Remark

MICROSOFTWORD BOOK 33 ညံ့
EXCEL BOOK 130 ပု ိေကာင္း
ADVANCED EXCEL BOOK 20 ညံ့
POWERPOINT BOOK 300 အလြန္ေကာင္း
PHOTOSHOP BOOK 220 အလြန္ေကာင္း
PAGEMAKER BOOK 120 ပု ိေကာင္း
BASIC DVD 44 ညံ့
EXCEL DVD-1 66 ေကာင္း
EXCEL DVD-2 200 အလြန္ေကာင္း
1-50=ညံ
50-100=ေကာင္း
100-150=ပု ိေကာင္း
150> အလြန္ေကာင္း
Goal Seek
Day Amount
Monday 18000 80000
Tuesday 16000
Wednesday 10000
Thursday 8500
Friday 9500
Saturday 18000
Sunday 80000
Scenario Manager
စားေသာက္ဆုိင္ အစီအစဥ္
ဆို ္င ္ခန္းခ 200000
မီတာခ 50000
၀န္ထမ္ းခ 300000
အေထြေထြ 100000
စု စု ေပါင္း 650000
 Max,if Ctrl-Shift-Enter
Name Place Sales Amount Min,if
Moe Moe Yangon 2000 Yangon-အမ်ား 5000
Soe Soe Yangon 4000 Yangon-အနည္း 1000
Win Win Mandalay 6000 Mandalay-အမ်ား 8000
Myint Myint Mandalay 8000 Mandalay-အနည္း 2000
Kyaw Kyaw Yangon 1000
Aung Aung Mandalay 2000
Lwin Lwin Mandalay 3000
Kyi Kyi Yangon 4000
Thin Thin Yangon 5000
Item Laptops Phones Televisions Dependent Drownlist & Vlookup
Laptops Dell Micromax Samsung
Phones HP Samsung Sony
Televisions Lenovo Oppo Toshiba
Apple Redmi Philips
Microsoft Apple LG

Gategory Brand Price Qty Amount

Phones Oppo #NAME? 2 #NAME?
Laptop Price
Dell 500000
HP 450000
Lenovo 300000
Apple 800000
Microsoft 500000
Phone Price
Micromax 300000
Samsung 200000
Oppo 150000
Apple 500000
Redmi 180000
Television Price
Samsung 200000
Sony 150000
Toshiba 180000
Philips 300000
LG 220000
 VLOOKUP FORMULA
>
Name Mark Grade 40
Moe Moe 40 E 50
Soe Soe 90 A 60
Win Win 60 C 70
Myint Myint 77 B 80
Kyaw Kyaw 66 C
Aung Aung 90 A
Lwin Lwin 75 B
Kyi Kyi 55 D
Thin Thin 45 E
E
D
C
B
A
GIRL-1 GIRL-3 =INDEX(Sheet42!$B$2:$B$6,

GIRL-2

GIRL-3 =INDEX(Sheet42!$B$2:$B$6,MATCH(Sheet42!$E$3,Sheet42!$A$2:$A$6,0))

GIRL-4
 =INDEX(Sheet42!$B$2:$B$6,MATCH(Sheet42!$E$3,Sheet42!$A$2:$A$6,0))

$2:$A$6,0))
 PICTURE LOOKUP
NAME PHOTO

GIRL-1 NAME PHOTO

GIRL-2 GIRL-3

GIRL-3

GIRL-4 =INDEX(Sheet43!$B$2:$B$6,MATCH(Sheet43!$D$4,Sheet43!$A$2:$A$6,0))
Year Months Item Seles Man Region Quantity Unit Price Total Price
2017 July Keyboard Win Win Bago 54 8000 432000
2017 November Mouse Win Win Mandalay 55 10000 550000
2017 Apr Cd Win Win Mandalay 99 4000 396000
2017 December Laptop Win Win Naypyitaw 77 250000 19250000
2017 June Usb Win Win Naypyitaw 55 20000 1100000
2017 September Tv Win Win Pathein 98 130000 12740000
2017 Mar MicrophonWin Win Pathein 90 30000 2700000
2017 August Dvd Win Win Yangon 60 6000 360000
Year Months Item Seles Man Region Quantity Unit Price Total Price
2017 August Cd Aung Lwin Bago 33 4000 132000
2017 December Dvd Aung Lwin Mandalay 67 6000 402000
2017 June Earphone Aung Lwin Mandalay 56 1500 84000
2017 November Keyboard Aung Lwin Pathein 70 8000 560000
2017 Mar Memory caAung Lwin Yangon 33 5000 165000
2017 July MicrophonAung Lwin Naypyitaw 12 30000 360000
2017 May Mobile Ph Aung Lwin Pathein 54 320000 17280000
2017 September Usb Aung Lwin Yangon 76 20000 1520000
Year Months Item Seles Man Region Quantity Unit Price Total Price
2017 December Bluetooth Yin Yin Pathein 22 40000 880000
Sum - Quantity Region
Seles Man Bago Total Result
Kyaw Kyaw 54 54
Total Result 54 54
Year Months Item Seles Man Region Quantity Unit Price
2017 Jan Desktop Aung Aung Mandalay 10 200000
2017 Jan Laptop Kyaw Kyaw Yangon 16 250000
2017 Jan Mouse Soe Soe Naypyitaw 55 10000
2017 Jan Tv Mon Mon Pathein 22 130000
2017 Feb Dvd Lwin Lwin Bago 60 6000
2017 Feb Keyboard Ei Ei Mandalay 70 8000
2017 Feb Usb Hnin Hnin Yangon 55 20000
2017 Feb Cd Chit Chit Naypyitaw 33 4000
2017 Mar Microphone Win Win Pathein 90 30000
2017 Mar Earphone Khin Khin Bago 56 1500
2017 Mar Mobile Phone Min Tun Mandalay 63 320000
2017 Mar Memory card Aung Lwin Yangon 33 5000
2017 Mar Bluetooth Aung Aung Naypyitaw 50 40000
2017 Mar Desktop Kyaw Kyaw Pathein 64 200000
2017 Mar Laptop Soe Soe Bago 77 250000
2017 Apr Mouse Mon Mon Mandalay 44 10000
2017 Apr Tv Lwin Lwin Yangon 98 130000
2017 Apr Dvd Ei Ei Naypyitaw 67 6000
2017 Apr Keyboard Hnin Hnin Pathein 54 8000
2017 Apr Usb Chit Chit Bago 76 20000
2017 Apr Cd Win Win Mandalay 99 4000
2017 May Microphone Khin Khin Yangon 12 30000
2017 May Earphone Min Tun Naypyitaw 45 1500
2017 May Mobile Phone Aung Lwin Pathein 54 320000
2017 May Earphone Aung Aung Bago 34 5000
2017 May Bluetooth Kyaw Kyaw Mandalay 76 40000
2017 May Desktop Soe Soe Yangon 10 200000
2017 May Laptop Mon Mon Naypyitaw 16 250000
2017 May Mouse Lwin Lwin Pathein 55 10000
2017 June Tv Ei Ei Bago 22 130000
2017 June Dvd Hnin Hnin Mandalay 60 6000
2017 June Keyboard Chit Chit Yangon 70 8000
2017 June Usb Win Win Naypyitaw 55 20000
2017 June Cd Khin Khin Pathein 33 4000
2017 June Microphone Min Tun Bago 90 30000
2017 June Earphone Aung Lwin Mandalay 56 1500
2017 July Mobile Phone Aung Aung Yangon 63 320000
2017 July Earphone Kyaw Kyaw Naypyitaw 33 5000
2017 July Bluetooth Soe Soe Pathein 50 40000
2017 July Desktop Mon Mon Bago 64 200000
2017 July Laptop Lwin Lwin Mandalay 77 250000
2017 July Mouse Ei Ei Yangon 44 10000
2017 July Tv Hnin Hnin Naypyitaw 98 130000
2017 July Dvd Chit Chit Pathein 67 6000
2017 July Keyboard Win Win Bago 54 8000
2017 July Usb Khin Khin Mandalay 76 20000
2017 July Cd Min Tun Yangon 99 4000
2017 July Microphone Aung Lwin Naypyitaw 12 30000
2017 August Earphone Aung Aung Pathein 45 1500
2017 August Mobile Phone Kyaw Kyaw Bago 54 320000
2017 August Earphone Soe Soe Mandalay 34 5000
2017 August Bluetooth Mon Mon Yangon 76 40000
2017 August Desktop Lwin Lwin Naypyitaw 10 200000
2017 August Laptop Ei Ei Pathein 16 250000
2017 August Mouse Hnin Hnin Bago 55 10000
2017 August Tv Chit Chit Mandalay 22 130000
2017 August Dvd Win Win Yangon 60 6000
2017 August Keyboard Khin Khin Naypyitaw 70 8000
2017 August Usb Min Tun Pathein 55 20000
2017 August Cd Aung Lwin Bago 33 4000
2017 August Microphone Aung Aung Mandalay 90 30000
2017 August Earphone Kyaw Kyaw Yangon 56 1500
2017 August Mobile Phone Soe Soe Naypyitaw 63 320000
2017 August Earphone Mon Mon Pathein 33 5000
2017 August Bluetooth Lwin Lwin Bago 50 40000
2017 August Desktop Ei Ei Mandalay 64 200000
2017 September Laptop Hnin Hnin Yangon 77 250000
2017 September Mouse Chit Chit Naypyitaw 44 10000
2017 September Tv Win Win Pathein 98 130000
2017 September Dvd Khin Khin Bago 67 6000
2017 September Keyboard Min Tun Mandalay 54 8000
2017 September Usb Aung Lwin Yangon 76 20000
2017 September Cd Aung Aung Naypyitaw 99 4000
2017 October Microphone Kyaw Kyaw Pathein 12 30000
2017 October Earphone Soe Soe Bago 45 1500
2017 October Mobile Phone Mon Mon Mandalay 54 320000
2017 October Earphone Lwin Lwin Yangon 34 5000
2017 October Bluetooth Ei Ei Naypyitaw 76 40000
2017 October Desktop Hnin Hnin Pathein 10 200000
2017 November Laptop Chit Chit Bago 16 250000
2017 November Mouse Win Win Mandalay 55 10000
2017 November Tv Khin Khin Yangon 22 130000
2017 November Dvd Min Tun Naypyitaw 60 6000
2017 November Keyboard Aung Lwin Pathein 70 8000
2017 November Usb Aung Aung Bago 55 20000
2017 November Cd Kyaw Kyaw Mandalay 33 4000
2017 November Microphone Soe Soe Yangon 90 30000
2017 December Earphone Mon Mon Naypyitaw 56 1500
2017 December Mobile Phone Lwin Lwin Pathein 63 320000
2017 December Earphone Ei Ei Bago 33 5000
2017 December Bluetooth Hnin Hnin Mandalay 50 40000
2017 December Desktop Chit Chit Yangon 64 200000
2017 December Laptop Win Win Naypyitaw 77 250000
2017 December Mouse Khin Khin Pathein 44 10000
2017 December Tv Min Tun Bago 98 130000
2017 December Dvd Aung Lwin Mandalay 67 6000
2017 December Keyboard Aung Aung Yangon 54 8000
2017 December Usb Kyaw Kyaw Naypyitaw 76 20000
2017 December Cd Soe Soe Pathein 99 4000
2017 December Microphone Mon Mon Bago 12 30000
2017 December Earphone Lwin Lwin Mandalay 45 1500
2017 December Mobile Phone Ei Ei Yangon 54 320000
2017 December Earphone Hnin Hnin Naypyitaw 34 5000
2017 December Bluetooth Chit Chit Pathein 76 40000
2017 December Bluetooth Yin Yin Pathein 22 40000
Total Price
2,000,000
4,000,000
550,000
2,860,000
360,000
560,000
1,100,000
132,000
2,700,000
84,000
20,160,000
165,000
2,000,000
12,800,000
19,250,000
440,000
12,740,000
402,000
432,000
1,520,000
396,000
360,000
67,500
17,280,000
170,000
3,040,000
2,000,000
4,000,000
550,000
2,860,000
360,000
560,000
1,100,000
132,000
2,700,000
84,000
20,160,000
165,000
2,000,000
12,800,000
19,250,000
440,000
12,740,000
402,000
 432,000
1,520,000
396,000
360,000
67,500
17,280,000
170,000
3,040,000
2,000,000
4,000,000
550,000
2,860,000
360,000
560,000
1,100,000
132,000
2,700,000
84,000
20,160,000
165,000
2,000,000
12,800,000
19,250,000
440,000
12,740,000
402,000
432,000
1,520,000
396,000
360,000
67,500
17,280,000
170,000
3,040,000
2,000,000
4,000,000
550,000
2,860,000
360,000
560,000
1,100,000
132,000
2,700,000
84,000
20,160,000
 165,000
2,000,000
12,800,000
19,250,000
440,000
12,740,000
402,000
432,000
1,520,000
396,000
360,000
67,500
17,280,000
170,000
3,040,000
880,000
Order ID Date Item Region Total Sumif,Sumifs,Pivot table
1001 1/1/2019 Laptop Mandalay 300000
1002 1/2/2019 Desktop Yangon 250000
1003 1/3/2019 Mouse Naypyitaw 10000 Laptop
1004 1/4/2019 Keyboard Mandalay 8000 Desktop
1005 1/5/2019 Usb Yangon 30000 Mouse
1006 1/6/2019 Laptop Naypyitaw 300000 Keyboard
1007 1/7/2019 Desktop Mandalay 250000 Usb
1008 1/8/2019 Mouse Yangon 10000
1009 1/9/2019 Keyboard Naypyitaw 8000 Region
1010 1/10/2019 Usb Mandalay 30000
1011 1/11/2019 Laptop Yangon 300000 Item
1012 1/12/2019 Desktop Naypyitaw 250000 Desktop
1013 1/13/2019 Mouse Mandalay 10000 Keyboard
1014 1/14/2019 Keyboard Yangon 8000 Laptop
1015 1/15/2019 Usb Naypyitaw 30000 Mouse
1016 1/16/2019 Laptop Mandalay 300000 Usb
1017 1/17/2019 Desktop Yangon 250000 Total Result
1018 1/18/2019 Mouse Naypyitaw 10000
1019 1/19/2019 Keyboard Mandalay 8000
1020 1/20/2019 Usb Yangon 30000
1021 1/21/2019 Laptop Naypyitaw 300000
1022 1/22/2019 Desktop Mandalay 250000
1023 1/23/2019 Mouse Yangon 10000
1024 1/24/2019 Keyboard Naypyitaw 8000
1025 1/25/2019 Usb Mandalay 30000
1026 1/26/2019 Laptop Yangon 300000
1027 1/27/2019 Desktop Naypyitaw 250000
1028 1/28/2019 Mouse Mandalay 10000
1029 1/29/2019 Keyboard Yangon 8000
1030 1/30/2019 Usb Naypyitaw 30000
1031 1/31/2019 Laptop Mandalay 300000
1032 2/1/2019 Desktop Naypyitaw 155600
1033 2/2/2019 Mouse Mandalay 167600
1034 2/3/2019 Keyboard Yangon 179600
1035 2/4/2019 Usb Naypyitaw 191600
1036 2/5/2019 Laptop Mandalay 203600
1037 2/6/2019 Desktop Naypyitaw 215600
1038 2/7/2019 Mouse Mandalay 227600
1039 2/8/2019 Keyboard Yangon 239600
1040 2/9/2019 Usb Naypyitaw 251600
1041 2/10/2019 Laptop Mandalay 263600
1042 2/11/2019 Desktop Naypyitaw 275600
1043 2/12/2019 Mouse Mandalay 287600
1044 2/13/2019 Keyboard Yangon 299600
1045 2/14/2019 Usb Naypyitaw 311600
1046 2/15/2019 Laptop Mandalay 323600
Sumif,Sumifs,Pivot table
mandalay
Total Amount
2890800 Laptop 1690800
2146800 Desktop 500000
742800 Mouse 702800
766800 Keyboard 16000
934800 Usb 60000
7482000 2969600
Mandalay

Sum - Total
500000
16000
1690800
702800
60000
2969600
 Aung Computer
Alt အသုံ းမ်ားေသာ Shortcut Key မ်ား

Ctrl+C Ctrl+N Ctrl+F

Ctrl+V Ctrl+P Ctrl+H

Ctrl+B Ctrl+S Ctrl+D
Ctrl+I Ctrl+Z Ctrl+R
Ctrl+U Ctrl+Y Ctrl+PgUp
Ctrl+A Ctrl+Dn
Ctrl+C Aung Aung Aung Aung

Aung Aung Aung Aung

fdjfjk Aung Aung Aung Aung
fdjfl Aung Aung Aung Aung
eioreoir Aung Aung Aung Aung