EXCEL

 An excel is a spreadsheet management program under Ms. Office.

 A spreadsheet is an electronic document which is used to store data and
informations.
 It is used to create,edit, format and save data .
 Not only that it is also used to perform data entry and analysis , prepare
sheets, perform calculation.

Starting Ms. Excel :-

 Click on “start”
 Go to “Run”
 Type “Excel”

Work book:-
Primary file of excel is known as work book.

Work sheets:-
Individual sheet of a work book. It is used to store data and information in rows &
column.

Rows:-
Horizontal grid in a work sheet is known as rows.
Rows are numbered as 1,2,3,4,…… .
There are 10,48,576 rows in a single work sheet ( office 2007,2010,2013)
And 65,536 (office xp, 2003)

Column:-
Vertical grid in a work sheet is known as column.
Column are named as A,B,C,D……. .
There are 16384 columns in a single work sheets (office 2007,2010,2013,)
And 256 in (2003, office XP)

Cells:-
Area formed by the intersection of rows and columns is known as cell.
Cell pointer ():-
It helps to select data, value, or cell in a work sheet.

Cell Address:-
Reference or indication to particular cell in a work sheet .
Combination of column name and number .
EG:- A4 ( A = COLUMN NAME & 4= ROW NUMBER)

Cell range:-
Group of cells taken horizontally as well as vertically
E.G:- A1:A10

Data :-
It is a raw fact or sources of information. Text, number, data , time, year, month, currency
etc are some of the datas.

# To customize the series:-

 Open Ms. Excel
 Click on “FILE” tab. A menu appears.
 Click on “options”
 “Excel options” window appears.
 Click on “advanced”. Go to “ General” and click on “Edit custom list” button.
 “Custom list” dialog box appears. In the “list Enteries” type your list.
 Click “Add” & then “ok” & again “ok”.

Merge And Center:-

In excel work sheet we can combine multiple cell.
 Select the required number of cells in work sheet
 “HOME”  click on this tab.
 In the “Allignment” group

Formula :-
It is an expression which is used to perform same sort of calculation on data.
 Always begins with an”=” sign
 use always cell address instead of direct values”
[To make change inside the cell press F2]

Operator:-
It is a symbol which is used for performing same specific operation on data
E.G:- a+b

Types of operator:-

Arithematic operator:
+ Addiction
- Substraction
* multiplication
/ division
^ power

comparision operator:-

> greater than

< less than
>= greater than & equal to
<= less than & equal to
<>= not equql to

# To change currency sign as “Rs.”

 select the required cells
 “HOME “ click on this tab
 In the “number” group click on this button.
 “ format cells” dialog box appears. from category click
“ currency” then click “custom”
 I n the “ type” field , type {“Rs”#,.##0}. click on”ok”

Function:-
It I s a buitt in a expression in excel to perform same tasks. A function starts with an
equal sign followed by opening parenthesis( then, a set of value separated by comma &
closing parethesis)

Syntax:-
= function name ( value1, value2)
E.G:- = date( ) = sum( ) = average( ) =or( ) = if ( ) etc

If:-
checks the value with the specified condition . If the value matches with the specified
condition, the result will be true otherwise false.
E.G :- =IF (MARKS.32,”PASS”,”FAIL’)

And:-
The result will be true if all the specified condition are true. The result will be false if any
of condition is false.
T&& T= T
T&&F= F
F&&T=F
F&&F= F
=AND (CONDITION, VALUE)

Or:-
The result will be true if any of the condition is true .
The result will be false if all the coindition is false.

TorT=T
ForF=F
TorF=T
ForT=T
syntax:-
=or (condition,value)

Conditional formatting:-
It is a format, such as cell shading or font color, that Excel automatically applies to cell if
a certain specified condition is true
Steps:-
 first of all select cells in which you want to use conditional formatting
 click “HOME” tab & from “ styles” group click on “ conditional formatting’
option
 click on “new rule” option
 click on second option “ format only cells that contains”
 specify the condition than click on “ ok”
Data validation :-
This option is used to validate the cell by applying. Certain conditions. If the condition is
matched then. You are allowed to enter the values otherwise you are not allowed to enter
the values.
steps:-
 select the cells in which you want to use the validation.
 click on “DATA” tab
 from “data tools” click on “ data validation “ option
 Then a data validation box appears
 click on “ setting” tab
 choose “ whole number” from allow drop drop down box
 choose any one option from data drop down box
 write an appropriate message after clicking on “ Error alert”
 click “ok”

Filter:-
The quickest way to select only those items that you want to display in your worksheet.
steps:-
 select the required cells
 click on “DATA” tab
 from “sort &filter” group click on “ filter” option then a filter button appears at
each field header
 click on the filter button A& select the item which u want to display

Sub-total:-
calculates subtotal and grand total values for the columns which you select in your work
sheet.
steps:-
 select the required cells for which you want to calculate subtotal
 click on “DATA” tab
 from “outline’ group click on subtotal
 then a dialog box appears
 set “item’ in “at each change in” box
 set “sum’ in ‘ use function”
 choose the required option from add subtotal to box
 click “ok’

MARK SHEET:-
syntax:-
Total: sum= ∑
Result: =if(min(eng:eph)>32,”pass”,” fail”)
percentage: if(result=”pass”,total/7,o)
Division : =if(percent>=80,” distinction”, if (percent>=60,”first”,
if(percent>=45,”second”,if(percent >=32,”third”,”fail”))))
Rank:=if{result=”fail”,”no rank”, rank(percentage,relative cell address of
percentage))

Charts:-
1. Bar\column chart:-
i. select the required data
ii. “INSERT” click on this tab
iii. I n the “chart s” group, click on “column” options
iv. from “2d column”, click on “clustered column”
2. pie chart:-
i. prepare data
ii. select the required data
iii. “INSERT” click on this tab
iv. in the “chart” group click on “pie” option
v. from “2d pie” click on “pie’
vi. from “ chart layout” select “layout 1”
3. line chart:-
i. prepare the data
ii. “INSERT” click on this tab
iii. in the “chart” group click on “line”option
iv. from “2d-line” click on “line”

#To protect work sheet :-

 save your work book
 “FILE” click on this tab
 click on “info” select “protect workbook”. A menu
appears click on “encrypt with password”
 “encrypt document” dialog box appears specify password
click “ok”
 Re-enter password,Again click “ok”
 save your workbook again

Goal sheek:-
 prepare your data {generated by using formula}
 select the required data
 “DATA” click on this tab
 in the “data tools”group click on “what if analysis” option click on goal seek
 “Goal seek” dialog box appears
set cell:- specify the result cell
 to value :- type your desired value
by changing cell:-set the cell of value to change

Scenario manager:-
 select the data
 “DATA” click on this tab
 in the “ data tools” group click on “what if analysis”option click on”scenario
manager”
 “scenario manager” dialog box appears click “add” button
 specify scenario name
 in the changing cells. specify the cell range of data
 click “ok”
 “scenario value”dialog box appears . set the value for each of the changing cells
 Click “add”,”ok”. Click on “ summary”