UNIVERSITY COLLEGE OF UNIVERSITY COLLEGE OF SCIENCESCIENCE

INTRODUCTION TO INTRODUCTION TO MICROSOFT EXCEL MICROSOFT EXCEL

GUIDED BY :GUIDED BY : KANIKA MA’AMKANIKA MA’AM PRESENTED BY :PRESENTED BY : DHAWAL MEHTADHAWAL MEHTA BCA( 1 BCA( 1STST SEMESTER)SEMESTER)

CONTENTCONTENT• Introducing excelIntroducing excel• Office buttonOffice button• Features overview and exploring of excelFeatures overview and exploring of excel• Basic mouse pointer shapesBasic mouse pointer shapes• Cell referenceCell reference• worksheetsworksheets• Find, replace and spelling checkerFind, replace and spelling checker• Entering formulas and functionsEntering formulas and functions• Autosum Autosum • Shortcut keysShortcut keys• Printing the workbook and print previewPrinting the workbook and print preview

INTRODUCING EXCELINTRODUCING EXCEL

• Microsoft Excel Microsoft Excel is a spreadsheet application developed by is a spreadsheet application developed by Microsoft for Microsoft windows operating system .Microsoft for Microsoft windows operating system .

• It It is a program used to enter, analyze, and present quantitative is a program used to enter, analyze, and present quantitative datadata

• It allows you to create professional spreadsheets and charts.It allows you to create professional spreadsheets and charts.• It features graphing tools , calculation and important tablesIt features graphing tools , calculation and important tables..

OFFICE BUTTON CONTAINS..NEW-TO OPEN NEW WORKBOOK. (CTRL+N)OPEN-TO OPEN EXISTING DOCUMENT (CTRL+O)

SAVE-TO SAVE A DOCUMENT. (CTRL+S)

SAVE AS-TO SAVE COPY DOCUMENT. (F12)

PRINT-TO PRINT A DOCUMENT. (CTRL+P)

PREPARE-TO PREPARE DOCUMENT FOR DISTRIBUTION.SEND-TO SEND A COPY OF DOCUMENT TO OTHER PEOPLE.

PUBLISH-TO DISTRIBUTE DOCUMENT TO OTHER PEOPLE.CLOSE-TO CLOSE A DOCUMENT (CTRL+W).

FEATURES OF EXCELFEATURES OF EXCEL• Graphical User InterfaceGraphical User Interface Excel is a GUI based Software most of the task performed by Excel is a GUI based Software most of the task performed by

excel can be carried out just by pressing a button in a toolbar.excel can be carried out just by pressing a button in a toolbar.• CalculationCalculation Excel is one of the fastest spreadsheet programs when you add Excel is one of the fastest spreadsheet programs when you add

or delete or modify a particular figure in between then Excel or delete or modify a particular figure in between then Excel recalculates and changes the result automatically. recalculates and changes the result automatically. 

• FunctionFunction The excel program offers several mathematical , statistical and financial along with The excel program offers several mathematical , statistical and financial along with

several date, time and logical functions.several date, time and logical functions.•   GraphsGraphs Excel also allow the graphical representation data many types of graphs may be drown Excel also allow the graphical representation data many types of graphs may be drown

like :-like :- - Bar graph- Bar graph - Line graph- Line graph - X-Y graph- X-Y graph - Pie chart- Pie chart

EXPLORING EXCELEXPLORING EXCEL

• Toolbars contain icons that perform common commandsToolbars contain icons that perform common commands• Formula bar displays the contents of the active cellFormula bar displays the contents of the active cell• located near the top of the worksheetlocated near the top of the worksheet

• Status bar keeps you informed of what is happening as you work Status bar keeps you informed of what is happening as you work located at the bottom of the worksheetlocated at the bottom of the worksheet

• Title bar Contains the name of the document you are working on at a Title bar Contains the name of the document you are working on at a given time. When we create a new workbook it is assigned provisionally given time. When we create a new workbook it is assigned provisionally the name Book 1, until we save it and give it the name we desire.the name Book 1, until we save it and give it the name we desire.

TOOLBARS

• Menu bar Contains those Excel operations gathered in dropdown Menu bar Contains those Excel operations gathered in dropdown menus. You can do all operations from these menusmenus. You can do all operations from these menus

• Standard tool bar ContainsStandard tool bar Contains icons to perform in an immediate icons to perform in an immediate way some of the operations that are most frequent like way some of the operations that are most frequent like save ,copy ,cut etc.save ,copy ,cut etc.

OVERVIEW OF EXCELOVERVIEW OF EXCEL

• A workbook is the MS Excel file in which you enter and store A workbook is the MS Excel file in which you enter and store related data. A related data. A Workbook is a collection of many worksheets.Workbook is a collection of many worksheets.

• Worksheet is made of many rows and columns and also called Worksheet is made of many rows and columns and also called spreadsheets.spreadsheets.

• There are There are 16384 columns and 1048576 rows 16384 columns and 1048576 rows in a worksheet.in a worksheet.• On the worksheet intersection of rows and column is called cell.On the worksheet intersection of rows and column is called cell.

•A column is formed by cells vertically arranged. Each column is identified by letters, for example A, B, C,... AA, AB,... IV.

•Each row is numbered, from 1 to 1048576, and is the horizontal selection of cells in a sheet.

BASIC MOUSE POINTER SHAPESBASIC MOUSE POINTER SHAPESMouse Pointer Meaning

Default Mouse PointerCursor

Auto-fill HandleRows and Columns Manual Resize

MoveVertical Resize

Horizontal ResizeDiagonal Resize 1Diagonal Resize 2

I-Beam Insertion Point

CELL REFERENCECELL REFERENCE

• Each cell has a specific address which is a combination of Each cell has a specific address which is a combination of numbers and letters. numbers and letters.

for example : A1,B10for example : A1,B10• On a worksheet thick rectangular box represents currently On a worksheet thick rectangular box represents currently

selected or active cell.selected or active cell.

CELL REFERENCECELL REFERENCE

A RELATIVE 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.

IN CELL (C1) SUM FUNCTION IS USED.THEN FUNCTION FROM CELL (C1) IS COPY TO CELL (D3).WHEN THE POSITION OF THE CELL IS CHANGED FROM (C1) TO (D3),THEN THE REFERENCE IS ALSO CHANGED FROM (A1,B1) TO (B3,C3).

Cell Refrencing

AN ABSOLUTE CELL REFERENCE AS ($A$1) 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.

IN CELL (C1) SUM FUNCTION IS USED.THEN FUNCTION FROM CELL (C1) IS COPY TO CELL (D3).WHEN THE POSITION OF THE CELL IS CHANGED FROM (C1) TO (D3),THEN THE ABSOLUTE REFERENCE REMAINS THE SAME(A1,B1).$ IS USED FOR CONSTANT ROW OR COLUMN.

Cell Refrencing

IN CELL (C1) SUM FUNCTION IS USED.THEN FUNCTION FROM CELL (C1) IS COPY TO CELL (D3).WHEN THE POSITION OF THE CELL IS CHANGED FROM (C1) TO (D3),THEN ROW REFERENCE IS CHANGED(FROM 1 TO 3) BUT COLUMN REFERENCE REMAINS SAME(A,B).

A MIXED REFERENCE HAS EITHER AN

ABSOLUTE COLUMN AND RELATIVE ROW OR ABSOLUTE ROW

AND RELATIVE COLUMN. AN

ABSOLUTE COLUMN REFERENCE TAKES

THE FORM $A1, $B1.AN ABSOLUTE ROW REFERENCE TAKES THE FORM

A$1, B$1.

Cell Refrencing

INSERTING AND DELETING A INSERTING AND DELETING A WORKSHEETWORKSHEET

• To insert a new worksheet into the workbook, right-click a sheet To insert a new worksheet into the workbook, right-click a sheet tab, click Insert on the shortcut menu, select a sheet type, and tab, click Insert on the shortcut menu, select a sheet type, and then click the OK buttonthen click the OK button

• You can delete a worksheet from a workbook in two waysYou can delete a worksheet from a workbook in two ways• You can right-click the sheet tab of the worksheet you want to You can right-click the sheet tab of the worksheet you want to

delete, and then click Delete on the shortcut menudelete, and then click Delete on the shortcut menu• You can also click the Delete button arrow in the Cells group on You can also click the Delete button arrow in the Cells group on

the Home tab, and then click Delete Sheetthe Home tab, and then click Delete Sheet

MODIFYING THE WORKSHEETMODIFYING THE WORKSHEET• Insert commandInsert command• can be used to add individual rows, columns, or cellscan be used to add individual rows, columns, or cells

• Delete commandDelete command• can be used to delete individual rows, columns, or cellscan be used to delete individual rows, columns, or cells

USING FIND AND REPLACEUSING FIND AND REPLACE

• The Find command locates data in a worksheet, which is particularly The Find command locates data in a worksheet, which is particularly helpful when a worksheet contains a large amount of data. You can helpful when a worksheet contains a large amount of data. You can use the Find command to locate words or parts of wordsuse the Find command to locate words or parts of words

• The Replace command is an extension of the Find command. The Replace command is an extension of the Find command. Replacing data substitutes new data for the data that the Find Replacing data substitutes new data for the data that the Find command locates.command locates.

ENTERING A FORMULAENTERING A FORMULA

• A A formula formula is an expression that returns a valueis an expression that returns a value• A formula is written using A formula is written using operators operators that combine different values, that combine different values,

returning a single value that is then displayed in the cellreturning a single value that is then displayed in the cell• The most commonly used operators are The most commonly used operators are arithmetic operatorsarithmetic operators

• The The order of precedence order of precedence is a set of predefined rules used to is a set of predefined rules used to determine the sequence in which operators are applied in a calculationdetermine the sequence in which operators are applied in a calculation

• Click the cell in which you want the formula results to appearClick the cell in which you want the formula results to appear• Type = and an expression that calculates a value using cell Type = and an expression that calculates a value using cell

references and arithmetic operatorsreferences and arithmetic operators• Press the Enter key or press the Tab key to complete the formulaPress the Enter key or press the Tab key to complete the formula

HOW FORMULA IS ENTEREDHOW FORMULA IS ENTERED

COPY & PASTE FORMULASCOPY & PASTE FORMULAS

INTRODUCING FUNCTIONSINTRODUCING FUNCTIONS

• A function in excel is a built-in formula that performs a mathematical A function in excel is a built-in formula that performs a mathematical operation or returns information specified by the formulaoperation or returns information specified by the formula

• Function syntax :- =function name(Argument)Function syntax :- =function name(Argument) For example :- =SUM(A1,C3)For example :- =SUM(A1,C3)• .You can easily calculate the sum of a large number of cells by using .You can easily calculate the sum of a large number of cells by using

a functiona function

• The SUM function is a very commonly used math function in Excel. The SUM function is a very commonly used math function in Excel.

• A basic formula example to add up a small number of cells is A basic formula example to add up a small number of cells is =A1+A2+A3+A4, but that method would be cumbersome if there =A1+A2+A3+A4, but that method would be cumbersome if there were 100 cells to add up. were 100 cells to add up.

• Use Excel's SUM function to total the values in a range of cells like Use Excel's SUM function to total the values in a range of cells like this: SUM (A1:A100)this: SUM (A1:A100)

FUNCTION DIALOG BOXFUNCTION DIALOG BOX

ENTERING FUNCTIONS WITH ENTERING FUNCTIONS WITH AUTOSUMAUTOSUM

• The The AutoSum AutoSum button quickly inserts Excel functions that button quickly inserts Excel functions that summarizes all the values in a column or row using a single statisticsummarizes all the values in a column or row using a single statistic• Sum of the values in the column or rowSum of the values in the column or row• Average value in the column or rowAverage value in the column or row• Total count of numeric values in the column or rowTotal count of numeric values in the column or row• Minimum value in the column or rowMinimum value in the column or row• Maximum value in the column or rowMaximum value in the column or row

PARTICULARS

EDIT THE ACTIVE CELL CREATE A CHART INSERT CELL COMMENT FUNCTION DIALOG BOX INSERT A NEW WORKSHEET NAME MANAGER DIALOGUE BOX VISUAL BASIC EDITOR MACRO DIALOG BOX HIDE THE SELECTED COLUMNS UNHIDE THE COLUMNS HIDE THE SELECTED ROWS UNHIDE THE ROWS SELECT ALL CELLS WITH COMMENT

KEYSF2 F11 SHIFT + F2 SHIFT + F3 SHIFT + F11 CTRL + F3 ALT + F11 ALT + F8 CTRL + 0CTRL + SHIFT + 0CTRL + 9CTRL + SHIFT + 9CTRL + SHIFT + O

PARTICULARS DOWN FILL RIGHT FILL ENTER SUM FUNCTION IN CELL EURO SYMBOL CENT SYMBOL POUND SYMBOL YEN SYMBOL ENTER NEW LINE IN ACTIVE CELL CURRENT DATE CURRENT TIME SHOW FORMULA SELECT ENTIRE COLUMN SELECT ENTIRE ROW

KEYSCTRL + DCTRL + RALT + =ALT + 0128ALT + 0162ALT + 0163ALT + 0165ALT + ENTERCTRL + ;CTRL + SHIFT + ;CTRL + `CTRL + SPACEBARSHIFT + SPACEBAR

PARTICULARS

APPLIES NUMBER FORMAT APPLIES CURRENCY FORMAT APPLIES PERCENTAGE FORMAT APPLIES EXPONENTIAL FORMAT APPLIES GENERAL NO. FORMAT APPLIES TIME FORMAT APPLIES DATE FORMAT APPLIES OUTLINE BORDER REMOVE OUTLINE BORDER

KEYSCTRL + SHIFT + !CTRL + SHIFT + $CTRL + SHIFT + %CTRL + SHIFT + ^CTRL + SHIFT + ~CTRL + SHIFT + @CTRL + SHIFT + #CTRL + SHIFT + &CTRL + SHIFT + _

PRINTING THE WORKBOOKPRINTING THE WORKBOOK• You can print the contents of your workbook by using the Print You can print the contents of your workbook by using the Print

command on the Office Buttoncommand on the Office Button• The Print command provides three options:The Print command provides three options:• You can open the Print dialog box from which you can specify the You can open the Print dialog box from which you can specify the

printer settings, including which printer to use, which worksheets printer settings, including which printer to use, which worksheets to include in the printout, and the number of copies to printto include in the printout, and the number of copies to print

• You can perform a Quick Print using the print options currently You can perform a Quick Print using the print options currently set in the Print dialog boxset in the Print dialog box

PRINT PREVIEWPRINT PREVIEW• The print preview is a tool that permits us to visualize our sheet The print preview is a tool that permits us to visualize our sheet

before printing it. It permits us to see the skips in the page, the before printing it. It permits us to see the skips in the page, the margins, the headers and footers, and the complete sheet formatmargins, the headers and footers, and the complete sheet format

• To visualize the print preview, follow these next steps:-To visualize the print preview, follow these next steps:-

1.1. Select the File menu.Select the File menu.

2.2. Then select the Print preview option. If this option does not Then select the Print preview option. If this option does not appear, extend the menu.appear, extend the menu.

Or, click on the Print preview button on the toolbarOr, click on the Print preview button on the toolbar

Questions