Ms excel 2007 pptx

L-I ITSS

1. Microsoft Excel
1

2. Introduction
Excelis a spreadsheet program in the Microsoft
Office system.
ASpreadsheet is a grid divided into columns
and rows.
The intersection of a column and row is referred
to as a cell.
2

3. Why we use Excel?
YoucanuseExceltocreateandformatworkbooks(a
collectionofspreadsheets)inordertoanalyzedataand
makemoreinformedbusinessdecisions.
Specifically,youcanuseExcel:
Tobuildmodelsforanalyzingdata
Towriteformulastoperformcalculationsonthatdata
Topivotthedatainnumerousways
Topresentdatainavarietyofprofessionallookingcharts
3

4. Method 1
1.Click the Start button
2.Click All Programs
3.Click Microsoft Office
4.Click Microsoft Office
Excel 2007
4
HOW TO EXPLORE EXCEL 2007?
Method2
1.Click the Start button
2.On the start menu, Click
Microsoft Office
Excel2007
Method 3
1.Right click on the Desktop
2.click New
3.Click Microsoft Office
Excel2007
4.Double click the file
Method 4
1.Press Log + R
2.Write Excel
3.ThenOk.

5. TO OPEN AN EXISTING FILE
1.Click the Office Button
2.Click Open
3.In the Open dialog box, navigate to the folder that contains the
file you want to open, and thenselect the file
4.Click Open
OR
1.Click the Office Button
2.Click Open
3.In the Open dialog box, navigate to the folder that contains the
file you want to open, and thenDouble-click the file
5

6. TO CREATE A NEW WORKBOOK
1.Click the Office Button
2.Click New
3.In the New Workbook window, SelectBlank Workbook
4.Click Create
OR
1.Click the Office Button
2.Click New
3.In the New Workbook window, Double-clickBlank Workbook
6

7. TO SAVE A WORKBOOK FOR THE FIRST TIME
1.Click the Office Button
2.Click Save/Save As
3.In the Save As dialog box navigate the locationwhere you want
to save the file
4.In the File name box, type a name for the document
5.Click Save
OR
1.On the Quick Access Toolbar, click the Savebutton
2.In the Save As dialog box navigate the locationwhere you want
to save the file.
3.In the File name box, type a name for the document
4.Click Save.
7

8. TO CREATE A NEW FOLDER WHILE SAVING A WORKBOOK
Click the Office Button
Click Save As
In the Save As dialog box, navigate to the folder where you want to
create the new folder
On the dialog box’s toolbar, click the New Folder button
Type the name of the new folder, and then press “Enter” key
Click Open
In the File name box, type a name for the Workbook
Click Save
8

9. Excel Working Environment
9

10. Naming Worksheet
Right Click on the Sheet Tab
Click on Rename
Type the new name for the sheet
Press “Enter” key
OR
Double-Click on the Sheet Tab
Press “Delete” or “Backspace” Key
Type the New Name
Press “Enter” key
10

11. 11
Adding Worksheet
Click one of the worksheet tab
On the home tab, in the cells
group, click insert button
Then click Insert Worksheet
OR
Right Click on one of
the worksheet tab
Click Insert
Select worksheet
Click OK
click Insert Worksheet
Simple
Method
Press
From
Keyboard
Shift +
F11

12. 12
Deleting Worksheet
Click one of the worksheet tab you
want to Delete
On the home tab, in the cells group,
click Delete button
Then click Delete Sheet
OR
Right Click on one of the worksheet
tab you want to Delete
Click Delete

13. Adjusting Column width & Row Height
Placethecursoronthelinebetweentworowsorcolumns.
Asymbolthatlookslikealowercasewitharrowsonthe
horizontallinewillappear
Dragtheboundaryontherightsideofthecolumn/row
headinguntilthecolumn/rowisthewidththatyouwant.
13

14. 14
Merging Cells
Selecttwoormoreadjacentcellsthatyouwanttomerge
OntheHometab,intheAlignmentgroup,clickMergeand
Centerbutton.
ClickMergecells
To Display multiple lines of text inside a cell
Clickthecellinwhichyouwanttowrapthetext.
OntheHometab,intheAlignmentgroup,clickWrap
Textbutton.

15. 15
Cell Borders
Selectthecellorrangeofcellsthatyouwanttoaddaborder
OntheHometab,intheFontgroup,Clickthearrownextto
Borders.
ClickontheborderstyleyouwouldliketoApply
Remove Cell Borders
OntheHometab,intheFontgroup,Clickthearrownextto
Borders.
ClickNoBorder

16. 16
Entering Data to worksheet
Click the cell where you want to enter data
Type the data in the cell
Press Enter or Tab key to move to the next cell
Exercise1
Enter the following Data to your worksheet/Spreadsheet

17. 17

18. 18
Working with
Formula &
Function
Argument

19. Sum
SUM(First cell : Last cell)
SUM(A1:A5)
(or) SUM(A1 + A2+A3+A4+A5)
SUM(A1:B1)
SUM(A1+B1)
Average
AVERAGE(First cell : Last cell)
AVERAGE(A1:A5)
AVERAGE(A1:B1)
19
Minimum
MIN(First cell : Last cell)
MIN(A1:A5)
MIN(A1:B1)
Maximum
MAX(First cell : Last
cell)
MAX(A1:A5)
MAX(A1:B1)

20. Exercise 2
20

21. Rank
RANK(Number, reference)
RANK(A8,$A$8:$A$23)
=RANK(I8,$I$8:$I$23)
Logical Function (IF)
IF(Logical test, value if TRUE, value if FALSE)
Grade
IF(A1>=85,"A",IF(A1>=70,"B",IF(A1>=50,"C",IF(A1>=30,"D","F"))))
=IF(I8>=85,"A",IF(I8>=70,"B",IF(I8>=50,"C",IF(I8>=30,"D","F"))))
21

22. Exercise 3
22
1.Give Rank for the Students
2.Compute Grade of each Students
3.Give Remark
A>=85
B>=70
C>=50
D>=30
F<30
A Excellent
B Verygood
C Satisfactory
D Poor
F Fail

23. 23

24. 24
=IF(K8="A","Execellent",IF(K8="B","Very
Good",IF(K8="C","Satisfactory",IF(K8="D","Po
or","Fail"))))

25. Count
COUNTIF(range, criteria)
COUNTIF(K8:K23,"A")
Sorting
On the home tab, in the Editing group, click Sort & Filter button
Then click Custom Sort
On the sort dialog box, select the options
Filtering
On the home tab, in the Editing group, click Sort & Filter button
Then click Filter
25

26. Exercise 4
26
Calculate:
1.Pension
2.Tax
3.Net salary
Pension = gross salary * 0.05
Tax deduction: If gross salary
>=3500, tax = 35%
>=2500, tax = 30%
>=1250, tax = 20%
>=650, tax = 15%
>=150, tax = 10%
< 150, tax = 0%
of each employee
Net Salary
=
gross salary
–
(pension +Tax)

27. Sort the employees based on their Net Salary
Filter the name of the employees whose net salary is >=2000
Filter/find top 5 highest paid employees
Filter/find bottom3 lowest paid employees
27

28. 28

29. Formatting Cells
29
Format Text Alignment
Format a cell value as phone number
Format cell data as a currency value
Select a foreign currency symbol

30. 30