A.E Module II Study Material PDF

Summary

This document is study material for a computer education course. It contains information and exercises on various Excel functions and features.

Full Transcript

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY NATIONAL TASK FORCE IT & SD. Registration Number: IV – 1903 – 04384 - 2017...

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY NATIONAL TASK FORCE IT & SD. Registration Number: IV – 1903 – 04384 - 2017 REGD UNDER MINISTRY OF MSME, GOVT OF INDIA A.E MODULE-II 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. EXCEL BASIC DATABASE PART -01 COUNT BLANK COUNT AND COUNTA MASTER LOGICAL SYSTEM LOGICAL STATEMENT AUTOFILL DATE, MONTH YEAR 02. EXCEL BASIC DATABASE PART -02 TEXT CASE TRIM AND LEN ROUND FUNCTION SUBTOTAL FUNCTION DEPOSITED AMOUNT 03. EXCEL BASIC DATABASE PART -03 PIVOT TABLE VLOOKUP & HLOOKUP VLOOKUP + MATCH PART 2 VLOOKUP + MATCH PART 3 04. Combine Data from Multiple worksheets into ONE sheet - PART -04 05. EXCEL BASIC DATABASE PART -05 USING FLASH INSERTING ROW AFTER EVERY ROW MATCH THE LIST DATA VALIDATION FILL ALL BLANK CELLS REMOVE FORMULA BUT KEEP DATA ADD 0 BEFORE A NUMBER SAVE EXCELS CHART AS IMAGE CAMERA TOOL EXCEL BASIC DATABASE PART - 01 COUNT BLANK FORMULA NAME BILL - 01 BILL - 02 BILL - 03 AA 2 3 BB 6 5 CC 7 8 DD 2 6 EE 6 FF 4 GG 3 HH 9 8 II 10 12 JJ 24 KK 30 11 LL 22 MM 21 NN 13 OO 41 14 PP 32 12 QQ 12 20 RR 10 21 SS 25 THE EXCEL COUNTBLANK FUNCTION RETURNS A COUNT OF EMPTY CELLS IN A RANGE. CELLS THAT CONTAIN TEXT, NUMBER, ERRORS, SPACES, ETC. ARE NOT COUNTED. FORMULAS THAT RETURN EMPTY TEXT ARE COUNTED FORMULA: =COUNTBLANK (SELECT THE DATA RANGE) PRESS ENTER PRESS COUNT AND COUNTA FORMULA NAME BILL - 01 BILL - 02 BILL - 03 AA 2 A 3 BB 6 5 D CC 7 G 8 DD L 2 6 EE 6 X C FF E 4 MM GG F I 3 HH 9 8 J II 10 K 12 JJ M 24 KK 30 N 11 LL P 22 O MM 21 Q R NN T S 13 OO 41 14 PP U 32 12 QQ 12 20 V RR 10 W 21 SS X 25 Z WHAT IS THE DIFFERENCE BETWEEN COUNT AND COUNTA IN EXCEL? THE COUNT FUNCTION IS GENERALLY USED TO COUNT A RANGE OF CELLS CONTAINING NUMBERS OR DATES EXCLUDING BLANKS. COUNTA, ON THE OTHER HAND WILL COUNT EVERYTHING........ NUMBER, DATES, TEXT OR RANGE COUNTINING A MIXTURE OF THESE ITEMS, BUT DOES NOT COUNT BLANK CELLS. COUNTA STANDS FOR COUNT ALL. FORMULA: =COUNT (SELECT THE DATA RANGE) FORMULA: =COUNTA (SELECT THE DATA RANGE MASTER LOGICAL STATEMENT SALARY P.A. (US$) DIVISION 38,261 HFD 82,135 RAD 24,566 HFD 15,097 CDFD 18,125 RDF 68,256 HFD 58,124 CDFD 50,256 RDF 62,362 RAD 19,125 CDFD 17.412 RAD 71,213 RDF 63,254 HFD Q1. THE QUESTION IS WHOSE SALARY IN THIS DATA RANGE IS ABOVE RS/- 50,000 =SELECT THE FIRST CELL OF SALARY P.A.>50,000 THEN PRESS ENTER KEY FROM KEY BOARD 02. THE QUESTION IS HOW MANY OF THEM WORK IN RAD DIVISION =SELECT THE FIRST CELL OF DIVISION=TYPE"RAD" THEN PRESS ENTER KEY FROM KEYBOARD. LOGICAL STATEMENT SALARY MORE THAN NAME P.A.(US$) DIVISION RATING AGE 65K NAYA ACHARYA 35,261 HFD 3 31 ISHAAN BASU 38,251 RAD 4 30 DHRUV BEDI 24,566 HFD 1 23 AMAR JAIN 15,097 CDFD 5 23 DIYA JHA 38,038 HFD 1 23 ANANYA VARMA 72,682 RAD 3 39 TAMIA YADAV 35,556 ED 5 35 NILA BANERJEE 55,089 CAB 2 32 AGASTYA DESHPANDE 26,746 RAD 1 29 JAYA GANDHI 98,527 HFD 4 19 SANA GHOSH 20,337 CDFD 1 25 ADITI JOSHI 48,158 RAD 3 29 AJAY NAIDU 67,602 ED 2 28 ANJALI SETH 27,826 CDFD 3 32 NIKHIL AGARWAL 53,546 HFD 3 31 SHAAN AHUJA 62,475 RAD 2 36 SAHIL CHOWDHURY 28,158 ED 5 37 ISHANI CHWALA 20,438 ED 2 27 AMBAR KULKARNI 89,275 CDFD 4 26 Q1. THE QUESTION IS WHOSE SALARY IN THIS DATA RANGE IS ABOVE RS/- 65,000 =SELECT THE FIRST CELL OF THE SALARY P.A.>65000 THEN PRESS ENTER KEY FROM KEYBOARD Q2. THE QUESTION IS WHO IS THE GUY IN THIS DATA RANGE DOESN'T WORK IN THE "RAD" DIVISION =SELECT THE FIRST CELL OF DIVISION"RAD" THEN PRESS ENTER KEY FROM KEYBOARD AUTOFILL DATE, MONTH, YEAR BASIC DATE 2 DATE 3 01-Jan-22 31-Mar-22 31-Jan-22 Q1. WHAT CAN WE DO IF A BLACK PLUS (+) SIGN IS NOT SEEN IN EXCEL WHEN THE DATA IS DRAG IN DOWN? FILE MENU > MORE > OPTION > ADVANCED > TICK THE CHECK BOX - "ENABLE FILL HANDLE AND CELL DRAG AND DROP" Q2. EXCEL HAS A DATE OF 31-MARCH-2022. IF WE SELECT IT AND DRAG IT DOWN, THEN 1 2 3 4 5 APRIL 2022 WILL COME AS SERIAL. IN THIS CASE, AFTER DRAGING THE DATA AT THE BOTTOM, YOU CAN SEE THAT IS SMALL BOX CALLED AUTOFILL. THERE WILL BE A SMALL DROP-DOWN ARROW IN THE AUTOFILL BOX. CLICK THERE. A DROPDOWN LIST OPEN AND FROM THAT LIST YOU HAVE TO CLICK ON THE FILL YEARS RADIO BUTTON. EXCEL BASIC DATABASE PART - 02 CHANGE TEXT CASE IN EXCEL nett zone computer education Centre NETT ZONE COMPUTER EDUCATION CENTRE * If any data in MS-Excel is written in Lowercase, then the formula to be used to move it to capitalization is ----- =upper (select the data which is written in lowercase) then press enter key from keyboard. * Similarly, in Excel, if any data is written in capital letters, the formula for converting it to small letters is -------- =lower (select the data which is written in uppercase) The PEOPER case formula: =Proper(select text) then press enter. TRIM & LEN FUNCTION NETT ZONE The Excel TRIM function strips extra space from text, leaving only a single space between words and no space character at the start or end. TRIM is useful when cleaning up text that has come from other application or environments TRIM is only removing the ASCII (American standard code for information interchange) space character (32) from text. Unicode text often contains a non - breaking space character (160) that appears in web page as an HTML entity. This will not be removed with TRIM *ASCII - American Standard code for information interchange. A standard data - transmission code that is used by smaller and less - powerful computers to represent both (Letters, numbers, and punctuation marks) and non-input device commands LEN function is a text function in excel that returns the length of a string / text. Len Function in Excel can be used to count the number of characters in a text string TRIM FUNCTION: =TRIM (SELECT THE DATA IN WHICH THERE IS SPACE) LEN FUNCTION =LEN (SELECT THE DATA WHICH WE WANT TO KNOW THE TOTAL CHARACTER NUMBER) ROUND FUNCTION SALARY P.A. (US$) DIVISION 38,261.5 HFD 82,135.50 RAD 24,566.60 HFD 15,097.03 CDFD 18,125.20 RDF 68,256.60 HFD 58,124.25 CDFD 50,256.35 RDF 62,362.70 RAD 19,125.25 CDFD 17,412.80 RAD 71,213.60 RDF 63,254.90 HFD The Excel ROUND function returns a number rounded to a given number of digits. The ROUND function can round to the right or left of the decimal point Purpose: Round a number to a given number of digits Return value: A rounded number. Syntax: =ROUND (number, num_digits) Arguments: - * Number - The number to round. * num_digits - The place at which number should be rounded. SUBTOTAL FUNCTION SL#NO CLIENT NAME COUNTRY CITY AMT. 1 ALLIED DISTILLERS AUSTRALIA SYDNEY 12900 2 ARNOTTS BISCUITS AUSTRALIA MELBOURNE 5300 3 BUTTERBALL TURKEY AUSTRALIA BRISBANE 8300 4 CADBURY BEVERAGES AUSTRALIA PERTH 7600 5 HERSHEY CANADA AUSTRALIA ADELAIDE 14800 HORMEL FOOD CENTRAL 6 CORPORATION AUSTRALIA COAST 8300 JOSEPH'S GOURMET SUNSHINE 7 PASTA AUSTRALIA COAST 14700 8 J.M. SMUCKER COMPANY AUSTRALIA WOLLONGONG 8300 9 LINDT CHOCOLATE AUSTRALIA GEELONG 14700 10 OCEAN SPRAY AUSTRALIA BRISBANE 19600 11 PERDUE FARMS AUSTRALIA BRISBANE 14100 12 PINNACLE FOODS GROUP AUSTRALIA BRISBANE 12700 13 PONTIAC FOOD AUSTRALIA BRISBANE 9900 14 RICH SEAPAK AUSTRALIA WOLLONGONG 16800 CENTRAL 15 SARA LEE BANKERY AUSTRALIA COAST 9900 16 TATE & LYLE SUGARS AUSTRALIA MELBOURNE 10200 17 UNCLE BEN'S AUSTRALIA PERTH 12500 18 WEAVER POPCORN CO. AUSTRALIA SYDNEY 16700 Ref1 Required. The first named range or reference for which you want the subtotal. Ref2,... Optional. Named ranges or references 2 to 254 for which you want the subtotal. For the function_num constants from 1 to 18, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list. The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL (109, B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal. FORMULA: =SUBTOTAL (109, REF1 DATA RANGE, REF2 DATA RANGE) ENTER PRESS DEPOSITED AMOUNT FUNCTION AMT DEPOSITED TODAY 300000 FOR _________ YRS 5 INTEREST 8% TOTAL AMT ON MATURITY FORMULA: =PRINCIPAL MONEY * (1+R) ^ N R = RATE OF INTEREST PRINCIPAL MONEY = THE DEPOSITE AMOUNT N = NUMBER OF YEARS HOW DO WE 24 CALCULATIONS =2^4 OR =POWER (2,4) ENTER EXCEL BASIC DATABASE PART - 03 PIVOT TABLE DATE NAME STATE PICKLES SALES (RS) 15-Jan-22 AAKESH ANDHRA GREEN CHILI 1200 SHARMA PRADESH 18-Jan-22 ADVIK SINGH PUNJAB LEMON 1050 21-Jan-22 CHAITANYA DAS KARNATAKA CARROT 2700 24-Jan-22 DARSH KAUR MAHARASHTRA RED CHILI 525 27-Jan-22 DARPAN RAM JHARRKHAND GREEN CHILI 350 30-Jan-22 GIRIK YADAV ASSAM TOMATO 450 03-Feb- HREDHAAN LAL WEST BENGAL TOMATO 40 22 06-Feb- HEMANG LAL ANDHRA COCONUT 100 22 PRADESH 09-Feb- INESH MANDAL KARNATAKA CARROT 1050 22 15-Feb- ISHAAN PUNJAB RED CHILI 2500 22 SHARMA 20-Feb- JAIRAJ RAY ANDHRA LEMON 2100 22 PRADESH 27-Feb- JIHAN MONDAL MAHARASHTRA LEMON 1018 22 04-Mar-22 LOHIT SAH WEST BENGAL CARROT 1250 06-Mar-22 MANBIR SINGH ASSAM COCONUT 1550 10-Mar-22 MAYAN PATEL JHARRKHAND COCONUT 588 15-Mar-22 MEET PRASAD JHARRKHAND TOMATO 985 19-Mar-22 NISHIT PATIL ANDHRA GREEN CHILI 970 PRADESH HERE ARE SOME THINGS TO KEEP IN MIND WHEN CREATING A PIVOT TABLE * THERE SHOULD BE NO BLANK HEADING ON THE TABLE. * ANY CELL IN THE TABLE IS NOT LIKE A MERGED. IF MERGED THEN PIVOT TABLE WILL NOT WORK. > SELECT THE DATA RANGE OF THE TABLE > CLICK ON THE INSERT MENU >TABLES GROUP> PIVOT TABLE > PIVOT TABLE DIAOUG BOX APPEAR > THEN CHOOSE "NEW WORKSHEET" > CLICK ON OK THE PIVOT TABLE WILL OPEN IN A NEW SHEET > WE WILL SEE THAT THERE IS A BOX CALLED "PIVOT TABLE 1" > IN THAT BOX WE WILL RIGHT CLICK. > THE DROP-DOWN LIST APPEAR ON THE SCREEN - CLICK ON "PIVOT TABLE OPTION" > PIVOT TABLE OPTION DIALOG BOX APPEAR ON THE SCREEN > THEN CLICK ON THE "DISPLAY" TAB > THEN CLICK ON "CLASSIC PIVOT TABLE LAYOUT" > WE SEE THAT FOUR DIFFERENT SECTION BOXES HAVE BEEN CREATED ON THE EXCEL SHEET. > THE LARGEST FIELD IS CALLED "DROP VALUE FIELD HERE" - ALL MATHEMATICAL CALCULATIONS ARE DONE IN THESE FIELD. > ON THE RIGHT-HAND SIDE - CLICK ON "STATE CHECK BOX" UNDER PIVOT TABLE FIELD OPTION. > IF WE DRAG THE SALES OPTION AND DROP THE "DROP VALUE FIELD HERE" - THEN WE CAN SEE HOW MUCH PICKLES HAS BEEN SOLD IN WHICH STATE. > IF I HAVE TO ANALYZE HOW MANY ORDERS HAVE BEEN RECEIVED IN ASTATE, THEN I AM AGAIN DRAG THE SALES OPTION AND DROP TO THE DROP VALUE FIELD HERE" AGAIN. > WE WILL NOTICE THAT THE SAME PEN IS MADE TWICE. > WE WILL RIGHT CLICK ON ANY ONE OF THE CELLS IN SECOND PANE. > THE DROP-DOWN LIST APPEAR - THEN CLICK ON THE "SUMMARIZE VALUES BY" - THEN CLICK ON COUNT. > RIGHT CLICK ON ANY ONE CELL OF THE FIRST PEN WHICH WE CAN SHORT IN LARGEST TO SMALEST. > NOW WE NEED TO ANALYZE WHICH PICKLES HAS BEEN SOLD MORE IN WHICH STATE THEN ---- CLICK ON THE PIVOT TABLE ANALYZE > UNDER THE FILTER GROUP > CLICK ON THE "INSERT SLICER" OPTION > INSERT SLICER DIALOG BOX APPEAR ON THE SCREEN > SELECT PICKLES THEN CLICK ON OK BUTTON > EXCEL OPENS AN INTERDACE OF 'PICKLES' - THAT INTERFACE WILL HAVE SOME TAB ACCORDING TO THE NAME OF THE PICKLES > CLICK ON ANY TAB OF INTERFACE - STATE WISE WILL SHOW YOU REPORT OF WHERE THOSE PICKLES SOLD BEST. > IF YOU WANT TO CHANGE THE DESIGN OF THE PIVOT TABLE, YOU CAN SELECT THE DESIGN OF YOUR CHOICE BY CLICKING ON THE DESIGN MENU > IF I WANT THE STATE WISE SALES REPORT AS A PERCENTAGE THEN RIGHT CLICK ON THE ANY CELL OF FIRST PANE - DROP DOWN LIST APPEAR ON THE SCREEN - CLICK ON "SHOW VALUE AS" - THEN CLICK ON "PERCENTAGE OF GRAND TOTAL" > IF YOU WANT TO MAKE A CHART ON PIVOT TABLE DATA ANALYZE REPORT, SIMPLY PRESS F11 KEY FROM THE KEYBOARD VLOOKUP VS HLOOKUP COMPANY NAME BAJAJ FINANCE LTD. COST COMPANY NAME SALES COST RELIANCE INDUSTRIES LTD 18,66,366 2694.85 TATA CONSULTANCY SERVICES LTD 13,21,595 3547.70 HDFC BANK LTD 7,51,802 3547.70 INFOSYS LTD 6,67,063 1365.55 ICIC BANK LTD 5,19,389 1561.45 HINDUSTAN UNILEVER LTD 5,00,392 752.80 ADANI GREEN ENERGY LTD 4,51,750 2100.90 STATE BANK OF INDIA 4,46,588 2825.00 BHARTI AIRTEL LTD. 4,34,704 494.70 BAJAJ FINANCE LTD. 4,30,421 739.45 KOTAK MAHINDRA BANK LTD. 4,00,027 7009.45 ASIAN PAINTS LTD 3,42,920 2212.55 HCL TECHNOLOGIES LTD. 3,21,699 1730.20 WIPRO LTD. 3,03,562 255.10 BAJAJ FINSERV LTD. 2,98,992 3130.05 AXIS BANK LTD. 2,94,859 1090.50 =VLOOKUP (LOOKUP_VALU, TABALE _ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP]) VLOOKUP + MATCH FUNCTION PART 1 BAJAJ PULSAR PUNE 150 PUNE MUMBAI DELHI KOLKATA Royal Enfield 156099 157098 157097 156097 classic 350 Royal Enfield 121586 121584 121583 120584 Bullet 350 Yamaha YZF R15 145302 145301 145300 144300 V3 Yamaha MT 15 138903 138985 138900 137900 Hero Splendor 52900 52895 52800 52885 Plus Bajaj Pulsar 150 85538 85537 85536 85530 KTM 200 Duke 172755 173750 172749 172748 Bajaj Pulsar NS200 125120 125045 125030 124030 KTM RC 125 148760 148744 148750 148755 TVS Apache RTR 95530 95520 95000 95100 160 FORMULA: =VLOOKUP (LOOKUP_VALUE, TABLE_ARRAY(F4), MATCH (LOOKUP_VALUE, LOOKUP_ARRAY(F4),0),0) VLOOKUP + MATCH FUNCTION PART 2 SHEET NO: 01 ID NAME DIVISION GRADE LIMIT GRAND TOTAL - EXPENSES 9191 ANKITA DAS CDFD M7 6669 9192 SUPARNA RAD M3 11285 MONDAL 9193 PRIANSU ROUTH CDFD M2 15715 9194 NIRALA SINGH ED M5 29254 9195 AMIT DAS RDD M4 39603 9196 AMAN DAS AD M6 37093 9197 DEPAN DAS PEMD M7 19050 9198 AJAY SARKAR LGAD M3 11196 9199 SANTANU ED M5 22067 MANNA 9200 SUMIT NASKAR RDD M7 36145 9201 APURBA DAS PEMD M4 15450 9202 SONALI LGAD M6 16750 MONDAL 9203 TISHA DOLUI HFD M2 18902 9204 RUPEN GHOSH RDD M7 19956 9205 SUBHADEEP RAD M4 24450 KURI SHEET NO: 02 M1 M2 M3 M4 M5 M6 M7 HFD 21000 73000 88000 45000 38000 51000 48000 RAD 93000 15000 95000 78000 52000 52000 13000 CDFD 88000 30000 76000 22000 72000 98000 14000 ED 100000 90000 89000 29000 13000 91000 61000 RDD 75000 66000 62000 98000 52000 93000 51000 AD 27000 95000 74000 25000 57000 79000 49000 PEMD 74000 62000 31000 76000 18000 61000 87000 LGAD 94000 42000 57000 91000 32000 74000 80000 FORMULA: =VLOOKUP (LOOKUP_VALU, TABLE_ARRAY (SHEET NO:2 & PRESS F4 FOR LOCK), MATCH (SHEET NO:1 GRADE, SHEET NO:2 LOOKUP_ARRAY (PRESS F4 FOR LOCK),0),0) THEN PRESS ENTER VLOOKUP + MATCH FUNCTION PART – 03 SHEET NO: 01 ID NAME DIVISION GRADE LIMIT GRAND TOTAL - CROSS EXPENSES 9191 ANKITA DAS CDFD M7 48000 6669 FALSE 9192 SUPARNA RAD M3 95000 11285 FALSE MONDAL 9193 PRIANSU CDFD M2 30000 15715 FALSE ROUTH 9194 NIRALA SINGH ED M5 13000 29254 TRUE 9195 AMIT DAS RDD M4 98000 39603 FALSE 9196 AMAN DAS AD M6 79000 37093 FALSE 9197 DEPAN DAS PEMD M7 87000 19050 FALSE 9198 AJAY SARKAR LGAD M3 57000 11196 FALSE 9199 SANTANU ED M5 13000 22067 TRUE MANNA 9200 SUMIT RDD M7 51000 36145 FALSE NASKAR 9201 APURBA DAS PEMD M4 76000 15450 FALSE 9202 SONALI LGAD M6 74000 16750 FALSE MONDAL 9203 TISHA DOLUI HFD M2 73000 18902 FALSE 9204 RUPEN GHOSH RDD M7 51000 19956 FALSE 9205 SUBHADEEP RAD M4 78000 24450 FALSE KURI SHEET NO: 02 ID NAME DIVISION GRADE GRAND TOTAL – EXPANSES 9192 9197 9199 9201 9203 9205 FORMULA: =VLOOKUP ($B5(SHEET NO:02), (SHEET NO:01) TABLE_ARRAY SELECT THEN PRESS F4 FOR LOCK, MATCH (Sheet2! C$4, SHEET NO: 01 LOOKUP_ ARRAY THEN PRESS F4 FOR LOCK,0),0) THEN PRESS ENTER COMBINE DATA FROM MULTIPLE WORKSHEET INTO ONE SHEET SHEET NO:01 (WATER PURIFIER BASIC) NAME JAN FEB MAR APR MAY JUN PRIANSU ROUTH 750 100 1500 450 1000 NIRALA SINGH 1200 1200 1450 400 1000 1200 AMIT DAS 1300 1050 450 200 1050 SUBHADEEP KURI 750 1400 1000 1350 600 650 ANIMESH DAS 200 200 1000 450 850 50 SK. JAHED TULLA 350 360 450 1000 410 ANKUR SAHA 1200 1450 1230 1100 950 PRATHAM 1400 250 1000 530 SRIVASTAV ANKITA DAS 4100 4200 200 430 SHEET NO: 02 (WATER PURIFIER RO) NAME JAN FEB MAR APR MAY JUN PRIANSU ROUTH 100 1500 450 1000 NIRALA SINGH 1200 1200 1450 400 AMIT DAS 450 200 1050 SUBHADEEP KURI 750 1400 1000 600 ANIMESH DAS 200 200 450 50 SK. JAHED TULLA 450 1000 410 ANKUR SAHA 1200 1450 1230 1100 PRATHAM 1400 250 530 SRIVASTAV ANKITA DAS 4100 4200 200 430 SHEET NO: 03 (WATER PURIFIER LATEST) NAME JAN FEB MAR APR MAY JUN PRIANSU ROUTH 1500 100 1500 450 1000 NIRALA SINGH 1200 1200 1450 400 AMIT DAS 1521 450 200 1500 SUBHADEEP KURI 1400 1000 1421 600 ANIMESH DAS 250 200 1470 450 750 500 SK. JAHED TULLA 120 241 450 410 ANKUR SAHA 1200 1450 1230 1002 PRATHAM 1221 1400 1236 250 530 SRIVASTAV ANKITA DAS 4100 4200 200 430 SHEET NO: 04 DATA MENU > DATA TOOLS GROUP > CONSOLIDATE > COMSOLIDATE DIALOG BOX APPEAR > UNDER THE FUNCTION TAB – CLICK ON ‘SUM’ > BROWSE DATA SHEET AND ADD > UNDER THE “USE LABELS IN” GROUP CLICK TO TICK TOP _ROW AND CREATE LINK TO SOURCE DATA > THEN PRESS OK EXCEL BASIC DATABASE PART - 05 CREATING E-MAIL ID USING FLASH  LET'S ASSUME AN ORGANIZATION WANTS TO OPEN EMAIL ID OF ALL ITS EMPLOYEES BUT NEED TO USE THEIR FIRST NAME AND LAST NAME IN ORDER TO OPEN THEIR EMAIL ID.  BUT IF WE HAVE A LONG LIST OF EMPLOYEES THEN GENERATING EACH INDIVIDUAL EMAIL ID IS VERY TIME CONSUMING.  WHAT WE CAN EASILY DO IS - ONLY FOR THE FIRST RECORD OR ROW WE WILL MANUALLY CREATE EMAIL ID USING FIRST NAME AND LAST NAME.  FOR EXAMPLE, WE CAN SAY LIKE - HERE IS THE FIRST RECORD ANIMESH DAS - WE WILL DO THIS MANUALLY ONLY FOR ANIMESH.  FOR ANIMESH WE WRITE [email protected].  IN CASE OF ANIMESH, WE WRITE [email protected] THEN PRESS ENTER THEN PRESS CTRL + E. INSERTING ROWS AFTER EVERY ROW COUNTRY Q1 Q2 TOTAL INDIA 1827 5700 7527 NETHERLANDS 9070 8874 17944 PUERTO RICO 8557 4423 12980 AUSTRALIA 3020 7883 10903 UNITED STATES 8239 7000 15239 CANADA 2801 5000 7801 SWITZERLAND 6680 4092 10772  THE OFFICE TOLD US ON THIS DATA THAT WE WANT EACH RECORD TO HAVE A BLANK RECORD UNDER IT. HOW DO WE DO THIS?  FIRST, WE WILL SELECT EACH RECORD BY CTRL PRESS.  THEN THE KEYBOARD SHORTCUT WILL BE CTRL + SHIFT + (SIGN) MATCH THE LIST NAME LIST 1 NAME LIST 2 NAME LIST 3 KASH KASH KAASH ISHAAN ISHAAN ISHAAN KANAN KANAN KANAN NAVI NAVI NAVVI AARNA AARNA AARNA AMAR AMAR AMAR NILA NLLAA NILA TENZIN TENZIN AMAR ANJALI ANJALI ANJALI ANANYA ANANYA ANANYA  IF THERE IS A LIST OF SOME NAMES IN TWO COLUMNS AND ACCORDING TO THE DATA, WE ARE TOLD WHETHER THE NAME LISTS ARE CORRECT OR NOT.  THEN WE CAN USE THE EXACT FORMULA FOR EXAMPLE =EXACT(D4,E4) PRESS ENTRE.  NOTE THAT EXACT FORMULA HELPS TO MATCH ONLY TWO COLUMN OR NAME LIST.  BUT THE BIGGEST ADVANTAGE IS THAT EXACT FORMULA IS KEY SENSITIVE, THAT IS IF THE FIRST COLUMN LIST CONTAINS RAM AND SECOND COLUMN LIST CIONTAINS LOWERCASE ram, THEN THE EXACT FOIRMULA OUTPUT RESULT WILL BE FALSE.  NOW WE WILL LEARN A SECOND METHOD, WHERE WE CAN MATCH ONE OR MORE COLUMN LISTS.  FIRST, WE SELECT ONE OR MORE COLUMNS IN THE LIST THAT WE WANT TO MATCH.  HOME MENU > EDITING GROUP > FIND AND SELECT OPTION > GO TO SPECIAL > GO TO SPECIAL DIALOG BOX APPEAR > CLICK ON ROW DIFFERENCE OPTION.  WE WILL SEE THAT THE CELLS ARE SELECTED THAT ARE DIFFERENT FROM THE OTHERS. 05. DATA VALIDATION NAME ADDRESS AGE MOBILE E-MAIL RISHAV DAS SNEH NAGAR, 1111111111 INDOR SHYEB KHAN MG ROAD, INDOR 2222222222 KALANI NAGAR, PRIYANKA DAS INDOR 3333333333 SWEETA BAG SNEH NAGAR, 4444444444 INDOR SUSHMITA SUDAMA NAGAR, SADHUKAN INDOR 5555555555 TIYA SARKAR SNEH NAGAR, 6666666666 INDOR SNEHANGSHU KALANI NAGAR, GHOSH INDOR 7777777777 LET’S SAY NETT ZONE IS AN ORGANIZATION WHERE AN EMPLOYEE LIST IS GIVEN AS DATA. WHERE IT IS SAID THAT NO EMPLOYEE HERE SHALL BE BELLOW 18 YEARS NOR ABOVE 60 YEARS. WHOLE NUMBER  FIRST OF ALL, WE NEED TO SET THE AGE LIMIT FIRST.  DATA MENU > DATA TOOLS GROUP > DATA VALIDATION > DATA VALIDATION DIALOG BOX APPEAR OR ALTERNATIVELY PRESS ALT + D +L  UNDER THE ALLOW SECTION  CLICK ON WHOLE NUMBER OPTION.  ONE THING TO REMEMBER HERE IS THAT WE CANNOT WRITE DECIMAL NUMBER AFTER SELECTING WHOLE NUMBER.  FOR EXAMPLE, IF AN EMPLOYEE’S AGE IS 50 YEARS 6 MONTHS, THEN I CAN WRITE ONLY 50 YEARS.  THEN WE WILL SELECT THE BETWEEN OPTION FROM WITHIN THE DATA GROUP.  THEN IN THE MINIMUM BOX WE WILL WRITE 18 BECAUSE ACCORDING CRITERIA AN EMPLOYEE’S MINIMUM AGE IS 18.  AGAIN, IN THE MAXIMUM BOX WE WILL WRITE 60 BECAUSE ACCORDING TO THE CRITERIA THE MAXIMUM AGE OF AN EMPLYEE IS 60.  THEN CLIK ON OK INPUT MESSAGE IF YOU WANT THE USER TO SEE A MESSAGE WITHIN WRONG AGE LIMIT, HE TO ENTER THE DATA.  UNDER THE DATA VALIDATION DIALOG BOX  CLICK ON ‘INPUT MESSAGE’ TAB > GIVE TITLE EXAMPLE – ‘AGE LIMIT’ > UNDER THE INPUT MESSAGE BOX > “PLEASE ENTER THE AGE BETWEEN 18 AND 60 YEARS THEN CLICK ON OK BUTTON. ERROR ALERT WE CAN SELECT ERROR ALERT SYMBOL FROM ERROR ALERT SECTION.  UNSER THE STYLE SECTION SELECT STOP AND FROM TITLE SECTION – WRITE YOUR MSG EXAMPLE “WRONG ENTRY”  UNDER ERROR MESSAGE BOX – WRITE YOU HAVE ENTERED WRONG AGE EMPLOYEE THEN CLICK ON OK BUTTON. 06. FILL ALL BLANK CELLS IN EXCEL NAME REGION SALES RISHAV DAS US 140 SHYEB KHAN CANADA 856 PRIYANKA DAS US SWEETA BAG CANADA 492 SUSHMITA ASIA 511 SADHUKAN TIYA SARKAR US SNEHANGSHU US 504 GHOSH SUBHAM SEN CANADA 637 SANJAY MAJHI CANADA HOW TO QUICK FILL BLANK CELLS IN EXCEL. SO HERE, I HAVE THIS DATA SET, AND IN THIS COLUMN, THE SALES COLUMN, YOU CAN SEE I HAVE SOME BLANK CELLS. NOW, TO QUICKLY FILL THESE BLANK CELLS, I CAN DO THIS MANUALLY IN THIS DATA SET, BUT IF YOU HAVE A HUGE DATA SET, THEN DOING THIS MANUALLY MAY NOT BE PSSIBLE. SO, IN THIS CASE, YOU CAN USE THE TECHNIQUE I’M GOING TO SHOW YOU.  FIRST, SELECT THE ENTIRE DATA RANGE WHERE YOU HAVE BLANK CELLS, AND THEN USE THE F5 KEY ON YOUR KEYBOARD.  WHEN YOU HIT THE F5 KEY, IT IS GOING TO OPEN THE “GO TO” DIALOGUE BOX.  YOU CAN ALSO GET THE SAME THING HERE BY GOING TO THE HOME TAB, AND IN THE EDITING GROUP > GO TO FIND & SELECT, AND THEN CLICK ON GO TO.  NOW, IN THE GO TO DIALUGE BOX, YOU HAVE THIS BUTTON CALLED “SPECIAL”, AND WHEN I CLICK ON IT, IT WILL SHOW ME THE GO TO SPECIAL DIALOG BOX. NOW, IN THIS CASE I HAVE ALL THESE OPTIONS.  WHERE I ALSO HAVE THIS OPTION CALLED “BLANKS”. NOW, WHEN I SELECT “BLANKS” AND CLICK “OK” IT IS GOING TO SELECT ALL THE BLANK CELLS AT ONECE. SEE WHAT HAPPENES! WHEN I CLICK “OK” – ALL THESE CELLS ARE SELECTED.  NOW, THIS CELL IS IN WHITE COLOR AND THESE ALL ARE IN GRAY BECAUSE THIS IS THE ACTIVE CELL, WHICH MEANS THAT ANYTHING THAT I DO WOULD HAPPEN TO THIS CELL HERE.  SO, IN THIS CASE, IF I WANT TO QUICK ENTER, LET’S SAY, “NOT AVAILABLE”, THE NEXT “NOT AVAILABLE”  BECAUSE THE DATA IS NOT AVAILABLE YET, I CAN SIMPLY TYPE DATA, AND DO NOT PRESS THE ENTER KEY. INSTAED, HOLD THE CONTROL KEY, AND THEN PRESS THE ENTER KEY, AND SEE WHAT HAPPENS WHEN I DO THIS. I HOLD THE CTROL KEY, AND I PRESS ENTER, AND IT WOULD INSTANTLY INSERT THE SAME TEXT IN ALL THE BLANK CELLS THAT ARE SELECTED. 07. REMOVE FORMULA BUT KEEP THE DATA COMPANY REVENUE EXPENSE NOT INCOME COMPANY 01 5009 4108 901 COMPANY 02 5351 3622 1729 COMPANY 03 9133 6519 2614 COMPANY 04 1406 1545 -139 COMPANY 05 6142 5096 1046 COMPANY 06 5984 5578 406 COMPANY 07 4896 5489 -593 COMPANY 08 3732 2954 778 COMPANY 09 3642 3503 139 COMPANY 10 3345 2709 636 HOW TO QUICKLY REMOVE THE FORMULA FROM EXCEL BUT KEEP THE DATA. SO HERE I HAVE THE DATA FOR THESE COMPANIES, I HAVE THE REVENUE NUMBER, EXPENSE NUMBERS, AND THE NET INCOME VALUE, WHICH IS CALCULATED BY SUBTRACTING EXPENSE FROM REVENUE. SO, IF YOU HAVE A LOOK AT THE FORMULA, IT IS REVENUE MINUS EXPENSE, AND THAT’S THE SAME CASE FOR ALL CELLS.  NOW, WHAT I WANT TO DO IS REMOVE THE FORMULA THE FORMULA BUT STILL KEEP THE NET INCOME VALUE AND IT’S REALLY EASY. YOU SIMPLY SELECT THIS ENTIRE DATA SET, COPY THIS, THEN RIGHT CLICK AND GO TO PASTE SPECIAL. AND HERE IN THE PASTE SPECIAL DIALOG BOX YOU HAVE THE VALUE OPTION.  AS SOON AS YOU CLICK ON VALUE AND CLICK OKEY, IT IS GOING PASTE THE VALUES ONLY AND THE FORMULA WILL GO AWAY.  SO NOW AND WHEN I CLICK OKEY, SEE WHAT HAPPENS. NOW, WHEN I SELECT THE CELL, YOU CAN SEE THAT THE FORMULA BAR SHOWS THE VALUE AND NOT THE FORMULA BECAUSE THE FORMULA HAS GONE. 08. HOW TO ADD 0 BEFORE A NUMBER 10 2345 43567 912 8 59549 32 4352 84385 HOW TO ADD ZERO BEFORE NUMBERS IN EXCEL. HERE I HAVE ALL THESE NUMBERS, AND YOU CAN SEE THAT THESE ARE OF VARYING LENGTHS. SO, THE FIRST ONE HERE IS TWO DIGIT LONG, AND THIS ONE IS FOUR DIGITS, AND THIS ONE IS FIVE DIGITS. IF THESE ARE LET’S SAY EMPLOYER IDs OR RECORD NUMBERS, AND I WANT TO MAKE THESE CONSISTENT, I WANT TO MAKE THESE ALL FIVE DIGITS LONG, THEN I CAN NOT DO THIS IN EXCEL BY DEFAULT. IF I COME TO THIS CELL HERE, AND I TRY AND ADD THREE ZEROS BEFORE THIS NUMBER 10, AND I HIT ENTER, EXCEL IS GOING TO REMOVE THIS.  THERE ARE A COUPLE OF WAYS OF DOING THIS. THE FIRST ONE IS IF YOU CONVERT THIS INTO TEXT. SO HERE I COME AT THE BEGINNING  OF THIS NUMBER, I USE THE APOSTROPHE SIGN, SO I HIT THE APOSTROPHE KEY, AND NOW I ENTER ZERO THREE TIMES, AND HIT THE ENTER KEY. IT IS GOING TO ALLIGN THIS TO THE LEFT.  SO, THIS IS ONE WAY OF DOING IT, BUT IT IS ALRIGHT TO DO THIS IF YOU ONLY HAVE A FEW NUMBERS. BUT IN CASE YOU HAVE A HUGE DATA SET, THEN THIS MAY NOT BE A PRACTICAL WAY. SO LET ME REMOVE THIS AND LET ME SHOW YOU A BATTER WAY OF DOING THIS. SO, I WOULD FIRST SELECT THIS ENTIRE DATA SET. I WOULD HOLD THE CONTROL KEY AND THEN I WOULD PRESS THE 1 KEY ON MY KEYBOARD. AND THIS WOULD OPEN THE ‘FORMAT CELLS DIALOG BOX’.  YOU CAN ALSO GET THE SAME THISNG BY GOING TO THE HOME TAB AND HERE IN NUMBERS GROUP, CLICK ON THIS SMALL DIALOG BOX LAUNCHER.  AND IT ALSO OPENS THE FORMAT CELLS DIALOG BOX.  NOW, IF I COME HERE TO THE CUSTOM OPTION, IT ALLOW ME TO SPECIFY THE CUSTOM FORM. AND THIS IS THE FORMAT IN WHICH THESE CELLS WOULD BE DISPLAYED. SO, I WOULD CHANGE THIS, AND REMOVE THE GENERAL FROM HERE, AND INSTEAD INSERT 0000. AND YOU CAN SEE IN SAMPLE IT SHOWS ME HOW THESE VALUES WOULD LOOK.  WHAT I’M DOING IN THIS CASE IS BY ENTERING THE ZEROS, I AM TELLING EXCEL, I ALWAYS NEED TO HAVE MY NUMBERS DISPLAYED, WHICH ARE AT LEAST FIVE DIGIT LONG. SEE WHAT HAPPENS NOW WHEN I CLICK OKEY? IT WILL AUTOMATICALLY INSERT THE ZEROS.  NOW WHEN YOU CHANGE CUSTOM NUMBER FORMATTING, IT WOULD NOT CHANGE THE VALUE IN THE CELL. IT ONLY CHANGES HOW IT IS BEING DISPLAYED. SO, IF I SELECT THE CELL, YOU CAN STILL SEE 10 HERE. IF I SELECT THE CELL, YOU CAN STILL SEE THE ORIGINAL NUMBER, IT ONLY CHANGES HOW THESE LOOK. AND NOW YOU CAN SEE THESE LOOK CONSISTENT. 09. SAVE EXCEL CHARTS AS IMAHES (JPG/PNG) PRODUCT SALES PRINTERS 252 SCANNERS 535 PROJECTORS 256 KEYBOARDS 330 MOUSE 165  HOW TO SAVE AN EXCEL CHART AS AN IMAGE. SO HERE I HAVE A WORKBOOK WITH THREE DIFFERENT SHEETS AND EACH OF THESE SHEETS HAVE A CHART IN IT. NOW WHAT TO DO IS SAVE ONE SINGLE CHART AS AN IMAGE. SO, I WOULD CLICK ON THE CHART I WANT TO SAVE > RIGHT CLICK > GO TO COPY > AND THEN OPEN ANY OF THE MICROSOFT APPLICATIONS SUCH AS MICROSOFT WORD OR POWERPOINT OR MS PAINT, AND THEN PASTE THIS THREE AS PICTURE. SO HERE I HAVE MICROSOFT WORD. I WOULD GO TO THE PASTE OPTION HERE, CLICK ON THIS DROP DOWN, CLICK ON PASTE SPECIAL AND IT GIVES ME THESE OPTIONS WHETHER I WANT TO SAVE IT AS A PNG OR A JPG. LET’S SAY I SELECT PNG HERE. CLICK OKEY. THIS CHART IS NOW PASTED AS A PICTURE. NOW I CAN RIGHT CLICK > GO TO SAVE AS PICTURE OPTION AND THEN IT WILL OPEN THE FILE SAVE DIALOUGE BOX WHERE I CAN SPECIFY THE NAME AND SAVE THIS. IF I WANT, I CAN ALSO CHANGE THE FORMAT HERE. SO, THIS IS ONE WAY AND THIS IS A GOOD METHOD IF YOU ONLY HAVE A COUPLE OF CHARTS THAT YOU WANT TO SAVE AS AN IMAGE. BUT LET’S SAY YOU HAVE HUNDREDS OF CHARTS IN A WORKBOOK. AND YOU WANT TO SAVE ALL THESE CHARTS AS IMAGES, THEN LET ME SHOW YOU ANOTHER METHOD. SO HERE I WOULD GO TO THE FILE TAB, CLICK ON SAVE AS, THEN CLICK ON BROWSE, AND THEN I WOULD BROWSE THE FOLDER WHERE I WANT TO SAVE THESE CHARTS. SO, I WOULD GO TO THE DESKTOP. HERE AND I HAD THIS FOLDER, CHARTS AS IMAGES (ND YOU CAN SAVE IT ANYWAY YOU WANT). GIVE THIS FILE NAME AND IN SAVE AS TYPE > CHANGE THIS TO WEB PAGE.HTM OR.HTML. NOW WHEN I CLICK ON THIS OPTION > CLICK ON SAVE AND THEN CLICK YES. IT WOULD SAVE THIS EXCEL WORKBOOK AS AN HTML FILE IN THE FOLDERS. SO, IF I GO BACK TO THAT FOLDER, YOU CAN SEE > IT HAS YOUR FILE NAMED FILE OPTION > WHEN I OPENED THIS, IT’LL HAVE ALL THE CHARTS. BY DEFAULT, THIS IS GOING TO SAVE THESE CHARTS IN THE PNG FORMAT. SO, IF YOU HAVE A LOT OF CHARTS IN AN EXCEL WORKBOOK, YOU CAN EASILY GET ALL THESE CHARTS AS PICTURES BY SAVING THE FILE AS A.HTML FILE.  CAMERA TOOL PRODUCT SALES - 2023 BOURNVITA 255 LASSI 256 DETTOL 550 LIFEBUOY 147 RASNA 580 CAMERA TOOL IS A REALLY COOL FEATURE IN EXCEL – CAMERA TOOL. SO HERE, I HAVE THIS DATA IN THESE CELLS AND HERE I HAVE AN IMAGE OF THIS DATA. SO, YOU CAN SEE THIS AS AN IMAGE. BUT WHAT IS REALLY COOL ABOUT THIS IMAGE IS THAT THIS IS LINKED TO THIS DATA, WHICH MEANS THAT IF I MAKE ANY CHANGES IN THIS DATA SET, THEN IT WOULD DYNAMICALLY BE REFLECTED HARA. SO, I CAN PUT THIS IMAGE ANYWHERE IN THIS WORKSHEET AND WHENEVER I MAKE A CHANGE HERE, THAT CHANGE WOULD APPEAR IN THE IMAGE. SO, IN THIS CASE, IF I CHANGE THIS VALUE, THEN INSTANTLY THAT VALUE ALSO CHANGES IN THE IMAGE. NOW THE WAY YOU CAN USE THESE IMAGES IS THAT IF YOU HAVE, LET’S SAY, MULTIPLE WORKSHEETS ON MULTIPLE CHARTS, THEN YOU CAN CREATE THESE IMAGES AND PUT THIS SOMEWHERE IN A SUMMARY DASHBOARD. NOW WHENEVER YOU GO BACK TO THE ORIGINAL DATA AND YOU MAKE ANY CHANGES, ITWOULD AUTOMATICALLY UPDATE IN THE SUMMARY WORKSHEET. NOW LET ME SHOW YOU HOW TO USE THE ‘CAMERA TOOL’.  THE FIRST THISNG YOU NEED TO KNOW IS THAT CAMERA TOOL IS NOT AVAILABLE BY DEFAULT, YOU HAVE TO ADD IT.  SO THAT I WOULD COME HERE IN THE QUICK ACCESS TOOLBAR OPTIONS AND I WOULD ADD THE CAMERA TOOL TO THE QUICK ACCESS TOOLBAR.  SO, I WOULD COME HERE, RIGHT CLICK, GO TO MORE COMMANDS, AND HERE I WOULD SELECT ALL COMMANDS. AND NOW I WOULD SCROLL – DOWN AND LOOK FOR THE OPTION – CAMERA.  HERE I HAVE IT. NOW WHEN I CLICK ON ADD AND CLICK OKEY. YOU CAN SEE THAT THE CAMERA TOOL HAS BEEN ADDED. NOW TO USE THE CAMERA TOOL YOU SIMPLY HAVE TO SELECT THE RANGE OF WHICH YOU WANT TO TAKE THAT IMAGE  CLICK ON THE CAMERA TOOL HERE, AND THEN CLICK WHEREEVER YOU WANT THAT IMAGE TO APPEAR. SO, IF I CLICK HERE, THEN IT WOULD INSTANTLY CREATE AN IMAGE WHICH IS LINKED TO THIS ORIGINAL DATA SET.   YOU CAN MOVE THIS IMAGE AND PUT IT ANYWHERE YOU WANT. YOU ARE NOT RESTRICTED TO THE SAME WORKSHEET. YOU CAN ALSO PUT THIS IMAGE IN OTHER WORKSHEETS.  SO, FOR EXAMPLE, IF I WANT THIS DATA TO BE ON THE SUMMARY, SHEET, THEN I WOULD SELECT THIS DATA, CLICK ON CAMERA TOOL ICON, GO TO THE SUMMARY TAB, AND CLICK HERE. AND INSTANTLY IT WOULD GIVE ME THIS IMAGE. AND YOU CAN RESIZE THIS IMAGE IF YOU WANT.  YOU CAN PLACE IT ANYWHERE YOU WANT. CHART  IF YOU HAVE CHARTS AND YOU WANT TO CREATE THESE IMAGE FOR CHARTS, YOU CANNOT SELECT THE CHART AND CLICK ON THE CAMERA TOOL. IT WILL NOT BE AVAILABLE  BECAUSE A CAMERA TOOL WORKS ONLY WITH THE RANGE OF THE CELLS IN THE WORKSHEET. SO, IF YOU HAVE TO TAKE AN IMAGE OF THIS CHART, INSTEAD OF SELECTING THE CHART, YOU SELECT THE CELLS AROUND THIS CHART.  SO, IF I SELECT ALL THESE CELLS HERE, NOW I GO TO THE CAMERA TOOL, CLICK HERE, IT TAKES AN IMAGE OF THESE CELLS, BUT BECAUSE THE CHART IS ON THESE CELLS, THE IMAGE OF CHART IS ALSO TAKEN.  AND NOW I CAN COME HERE IN SUMMARY SHEET AND CLICK THE CAMERA TOOL ICON, AND YOU CAN SEE THAT I HAVE THE CHART HERE. IF YOU WANT, YOU CAN CHANGE THE IMAGE BY CROPPING IT. NOW LET ME ALSO SHOW YOU ANOTHER WAY OF DOING IT WITHOUT THE CAMERA TOOL. IT’S NICE TO HAVE THIS CAMERA TOOL HERE, IT DOES IT WITH ONE SINGLE CLICK, BUT IF YOU DON’T WANT TO ADD THIS HERE OR YOU HAVE TO DO IT A COUPLE OF TIMES, THERE IS ANOTHER WAY OF DOING IT…………………………………………  YOU SELECT THIS RANGE, YOU COPY THIS DATA, THEN YOU GO TO THE HOME TAB AND HERE IN PASTE.  YOU HAVE THIS OPTION ‘LINKED PICTURE’. AND WHEN YOU CLICK ON IT WILL CREATE A LINKED PICTURE.

Use Quizgecko on...
Browser
Browser