EXCEL ASSIGNMENT
PG202101188 ASSIGNMENT SHEETS
Sheet1
Sheet2
CHILAKA NAMRATA Sheet3
PROTECT
MBA- C (1st YEAR)
SIGNMENT
BASIC OPERATORS (+ - / * ^) SUM() ,MAX(),MIN(),AVERAGE()
RESULT NAME MARKS SUM() MAX()
ADD(+) 92 53 145 RAM 84 346 95
SUB(-) 92 53 39 ABHI 95
DIV(/) 92 53 1.7358490566 INDHU 63
MUL(*) 92 53 4876 PRIYA 48
^ 92 53 1.204356E+104 ARJUN 56
PASTE SPECIAL ( VALUE, TRANSPOSE)
DATA PASTE SPECIAL (VALUES) PASTE SPECIAL ( TRANSPOSE)
NAME MARKS NAME MARKS NAME RAM ABHI INDHU PRIYA
RAM 84 RAM 84 MARKS 84 95 63 48
ABHI 95 ABHI 95
INDHU 63 INDHU 63
PRIYA 48 PRIYA 48
ARJUN 56 ARJUN 56
LEFT(), RIGHT(), MID() T(), N(), VALUE()
DATA LEFT() RIGHT() MID() DATA T() N() VALUE()
ABHIRAM ABH RAM BHI ABHIRAM ABHIRAM 0
PIYASREE PIY REE IYA PIYASREE PIYASREE 0
15131540 151 540 513 15131540 15131540 15131540
SIRI SIR IRI IRI SIRI SIRI 0
75623 756 623 562 75623 75623 75623
5123456 512 456 123 5123456 5123456 5123456
SUMIFS(), COUNTIFS() ISTEXT(),ISNUMBER(),ISBLANK(),IS
ISNUMBER
DATA ISTEXT()
SUMIFS() COUNTIFS(
)
()
SNO MONTH ITEM SALES QTY AREA (ITEM) (SALES)
1 MARCH MOBILE ₹ 65.00 AP 358 4 1 1
2 MARCH IPAD ₹ 56.00 TN TRUE TRUE
3 APRIL LAPTOP ₹ 87.00 TS TRUE TRUE
Sum of
4 MARCH MOBILE ₹ 78.00 AP Count of TRUE TRUE
sales of
mobiles in
mobiles in
AP
AP
Sum of
Count of
sales of
mobiles in
5 MAY LAPTOP ₹ 85.00 AP mobiles in TRUE TRUE
AP
AP
6 MARCH MOBILE ₹ 92.00 AP TRUE TRUE
7 MARCH IPAD ₹ 99.00 TN TRUE TRUE
8 APRIL LAPTOP ₹ 105.00 #VALUE! TRUE TRUE
9 MARCH MOBILE ₹ 111.00 TS TRUE TRUE
10 MAY LAPTOP ₹ 117.00 AP TRUE TRUE
11 MARCH MOBILE ₹ 123.00 AP TRUE TRUE
MARCH ₹ 130.00 IPAD #VALUE! FALSE FALSE
APRIL ₹ 136.00 LAPTOP TS FALSE FALSE
VLOOKUP() CUSTOM
SORT (Smallest to
VLOOKUP() DATA
DATA Largest)
ITEM CODE RATE NAME RATE SUBJECT NUMBER SUBJECT
FRIES 12FR3 500 FRIES 500 AFM 5 QT
CHIPS HFC13 45 CHIPS 45 MCS 2 MCS
DRINKS 83DRK 200 83DRK 200 QT 1 BL
BISCUITS 9BI39 30 9BI39 30 MPOB 7 ME
CHOCO CH398 100 CH398 100 BL 3 AFM
BREAD 52BRD 20 BREAD 20 BE 6 BE
ME 4 MPOB
FILTER AND SUBTOTAL
PRODUCT QTY AMOUNT ADVANCED FILTER
BANANA 50 2500 ID NAME AGE DEPT SALE
MANGO 50 2000 100 OM 22 CLOTHES 2300
ORANGE 20 1600 101 SRI 31 SHOES 1500
GRAPES 15 1500 102 VANSH 25 FOOD 2500
SUBTOTAL 135 7600 103 KABIR 24 FOOD 2000
104 VANSH 20 CLOTHES 3000
105 KABIR 22 SHOES 2400
FIND AND REPLACE
NOTE: BEN10 WAS ENTERED IN PLACE OF BEN-10 IN 2ND TABLE. BEN10 IS USED FOR 'FIND' AND 'REPLACE' WITH '[Link]'
INITIAL DATA FINAL DATA (FIND AND REPLACE)
SNO CARTOON DAY TIMING DURATION SNO CARTOON DAY TIMING
1 SHINCHAN MON 8:00 AM 1 HR 1 SHINCHAN MON 8:00 AM
2 SHINCHAN TUES 8:00 AM 1 HR 2 SHINCHAN TUES 8:00 AM
3 OSWALD WED 9:00 AM 1 HR 3 OSWALD WED 9:00 AM
4 BEN-10 THR 9:30 AM 1 HR 4 [Link] THR 9:30 AM
5 OSWALD FRI 10:00 AM 1 HR 5 OSWALD FRI 10:00 AM
6 MICKEY SAT 9:00 AM 1 HR 6 MICKEY SAT 9:00 AM
7 BEN-10 SUN 10:00 AM 1 HR 7 [Link] SUN 10:00 AM
IN(),AVERAGE() AUTO FILL OPTIONS
FILL WITHOUT
MIN() AVG() DATA FILL SERIES COPY CELLS FILL FORMATTING
ONLY FORMATTING
48 69.2 1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 1 4
5 2 5
6 3 6
7 1 7
SE) 8 2 8
ARJUN 9 3 9
56 10 1 10
REMOVE DUPLICATE VALUES
TABLE EXPAND THE SELECTION(ROLL) CONTINUE WITH CURRENT SELECTIO
SNO ROLL SUBJECT SNO ROLL SUBJECT SNO
1 188 EXCEL 1 188 EXCEL 1
2 198 EXCEL 2 198 EXCEL 2
3 147 EXCEL 3 147 EXCEL 3
4 188 EXCEL 6 178 EXCEL 4
5 198 EXCEL 5
6 178 EXCEL 6
IF(), NESTED IF STATEMENT, AND(), OR()
DATA IF() NESTEDIF()
ROLL CLASS SUBJECT MARKS RESULT GRADE
NUMBER(),ISBLANK(),ISERROR() 188 MBA EXCEL 40 PASS C
ISBLANK() ISERROR() 198 MBA EXCEL 52 PASS B
(SNO) (AREA) 147 MBA AFM 26 FAIL F
0 0 188 BBM AFM 99 PASS A+
FALSE FALSE 198 BBM EXCEL 18 FAIL F
FALSE FALSE 178 MBA EXCEL 45 PASS C
FALSE FALSE
FALSE FALSE ABSOLUTE AND RELATIVE REFERENCING($)
RELATIVE ABSOLUTE
FALSE FALSE ITEMS COST QTY
REFERENCE REFERENCE
FALSE FALSE CHIPS ₹ 10.00 5 ₹ 50.00 ₹ 300.00
FALSE TRUE CAKE ₹ 15.00 3 ₹ 45.00 ₹ 450.00
FALSE FALSE DRINKS ₹ 12.00 4 ₹ 48.00 ₹ 360.00
FALSE FALSE COOKIE ₹ 30.00 9 ₹ 270.00 ₹ 900.00
FALSE FALSE COFFEE ₹ 40.00 5 ₹ 200.00 ₹ 1,200.00
TRUE TRUE TEA ₹ 40.00 6 ₹ 240.00 ₹ 1,200.00
TRUE FALSE
CUSTOM SORT DATA VALIDATION(LIST)
SORT (Smallest to
SORT (A to Z) SORT (by Color) SUBJECTS LIST
Largest)
NUMBER SUBJECT NUMBER SUBJECT NUMBER SUBJECT 1 MCS
1 AFM 5 AFM 5 SUBJECT 2
2 BE 6 MPOB 7 SUBJECT 3 QT
3 BL 3 QT 1
4 MCS 2 BE 6
5 ME 4 ME 4
6 MPOB 7 MCS 2
7 QT 1 BL 3
ADVANCED FILTER
SEARCH DATA
ID NAME AGE DEPT SALE
VANSH
ADVANCED FILTER RESULT
ID NAME AGE DEPT SALE
102 VANSH 25 FOOD 2500
104 VANSH 20 CLOTHES 3000
SEARCH() AND FIND()
CE' WITH '[Link]' SEARCH() FIND()
D REPLACE) SHINCHAN OSWALD
DURATION C 5 S 2
1 HR c 5 s #VALUE!
SEARCH() - not case FIND() - case
1 HR
sensitive sensitive
1 HR
1 HR
1 HR
1 HR
1 HR
TEXT TO COLUMN CONCATENATE()
INTIAL DATA TEXT TO COLUMN NO SPACE WITH SPACE
NAME NAME NAME NAME
HARRY HARRYPOTTE
HARRY POTTER HARRY POTTER
POTTER R
TONY STARK TONY STARK TONYSTARK TONY STARK
BEN TEN BEN TEN BENTEN BEN TEN
KEVIN ELEVEN KEVIN ELEVEN KEVINELEVEN KEVIN ELEVEN
ROUND(), MROUND()
DATA ROUND() MROUND()
86.5 87 85
78.49 78 80
54.27 54 55
23.87 24 25
NTINUE WITH CURRENT SELECTION(ROLL) 15.75 16 15
ROLL SUBJECT 65.43 65 65
188 EXCEL
198 EXCEL DATE(), DAY(), MONTH(), YEAR() DATA FORMATS-TEXT,NUMBER,DATE
147 EXCEL DATE() 7/18/2021 TEXT NUMBER DATE
178 EXCEL DAY() 18 1324 56.235423 7/19/2021
EXCEL MONTH() 7 1324 56.24 ###
EXCEL YEAR() 2021
EDATE(), EOMONTH() HYPERLINKING
(), OR() EDATE() 10/18/2021 PICTURE WORD EXCEL
AND() OR() EOMONTH() 10/31/2021 IMAGE WORD EXCEL
MBA AND MBA OR
EXCEL EXCEL
MY SUB MY SUB TRIM(),TEXT(), LEN()
MY SUB MY SUB TRIM() TEXT() LEN()
NOT MY SUB MY SUB DATA TRIM() DATA TEXT() DATA
NOT MY SUB NOT MY SUB CODE C CODE C 0.45 45% CODE C
NOT MY SUB MY SUB CLASS A CLASS A 6:00 PM 18:00 CLASS
MY SUB MY SUB MY BOOK MY BOOK 7/19/2021 Mon, July 19 EXCEL
CING($)
KEYBOARD SHORTCUTS
30
SELECT ENTIRE ROW SHIFT+SPACE
SELECT ENTIRE COLUMN CTRL+SPACE
INSERT ROW/COLUMN CTRL+SHIFT+'+'
DELETE ROW/COLUMN CTRL+'-'
OPENS HOME TAB IN RIBBON ALT+H
LEN()
LEN()
7
5
5
CUTS
SHIFT+SPACE
CTRL+SPACE
CTRL+SHIFT+'+'
CTRL+'-'
ALT+H
1. FREEZE & UNFREEZE PANES 2. PAGE SETUP AND PRINT FORMATTING
3. HIDE AND UNHIDE COLUMN AND ROW 5. PIVOT TABLE
HIDE COLUMN DATA DATA
ITEM RATE ITEM CODE RATE SNO MONTH ITEM SALES QTY
FRIES 500 FRIES 12FR3 500 1 MARCH MOBILE ₹ 65.00
CHIPS 45 CHIPS HFC13 45 2 MARCH IPAD ₹ 56.00
DRINKS 200 DRINKS 83DRK 200 3 APRIL LAPTOP ₹ 87.00
BISCUITS 30 BISCUITS 9BI39 30 4 MARCH MOBILE ₹ 78.00
CHOCO 100 CHOCO CH398 100 5 MAY LAPTOP ₹ 85.00
BREAD 20 BREAD 52BRD 20 6 MARCH MOBILE ₹ 92.00
HIDE ROW 7 MARCH IPAD ₹ 99.00
NOTE: B column and ITEM CODE RATE 8 APRIL LAPTOP ₹ 105.00
16th row is HIDE. To FRIES 12FR3 500 9 MARCH MOBILE ₹ 111.00
UNHIDE- (column:
select A&C Column) or CHIPS HFC13 45 10 MAY LAPTOP ₹ 117.00
(row: select 15th & BISCUITS 9BI39 30 NOTE: PIVOT TABLE IN SHEET3
17th row) right click
and unhide CHOCO CH398 100
BREAD 52BRD 20
4. GROUPING
GROUPING(ROWS &
COULUMNS)
ITEM RATE
FRIES 500
CHIPS 45
BISCUITS 30
CHOCO 100
BREAD 20
ORMATTING
6. CONDITIONAL FORMATTING
BLE DATA (>=40)
FORMULA
SNO ROLL CLASS SUBJECT MARKS PERCENT
based CF
AREA 1 188 MBA EXCEL 40 40% TRUE
AP 2 198 MBA EXCEL 52 52% TRUE
TN 3 147 MBA AFM 26 26% FALSE
TS 4 188 BBM AFM 99 99% TRUE
AP 5 198 BBM EXCEL 18 18% FALSE
AP 6 178 MBA EXCEL 45 45% TRUE
AP 7 156 MBA QT 95 95% TRUE
TN 8 198 BBM EXCEL 90 90% TRUE
TS 9 178 MBA AFM 85 85% TRUE
TS 10 155 MBA QT 15 15% FALSE
AP 11 185 BBM AFM 42 42% TRUE
IN SHEET3 13 175 MBA QT 76 76% TRUE
14 153 BBM QT 84 84% TRUE
15 154 BBM EXCEL 13 13% FALSE
16 153 MBA EXCEL 29 29% FALSE
17 132 MBA QT 34 34% FALSE
18 123 MBA EXCEL 56 56% TRUE
19 145 BBM EXCEL 63 63% TRUE
20 143 MBA QT 54 54% TRUE
21 183 BBM AFM 74 74% TRUE
23 124 MBA AFM 63 63% TRUE
3 147 MBA AFM 26 26% FALSE
4 188 BBM AFM 99 99% TRUE
5 198 BBM EXCEL 18 18% FALSE
6 178 MBA EXCEL 45 45% TRUE
7 156 MBA QT 95 95% TRUE
8 198 BBM EXCEL 90 90% TRUE
9 178 MBA AFM 85 85% TRUE
10 155 MBA QT 15 15% FALSE
11 185 BBM AFM 42 42% TRUE
12 134 BBM AFM 32 32% FALSE
13 175 MBA QT 76 76% TRUE
14 153 BBM QT 84 84% TRUE
15 154 BBM EXCEL 13 13% FALSE
16 153 MBA EXCEL 29 29% FALSE
17 132 MBA QT 34 34% FALSE
18 123 MBA EXCEL 56 56% TRUE
19 145 BBM EXCEL 63 63% TRUE
ORMATTING
20 143 MBA QT 54 54% TRUE
3 147 MBA AFM 26 26% FALSE
4 188 BBM AFM 99 99% TRUE
5 198 BBM EXCEL 18 18% FALSE
6 178 MBA EXCEL 45 45% TRUE
7 156 MBA QT 95 95% TRUE
8 198 BBM EXCEL 90 90% TRUE
9 178 MBA AFM 85 85% TRUE
10 155 MBA QT 15 15% FALSE
11 185 BBM AFM 42 42% TRUE
12 134 BBM AFM 32 32% FALSE
13 175 MBA QT 76 76% TRUE
14 153 BBM QT 84 84% TRUE
15 154 BBM EXCEL 13 13% FALSE
16 153 MBA EXCEL 29 2% FALSE
17 132 MBA QT 34 34% FALSE
18 123 MBA EXCEL 56 56% TRUE
19 145 BBM EXCEL 63 63% TRUE
20 143 MBA QT 54 54% TRUE
DUPLICATE VALUES
(ROLL) (COLORED)
DATA BARS (MARKS)
ICON SETS(PERCENT)
COLOR SCALES
(PERCENT)
1. PIVOT TABLE 2. ACTIVATE - DEACTIVATE
AREA - all -
GRIDLINES
ITEM MONTH Sum of SALES QTY
IPAD ###
MARCH ###
LAPTOP ###
APRIL ###
MAY ###
MOBILE ###
MARCH ###
Total Result ###
SECURITY AND FILE PROTECTION
SNO CARTOON DAY TIMING DURATION
1 SHINCHAN MON 8:00 AM 1 HR
PASSWORD: 123
2 SHINCHAN TUES 8:00 AM 1 HR
3 OSWALD WED 9:00 AM 1 HR
4 [Link] THR 9:30 AM 1 HR
5 OSWALD FRI 10:00 AM 1 HR
6 MICKEY SAT 9:00 AM 1 HR
7 [Link] SUN 10:00 AM 1 HR