THE EXCEL SCREEN

ROWS,COLUMNS,CELLS
TABS 16384 1048576
RIBBION
QUICK ACCESS TOOLBAR
NAME BOX
FORMULA BAR
ZOOM
SHEETS
FILENAME
[Link] AROUND THE WORK BOOK
[Link] VS WORKSHEET
[Link] DATA ENTRY
[Link] RANGES
[Link] WORKBOOK FORATS
[Link] & CLOSE FILE
FORMATTING IN EXCEL
FONT FONT ALIGNMENT
ALIGNMENT
NUMBER BOLD
ITALIC
UNDERLINE
BORDER
BACKGROUND COLOR
FONT COLOR
FONT1
FONT2
FONT3
NUMBERS SHORTCUT KEY CTRL-1

NUMBER
10
20
30

DATE
2/1/2023
2/2/2023
2/3/2023
2/4/2023

CURRENCY
10
20
30
BASIC FORMULAS ADD SUBTRACT DIVIDE MULTIPLAY
ADD/SUB/MULTIPLAY/DIVIDE 20 20 20 20
BODMAS 10 10 10 10
MOBILE PHONE COST CALCULATOR

B BRACKET ()
VOICE O ORDER ^
FREE MINUTES INCLUDED 120 D DIVIDE /
ESTIMATED MINUTES USED 200 M MULTIPLY *
COST PER MINUTE ₹ 2.00 A ADDITION +
TOTAL COST S SUBTRACTS -

SMS TEXT MASSAGES

FREE MESSAGES 100
ESTIMATED MESSAGES 300
COST PER SMS ₹ 1.00
TOTAL COST
TABS AND GROUP
CREAT TAB
RENAME TAB
DELET TAB
ADD TAB
ADD OPTIONS IN GROUP
ADJUSTING A WORK SHEET
INSERTING /DELETING CELLS MONTH 10 20 40
INSERTING /DELETING ROW JANUARY
INSERTING/DELETING COLUMN FEBRUARY
REZIZING COLOUM/ROW SIZE MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
CELL REFERENCE

RELATIVE REFERENCE
MONTH TOTAL REVENUE TOTAL EXPENSES NET IN COME
JANUARY 100000 75000 25000
FEBRUARY 210000 150000 60000
MARCH 324000 250000 74000
APRIL 540000 400000 140000
MAY 567890 657000 -89110
JUNE 657000 567000 90000

ABSOLUTE REFERENCE
NAME GRAND TOTAL PERCENTAGE
MAX SCORE 100
RAAM 98 1
RADHA 85 4
KRISHNA 67 7
MANI 45 9
MANOJ 78 5
GEETHA 96 2
KAVYA 56 8
KIRAN 76 6
GOPAL 86 3
CTRL/ENTER COPY FORMULA
COMMENTS
INSERT COMMENTS
EDIT COMMENTS
DELETE COMMENTS
SHOW ALL COMMENTS
COPYING & FILLING
COPY
PASTE
PASTE SPECIAL
COPYING SPECIAL (s) TO ONE/MANY CELLS NAME PRICE USD
COPYING TO DIFFERENT WORK BOOK KOMALA ₹ 10.00 $ 25.00
USING FILL FOR QUICK COPYING VANI ₹ 20.00 $ 30.00
MANOJ ₹ 30.00 $ 35.00
KIRAN ₹ 40.00 $ 45.00

ADVANCE FILLING
ADVANCE FILLING
FILL DRAG NUMBER
FILL DRAG MONTH
FILL DRAG DAYS 10 10 JAN
FILL DRAG CUSTOM LIST 11 FEB
FILL DOWN,RIGHT,UP,LEFT 12 MAR
13 APR
14 MAY
15 JUN
16 JUL
17 AUG
18 SEP
19 OCT
20 NOV
21 DEC
JANUARY MON MONDAY
FEBRUARY TUE TUESDAY RAAM RAAM RAVI
MARCH WED WEDNESDAY RADHA RAAM KIRAN
APRIL THU THURSDAY RAVI RAAM RAAM
MAY FRI FRIDAY KIRAN RAAM RADHA
JUNE SAT SATURDAY MANU RAAM RAVI
JULY SUN SUNDAY RAAM RAAM KIRAN
AUGUST MON MONDAY RADHA RAAM MANU
SEPTEMBER TUE TUESDAY RAVI RAAM RAAM
OCTOBER WED WEDNESDAY KIRAN RAAM RADHA
NOVEMBER THU THURSDAY RAAM RAVI
DECEMBER FRI FRIDAY
FILL SERIES
FILLING A SERIES WITH FORMATING
FILLING A SERIES WITHOUT FORMATING MON MON MON MON
FILLING A WEAKEND TUE TUE TUE
FILL DAYS WED WED WED
THU THU THU
FRI FRI FRI
SAT SAT MON
SUN SUN TUE
MON MON WED
TUE TUE THU
WED WED FRI
THU THU MON
FRI FRI TUE
 SHORT CUT KEY CTRL+E

FLASH FILL PARE 1

PRODUCT CODE FIRST 4 DIGITS PRODUCT CODE LAST 3 DIGITS

1000-165-B100 b100 1000-165-B100
1002-162-G101 g101 1002-162-G101
1004-167-H102 h102 1004-167-H102
1050-155-R103 r103 1050-155-R103
1002-185-W104 w104 1002-185-W104
1008-195-T105 t105 1008-195-T105
1006-165-N106 n106 1006-165-N106
1003-145-L107 l107 1003-145-L107
1005-135-P108 p108 1005-135-P108

DAYS FIRST ALPHABET DAYS FIRST TOW ALPHABET

MONDAY MONDAY
TUESDAY TUESDAY
WEDNESDAY WEDNESDAY
THURSDAY THURSDAY
FRIDAY FRIDAY
SATURDAY SATURDAY
SUNDAY SUNDAY

FIRST SECOND ,JHON-MARRY

JHON MARY
JOESPH RITA
MARY NEENA
ROBERT RONNY
JAY KUMAR
PEBREJA TALOD
PRODUCT CODE MID 3 DIGITS
1000-165-B100
1002-162-G101
1004-167-H102
1050-155-R103
1002-185-W104
1008-195-T105
1006-165-N106
1003-145-L107
1005-135-P108

DAYS LAST TWO ALPHABET

MONDAY
TUESDAY
WEDNESDAY
THURSDAY
FRIDAY
SATURDAY
SUNDAY
CUSTOM VIEW
CREATING VIEWS
DISPLAYING CUSTOM VIEWS
DELETING CUSTOM VIEWA

OWNER NAME PRODUCT PRICE IN DOLLARS

RAM SUZUKI 250000
YUGESH MARUTHI 350000
MANOJ TATA 275000
GEETHA ALTO 175000
KAVYA GRAND I10 450000
DIVYA BMW 1000000
RAMESH K10 350000
VIEWING WORKSHEETS
HIDING/UNHIDING COLUMNS/ROWS 1 2 3
FREEZING PANES
SPLITTING WINDOWS
10
EDITING DATA
OVERWRITING/EDITING CELL CONTENTS
EDITING FORMULA FUNCTIONS
USING UNDO/REDO MONDAY
FIND/REPLACE TUESDAY
GO TO WEDNESDAY
THURSDAY
FRIDAY
SATURDAY
SUNDAY
10
20
30
40
50
60
SPEEK CELLS
SPEEK CELLS BY ROW
SPEEK CELLS BY COLUMAN
SPEEK CELLS ON ENTER
STOP SPEAKING CELLS
Top product product code quantity price
Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000
VIEW FILE SIDE BYSSIDE
TILED
HORIZONTAL
CASCADE
VIRTICAL
SYNCHRONOUS SCROLLING
WORKING WITH WORKSHEETS
INSERTING/DELETING/RENAMEING WORKSHEET
COPYING/MOVING WORKSHEETS
HIDING & UNHIDING WORKSHEETS
CHANGING SHEETS COLOR
FORMATTING WORKSHEETS AFTER GROUPING
 VLOOKUP
V-VERTICAL

Top product product code quantity price

Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000

VLOOK UP
TOP PRODUCT PRICE UNIT TOTAL
mobile 25000 1 25000

VLOOKUP AND DATA VALIDATION

TOP PRODUCT PRICE UNIT TOTAL
camera 30000 3 90000

VLOOKUP AS FORMULA ELEMENT

TOP PRODUCT PRICE UNIT TOTAL
cosmetics 900

VLOOKUP AS NAME RANGE

TOP PRODUCT PRICE UNIT TOTAL
watches 5000
RULES OF V LOOKUP
LOOK VALUE SHOULD BE UNIQU IN THE COLUMN
YOU CAN FIND VALUES LEFT TO RIGHT
LOOKUP VALUE SHOULD BE FIRST COLUMN OF THE TABLE

NORMAL VLOOKUP
VLOOKUP FROM DIFFRRENT SHEET
VLOOKUP FROM DIFFRRENT WORK BOOK
COPY VLOOKUP FORMULA
VLOOKUP MULTIPLE VALUES AT ONCE
VLOOKUP SMALLEST VALUE
LOOKUP FUNCTION

INCOME TAX
INCOME TAX ₹ - ₹ 5.00
₹ 5,000.00 ₹ 40.00 ₹ 500.00 ₹ 10.00
₹ 1,000.00 ₹ 20.00
₹ 5,000.00 ₹ 40.00
₹ 10,000.00 ₹ 50.00
₹ 20,000.00 ₹ 40.00

INCOME ₹ - ₹ 500.00 ₹ 1,000.00 ₹ 5,000.00 ₹ 10,000.00

TAX ₹ 5.00 ₹ 10.00 ₹ 20.00 ₹ 40.00 ₹ 50.00

INCOME TAX
₹ 5,000.00 ₹ 40.00

ID LAST FIRST EMAILID PHONE NO

880-10048 RAAM E RAAM@[Link] 8073856246
880-10049 KAMALA R KAMALA@[Link] 8073856247
880-10050 MANOJ T MANOJ@[Link] 8073856248
880-10051 GEETHA H GEETHA@[Link] 8073856249
880-10052 KAVYA J KAVYA@[Link] 8073856250
880-10053 HEMA K HEMA@[Link] 8073856251
880-10054 LATHA Q LATHA@[Link] 8073856252
880-10055 RANGANATHA D RANGANATHA@[Link] 8073856253

ID PHONE NO
880-10053 8073856251

ID LAST FIRST EMAILID PHONE NO

880-10050 MANOJ T MANOJ@[Link] 8073856248
₹ 20,000.00
₹ 40.00
Top product product code quantity price
Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000

SPACE IN LOOKUP VALUE PRICE

MOBILE 25000
CAMERA
WATCHES
COSMETICS

SPACE IN 1ST COLUMN OF THE TABLE PRICE

MOBILE 25000
CAMERA 30000
WATCHES 5000
COSMETICS 900
VLOOKUP : SHOW FULL RECORD
PREREQUISITE IS MATCH FUNCTION

Top product product code quantity price

Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000

TOP PRODUCT PRODUCT CODE QUANTITY PRICE

LAPTOP 100-BUUT-004 7 50000
 HLOOKUP
HORIZONTAL

Top product Mobile camera watches cosmetics shoes laptop

product code 100-BCII-009 100-BVIO-088 100-BNIO-077 100-BHIK-066 100-BUIP-055 100-BUUT-004
quantity 20 21 34 5 6 7
price 25000 30000 5000 900 1500 50000

HLOOK UP Mobile
100-BCII-009
20
25000
perfums clothes
100-BHKL-003 100-BHEF-002
98 76
350 2000
 LOGICAL FUNCTION
LOGICAL TEST

WHAT NUMBER NUMBER LOGICAL FORMULA COMPARATIVE OPERATOR

IS 25=45 46 45 0 .=
IS 25<>45 44 45 1 <>
IS 25>45 46 45 1>
IS 25>=45 45 45 1 >=
IS 25<45 45 45 0<
IS 25<=45 45 45 1 <=
COMPARATIVE OPERATOR
EQUAL
NOT EQUAL
GREATER THAN
GREATER THAN EQUAL
LESS THAN
LESS THAN EQUAL
 IF FUNCTION

SALES1 SALES2
46 45
ANS AS FORMULA >
TRUE,FALSE TRUE 10 20
1,0 1
pass,fail PASS EXAMPLE
PASS,FAIL PASS PASS FAIL
YES,NO RAAM
E6+F6,0 30
TODAY(),TODAY()+10 Monday, March 24, 2025
 NESTED IF FUNCTION
<40 fail 500 3/24/2025
>40 pass 1500 3/25/2025
>60 distincation 2500 3/26/2025
NAME MARKS RESULT
VINOD 40 500 3/24/2025
RAAM 60 PASS 1500
RADHA 59 PASS 1500 3/25/2025
MANU 61 DIST 2500 3/26/2025
RAJIV 33 FAIL 500 3/24/2025
KIRAN 90 DIST 2500 3/26/2025
PAVAN 70 DIST 2500 3/26/2025
LATHA 35 FAIL 500 3/24/2025
SUMA 40 FAIL 500 3/24/2025
 AND FUNCTION
AND FUNCTION MARKS
45 >=0 TRUE
<=100
CRITRIA-1 CRITERIA 2 RESULT
>=0 <40 FAIL
>=40 <60 PASS
>=60 <100 DISTINCTION
<0 >100 INVALID

NAME MARKS RESULT

VINOD 100 invalid
RAAM 35 fail
RADHA 56 pass
MANU 45 pass
RAJIV 33 fail
KIRAN 90 dist
PAVAN 70 dist
LATHA 35 fail
SUMA 40 pass
 NOT FUNCTION

DATA 1 NOT
TRUE FALSE
FALSE TRUE

IF ERROR FUNCTION

DATA TYPE DATA ERROR FUNCTION

TEXT BALC COMPUTER BALC COMPUTER
NUMBER 57 57
1 TRUE TRUE
0 FALSE FALSE
ERROR FROM FORMULA #NAME? RAAM UPDATE IT AS SOON AS
 AUDITING TOOLS

PRODUCT QTY PRICE TOTAL TAX

CHAIR 123 25 3075 246
DESK 103 30 3090 247.2
TABLE 85 15 1275 102

TAXPER 100 20
8%

FORMULA
.=8*2+9-8/4 23 BODMAS
() < / * ,+ ,-
 DATE FUNCTION

FUNCTION DATE FORMULA

TODAY 3/24/2025
NOW 3/24/2025 16:32
DAY 3/24/2025 24
MOUNTH 3/24/2025 3
YEAR 3/24/2025 2025
DATE 3/24/2025

ADD/SUBTRACT DATE

DATE 3/24/2025
DATE+5 5/9/2025 5
DATE-5 2/7/2025 -5

ADD/SUBTRACT MONTH
EDATE
DATE 3/24/2025
DATE+10 MONTH 7/24/2028 10
DATE-10 MONTH 5/24/2024 -10

ADD/SUBTRACT YEAR
EDATE
DATE 3/24/2025
DATE+10 YEAR 3/24/2030 10
DATE-10 YEAR 3/24/2020 -10

NET WORK DAYS

[Link]

DATE VS DAY

NUMBRES OF DAYS IN A YEAR

START DATE 1/1/2018 52 WEEKS IN A YEAR
END DATE 1/1/2019 104 SAT,SUN

NETWORKDAYS 262 HOLIDAYS

NETWORKDAYS 261 8/15/2018
[Link] 314 2/10/2018
312 1/26/2019
DATEDIF
8/18/2003 12/27/2024
DATE OF BIRTH TODAY DATES
TOTAL YEAR 21
TOTAL MONTHS 256
TOTAL DAYS 7802

YEAR 21
MONTHS 4
DAYS 9
365
1YEAR
DAYS

INDEPENDENCE DAY
GANDI JAYANTHI
REPUBLIC DAY
STATISTICAL FUNCTIONS

NAME DAYS NUMBERS

SUMIF VINOD MON 10
COUNTIF RAAM TUE 20
AVERAGEIF RADHA WED 30
MANU THU 40
SUMIFS RAJIV FRI 50
AVERAGESIFS KIRAN SAT 60
COUNTIFS PAVAN SUN 70
LATHA MON 80
SUMA MON 90
SUM OF NUMBER LESS THAN 50 100

SUM OF MUNGER AGAINST MONDAY 180

COUNT OF NUMBERS LESS THAN 50 4

AVERAHE OF NUMBER LESS THAN 50 25

MATH & TRIG FUNCATION
SUMPRODUCT
MROUND
FLOOR
CEILING
MOD
QUOTIENT SUMPRODUCT
ARRAY 1
2
3
6

MROUND
NUMBER

4.1

7.2

11.2

FLOOR
NUMBER

4.1

6.1

15

CEILING
NUMBER

4.1

6.1

15

MOD
 FORMULS
1

QUOTIENT
FORMULS
2
3
ARRAY 2 MULTIPLAY
4 8
5 15
2 12
35
RESULT 35

DISCRIPTION RESULT

MROUND NUMBER TO THE NEAREST MULTIPLE OF 1 4

MROUND NUMBER TO THE NEAREST MULTIPLE OF 5 5

MROUND NUMBER TO THE NEAREST MULTIPLE OF 10 10

DISCRIPTION RESULT

ROUNDS 2.5 DOWN TO NEAREST MULTIPLE OF 1 4

ROUNDS 6 DOWN TO NEAREST MULTIPLE OF 5 5

ROUNDS 15 DOWN TO NEAREST MULTIPLE OF 10 10

DISCRIPTION RESULT

ROUNDS NUMBER TO NEAREST MULTIPLE OF 1 5

ROUNDS NUMBER TO NEAREST MULTIPLE OF 5 10

ROUNDS NUMBER TO NEAREST MULTIPLE OF 10 20

DISCRIPTION RESULT
REMAINDER OF 3/2 1
REMAINDER OF -3/2 THE SIGN IS THE SAME AS DIVISION -1

DISCRIPTION RESULT
INTEGER PROTION OF 5/2 2
INTEGER PROTION OF 10/3 3
FORMULA
.=SUMPRODUCT(E9:E11,F9:F11)
EXAMPLE
1.5
 DIFINE NAME RANGE
CREATING NAME RANGE
RENAME NAME RANGE
CREATE FROM SELECTION
DELETING NAME RANGE NAME DESIGNATION
USE NAME RANGE IN WORK BOOK RAAM MD
RADHA GM
KAMALA MD
KRISHNA GM
ROOPA SUPERVISOR

NAME RANGE WITH FORMULAS

WORKING WITH FUNCTION WITH NAME RANGE TEXT NUMBERS

SUM 280 MONDAY 10
MINIMUM 10 TUESDAY 20
MAXIMUM WEDNESDAY 30
AVERAGE THURSDAY 40
COUNT FRIDAY 50
COUNTA SATURDAY 60
COUNTBLANK SUNDAY 70
SMALL 7
LARGE
SALARY DEPT
45000 ACCOUNTS
23000 SALES
18500 ACCOUNTS
25000 PURCHASE
19500 HELP DESK
HOW LOOKUP RECOGNIZES VALUES FOR COMMISSION
$ 0 <=SALE < 2000
$ 2000<=SALE <3000
$ 3000<=SALE<5000
$ 5000<=SALE<10000
$ 10000>=SALE

SALES DONE
2000

LESS THEN 1ST VALUE IN LOOKUP COLUMAN DISPLAYS VALUE AS #NA

LOOKUP VALUE COLUMAN SHOULD BE SORTED IN ASCENDING ORDER
LEAVING 4TH ARGUMENTS BLANK MAKES IT AS APROXIMATE MATCH
SALE AMOUNT CATEGORY COMMISSION PAYED
$0 FAIR 0
$2,000 OK 40
$3,000 GOOD 200
$5,000 VERY GOOD 500
$10,000 EXCELLENT 1000

COMMISSON RESIVED
OK
INDEX

Top product product code quantity price

Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000

MATCH

Top product
Mobile
camera TOP PRODUCT POSITION NUMBER
watches LAPTOP 6
cosmetics
shoes
laptop
perfums
clothes
VALUE SERIAL NUMBER
shoes 5
Mobile 1
VALUE ROW NUMBER COLUMN NUMBER
5 4 3
100-BCII-0 1 2
PRINT PAGE SETUP &PRINT DATA

MARGINS
ORIENTATION
PAGE SIZE
PRINT AREA
BREAK
BACKGROUND
PRINT TITLES
SORT
NUMBERS
TEXT
DATES
MULTI COLUMN SORTING
COLOR SORT

SMALLEST TO LARGEST LARGEST TO SMALLEST

TIME (MIN) SALES DONE

1.4 ₹ 2,003.00
2.3 ₹ 2,344.00
2.5 ₹ 4,356.00
3.4 ₹ 6,543.00
5.4 ₹ 7,654.00
5.6 ₹ 7,869.00
7.6 ₹ 9,876.00

SORT BY COLOR, USE SORT DIALOG BOX, OR RIGHT-CLICK SORT MENU

SALESMAN SALESDONE
KIRAN ₹ 2,003.00
GEETHA ₹ 4,356.00
KAVYA ₹ 7,654.00
BHAVYA ₹ 9,876.00
MANOJ ₹ 2,344.00
LATHA ₹ 6,543.00
PRIYA ₹ 7,869.00
WHOLE RECORD IS SORTED OLDEST TO NEWEST

SALESMAN SALESDONE DATES

PRIYA ₹ 7,869.00 5/4/2006
MANOJ ₹ 2,344.00 5/3/2022
LATHA ₹ 6,543.00 5/4/2022
KIRAN ₹ 2,003.00 1/2/2023
KAVYA ₹ 7,654.00 2/1/2023
GEETHA ₹ 4,356.00 2/1/2023
BHAVYA ₹ 9,876.00 6/5/2023
protect sheet
protect workbook

product unlock 10cells & lock entire sheet

Mobile select 10 cells
camera unlock 10 cells
watches protect sheet
cosmetics
shoes lock 10 cells & unlock entire sheet
laptop select entire sheet
perfums unlock entire sheet
clothes lock 10 cells
protect sheet

Top product product code quantity

Mobile 100-BCII-009 20
camera 100-BVIO-088 21
watches 100-BNIO-077 34
cosmetics 100-BHIK-066 5
shoes 100-BUIP-055 6
laptop 100-BUUT-004 7
perfums 100-BHKL-003 98
clothes 100-BHEF-002 76
CTR +1 FORMAT CELL

price
25000
30000
5000
900
1500
50000
350
2000
CONDITIONAL FORMATING
HIGHLIGHT CELL RULES
TO BOTTOM RULES
CLEAR RULES NUMBERS TEXT DATES NUMBERS
10 MON 8/1/2024 10
80 TUE 8/2/2024 20
30 WED 8/3/2024 30
40 THU 7/4/2024 40
50 FRI 8/5/2024 50
60 SAT 8/6/2024 60
70 SUN 7/7/2024 70
80 MON 8/8/2024 80
90 TUE 8/9/2024 90
100 WED 8/10/2024 100
AVANCE FILTER CTRL+SHIFT+L

DATE RELEGION SALES REP DEPARTMENT COGS

2/5/2022 SOUTH ANAND ACCOUNT 5000 RELEGION
2/6/2021 WEST VINOD SALES 200 WEST
2/5/2025 NORTH KARTHIK ACCOUNT 2000
2/25/2022 EAST KEERTHI SALES 3000
2/26/2020 WEST RAAM MARKETING 5000
2/28/2023 EAST RADHA ACCOUNT 6520
2/1/2020 WEST RAVI SALES 8000
2/13/2024 NORTH KRISHNA MARKETING 350
DEPARTMENT COGS DATE
SALES <500 >05-02-2020
CREATE A TABLE SHORTCUT TO CREAT A TABLE : CTRL+T
CREAT TABLE (SHORTCUT) SHORTCUT TO CREAT A TABLE : CTRL+L
FILTER,SORT,SORT BY COLOR
DESIGN TAB
TABLE STYLE

DATE RELEGION SALES REP DEPARTMENT

2/5/2022 SOUTH ANAND ACCOUNT
2/6/2021 WEST VINOD SALES
2/5/2025 NORTH KARTHIK ACCOUNT
2/25/2022 EAST KEERTHI SALES
2/26/2020 WEST RAAM MARKETING
2/28/2023 EAST RADHA ACCOUNT
2/1/2020 WEST RAVI SALES
2/13/2024 NORTH KRISHNA MARKETING
Total
ADVANCE TABLE
RENAME TABLE
RESIZE TABLE
SLICER FOR TABLE
REMOVE DUPLICATES
CONVERT TO RANGE

DATE
2/5/2022 RELEGION SALES REP DEPARTMENT COGS
2/6/2021 SOUTH ANAND ACCOUNT 5000
2/5/2025 WEST VINOD SALES 200
2/25/2022 NORTH KARTHIK ACCOUNT 2000
2/26/2020 EAST KEERTHI SALES 3000
2/28/2023 WEST RAAM MARKETING 5000
2/1/2020 EAST RADHA ACCOUNT 6520
2/13/2024 WEST RAVI SALES 8000
NORTH KRISHNA MARKETING 350
BLE : CTRL+T
BLE : CTRL+L

COGS
5000
200
2000
3000
5000
6520
8000
350
30070
DATA VALIDATIONS

DATE PRODUCT REGION NAME UNITS SALES COGS

1/18/2023 LAPTOP EAST JHON 45 ₹ 200.00 ₹ 100.00
1/19/2023 MOBILE SOUTH REETA 65 ₹ 300.00 ₹ 150.00
1/20/2023 CHAIR NORTH MANU 32 ₹ 600.00 ₹ 120.00
1/21/2023 MARKER WEST LATHA 20 ₹ 145.00 ₹ 130.00
1/22/2023 SPEAKER SOUTH JHON 30 ₹ 450.00 ₹ 150.00
1/23/2023 PROJECTOR EAST REETA 54 ₹ 632.00 ₹ 160.00
1/24/2023 CAMERA SOUTH MANU 62 ₹ 354.00 ₹ 456.00
1/25/2023 BOARD WEST LATHA 98 ₹ 586.00 ₹ 256.00
1/26/2023 PENDRIVE SOUTH KAVYA 78 ₹ 965.00 ₹ 147.00
1/27/2023 SWITCH NORTH HEMA 54 ₹ 654.00 ₹ 258.00
1/28/2023 LIGHTS EAST JHON 23 ₹ 987.00 ₹ 369.00
1/29/2023 MARKER SOUTH REETA 12 ₹ 563.00 ₹ 963.00
1/30/2023 TABLE WEST MANU 22 ₹ 214.00 ₹ 147.00
1/31/2023 LAPTOP NORTH LATHA 30 ₹ 523.00 ₹ 258.00
2/1/2023 MOBILE EAST KAVYA 45 ₹ 356.00 ₹ 852.00
2/2/2023 CHAIR EAST MANU 4 ₹ 123.00 ₹ 369.00
2/3/2023 SPEAKER SOUTH LATHA 62 ₹ 654.00 ₹ 745.00
2/4/2023 BOARD NORTH KAVYA 63 ₹ 987.00 ₹ 123.00
2/5/2023 PENDRIVE SOUTH HEMA 52 ₹ 45.00 ₹ 654.00
2/6/2023 CAMERA WEST JHON 51 ₹ 444.00 ₹ 987.00
2/7/2023 MARKER EAST MANU 78 ₹ 553.00 ₹ 14.00
2/8/2023 LIGHTS SOUTH LATHA 96 ₹ 365.00 ₹ 741.00
2/9/2023 LAPTOP EAST JHON 78 ₹ 125.00 ₹ 25.00
2/10/2023 BAGS WEST REETA 14 ₹ 354.00 ₹ 369.00
2/11/2023 TABLES NORTH MANU 25 ₹ 369.00 ₹ 258.00
2/12/2023 SWITCH EAST LATHA 36 ₹ 875.00 ₹ 369.00
2/13/2023 MOBILE EAST KAVYA 58 ₹ 245.00 ₹ 359.00
2/14/2023 LIGHTS SOUTH HEMA 69 ₹ 145.00 ₹ 486.00
2/15/2023 BOARD EAST JHON 47 ₹ 325.00 ₹ 146.00
2/16/2023 PENDRIVE NORTH REETA 12 ₹ 652.00 ₹ 624.00
2/17/2023 BOARD EAST MANU 32 ₹ 379.00 ₹ 351.00
2/18/2023 PROJECTOR WEST LATHA 21 ₹ 167.00 ₹ 759.00
2/19/2023 BOARD EAST KAVYA 31 ₹ 742.00 ₹ 957.00
2/20/2023 CHAIR WEST MANU 23 ₹ 359.00 ₹ 864.00
2/21/2023 TABLE SOUTH LATHA 35 ₹ 751.00 ₹ 146.00
REGION EAST WEST NORTH SOUTH
EAST LAPTOP MARKER CHAIR MOBILE
NORTH PROJECTOR BOARD SWITCH SPEAKER
SOUTH LIGHTS TABLE LAPTOP CAMERA
WEST MOBILE CAMERA BOARD PENDRIVE
CHAIR BAGS TABLES MARKER
MARKER PROJECTOR PENDRIVE SPEAKER
LAPTOP CHAIR PENDRIVE
SWITCH LIGHTS
MOBILE LIGHTS
BOARD TABLE
BOARD
BOARD

SL NO REGION PRODUCT
1 EAST
 DATE 1/1/2021 1/2/2021 1/3/2021 1/4/2021 1/5/2021
[Link] NAMES DEPARTMENT DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
1 KEERTHI ACCOUNTANT p A P P P
2 NAVEEN SALES P A P P P
3 PREMA MARKETING P P P P P
4 DIVYA BPO P P P P P
5 HEMA SALES P P A P P
6 PREETHI ACCOUNTANT P P P P A
7 GAGAN ACCOUNTANT A P P A A
8 BINDU SALES A P P A P
9 RASHMI MARKETING P P P P P
10 KIRAN MARKETING P P A P P
 ATTENDANCE LIST
1/6/2021 1/7/2021 1/8/2021 1/9/2021 1/10/2021 1/11/2021 1/12/2021 1/13/2021
SATURDAY SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
A P P P A P P
P P P P P P P
P P P P P L P
S
P U P A P P C P
P N P P P P P P
P D A A P P P A
A
P Y A P P P P P
P P P P P P P
P P P P P P P
P P A P P C C
1/14/2021 1/15/2021 1/16/2021 1/17/2021 1/18/2021 1/19/2021 1/20/2021 1/21/2021
SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY
A H P P P P
L C A A P P
P P P P A P
S P P H P A P S
U P P P P P P U
N N
D P P P C L P D
A P P P L P P A
Y H P P P P P Y
P P P P P P
P P P H P P
1/22/2021 1/23/2021 1/24/2021 1/25/2021 1/26/2021 1/27/2021 1/28/2021
MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY
P P P P P P
L L C P P P
P P P H P P
P P P P P P S
P P P P P P U
N
P H P P P H D
P P P P P P A
P P P P P P Y
P P P L P P
P P P P P P
1/29/2021 1/30/2021 P A
MONDAY TUESDAY [Link] TOTAL PRESENT [Link] TOTAL ABSENT
P 20 4
P P 18 3
P P 23 1
C P 21 2
L P 24 1
P P 18 4
P H 20 4
P C 22 2
P P 25 0
A P 20 3
 H C L
[Link] TOTAL HOLIDAYS NO. OF TOTAL COMP OFF [Link] TOTAL LEAVES TOTAL NO. OF PRESENT
1 0 0 20
0 2 3 20
1 0 1 23
1 2 0 23
0 0 1 24
2 1 1 19
1 0 1 20
1 1 0 23
0 0 1 25
1 2 0 22
INSENTIVE
inactive
active
inactive
inactive
inactive
inactive
inactive
inactive
active
inactive
FIRST NAME LAST NAME CONCATENATE UPPER LOWER PROPER
RaHual dravid
ram kapoor
vinay kumar
dhiraj sharma
abdul kalam
kalpana chawla
taj mahal
akshya kumar
aishwarya rai

COMPUTER BALC
LEFT
RIGHT
MID
CANCATENATE

TEXT TO COLUMNS
RAHULDRAVID
RAMKAPOOR
VINAYKUMAR
DAESHANGOWDA
AKSHYAKUMAR
PRABHUDVE

SAPERATOR IS @
EMAILS ID
RAHULDRAVID
RAMKAPOOR
VINAYKUMAR
DAESHANGOWDA
AKSHYAKUMAR
PRABHUDVE
LEN TRIM
Sum of UNITS REGION
NAME EAST NORTH SOUTH WEST Total Result
HEMA 54 121 175
1800
JHON 193 30 51 274
KAVYA 134 63 78
1600 275
LATHA 36 30 193 139 398
1400
MANU 114 57 62 45 278
REETA 54 12 1200
77 14 157
Total Result 531 216 561
1000 249 1557

800

600

400

200
193 134 114
0 36
HEMA JHON KAVYA LATHA MANU
 WEST
SOUTH
NORTH
REGION EAST

531

36 114 54
LATHA MANU REETA Total Result
PIVOTE TABLE

DATE PRODUCT REGION NAME UNITS SALES

1/18/2023 LAPTOP EAST JHON 45 ₹ 200.00
1/19/2023 MOBILE SOUTH REETA 65 ₹ 300.00
1/20/2023 CHAIR NORTH MANU 32 ₹ 600.00
1/21/2023 MARKER WEST LATHA 20 ₹ 145.00
1/22/2023 SPEAKER SOUTH JHON 30 ₹ 450.00
1/23/2023 PROJECTOR EAST REETA 54 ₹ 632.00
1/24/2023 CAMERA SOUTH MANU 62 ₹ 354.00
1/25/2023 BOARD WEST LATHA 98 ₹ 586.00
1/26/2023 PENDRIVE SOUTH KAVYA 78 ₹ 965.00
1/27/2023 SWITCH NORTH HEMA 54 ₹ 654.00
1/28/2023 LIGHTS EAST JHON 23 ₹ 987.00
1/29/2023 MARKER SOUTH REETA 12 ₹ 563.00
1/30/2023 TABLE WEST MANU 22 ₹ 214.00
1/31/2023 LAPTOP NORTH LATHA 30 ₹ 523.00
2/1/2023 MOBILE EAST KAVYA 45 ₹ 356.00
2/2/2023 CHAIR EAST MANU 4 ₹ 123.00
2/3/2023 SPEAKER SOUTH LATHA 62 ₹ 654.00
2/4/2023 BOARD NORTH KAVYA 63 ₹ 987.00
2/5/2023 PENDRIVE SOUTH HEMA 52 ₹ 45.00
2/6/2023 CAMERA WEST JHON 51 ₹ 444.00
2/7/2023 MARKER EAST MANU 78 ₹ 553.00
2/8/2023 LIGHTS SOUTH LATHA 96 ₹ 365.00
2/9/2023 LAPTOP EAST JHON 78 ₹ 125.00
2/10/2023 BAGS WEST REETA 14 ₹ 354.00
2/11/2023 TABLES NORTH MANU 25 ₹ 369.00
2/12/2023 SWITCH EAST LATHA 36 ₹ 875.00
2/13/2023 MOBILE EAST KAVYA 58 ₹ 245.00
2/14/2023 LIGHTS SOUTH HEMA 69 ₹ 145.00
2/15/2023 BOARD EAST JHON 47 ₹ 325.00
2/16/2023 PENDRIVE NORTH REETA 12 ₹ 652.00
2/17/2023 BOARD EAST MANU 32 ₹ 379.00
2/18/2023 PROJECTOR WEST LATHA 21 ₹ 167.00
2/19/2023 BOARD EAST KAVYA 31 ₹ 742.00
2/20/2023 CHAIR WEST MANU 23 ₹ 359.00
2/21/2023 TABLE SOUTH LATHA 35 ₹ 751.00
COST OF GOODS SOLD
COGS
₹ 100.00
₹ 150.00
₹ 120.00
₹ 130.00
₹ 150.00
₹ 160.00
₹ 456.00
₹ 256.00
₹ 147.00
₹ 258.00
₹ 369.00
₹ 963.00
₹ 147.00
₹ 258.00
₹ 852.00
₹ 369.00
₹ 745.00
₹ 123.00
₹ 654.00
₹ 987.00
₹ 14.00
₹ 741.00
₹ 25.00
₹ 369.00
₹ 258.00
₹ 369.00
₹ 359.00
₹ 486.00
₹ 146.00
₹ 624.00
₹ 351.00
₹ 759.00
₹ 957.00
₹ 864.00
₹ 146.00
SHORT CUT KEY

KEY CTRL
A SELECT ALL
B BOLD
C COPY
D FILL DOWN
E FLASH FILL
F FIND
G GOTO
H REPLACE
I ITALIC
J
K INSERT HYPERLINK
L INSERT TABLE
M
N NEW WORKBOOK
O OPEN WORKBOOK
P PRINT
Q
R FILLRIGHT
S SAVE
T INSERT TABLE
U UNDERLINE
V PASTE
W CLOSE WORKBOOK
X CUT
Y REDO
Z UNDO
Top product NAMES price
camera RAMYA 30000
camera RAMYA 30000
watches GEETHA 5000
cosmetics PAVAN 900
shoes MANI 1500
laptop LATHA 50000
perfums RAAM 350
clothes RAVI 2000
Top product NAMES
Mobile RADHA 100-BCII-0 20 25000
camera RAMYA 100-BVIO-0 21 30000
watches GEETHA
cosmetics PAVAN
shoes MANI
laptop LATHA
perfums RAAM
clothes RAVI
Top product product code quantity price
Mobile 100-BCII-009 20 25000
camera 100-BVIO-088 21 30000
watches 100-BNIO-077 34 5000
cosmetics 100-BHIK-066 5 900
shoes 100-BUIP-055 6 1500
laptop 100-BUUT-004 7 50000
perfums 100-BHKL-003 98 350
clothes 100-BHEF-002 76 2000