eb0394e61881a96afd7d89446d4c0a7d

cyaz1AEA1) cr or ICA' TO ICMSEXCEL SUBMITTED BY MRIDUL BANSAL CRO-0407449 BATCH-154 —--——.--"StjÉÅiTTED TO MR SANDEEP TYAGI (ITT FACULTY).

a INTRODUCTI OVERVIEW O OFFICE BU RIBBONS WORKING W FORMATTIN CONDITION TO INSERT R EDITING - Fl L. SORTING CELL REFER FUNCTIONS FUNCTION SHORTCUT Ilsnr-x N TO EXCEL EXCEL ON H CELLS 7-8 TEXT 9-11 FORMATTING WS & COLUMNS 14 15 16 CING DITING YS 12-13 17-19 • 20-26 28-30 1719/2013.

us-EXCEL Excel is a c puter program used to create electronic spreadsheet . user can organize data ,create chart and Within exce perform cal ulations. Excel js-a c venient program because it allow user to create large preadsheets, reference information, and it allows for b tter storage of information. Excels oper es like other Microsoft(MS) office programs and has ma of the same functions and shortcuts of other MS pr rams..

cvrnvurw cr Home 05 tnnd Snkl Microsoft bcel Laput Oat* f,] CELLD5 c' CELL Ds 5 9 10 Microsoft excel consists of workbooks. Within each workbook, there is an infinite number of worksheets. Each worksheet contains Columns and Rows. Where a column and a row intersect is called a cell. For e.g. cell D5 is located where column D and row 5 meet. The tabs at the bottom of the screen represent different worksheets within a workbook. You can use the scrolling buttons on the left to bring other worksheets into view..

orr•cr curr0N OFFICE BUTTON CONTAINS.. NEW-TO OPEN NEW WORKBOOK. — (CTRL*N) PEN-TO OPEN EXISTING DOCUMENT (CTRL+O) AISNE-TO SAVE A DOCUMENT. (CTRL+S) SAVE AS-TO SAVE COPY DOCUMENT. (F12) PRINT-TO PRINT A DOCUMENT. (CTRL+P) PREPARE-TO PREPA SEND-TO SEND A C PUBLISH-TO DISTRIB CLOSE-TO CLOSE Å DOCUMENT FOR DISTRIBUTION. OF DOCUMENT TO OTHER PEOPLE. E DOCUMENT TO OTHER PEOPLE. CUMENT (CTRL+W)..

THE THREE PARTS QI nncss OF THE RIBBON ARE V'nv C.iibri Copy Paste Font TAOS:THERE AR SEVEN TABS ACROSS THE TOP OF THE EXCEL WIN ow. cacwvs: GROU S ARE SETS OF RELATED COMMANDS,DISP YED ON TABS. OMMAND IS A BUTTON,Å MENU OR A BOX WHE YOU ENTER INFORMATION..

crus Home U buert Page uyout Fomuns cut , Copy Paste V Pwnter Caiibn y FormåtP•wrttet (SMART PERFECT 2 'AwÉSOMÉ OASEING (GORGEOUS ATTRACTIVE suPEFB SMART 2 AWESOME 'GORGEOUS Font BEAUTIFUL PERFECT DASHING HANDSOME ATTRACTIVE supERB OASHING TO COPY AND P STE CONTENTS: Selecg the cell or cel Click the Copycomm Select the cell or ce Click the Paste com The copied informat you wish to copy. nd in the Clipboard group on the Home tab. s where you want to paste the information. nd. n will now appear in the new cells..

Home Llnsegt Copy Paste Format painter Page Oyout Formutu Calibri crus paste Painter C"øb0å'd SMART 2 AWESOME 3 IGORGEOUS AWESOME c PERFECT BEAUTIFUL DASHING 2 3 CliDboard SMART GORGEOUS Font ß AWESOME BEAUTIFUL PERFECT DASHING HANDSOME ATTRACTIVE suPERB wz-saME To Cut and Pas e Cell Contents: s you wish to cut. Select the cell or ce -Click •the Cut comm Select the cell or ce Click the Paste com The cut information d in the Clipboard group on the Home tab. s where you want to paste the information. and. •II be removed and now appear in the new cells..

Home Copy Data TO FORMAT TEX IN BOLD ITALICS OR UNDE LINE: Left-click a cell to sele it or drag •your cursor over the te in the formula, bar to select it Click the Bold, •Italics underline command. O Cure Aha roni O ALGERIAE TO CHANGE THE FONT STYLE: Select the cell or cells you want to format. Left-click the drop-down arrow next to the Font Style box on the Home tab. Select a font style from the list..

Home U intert Cut -u Copy Page Layout Cilibti torn* P»nter -ü•ard To CHANGE TH Select the cell or cell format. Left-click the drop-d to the Font Size box Select a font size fro 12 FONT SIZE: you want to n arrow next the Home tab. the list. eqttom Esght Outiidt TO ADD A BORDER: Select the cell or cells you want to format. Click the drop-down arrow next to the Borders command on the Home tab. A menu will appear with border options..

Ttxr tn«rt cut rmst TO CHANGE THE Select the cell or cells Left-click thedrop-do the Text Color comma Will appear. Select a color from th Vies. Theme Cohes EXT COLOUR: ou want to format. n arrow next to d. A color palette palette. 'liiii'i"' More TO ADD A FILL COLOUR: Select the cell or cells you want to format. Click the Fill command. A color palette will appear, Select a color from the palette. 1719/2013.

CONDITIONAL TO APPLY CONDITIONAL FORMATTING: Select the cells you would like to format. Select the Home tab, Locate the Styles group. Click the Conditional Formatting command. A menu will appear with your formatting options. TO REMOVE CONDITIONAL FORMATTING: Click the Conditional Formatting command. Select Clear Rules. Choose to clear rules from the entire worksheet or the selected cells..

CONDITIONAL Ruk TZO TO APPLY NEW FO Click the Conditional —command. Select New the menu. There are d rules, you can apply t jdjfferentiate Particula TTING: rmatting ules from ferent se rules to cell. TO MANAGE CONDITIONAL FORMATTING: Click the Conditional Formatting command, Select Manage Rules from the menu. The Conditional Formatting Rules Manager dialog box will appear. From here you can edit a rule, delete a rule, or change the order of rules. 13.

w msrnr & CCLCU&iS E Autos...."" - A Format table • cen V tme•rf Delete Formot Puert Cells.„ Fin - Q aear sort & Finer Find & Select • Insert sneet Bows Sheet columns bVert Sheet 1. 2. The new row always appears above the selected row. The new column always appears to the left of the selected column. TO INSERT RO Select the •row belo Click the Insert co appear. To Insert Colu *Select the colümn t Click the Insert com will appear, s: where you want the new row to appear. and in the Celts group on the Home tab. The row will ns: the right of where you want the column to appear. and in the Cells group on the Home tab, The column 14.

IN THE LOWER RIGHT HAND CORNER OF THE ACTIVE CELL IS EXCEL'S "FILL HANDLE" .WHEN YOU HOLD YOUR MOUSE OVER THE TOP OF IT, YOUR CURSOR WILL TURN TO A CROSSHAIR. IF YOU HAVE JUST ONE CELL SELECTED, IF YOU CLICK AND DRAG TO FILL DOWN A COLUMN OR ACROSS ROW, IT WILL COPY THAT NUMBER OR TEXT TO EACH OF THE OTHER CELLS. IF YOU HAVE TWO CELLS SELECTED, EXCEL WILL FILL IN A SERIES. IT WILL COMPLETE THE PATTERN.FOR EXAMPLE,IF YOU PUT 4 AND 8 IN TWO CELLS SELECT THEM,CLICK AND DRAG THE FILL HANDLE ,EXCEL WILL CONTINUE THE PATTERN WITH 12, 16,20.ETC. EXCEL CAN ALSO AUTO- FILL SERIES OF DATES, TIMES, DAYS OF THE WEEK, MONTHS. 15.

d a 7 3 6 2 4 Sart & oner 7. •Select Sort Z Cust g— d 1 2 5 6 scousc TO SORT IN ALPHABETICAL ORDER: Select a cell in the column you want to sort (In this example, we choose a cell in column Q). Click the Sort & Filter command in the Editing group on the Home tab. Select Sort A to Z. Now the information in the Category column is organized in alphabetical order. TO SORT FROM SMALLEST TO LARGEST: Select a cell in the column you want to sort (In this example, We choose a cell in column Q). Click the Sort & Filter command in the Editing group on the Home tab. Select From Smallest to Largest. Now the information is organized from the smallest to largest amount..

cru-L nrrrnrscmc RELATIVE REFERENCE A RELATIVE 3 c -A1*B1 3 5 2 3 IN CELL SUM FUN THEN FUNCTION FROM WHEN THE POSITION O (Ci) TO TH FROM (A1,B1) TO (83, 2 ION IS USED. ELL (Cl) IS COPY TO CELL 03). THE CELL IS CHANGED FROM REFERENCE IS ALSO CHANGED 2 3 283•C3 4 5 CELL REFERENCE AS (A1) IS BASED ON THE RELATIVE POSITION OF THE CELL. IF THE POSITION OF THE CELL THAT CONTAINS THE REFERENCE CHANGES, THE REFERENCE ITSELF IS CHANGED. 17.

cru-L nrrrnrscmc ABSOLUTE REFERENCE 3 c -SASI*Sg$1 3 5 2 2 3 IN CELL SUM FUN THEN FUNCTION FROM WHEN THE POSITION O TO THE ABS IS USE 3 3 ION IS USED. ELL (Cl) IS COPY TO CELL 03). THE CELL IS CHANGED FROM (Cl) LUTE REFERENCE REMAINS THE FOR CONSTANT ROW OR COLUMN. ,SB$I 3 4 C 5 6 AN ABSOLUTE CELL REFERENCE AS (SASI) ALWAYS REFERS TO A CELL IN A SPECIFIC LOCATION. IF THE POSITION OF THE CELL THAT CONTAINS THE FORMULA CHANGES, THE ABSOLUTE REFERENCE REMAINS THE SAME. 719/2013.

cru-L nrrrnrscmc MIXED REFIÄRQNCE g 3 2 3 c -SAI+$BI 3 2 5 IN CELL SUM FUN THEN FUNCTION FROM WHEN THE POSITION O ION IS USED. ELL (Cl) IS COPY TO CELL 03). THE CELL IS CHANGED FROM REFERENCE IS CHANGED(FROM 1 TO 3) BUT COLUMN RE ERENCE REMAINS a c D 2 4 3 6 A MIXED REFERENCE HAS EITHER AN ABSOLUTE COLUMN AND RELATIVE ROW OR ABSOLUTE ROW AND RELATIVE COLUMN. AN ABSOLUTE COLUMN REFERENCE TAKES THE FORM SAI, SBI .AN ABSOLUTE ROW REFERENCE TAKES THE FORM ASI, BSI. 9/2013.

ruscrrcss DATEDIF FUNCTION! MY DATE OF TODAY'S DATE NO. OF DAYS NO. OF MONTHS NO, OF YEARS NO. OF YEARS MONTHS OF YEAR DAYS OVER MONTH - DAYS 23/06/1993 10/01/2013 FUNCTIONS -DATEOlF(Bi.ui'YM") c RESULTS 7141 234 19 19 6 18 MONTHS YEARS MONTHS OVER YEAR DAYS OV R MONTH SYNTAX OF DATEDIF START DATE- Date from which u want to calculate difference. END DATE. Date up to which u want to calculate difference. INTERVAL. Form in which u want to calculate difference. This says that I 19 years 6 months 18 days old 20.

rtJNcrrcss SUMIF 5 1 3 9 4 2 FUNCTION WITHOUT SUM_RÅNGE 3 7 1 6 2 RESULT 10 16 WITH SUM_RANGE SYNTAX OF SUMIF RANGE- Range of cells on which conditions are applied. CRITERIA- Condition that defines which cell or cells will be added. SUM RANGE. Actual cells to sum. NOTE:- If sum range is not used then range is used for sum. 21.

ruscrrcss 5 .4 IF FUNCTION FUNC ION t"FALSE") "FALSE") IFIA2>+20, SYNTAX OF IF -IF(LOGICALTEXT, VALUE IF TRUE, VALUE IF FALSE) RE ULT F LSE LSE RUE 20 LOGICAL TEXT- Any value or expression that can be evaluated to TRUE or FALSE. VALUE IF TRUE- Value that is returned if logical text is TRUE. VALUE IF FALSE- Value that is returned if logical text is FALSE. IN COLUMN B DIFFERENT CONDITIONS ARE USED AND BASED ON THIS, IN COLUMN C DIFFERENT RESULTS ARE SHOWN..

COUNT SYNTAX OF FUNCTIONS 1 2 3 4 5 6 7 8 9 10 3 5 8 0 FUNCTIONS c RESULT 4 8 2 3 2. 3. 4. COUNT -COUNT(VÅLUEI COUNTA COUNTBLANK -COUNTBLANK(RANGE) COUNTIF COUNT ONLY CELLS THAT CONTAINS NUMBER. 2. COUNT CELLS THAT ARENOT EMPTY. 3. COUNT CELLS THAT ARE BLANK. 4. COUNT NO. OF CELLS THAT MEET GIVEN SONDITION..

FUSCTICSS c UPPER FUNCTION FUNCTION FUNCTION SMART BEAUTIFUL DASHING GORGEOUS PERFECT EXCELLENT AWESOME 8 LOWER smart beautiful dashing gorgeous perfect excellent awesome SYNTAX OF FUNCTIONS 1 2 3 4 5 6 7 8 SmaRt BeautiFul Dashing GorgeOus PerfEct ExcellEnt Awesome PROPER Smart Beautiful Dashing Gorgeous Perfect Excellent 2. TO CONVERT TEXT FROM SMALL TO CAPITAL. 2. 3. TO CONVERT TEXT FROM CAPITAL TO SMALL. LOWER FUNCTION -LOWER(TEXT) UPPER FUNCTION -UPPER(TEXT) PROPER FUNCTION -PROPER(TEXT) 3. TO CAPITALISED EACH WORD OF.

EUSCTICSS c RIGHT SYNTAX OF FUNCTIONS LEFT MID 1 2 4 5 7 8 9 FUNCTION FUNCTION FUNCTION smart beautiful dashing gorgeous perfect excellent awesome bea gor per exc awe art ful ing ous mar eau ash xce wes 2. 3. RETURN SPECIFIED NO. OF CHARACTER FROM START OF TEXT. 2. RETURN SPECIFIED OF CHRACTE& Ft0M END OF TEXT. LEFT FUNCTION RIGHT FUNCTION MID FUNCTION 3. RETURN CHARACTER FROM MIDDLE OF TEXT,GIVEN A STARTING POSITION..

FUNCTIONS RESULTS = NOW] USES OF FUNCTIONS NOW RETURNS CURRENT DATE AND TIME. TODAY RETURNS CURRENT DATE ONLY. 4 5 6 9 10 11 15 16 14/01/2013 ors; 14/01/2013 1 9 5 MOD LEN SUM RETURNS THE REMAINDER AFTER NO. IS DIVIDED BY A DIVISOR. RETURNS THE NO. OF CHARACTERS IN A TEXT STRING. ADD ALL THE NUMBERS. 26.

TRACE PREC DENTS c (A1+A3) 7 TRACE DEPE DENTS 5 6 7 c M(AJ+A3) M(A1+A4) SHOW ARROW THAT INDICATE WHAT CELLS AFFECT THE VALUE OF THE CURRENTLY SELECTED CELL. IN THIS EXAMPLE CELLS A1 & A3 AFFECT THE VALUE OF CELL Cz & CELLS A1 & A4 AFFECT THE VALUE OF CELL C6. SHOW ARROW THAT INDICATE WHAT CELLS ARE AFFECTED BY THE VALUE OF THE CURRENTLY SELECTED CELL. IN THIS EXAMPLE CELL C2 a C6 ARE AFFECTED BY THE VALUE OF CELL & CELL C6 IS ALSO AFFECTED BY THE CELL A4..

SI-ICQTCUT KEYS PART CULARS EDIT THE CTIVE CELL CREATE A a HART INSERT C LL COMMENT FUNCTIO DIALOGUE BOX INSERT EW WORKSHEET NAME MA GER DIALOGUE BOX VISUAL EDITOR MACRO Dl LOGUE BOX HIDE THE ELECTED COLUMNS UNHIDE T E COLUMNS HIDETHE ELECTED ROWS UNHIDE T E ROWS SELECT A KEYS 2 3 11 CTRL + ALT + F8 CTRL + O CTRL + SHIFT + O CTRL + 9 CTRL + SHIFT + 9 CELLS WITH COMMENT CTRL + SHIFT + O.

a SI-ICQTCUT KEYS PARTI ULARS DOWN Fl RIGHT Fl FUNCTION IN CELL ENTER S EURO sy BOL CENT SY BOL POUNDS MBOL YEN ENTER N LINE IN ACTIVE CELL CURREN DATE CURREN IME SHOW FO ULA SELECT E TIRE COLUMN SELECT E TIRE ROW KEYS CTRL D CTRL + R ALT + = ALT + 0128 ALT + 0162 ALT + 0163 ALT + 0165 ALT + ENTER CTRL + ; CTRL + SHIFT + ; CTRL + CTRL + SPACEBAR SHIFT + SPACEBAR 29.

a SI-ICQTCUT KEYS PART CULARS APPLIES MBER FORMAT APPLIES C RRENCY FORMAT APPLIES P RCENTAGE FORMAT APPLIES E PONENTIAL FORMAT APPLIES G NEUL NO. FORMAT APPLIES T E FORMAT APPLIES D TE FORMAT APPLIES O TLINE BORDER REMOVE TLINE BORDER KEYS CTRL + SHIFT + ! CTRL + SHIFT + S CTRL + SHIFT + % CTRL + SHIFT + CTRL + SHIFT + CTRL + SHIFT + @ CTRL + SHIFT + # CTRL + SHIFT + & CTRL + SHIFT +.