1

MS- EXCEL

Introduction- Microsoft Excel is an application software of MS – Office

package. Its Extension name is .XLS. The page of Excel is called work sheet. Each
work sheet is divided into rows and columns. Every sheet has 10, 48, 576 Rows and
16,384 Columns. The intersection point of Rows and Columns make cells. Each cell
has a fixed address which is denoted by column header (A, B, C, D, E…………) and
row header (1, 2, 3, 4……..)
Excel is mainly use for calculation work, making
result, making graph/chart, calculate the future value amount, loan installment
calculation, and filter the particular data from data table.
Syllabus:-
1. Pointer types
2. Function (1)Number Function (2)Text function (3)Date function
3. Custom list
4. Making result
5. Data validation
6. Conditional formatting
7. Graph / Chart
8. Filter
9. Advance filter
10. Fill
11. Define name
12. Formula Auditing
13. Salary sheet / Pay roll 22. Sheet Tab color ,Sheet Tab Rename
14. Sort 23. Sheet Background setting
15. Sub – total 24. Pivot table
16. Attendance sheet 25. Freeze panes
17. Consolidate 26. Protect sheet
18. Future value 27. Protect workbook
19. Goal seek 28. Page break preview
20. Scenario 29. Print out of excel sheet
21. P.M.T. 30. Project work

How to start MS- Excel

1. Start MS- EXCEL 2007
2. Start All programs MS-Office MS- EXCEL 2007

3. Start Run
 2
1) Pointer types
a) This pointer type is used to select any area on sheet.

b) This pointer type is used to increase or decrease the height and

width of rows and columns.

c) This pointer type is used to move the selected area from one

place to another place.

d) This pointer types is used to make copy the selected text/

number.

e) This pointer type is used to apply the same formula from one cell

to another cell.

2)Function
Functions are the predefined formula that performs calculation by using
specific value, called argument.
 Structure of a function

a) The structure of a function begins with an equal sign (=)

b) It is followed by the function name.
c) The argument for the function separated by commas.

=SUM (A1:E1) Argument

1) Number function-
1) =Sum () --- This function is used to add the given numbers.

Syntax- =sum (A2:H2)

 3
2) =MAX () --- This function is used to find the maximum value from given
cells address.

Syntax- =Max (A2:H2)

3) =Min () ----This function is used to find the minimum value from given
cells address.

Syntax- =Min (A2:H2)

4) =Average () - This function is used to find the average value of given

numbers.

Syntax- =Average (A2:H2)

5) =Fact () - This function is used to find the factorial value of given

numbers.

Syntax-

6) =Product () - This function is used to find product value of given

numbers.

Syntax-
 4
7) =power () --- This function is used to find the power value of any number.

Syntax

8) =sqrt() --- This function is used to find the square root value of any
number.

Syntax-

9) =Mod () --- This function is used to find the remainder value of any
number.

Syntax-

10) =Count () --- This function is used to count the numbers within given a
cell address.

Syntax- =count (range)

=count (A1:A14)
 5
11) =COUNTIF () --- This function is used to count the number within a range by given
condition.

Syntax - =countif(range, “criteria”)

=countif(A1:A9,”>=450”)

Text function-

a) =Upper () - This function is used to convert small letter into

capital letter.
Syntax- =Upper(“account”)
ACCOUNT
b) =Lower() – This function is used to convert capital letter into
Small letter.
Syntax - =lower(“ACCOUNT”)
account
c) =Proper() – This function is used to convert small/capital
Letter into sentence case.
Syntax - =proper(“account”)
Account
 6
2) Date Function –
a) =Today() - This function is used to write the current date in a
cell.
Syntax - =Today()

b) =Now()- This function is used to write the current date and

time in a cell.
Syntax - =Now()

3) Custom List
Custom list- This command is used to make a list which are automatically
appears on sheet, when you drag one name of list down. `

 For custom list go to Home menu and click on sort & filter command.
 Now click on custom sort command from option.
 Now you find sort command dialog box is appears.

 Now click on order button then click on custom list.

 Now you find custom list dialog box is open.

 Now make your list and click on add button.

 Then click on ok button.
 7
4. Making Result

Formula:
1) Total- =Sum(B4:F4)
2) MAX- =MAX(B4:F4)
3) MIN- =MIN(B4:F4)
4) %- =Total obtain marks*100/Total Subjective marks
=G4*100/500
5) Div =if(or(B4<33,C4<33,D4<33,E4<33,F4<33),”FAIL”
,IF(J4>=60,”1ST”, IF(J4>=45,”2ND” ,IF(J4>=33, ”3RD”))))
 8
5. Conditional Formatting

Conditional Formatting-: This command is used to highlight any particular number in

a particular area.

 For conditional formatting, first select the area where you want to apply
conditional formatting.
 Then go to Home menu and click on conditional formatting command.
 Now you find some options are appears-
 Now click on highlight cells rules and choose between option

 Now you find between box is open.

 Now write the range of numbers that you want to highlight.
 9
 Then choose a particular color for highlight the numbers.

Click here for color

6) Data Validation- This command is used to make restriction on any particular

number in particular area.

 For data validation, first select the area where you want to apply data
validation.
 Then go to data menu and click on data validation command.
 Now you find data validation command dialog box is open.
 Now set the Input Setting, input Message, and Error Alert button.
 Then click on OK button
 10

bvb

7) Formula Auditing- This command is used to show how any value came

And which value is dependent on this selected value.

 Select the total value amount.

 Now go to Formula Menu and click on -:
 Trace Precedent- It is used to show how total value is came.
 Trace Dependent – It is used to show which value is dependent on total value.
 Remove all Arrow - It is used to remove all Arrows from sheet.
 11
8) Graph- This command is used to convert the data table records into Graph/chart.

 Select the whole data table.

 Now go to insert menu and click on column chart command.

 Now you find some column chart options are appears.

 Now click on any column chart option.

 Now you find a chart of your data table is appear on sheet.

 Now fill color in your chart and write title of chart “Rain fall analysis”.
 12
9) Filter
Filter- This command is used to filter the any particular record from
data table.

 For filter the data table, first select the whole data table.
 Now go to Home Menu and click on sort & filter command.
 Now you find some options are appears.
 Now click on filter option.
 Now you find a filter button is appears on every row header.

 Filter the text field

Now click on Name filter button.

 13
10) Advance filter
Advance filter- This command is used to filter two or more than two rows of
data table in one time.

 For Advance Filter, copy the all subject name and paste it three rows below of
data table.
 Now write the criteria for filter the data table.
 Now select the whole data table.
 Now go to Data Manu and click on Advance filter command.
 Now you find Advance filter command dialog box is open.
 14
 Now bring the cursor in criteria box.
 Then select written criteria.
 Now click on copy to another location button.
 Now select an aria below the written criteria.
 Now click on  unique record only.
 Then click on OK button.
 15
11) Pay Roll – It is used to calculate the net salary and show the salary sheet
details with deferent – deferent pay head. Like-Basic Salary, DA, TA, HRA, PF

1) DA (Dearness Allowance) 80%

2) TA (Travelling Allowance) 20%
3) HRA (House Rent Allowance)30%
4) PF (Provident Fund) 25%

Formula:-
DA - =Basic Salary *DA/100
=C2*80/100

TA - =Basic Salary *TA/100

=C2*20/100

HRA - =Basic Salary *HRA/100

=C2*30/100

PF - =Basic Salary *PF/100

=C2*25/100

Net Salary- = (Basic Salary + DA + TA + HRA)-PF

= (C2 + D2 + E2 + F2) – G2
 16
12) Sort
Sort - This command is used to arrange the date table in
ascending order or descending order.

 For sort, first select the whole data table.

 Then go to Home menu and click on sort & filter command.
 Now you find some options are appears.
 Now click on custom sort command.
 Now you find sort command dialog box is open.

 Now choose Name in sort by box.

 Then click on Order button and choose A to Z.
 Then click on OK.
 17
13) Sub Total

SubTotal- this command is used to add the Basic Salary, DA, TA,
HRA, PF and Net Salary of each post.

 For subtotal first sort the post in ascending order.

 For this, first select the whole data table.
 Then go to Home menu and click on sort & filter command.
 Now you find some options are appears.
 Now click on custom sort command.
 Now you find sort command dialog box is open.

 Now choose post in sort by box.

 Then click on Order button and choose A to Z.
 Then click on OK.
 18
 Now go to Data menu and click on subtotal command.
 Now you find sub total command dialog box is open.

 Now choose Post in At each change in box.

 Then choose Sum in Use Function box.
 Then click on Basic Salary, DA, TA, HRA, PF and Net Salary
cheek box.
14 Future Value

Future value= This command is used to calculate the future value

amount of any saving at given rate of interest and time.

Formula-

1) When saving is yearly-

Future value - =FV (rate, nper, pmt, [pv], [type])

=FV (B2, B3, B1, 0, 1)
 19
2) When saving is Monthly-
=FV (rate/12, nper*12, pmt, [pv], [type])

=FV (B2/12, B3*12, B1, 0, 1)

15) Goal seek

Goal Seek- This command is used to find any goal.

 For goal seek, first calculate the future value amount.

 Then select the future value amount.
 Now go to Data menu and click on “What if Analysis” and
choose Goal Seek .
 Now you find goal seek command dialog box is open.

 Now bring the cursor in To value box.

 Now write that value/amount in To Value box of which you want
to get.
 Now bring the cursor in By changing cell.
 Then select the saving amount.
 At last click on OK.
 20
16 Scenario
Scenario- this command is used to calculate two or more then two future
value amount in one place to analysis any saving.

 For scenario, first calculate the future value amount.

 Then select the saving amount.
 Now go to Data menu and click on “What if Analysis” and choose
Scenario.
 Now you find scenario command dialog box is open.

 Now click on Add button.

 Now you find scenario name box is open.

 Now write a name for your scenario.

 Then click on OK button.
 Now you find Scenario Value box is open.
 21

 Now write amount of scenario (6000) that you want to set.

 Then click on button.

17 PMT (Per Month Total)

PMT- this command is used to calculate the per month total
(installment) amount of any loan.

Formula
Pmt- =pmt (rate, nper, PV, [fv], type)
=pmt (B2/12, B3*12, B1, 0, 1)

18 Fill
Fill- this command is used to write table of any number.
This command is used to write any series of numbers.
Step-
 Write a number of which you want to make table.
 Then select this number.
 Now go to Home menu and click on fill command.
 22
 Now you find some options are appears.
 Now click on series from option.

 Now you find series box is open.

 Now fill all information in table.

 Then click on OK button

19 Define name
Define name- This command is used to convert any text into
number.

 Write any number in a cell.

 Now select this number.
 Now go to formula menu and click on define name command.
 Now you find define name command dialog box is open.
 Now write a name for selected number in name box.

 Then click on ok button.

 23

20) Consolidate-

Consolidate- This command is used to add the numbers which are

written in different place.
 24

STEP-

 Copy the all student name and paste it below the data table.
 Now write Grand Total in front of name then press enter key.
 Now go to Data menu and click on consolidate command.
 Now you find consolidate command dialog box is open.

 Now bring the cursor in reference box and select the semester- 1 total.
 Now click on add button.
 Now again bring the cursor in reference box and select the semester- 2 total.
 Then click on add button.
 Now again bring the cursor in reference box and select the semester- 3 total.
 Then click on add button.
 At last click on ok button.
 25

PivotTable
PivotTable – Pivot tables are one of Excel powerful features. PivotTables
are a great way to summarize, analyze, explore, and present your data,
and you can create them with just a few clicks. PivotTables are highly
flexible and can be quickly adjusted depending on how you need to display
your results.

Order
PRODUCT CATEGORY AMOUNT DATE COUNTRY
ID
1 CARROT VAGETABLE 4270 16/7/2017 UNITED STATED
2 BROCCOLI VAGETABLE 8239 16/7/2017 UNITED KINGDOM
3 BANANA FRUIT 617 16/7/2017 UNITED STATED
4 BANANA FRUIT 8384 16/7/2017 CANADA
5 BEANS VAGETABLE 2626 16/7/2017 GERMANY
6 ORANGE FRUIT 3610 16/7/2017 UNITED STATED
7 BROCCOLI VAGETABLE 9062 16/7/2017 AUSTRALIA
8 BANANA FRUIT 6906 16/7/2017 NEW ZEELAND
9 APPLE FRUIT 2417 16/7/2017 FRANCE

 Before creating pivot table –

 Your data should be organized in a tabular format, and not have any blank rows or columns.
 Data types in columns should be the same. For example, you shouldn't mix dates and text in the
same column


Insert a pivot table-:

 Click in first cell of data table

 Now go to insert and click on pivot table command.
 Now you find A dialog box is open.
 For place pivot table, new worksheet is selected by default.
 Now click button.
 Now you find the pivot table field list is appear.

 26

 Now drag the product in row labels area.

 Drag the Amount field in values box.
 Then drag the country field in filter box1
 Now you find pivot table is appears with desire format.

Filter the pivot table-:

 We added the country field in the report filter area.

 Now we can filter this pivot table by country.
 Click on filter drop down list button and select France.
 Now we find the pivot table shows the France with their product.
27