MSExcel XP Lesson 2 Handling Worksheets 1 Bottom

MS-Excel XP Lesson 2

Handling Worksheets 1. Bottom of the every workbook you can get worksheets. 2. No of sheets for a book is three. But you can add, remove and delete any no of sheets. 3. This can be done by right clicking the sheet tab 1. 2. 3. 4. 5. Insert Delete Rename Move or copy Select all sheets

Renaming a Worksheet 1. Click on format menu 2. Select sheet sub menu and click on rename menu item 3. Type the text for name of a worksheet 1. Right click on worksheet tab 2. Click rename menu item 3. Type the text for name of a worksheet

Inserting a Worksheet 1. Click on insert menu 2. Click on worksheet menu item 3. New worksheet inserted to left from current worksheet 1. 2. 3. 4. Right click on worksheet tab Click insert menu item Select worksheet from general tab sheet Click on ok button

Deleting a Worksheet 1. Click on edit menu 2. Click on delete sheet menu item 1. Right click on worksheet tab 2. Click delete menu item

Moving and/ or Coping a Worksheet 1. Click on edit menu 2. Click on move or copy sheet menu item 3. Select current workbook or new workbook for to book field 4. Select sheet to before sheet field 5. Select or cancel create a copy option 6. Click on ok 7. Right click on sheet and click on move or copy menu item

Data Entering 1. 2. 3. 4. Select cell and type data in formula bar To edit data, select cell and use formula bar A 1 10 Use fill handle, drag top to bottom or left to right 5. A 1 10 A 2 11 Select A 1 and A 2 cell and using fill handle drag and fill the data 6. Use fill handle 1. A 1 Sunday or Sun 2. B 1 January or Jan

Data Entering 1. A 1 10 A 2 =A 1+2 Select A 2 cell and use fill handle to entering data 2. To delete data in cells, select cell range and press delete button or edit, clear, contents or right click, clear contents

Create a Custom List 1. Tools menu, Options menu item, Custom Lists tab sheet 2. Select new list to custom lists 3. Type list entries and create a new list eg. 1 11 111 4. Click add, ok 5. A 1 1 st item of new list eg. 1 6. Use fill handle and enter the data

Writing Formulas 1. 2. 3. 4. 5. 6. 7. 8. =A 1+A 2 =A 1+10 =A 1+A 2+A 3 =A 1*5 =A 1 -5 =A 1/5 =C 1+A 1+D 1 =A 3+B 4 -C 3

Cell References 1. Cell references 1. Relative eg. =A 1+A 2 2. Absolute eg. =$A$1+$A$2 3. Mixed (Relative+ Absolute) eg. =A$1+$A 2

A 1 10 B 1 50 A 2 20 B 2 60 A 3 30 B 3 70 A 4 40 B 4 80 A 5 =A 1+A 2+A 3+A 4 B 5 Select A 5 and drag fill handle to A 5 to B 5 (Relative cell reference) Then A 5 =$A$1+$A$2+$A$3+$A$4 B 5 Select A 5 and drag fill handle to A 5 to B 5 (Absolute cell reference)

Functions 1. Insert menu, Function menu item 1. 2. 3. 4. 5. 6. 7. 8. 9. Financial functions Date & Time functions Math & Trig functions Statistical functions Lookup & Reference Text functions Database functions Logical functions Information functions

SUM Function 1. 2. 3. 4. 5. 6. 7. Math & Trig functions Adds all the numbers in a range of cells =SUM(A 1+A 2) =SUM(A 1: A 2) =SUM(A 1+B 3) =SUM(A 1: B 2, A 5: B 8) Insert, Function, Select category as a Math & Trig, Select SUM function, click ok, Select ranges to number 1 and/ or number 2 fields, click ok 8. You can use auto sum button in standard tool bar and use sum function

MIN Function 1. Statistical functions 2. Returns the smallest number in set of values and ignores logical values and texts 3. =MIN (A 1: B 3) 4. =MIN (A 1: B 2, A 5: B 8) 5. Insert, Function, Select category as a Statistical, Select MIN function, click ok, Select ranges to number 1 and/ or number 2 fields, click ok 6. You can use auto sum button in standard tool bar and use min function

MAX Function 1. Statistical functions 2. Returns the largest number in set of values and ignores logical values and texts 3. =MAX (A 1: B 3) 4. =MAX(A 1: B 2, A 5: B 8) 5. Insert, Function, Select category as a Statistical, Select MAX function, click ok, Select ranges to number 1 and/ or number 2 fields, click ok 6. You can use auto sum button in standard tool bar and use max function

COUNT Function 1. Statistical functions 2. Counts the number of cells that contain numbers and numbers within the list of arguments 3. =COUNT (A 1: B 3) 4. =COUNT(A 1: B 2, A 5: B 8) 5. Insert, Function, Select category as a Statistical, Select COUNT function, click ok, Select ranges to number 1 and/ or number 2 fields, click ok 6. You can use auto sum button in standard tool bar and use count function

AVERAGE Function 1. Statistical functions 2. Returns the average (arithmetic mean) of it’s arguments, which can be numbers or names, arrays or references that contain numbers 3. =AVERAGE (A 1: B 3) 4. =AVERAGE (A 1: B 2, A 5: B 8) 5. Insert, Function, Select category as a Statistical, Select AVERAGE function, click ok, Select ranges to number 1 and/ or number 2 fields, click ok 6. You can use auto sum button in standard tool bar and use average function