A.E Module III Study Material PDF

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY A.E MODULE-III NAT...

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY A.E MODULE-III NATIONAL TASK FORCE IT & SD. Registration Number: IV – 1903 – 04384 - 2017 REGD UNDER MINISTRY OF MSME, GOVT OF INDIA Registration Number: UDYAM-WB-10-0043486 Quality Management System ISO 9001:2015 Certificate No: QMS/230620/1271 STUDENT NAME: ____________________ REG. NUMBER_______________________ BATCH NUMBER_____________________ COURSE NAME______________________ ISSUED DATE_______________________ GIVER SIGNATURE__________________  Contents 01. HORIZANTAL SORT 02. PMT- FORMULA & WHAT IF ANALYSIS 03. GOAL SEEK 04. PMT, IPMT, PPMT 05. DATE FUNCTION 06. PV FV FUNCTION 07. TRANSFER DATA FROM MASTER SHEET TO MULTIPLE SHEET 08. INTERMEDIATE POINT 1 ABSOLUTE FUNCTION CURRENCY SUM IF & SUM IFS COUNT, COUNT IF & COUNT IFS AVERGEA, AVERAGEIF & AVERAGE IFS NESTED IF INDEX + MATCH CHART ELEMENTS TREEMAP CHART PAI CHART COLUMN CHART COMBO CHART RICHER DATA LABLES 09. INTERMEDIATE POINT 2 EASY SEARCH HIDE ZERO DELETE HIDDEN ROWS & COLUMNS MAGICAL SUM QUICK ANALYSIS AUTOMATE SERIAL NUMBER 10. INTERMEDIATE POINT 3 TEXT FUNCTION REMOVE SURNAME FORM STYLE DATA ENTRY DATA COPY PROCESS AGE CALCULATION DATA PROCESS 1. HORIZONTAL SORT – RE ARRANGE COLUMNS IN EXCEL  SUPPOSE WE HAVE A DATA IN THE EXCEL SHEET AND IT IS SORTED WITH COLUMN HEADINGS LIKE OURS.  WE WILL SEND THIS DATA TO ANOTHER ORGANIZATION BUT THEY HAVE THIS SAME DATA BUT THE COLUMN ARRANGED DIFFERENTLY. WE NOW HAVE TO SORT OUR DATA LIKE THEM.  THE COLUMN IN THE EXCEL DATA THAT WE NEED TO PUT BEFORE OR BEHIEND THEM SHOULD BE NUMBERED AS CELLS IN THE HEAD.  SELECT THE ENTIRE EXCEL DATA RANHGE INCLUDING MENTION THE NUMBER ROW  THEN CLICK ON THE DATA MENU > UNDER THE SORT & FILTER GROUP > CLICK ON THE SORT OPTION > SORT OPTION DIALOG BOX APPEAR ON THE SCREEN > THEN CLICK ON THE OPTION UNDER THE SORT DIALOG BOX > SORT OPTION DIALOG BOX APPEAR > IN THE SORT OPTIONS DIALOG BOX – IN THE ORIENTATION GROUP – THERE ARE TWO OPTIONS – ONE IS SORT TOP TO BOTTOM AND THE OTHER SORT LEFT TO RIGHT.  SINCE WE WILL DO LEFT TO RIGHT SORT, WE WILL CHOOSE THE SECOND OPTION. THEN PRESS OK  THEN CLICK ON “SORT BY” – DROP DOWN LIST APPEAR – SELECT THE RIGHT ROW NUMBER – UNDER THE ORDER SECTION – SELECT SMALLEST TO LARGEST – THEN PRESS OK BUTTON. 2. PMT FORMULA – WHAT IF ANALYSIS DATA THROUGH PMT FUNCTION WE WILL LEARN HOW TO DO EMI CALCULATION… LONE AMT. RS. 500,000.00 INTEREST % P. A 13.00% DURATION (YRS) 2 PAYMENT (BEGN,1/END 0) 1 EMI (Rs.) – SOLUTION THE FORMULA TO BE USED TO CALCULATE EMI IS PMT FORMULA - PMT FORMULA IS MENTIONED BELOW. =PMT (RATE OF INTEREST/12, NET PAYMENT PER YEAR*12, PAYMENT VALUE) PRESS ENTER WHAT IF ANALYSIS – DATA MENU >FORECAST GROUP > WHAT IF ANALYSIS > DROP DOWN LIST > DATA TABLE > DATA TABLE DIALOG BOX OPEN 3. GOAL SEEK – BACK CALCULATE HERE WE WILL LEARN GOLE SEEK ANALYSIS ON A DATABASE.  PRICE 12 QTY 5000 A SALES 60000 FIXED COST 12000 VARIABLE COST AS % OF B SALES 65% TOTAL COST 51000 A-B If you think you have to reach 10000 profits then this is your goal. So how much money do I have to increase the price so that we can PROFIT 9000 reach a profit of 10,000 rupees. So now we have to do a BACK calculation so that if the price is from 12 then the profit reaches 10,000 rupees. For that we have to use GOAL SEEK, which means our goal is fixed 10,000 rupees. Now we just have to get there, that is, we have to SEEK. DATA MENU > FORECAST GROUP > WHAT IF ANALYSIS > GOEL SEEK > GOEL SEEK DIALOG BOX APPEAR 04. PMT, IPMT, PPMT – FORMULA TOTAL AMOUNT = MONTHLY EMI * 60 (5*12) WEEKYLY EMI =PMT (RATE OF INTEREST/52, LONE PERIOD *52, PV) PRESS ENTER CONDITION NO: 01 IF I WANT TO KEEP EMI AT 8000 THEN HOW MUCH DOWN PAYMENT SHOULD I MAKE? CONDITION NO: 02 “How much interest and how much principal in first month EMI?" FORMULA - =IPMT (RATE OF INTEREST /12,1, LOAN PERIOD * 12, PV) PRESS ENTER CONDITION NO: 03 - How much interest paid per month for 12 months LONE period FORMULA - =IPMT (RATE OF INTEREST /12, SELECT THE CELL OF MONTH COLUMN, LOAN PERIOD * 12, PV) PRESS ENTER IF WE NEED TO FIND THE PRINCIPAL AMOUNT, WE USE THE FOLLOWING FORMULA =PPMT ((RATE OF INTEREST /12,1, LOAN PERIOD * 12, PV) PRESS ENTER 05. EXCEL ALL DATE FORMULA AND FUNCTION WORKDAY FORMULA  FIRST OF ALL, WE WILL LEARN THE WORKDAYS FORMULA. WORKDAYS FORMULA DOES NOT COUNT SATURDAY AND SUNDAY.  ACCORDING TO WORKDAYS FORMULA THE GIVEN DATA - WE HAVE TO CHECK THE TASK AND DATE.  =WORKDAY (START DATE, DAYS, [HOLIDAYS]) WORKDAY.INTL FORMULA  THE FEATURE OF WORKDAY.INTL FORMULA IS THAT HERE WE CAN SELECT WEEKOFF AS WE LIKE.  =WORKDAY.INTL(START DATE, DAYS, WEEKEND, HOLIDAYS) NETWORKDAYS FORMULA  THE NETWORKDAYS FORMULA SHOWS OUR TOTAL WORKING DAYS.  NETWORKDAYS FORMULA DOES NOT COUNT SATURDAY AND SUNDAY.  =NETWORKDAYS (START DATE, END DATE, HOLIDAYS) NETWORKDAYS.INTL FORMULA  THE NETWORKDAYS.INTL FORMULA SHOWS OUR TOTAL WORKING DAYS.  THE FEATURE OF NETWORKDAYS.INT FORMULA IS THAT HARE WE CAN SELECT WEEK OFF AS WE LIKE.  =NETWORKDAYS.INTL(START DATE, END DATE, WEEKEND, HOLIDAYS) EDATE FORMULA  EDATE FORMULA IS AN IMPORTANT FORMULA IN EXCEL. WE HAVE DISCUSSED HERE ABOUT 3 MONTHS AFTER DATE, BUT THE FORMULA WILL GIVE THE RESULT OF THE NUMBER OF DAYS WE PUT AS DAYS WITHOUT 3 DAYS.  IN EDATE FORMULA IF WE NEED TO KNOW THE BEFORE DATE THEN WE HAVE TO GIVE MINUS SIGN BEFORE THE NUMBER.  FORMULA: =EDATE (START_DATE, MONTH) EOMONTH FORMULA  EOMONTH FORMULA IS VERY IMPORTANT FORMULA IN EXCEL.  BY EOMONTH FORMULA WE CAN KNOW THE LAST DATE OF ANY MONTH.  A DATE IS SEPCIFIED IN THE DATA, IF WE WANT TO KNOW THE LAST DATE OF THE MONTH THEN THE FORMULA WILL BE =EOMONTH (START_DATE, MONTH0)  AND IF WE WANT TO KNOW THE LAST DATE OF NEXT MONTH THEN THE FORMULA WILL BE =EOMONTH (START_DATE, MONTH1)  AND IF YOU WANT TO KNOW THE LAST DATE OF THE PREVIOUS MONTH THEN THE FORMULA WILL BE =EOMONTH (START_DATE, MONTH-1)  AND IF WE WANT TO KNOW THE FIRST DATE OF THAT MONTH THEN THE FORMULA WILL BE =EOMONTH (START_DATE, MONTH-1) +1 06. PV, FV, FORMULA  PV FORMULA: =PV (RATE/12, NPER*12, PMT,0) PRESS ENTER FV FORMULA: =FV (RATE/12, NPER*12, PV,0) 07. TRANSFER DATA FROM MASTER SHEET TO MULTIPLE SHEET  In case of “TRANSFER DATA FROM MASTER SHEET TO MULTIPLE SHEET”, we have to remember to use a serial number or unique number.  We will transfer the data to the multiple seats by holding the Salesman data in the field.  Now we will create a column called Filter next to the Sales column. There = select the first cell of the salesman column and press enter. Then drag it downwards.  Now we will press Ctrl+A to select all the data. Then press Ctrl+T to transfer the data to the table  Insert Menu > Tables Group > Pivot Table > New Worksheet  Right Click on The Pivot Table Area > Pivot Table Option > Pivot Table Option Dialog Box Appear > Click on The Display Tab > Then Click on “Classic Pivot Table Layout”> Then ok.  Then Click on the Design Menu > Under the Layout Group > Click on Subtotal Option > Click on “Do Not Show Subtotal”.  If you don’t want to see the grand total, you can turn the grand total off. Design Menu > Layout Group > Grand Total Option > Off for Row or Column.  Now we will tick the ID option from the pivot table field. But it will appear that the ID value has been stored - because the ID number is in digits. Now drag the ID from the value box and drop it in the raw field  Then click to tick all check box from pivot table field without filter  Now drag and drop the filter check box to the filter box  If we want to check the data by filtering then we can do it by clicking on filter sign next to all option above the data  But here we want the name of the salesman to be transferred to their data in a separate sheet.  Then click on the PivotTable Analyze > Pivot Table Group > Click on the Option > Show Report Filter Page > Show Report Filter Page Dialog Box Open > Then Click on the ok button. 08. INTERMEDIATE POINT 1 ABSOLUTE FUNCTION ABSOLUTE FUNCTION THAT IS THE WORK OF ABS FUNCTION IS – IF ANY NEGATIVE VALUE IS INSIDE THE EXCEL, THEN IT TRANSFERS IT TO POSITIVES VALUE. D E F G H I  NUMBER NUMBER 4 S.NO ABS 1 2 RESULT 5 1 75 59 16 6 2 82 70 12 7 3 82 67 15 8 4 85 60 25 9 5 71 66 5 10 6 87 85 2 11 7 69 77 -8 12 8 68 77 -9 13 9 69 75 -6 14 10 78 65 13 =ABS(H5) ENTER MIXED FUNCTION WE KNOW ABOUT CELL REFERENCE IN EXCEL THAT WHEN WE USE A FORMULA IN A TABLE - THEN WHEN WE USE THAT FORMULA IN ANOTHER TABLE THROUGH COPY PASTE - WE WILL SEE THAT FORMULA GIVES THE CORRECT RESULT ACCORDING TO THE OTHER TABLE VALUE. C D H I J 5 1 5 1 620 6 2 6 2 480 7 3 7 3 712 8 TOTAL 8 TOTAL LET'S SAY WE HAVE APPLIED SUM FORMULA IN THE CASE OF THE FIRST TABLE - WE HAVE COPIED THE RESULT ACCORDING TO THAT FORMULA AND USED IT IN THE CASE OF THE TOTAL OF THE SECOND TABLE - THE RESULT OF THE SECOND TABLE WILL BE CORRECT BECAUSE WE HAVE NOT COPIED THE RESULT OF THE FIRST TABLE, WE HAVE COPIED THE FORMULA BUT IF WE LOCK EACH CELL WHILE DOING THE SUM FORMULA OF THE FIRST TABLE, THEN COPYING THE RESULT WILL NEVER COPY THE FORMULA - ONLY THE RESULT OF THE FIRST TABLE WILL BE COPIED ACCORDING TO THE VALUE OF THE FIRST TABLE. CURRENCY FORMAT  NAME AMOUNT SUBHAM SHAW 1987000987 NEEL RANA 1948864735 SHAYAN DUTTA 1009374932 AMAN BHAGAT 1100354024 PRIYANKA MONDAL 1009386008 MOHIT RAJBANGSHI 1000000000 SAPNA DAS 1997888366 SELECT ALL THE > HOME MENU > NUMBER GROUP > MORE NUMBER FORMAT > FORMAT CELL DIALOG BOX APPEAR > CURRENCY > SHORT CUT KEY – SHIFT + CTRL + 4 SUMIF VS SUMIFS EMP NAME REGION STATE SALES PRIYA DAS NORTH PANJAB 12000 NAYNA MAKAL WEST GOA 10000 SUBHADIP DAS EAST BIHAR 14000 RAJ PAL NORTH PANJAB 1200 SAYAN DEY WEST GUJRAT 33000 BISHAKHA HAZRA NORTH HARYANA 12000 ARNAB MONDAL WEST GOA 7000 SUPARNA EAST JHARKHAND 21000 MONDAL SONALI MONDAL WEST GOA 12000 TISHA DOLUI NORTH HARYANA 16000 =SUMIF (SELECT THE REGION RANGE,” NORTH”, SELECT THE SALES RANGE) N: B – “FOR ONLY ONE CONDITION – WE USE SUMIF FORMULA” EMP NAME REGION STATE SALES PRIYA DAS NORTH PUNJAB 12000  NAYNA WEST GOA 10000 MAKAL SUBHADIP EAST BIHAR 14000 DAS RAJ PAL NORTH PUNJAB 1200 SAYAN DEY WEST GUJRAT 33000 BISHAKHA NORTH HARYANA 12000 HAZRA ARNAB MONDAL WEST GOA 7000 SUPARNA EAST JHARKHAND 21000 MONDAL SONALI MONDAL WEST GOA 12000 TISHA DOLUI NORTH PUNJAB 16000 =SUMIFS (SELECT THE SALES RANGE, SELECT THE REGION RANGE,” NORTH”, SELECT THE STATE RANGE,” PUNJAB”) PRESS ENTER KEY N: B: - “IN CASE OF MANY CONDITIONS – WE USE SUMIF FORMULA” COUNT, COUNTIF, COUNTIFS EMP NAME COLOR SALES PRIYA DAS RED 20 NAYNA MAKAL GREEN 25 SUBHADIP DAS YELLOW 12 RAJ PAL RED 15 SAYAN DEY GREEN 10 BISHAKHA HAZRA ORANGE 25 ARNAB MONDAL GREEN 23 SUPARNA RED 27 MONDAL SONALI MONDAL YELLOW 23 TISHA DOLUI RED 15 COUNT =COUNT (SELECT THE RANGE OF CELL) THEN PRESS ENTRE COUNTIF IF I PUT A FILTERS IN THE COUNT FORMULA – WE WILL USE COUNTIF FORMULA. =COUNTIF (SELECT THE SALES RANGE, “>20) THEN PRESS ENTER  COUNTIFS IF I PUT TWO OR MORE FILTERS IN THE COUNT FORMULA – WE WILL USE COUNTIF FORMULA. =COUNTIFS (SELECT THE SALES RANGE, “>10”, THEN SELECT THE COLOR RANGE, “RED”) AVERAGEA, AVERAGEIF, AVERAGEIFS AVERAGEA MONTH SALES JANUARY 2837 FEBRUARY 1386 MARCH HOLIDAY MONTH APRIL 1161 MAY 1643 JUNE 1380 JULY 2579 AUGUST 2420 SEPTEMBER 2107 OCTOBER 1289 NOVEMBER 1788 DECEMBER 1800 IN THIS CASE WE NEED TO PERFORM AVERAGEA FUNCTION BECAUSE HOLIDAY MONTH IS WRITTEN IN MARCH. THAT IS, IT IS TEXT FUNCTION - THIS TEXT FUNCTION WILL BE USEFUL TO CALCULATE THE CORRECT AVERAGE AS COUNT 0 =AVERAGEA (SELECT THE SALES RANGE) THEN PRESS ENTRE AVERAGEIF SALES REP UNITS SALES ARNAB MONDAL 130 40594 MOUMITA BISWAS 205 42154 ARPITA RANA 480 5460 SRIJONI BHOWMIK 405 37976 ANKIT PASWAN 421 39011 SUBHAM SHAW 54 40840 SOMNATH DAS 379 40202  RISHAV JANA 390 20750 DEBOJIT NASKAR 408 20750 SARMISTHA HAZRA 402 34877 ARNAB MONDAL 207 32450 AMAN KHATICK 348 12540 NEVAAN MALLICK 429 16852 ROHAN ROY 343 6540 DEBJIT KURI 119 41255 SUBHAM D. CRUZE 438 39800 SHARANYA ROY 61 36200 SHRESTHA ROY 383 37585 SUKANYA BHATTACHARYYA 126 54412 KAUSHIKI 325 65127 CHAKRABORTY DEVRAJ SARDAR 412 63220 RIDHI DAS 253 25487 SUPRITAM DAS 268 36500 SRIJIT DAS 360 38560 ANKIT PASWAN SOMNATH DAS SARMISTHA HAZRA NEVAAN MALLICK SUBHAM D. CRUZE KAUSHIKI CHAKRABORTY SUPRITAM DAS SRIJIT DAS =AVERAGEIF (SELECT THE SALES REP PRESS F4 FOR FREEZ, THEN SELECT ANKIT PASWAN FROM TABLE 2, THEN SELECT SALES RANGE PRESS F4 FOR FREEZ) THEN PRESS ENTRE AVERAGEIFS FUNCTION DATE PRODUCT STORE SALES STORE 02-04-2022 JUICE-AQN 39200 2 STORE 01-04-2022 WINE-SJM 16200 1 STORE 04-04-2022 JUICE-RXO 54000 2 STORE 06-04-2022 JUICE-BTF 7500 3 STORE 05-04-2022 WINE-FVT 40600 4 STORE 01-04-2022 SODA-QFF 5700 5 STORE 07-04-2022 BEER-NIM 69000 1 STORE 06-04-2022 FVT-WINE 37000 1 STORE 04-04-2022 JUICE-AQN 6500 1 STORE 04-04-2022 JUICE-RXO 5600 2 STORE 04-04-2022 BEER-GSA 6000 3 STORE 04-04-2022 BEER-ESK 55100 4 STORE 01-04-2022 BEER-ESK 30800 5 STORE 01-04-2022 JUICE-AQN 6600 6 STORE 01-04-2022 JUICE-RXO 84000 1 STORE 01-04-2022 SODA-FAK 16000 2 STORE 14-04-2022 WINE-FVT 13200 2 STORE 12-04-2022 JUICE-BTF 66700 2 STORE 08-04-2022 JUICE-RXO 75000 4 STORE 13-04-2022 JUICE-AQN 13500 5 STORE 04-04-2022 BEER-GSA 16400 1 STORE 13-04-2022 BEER-GSA 15000 2 STORE 12-04-2022 BEER-ESK 18500 1 STORE 14-04-2022 WINE-FVT 16000 4 PRODUCT WINE PRODUCT STORE STORE 1 AVERAGEIFS =AVERAGEIFS (SELECT THE SALES RANGE FREEZ, PRODUCT RANGE FREEZ, SELECT THE PRODUCT CRITERIA FROM TABLE 2, STORE RANGE FREEZ, STORE 1 FROM TABLE 2) THEN PRESS ENTRE NESTED IF NAME MARKS RESULT RIDHI DAS 25 SOHAM RANA 45 ASHWIN THAKUR 65 MUKESH HAZRA 15 SUPRITAM DAS 85 SRIJIT DAS 42 RACHANA ADHIKARY 25 ANIK DAS 85 ABHAS LODH 96 CHIRANTH MANKANI 47 IATIJHYA HAZRA 67 LIPIKA DEWAN 26 SUBHADEEP MANNA 36 SUBHAM MANDAL 63 SOUMOJIT SANTRA 55 SUBHAM D. CRUZE 52 SHARANYA ROY 15  SHRESTHA ROY 82 PAYEL MONDAL 57 SUKANYA BHATTACHARYYA 65 SOUMYADEEP BISWAS 45 KAUSHIKI 35 CHAKRABORTY DEVRAJ SARDAR 53 SUDIPTA MONDAL 65 40 PASS >60 DISTINCTION =IF(F6>60,"DISTINCTION”, IF(F6>40,"PASS","FAIL")) TREEMAP CHART BRANCH PRODUCT AMOUNT PATIALA LCD 23654 BARNALA REFRIGRATOR 78965 AMRITSAR LED 63245 WASHING MACHINE MOGA 74106 PATIALA REFRIGRATOR 86321 AMRITSAR LCD 45987 BARNALA LCD 53698 PATIALA LCD 41503 MOGA LCD 32065 WASHING MACHINE BARNALA 62134 AMRITSAR REFRIGRATOR 69800 MOGA LCD 36540 CLICK ON THE INSERT MENU > UNDER THE CHART GROPU > UNDER INSERT HIERARCHY CHART > TREEEMAP CHART  THEN DOUBLE CLICK ON THE CHART AREA  ON THE RIGHT HAD SIDE – FORMAT DATA SERIES SECTION IS OPEN  UNDER THE SERIES OPTION  UNDER THE LABEL OPTION  CLICK ON BANNER  AFTER BANNERING WE CAN SEE THAT THE SIZES OF THE RECTANGULES ARE DIFFERENT – BECAUSE THE SIZES OF THE RECTANGLES DEPEND ON THE AMOUNT IN THE DATA TABLE.  IF WE WANT TO SEE THE AMOUNTS IN THE RECTANGULAR BOX THEN – DOUBLE CLICK ON THE TEXT ARROW IN THE RECTANGULAR BOX – FORMAT DATA LABELS WILL BE OPEN – UNDER THE LABEL OPTION – VALUE SHOULD BE TICKED.  WE CAN ABBREVIATE OUR TREEMAP AS A TO Z SO IT’S MORE ORGANIZED.  INDEX & MATCH - PERFECT FOR COMPLEX LOOKUP IN THIS EXAMPLE, WE’RE GOING TO HAVE A LOOK AT A MORE COMPLEX PROBLEM THAT’S GOING TO REQUIRE INDEX TO MATCH. SO, YOU SEE I HAVE THE DATA SET UP AS BEFORE, EXCEPT THAT I HAVE A SEPARATE COLUMN FOR ACTUAL. AND A SEPARATE ONE FOR BUDGET. LET’S SEE, I CAN’T CHANGE THIS FORMAT, BECAUSE THAT’S THE FORMAT THAT I GET FROM THE SYSTEM. AND WHAT I WANT TO DO IS TO CREATE A REPORT WHERE I’M GOING TO SELECT BETWEEN ACTUAL AND BUDGET, SELECT BETWEEN PROFIT AND REVENUE. =INDEX (ARRAY – ACTUAL REVENUE TO BUDGET PROFIT, MATCH (LOOKUP VALUE – WENCAL, LOOKUP ARRAY – APPS COLUMN,0), MATCH (LOOKUP VALUE – ACTUAL & PROFIT, LOOKUP ARRAY – UNIQUE IDENTIFIER,0)) SO, WHAT WE CAN DO IS TO ADD A UNIQUE IDENTIFIER, THE PROCESS IS =ACTUAL&REVENUE CELL>PRESS ENTER. ACTUAL ACTUAL BUDGET BUDGET DIVISION APPS REVENUE PROFIT REVENUE PROFIT GAME FIGHTRR 11649 802 10593 554 GAME KRYPTIS 7718 876 6409 654 GAME PERINO 15033 469 12724 530 GAME HACKRR 14432 240 19102 1302 ACTUALREVENUE PRODUCTIVITY WENCAL 14432 240 15113 363 ACTUALPROFIT PRODUCTIVITY BLEND 17990 1166 18181 1223 BUDGETREVENUE BUDGETPROFIT PRODUCTIVITY SLEOPS 11022 550 13112 474 UTILITY ACCORD 17760 800 16854 572 MISTY UTILITY WASH 30400 787 30237 932 20400 614 18477 1120 UTILITY TWENTY 20 PAI CHART  QTR SALES QTR - 01 250000 QTR - 02 100000 QTR - 03 260000 QTR - 04 260000 QTR - 05 230000 01. SELECT THE DATA RANGE 02. CLICK ON THE INSERT MENU > CHART GROUP > CLICK ON THE PAI CHART 03. THE DROP-DOWN LIST APPEAR ON THE SCREEN 04. SELECT THE 3D PAI CHART 05. IN THIS PAI CHART WE CAN SEE THAT QTR-04 IS THE HIGHEST YEAR. 06. QTR-04 SHOULD BE SEEN WELL DURING THE PRESENTATION. 07. FOR THAT I HAVE TO DOUBLE CLICK IN THE CHART AREA. 08. FORMAT DATA SERIES OPEN ON THE RIGHT HAD SIDE 09. IN SERIES OPTION WE GET TWO OPSON > 01. ANGLE FOR FIRST SLICE 02. POINT EXPLOSION 10. ANGLE FOR FIRST SLICE WILL BE THE ANGLE POSITION MOVEMENT OF THE CHART. 11. WHEN INTERPRETING THE CHART, IF WE NEED TO SEPARATE EACH PIE IN THE PIE CHART, WE DO IT FORM POINT EXPLOSION  COLUMN CHART YEAR SALES 2020 91163 2021 134599 2022 148414 2023 138115 2024 199845 2025 199185 2026 122919 2027 180820 2028 162194 2029 249388 2030 196892 2031 109926 2032 355488 01. WE WILL CONVERT THIS DATA TO COLUMN CHART > THE SHORT KEY IS > ALT + F1 02. HERE WE CAN SEE THAT THE YEAR NUMBERS DO NOT COME HORIZONTALLY. 03. SELECT THE CHART AREA > SELECT CHART DESIGN OPTION > UNDER THE DATA GROUP > CLICK ON SELECT DATA OPTION. 04. THE SELECT DATA SOURSE DIALOG BOX APPEAR. 05. UNDER HORIZONTAL AXIS LABELS > CLICK ON EDIT OPTION > THEN SELECT THE YEAR RANGE. 06. IF WE CHANGE COLUMN CHART TO BAR CHART THERE ARE TWO METHODS. 07. SELECT THE CHART AREA > CHART DESIGN > TYPE GROUP CHANGE CHART TYPE. 08. RIGHT CLICK ON THE CHART AREA > CHANGE CHART TYPE > CHART TYPE DIALOG BOX APPEAR> THEN SELECT BAR CHART TYPE.  COMBO CHART YEAR REVENUE EXPENSES QTR 1 100 50 QTR 2 200 100 QTR 3 300 150 QTR 4 400 200 01. WE WILL CONVERT THIS DATA TO COLUMN CHART > THE SHORT KEY IS > ALT + F1 02. RIGHT CLICK ON THE CHART AREA > CHANGE CHART TYPE > CHART TYPE DIALOG BOX APPEAR> THEN SELECT BAR CHART TYPE. 03. CHANGE CHART TYPE DIALOG BOX APPEAR 04. THEN CLICK ON THE COMBO CHART OPTION FROM THE RIGHT SAID PAN > 05. WE SELECT CLUSTERED COLUMN - LINE OPTION 06. DOUBLE CLICK ON THE ANY COLUMN AXCES > FORMAT DATA SERIES OPEN ON THE RIGHT-SIDE PAN 07.. UNDER THE SERIES OPTION > SELECT SECONDARY AXIS, SERIES, BACKGROUND IS CHART AREA, INSIDE AREA IS PLOT AREA  CHART ELEMENTS NAME UNITS PRINCE MONDAL 900 SAYANTI PARUI 800 SUBHAM SEN 700 DRISTI DAS 600 SAMPURNA MUKHERJEE 500 ARJYA KEVIN 400 SARKAR SANJIT AIRJO 300 NEEL RANA 200 UNITS 0 900 800 700 600 500 400 300 200 100 0 PRINCE SAYANTI SUBHAM DRISTI DAS SAMPURNA ARJYA KEVIN SANJIT AIRJO NEEL RANA MONDAL PARUI SEN MUKHERJEE SARKAR 01. TO WORK VERY WELL IN EXCEL CHARTS, YOU NEED TO KNOW THE CHART ELEMENTS 02. (i)CHART TITLE,(ii) VERTICAL AXIS, (iii) GRIDLINES, (iv)HORIZONTAL AXIS, (v)SERIES, (vi) PLOT AREA, (vii) CHART AREA. 03. IF A COMPANY LOGO IS TO BE PLACED BEHIND THE CHART AREA > DOUBLE CLICK ON THE BACKGROUND > 04. FORMAT CHART AREA OPTION OPEN ON THE RIGHT-SIDE PAN 05. UNDER THE FILL > CLICK ON PICTURE OR TEXTURE 06. IF I WANT TO GIVE MY COMPANY LOGO > UNDER THE PICTURE SOURCE > CLICK ON INSERT RICHER DATA LABELS NAME UNITS PRINCE MONDAL 900 SAYANTI PARUI 800 SUBHAM SEN 700  SELECT THE DATA RANGE > CLICK ON THE INSERT MENU > UNDER THE CHART GROUP > CLICK ON INSERT SCATTER.  THEN SELECT ON BUBBLE CHART.  WHEN WE CLICK ONE BUBBLE, THE DATA IN THE EXCEL WILL BE HIGHLIGHTED  THEN WE CLICK THE +SIGN ON THE CHART AND TICK THE DATA LABLE FOR BETTER UNDERSTANDING.  WE WILL MOVE THE DATA ELEMENTS FROM THE DATA LABLE TO THE RIGHT ALIGNMENT.  CLICK ON THE NUMBER ON THE RIGHT SIDE OF THE CHART AREA TO COLOUR  THE  AFTER SELECTING THE CHART AREA NUMBER, THE FORMAT SHAPE WILL OPEN ON THE RIGHT > FROM THERE GO TO FIIL AND CHANGE THE COLOR. THEN RIGHT CLICK ON THE NUMBER AND CHANGE DATA TO SHAPE 09. INTERMEDIATE POINT 02  RIGHT-CLICK SEARCH TO QUICKLY FIND THE COMMAND YOU NEED. FINDING A SPECIFIC FEATURE IN THIS MENU IS DIFFICULT.  DON'T WORRY ABOUT THIS ANYMORE. INSTEAD, WHAT YOU'RE GOING TO DO.  IS RIGHT - MOUSE CLICK, AND YOU'RE GOING TO SEE A NEW BOX POP UP HERE CALLED SEARCH THE MENUES. TYPE IN WHAT YOU'RE LOOKING FOR. LET'S SAY YOU WANT TO ADD A DROP -DOWN LIST  TO START TYPING IN ‘LIST', AND YOU'RE GOING TO SEE THE VALIDATION FEATURE POP UP. USE THE ARROW KEYS TO NAVIGATE, PRESS SPACEBAR TO SELECT IT, AND THEN YOU CAN GO AHEAD AND ADD YOUR LIST  I'M JUST GOING TO USE TAB. PRESS THE DOWN ARROW KEY TO SELECT LIST, PRESS TAB, FOR SOURCE, I'M GOING TO GO WITH DIVISION. ENTER > AND NOW I HAVE MY DROP-DOWN LIST INSERTED.  LET'S SAY I DECIDE TO REMOVE IT. THIS TIME I'M GOING TO BRING UP THE MENU ITEMS USING MY SHORTCUT KEYS. USE SHIFT+F10. YOU MIGHT NEED > TO USE WINDOWS+SHIFT+F10 DEPENDING ON YOUR DEVICE.  GO UP AND TYPE IN "CLEAR". USE THE ARROW KEYS TO NAVIGATE AND SELECT "CLEAR ALL" YOU CAN PRETTY MUCH FIND ANY COMMAND USING THIS FEATURE.  LET'S SAY I WANT TO ADD CONDITIONAL FORMATTING TO THIS. I'M GOING TO GO AHEAD BRING UP THE MENU, PRESS TAB, AND TYPE IN "CONDITIONAL FORMATTING". PRESS THE SPACEBAR.  NAVIGATE DOWN , DATA BARS, AND GO WITH THIS ONE. ALL THE COMMANDS ARE NOW EASILY ACCESSIBLE.  HIDE ZERO ID ITEM QTY PRICE AMOUNT 100 MOUSE 10 200 2000 101 PRINTER 20 1300 26000 102 MOUSE 25 210 5250 103 MONITOR 11 2000 22000 104 MONITOR 13 2300 29900 105 KEYBOARD 29 310 8990 106 KEYBOARD 30 270 8100 102240  We can see that AMOUNT is SUM totaled in the database. If we select and delete all the values of AMOUNT then total SUM will show zero instead.  Now I want to delete the value of Amount so that the Total SUM cell does not show ZERO.  You can select the ZERO NUMBER cell and press CTRL + 1 or you can also right click and go to format cell.  Then you have to go to Custom > where General is written remove it and type 0 > then double semicolon  Suppose we now hide some columns and rows from this database  Now we want to permanently delete the row and column that we have hidden from the sheet  Note that the hidden row or column will never come back after being deleted  click on the File > then click on info > Then click Check for issue > Then click on Inspect Document > Microsoft Excel Dialog Box Open   Then click on OK > Then Document Inspector Dialog Box Open > Then Scroll Down > Then Hidden Row and Column > Then Click On Inspect  Then Scroll Down > Hidden Row and Column > Then click on Remove All > Then Click On close Button  MAGICAL AUTOSUM MONTHS SELLER NAME SELLER SELLER SELLER SELLER SELLER SELLER SELLER SELLER TOTAL 1 2 SELLER 8 9 3 4 5 6 7 JAN 81 90 65 34 50 92 76 58 58 604 FEB 60 89 98 81 52 74 65 80 80 679 MAR 99 32 84 80 94 80 84 49 49 651 APR 37 80 30 91 54 71 49 77 77 566 MAY 98 38 65 72 90 49 63 98 98 671 JUN 81 79 35 47 79 76 45 99 99 640 JUL 72 54 55 78 54 40 85 77 77 592 AUG 63 45 80 51 44 97 86 72 72 610 SEP 87 85 97 45 99 91 55 56 56 671 OCT 63 82 52 74 67 75 77 36 36 562 NOV 40 44 38 54 91 49 82 89 89 576 DEC 61 86 66 63 32 82 97 94 94 675 TOTAL 842 804 765 770 806 876 864 885 885 7497  In this database we have to do horizontal total and vertical and total  Then we have to select SELLER from 1 to total > then press ALT + =  QUICK ANALYSIS ROLL NO PHY CHE MATH AVG TOTAL 1 81 90 65 79 236 2 60 89 98 82 247 3 99 32 84 72 215 4 37 80 30 49 147 5 98 38 65 67 201 6 81 79 35 65 195 7 72 54 55 60 181 8 63 45 80 63 188  By selecting the data range of total we can see below the selected data range we will get QUICK ANYALYSYS option or press CTRL + Q AUTOMATE SERIAL NUMBER B C SERIAL PRODUCT NUMBER NAME 1 MOUSE 2 MONITOR 3 PRINTER 4 SCANNER  =SEQUENCE(COUNTA(C3:C100)) 10. INTERMEDIATE POINT 3 TEXT FUNCTION 11- 092023 Monday 12- 092023 Tuesday 13- 092023 Wednesday 14- 092023 Thursday 15- 092023 Friday 16- 092023 Saturday 17- 092023 Sunday 18- 092023 Monday 19- 092023 Tuesday  20- 092023 Wednesday 21- 092023 Thursday 22- 092023 Friday 23- 092023 Saturday 24- 092023 Sunday 25- 092023 Monday 29- 092023 Friday  If we need to insert the day by date into a database - then we can use a simple formula: =TEXT(D3,”DDDD”) REMOVE SURNAME NAME TUSHAR DAS RAHUL MAKAL SUJAY DAS PULAK MALLICK APURBA DAS  =CTRL + H THEN IN FIND WHAT SELECTION BOX WE PRESS SPACE BY KEY BOARD THEN TYPE * AND SELECT REPLACE ALL OPTION. FORM STYLE DATA ENTRY SR.NO NAME DEPARTMENT LOCATION 2 Dipankar Accounts BANGLORE  PRESS ALT + D + O DATA COPY PROCESS NAME  SHYEB KHAN AJOY SARKAR SONU DAS AVIJIT KUMAR DEY SUSHMITA SADHUKAN SUMAN DAS PRIYANKA DAS TIYA SARKAR ANIMESH DAS ANKUR SAHA RISHAV DAS NIRNAYA GOSWAMI SOUMEN DEY DEB KUMAR BOSE SUDIPTA DOLUI TRIDEEP DAS  After selecting the cell range in Excel, press CTRL and drag where we can drop it. We will see that the cell range will be copied AGE CALCULATION DATE OF NAME BIRTH SNEHA RAJAK 06-07-1993 SANCHITA RAJAK 10-07-1994 ISHANI 29-03-1998 GANGULY SOUVIK MALLICK 21-02-1993 ANIKET SHAW 25-06-1993 MOHIT RAJBANGSHI 03-05-1997 SWARUP 14-02-1996 MONDAL BISHAL RANA 15-05-1998  SOUMEN DEY 12-03-1996 AMAN BHAGAT 25-07-1996 NEHA SINGH 11-07-1994  =YEARFRAC(STARTDATE,TODAY()) DATA PROCESS PROFFESIONAL ATHLETE SPORT INCOME 2023 (USD) LIONEL MESSI SOCCER 130000000 LEBRON JAMES BASKETBALL 12120000 CRISTIANO RONALDO SOCCER 115000000 NEYMAR SOCCER 95000000 STEPHEN CURRY BASKETBALL 92800000 KEVIN DURANT BASKETBALL 921000000 ROGER FEDERER TENNIS 90700000 CANELO ALVAREZ BOXING 90000000 TOM BRADY FOTBALL 83900000 GLANNI ANTETOKOUNMPO BASKETBALL 80900000 RUSSEL WESTBROOK BASKETBALL 79200000 JAMES HARDEN BASKETBALL 74400000 MATHEW STAFFORD FOTBALL 72300000 ARON RODGER FOTBALL 68000000 TIGER WOODS GOLF 68000000 JOSH ALLEN FOTBALL 67000000 LEWIS HAMILTON AUTO RACING 65000000 TYSON FURY BOXING 62000000 NAOMI OSAKA TENNIS 59200000 DEMIAN LILLARD BASKETBALL 57400000 DESHAUM WATSON FOTBALL 55600000 KLAY THOMPSON BASKETBALL 55000000  FIRST SELECT ALL DATA THEN SELECT INSERT MENU FILTERS GROUP SLICER OPTION THEN WE TICK THE SPORTS OPTION THEN PRESS OK.  THEN WE SEE A SLICER BOX OPEN AND It NAME IS SPORT.  YOU CAN RESIZE THE SLICER BOX ACCODING TO YOU. ALSO YOU CAN ADJUST THE SLICER BOX. FOR THE ADJUSTING SLICER BOX, FIRSTLY YOU SELECT THE SLICER BOX THEN YOU SEE SLICER MENU OPTION APPEAR ON MENU BAR WE SELECT THE SLICER MENU BUTTOND GROUP THEIR YOU HAVE VARIOUS OPTION THEIR.