Microsoft Excel is the popular
Windows spreadsheet program that provides worksheets, charts, database and list
operations, and application programming all in one software environment. It is
used for managing, analyzing and presenting data in a graphical manner. It is
MS-Office Package developed by Microsoft
Corp. USA.
Spreadsheet:-
Spreadsheet is worksheet that organizes
data into rows and columns, and one work
sheet contains the 256 columns and 65536 rows. Worksheet allows the user to perform
calculations on it and express the results in reports. The spreadsheet has rows
and columns. Each intersection of a row and
a column forms a cell, in which you store data. A cell accepts four
types of data: Characters, Numeric values, Formula and Functions. They are used
for tracking expenses, budgeting, Inventory Management, Accounting, Production
Planning etc. Spreadsheets may also be referred to as worksheets. Examples of
Popular spreadsheet packages are Microsoft Excel, Lotus-123 etc.
Work Book
:- A work book is a file like a document in MS-Word when you save your
file by giving file name then it will renamed with your file name (e.g. Book1
to Anant.xls). A work book contains 256 worksheets. By default 3 worksheets
appears. Other you can insert form insert menu.
Features of
Excel:
- Windows Based on an Application : - Like all windows application it has tool bars, short menus, Auto Correct, Online help and wizard.
- Work Book : - Work book contain or holds one or more work sheets.
Starting MS-Excel
Steps:
1.
Click on
Start menu
2.
Click on
Run…
3.
Type the
Excel and click on Ok button
Or, Click on StartàProgramàMS-OfficeàMS-Excel
Then excel window will appear
Moving Cell Pointer on the Work Sheets.
Right Arrow Key: - To move the cell point one
cell right.
Left Arrow:- To
move the cell pointer one cell left.
UP Arrow: - To
move the cell pointer one cell up.
Down Arrow :- To move the cell pointer
one cell down.
Page UP :- To move the cell pointer one
screen up.
Page Down:- To move the cell pointer one screen Down.
Tab key :- To move the cell
pointer one cell right.
Shift Tab : - To
move the cell pointer one cell leftEnter key
To
move the cell pointer next row.
Ctrl + Right Arrow: - To
move the cell pointer end of the column.
Ctrl + Left Arrow : - To move the cell pointer first of
column.
Ctrl + Down Arrow:- To
move the cell pointer end of the rows.
Ctrl + Up Arrow : - To move the cell pointer first of
the row.
Ctrl + Page Down : - To open or switch to next or second
sheet.
Ctrl + Page UP : - To
open or switch to previous sheet.
Selecting Cell or cells
By Mouse
Drag
and drop method.
By Keyboard
Pressing
Shift key + Arrow keys (Up, down, righ, left)
Deleting data from the cell or cells
Steps:
1.
Select the required cell or cells.
2.
Press Delete
key from the keyboard.
Renaming Sheets
Steps:
1.
Right button click
at required sheet.
2.
Click on Rename
3.
Type the sheet name and press enter key.
Or,
Double click on Sheet Tab and type
the sheet name and press Enter key
Deleting Work Sheets
Steps:
1.
Right button click at required sheet.
2.
Click on Delete
3.
Click on Ok
Or,
1.
Select required sheet.
2.
Click on Edit
menu
3.
Click on Delete Sheet
4.
Click on Ok
button.
Copy Sheet
Steps:
1.
Right button click at sheet tab
2.
Click on Move
or Copy
3.
Tick on at create a copy
4.
Click on Ok
button
Move Work Sheet
Steps:
Select the required sheet and drag and drop the required
place.
Page Setup
The Page setup command lets you to set the page margin, page
Size, paper orientation(tall or wide and
also you can give the Header and footer.
Steps:
- Click on File menu
- Click on Page Setup
- Then a page setup dialog box will appear.
- Click on Page and set the size of the paper and set the paper orientation (Portrait or Landscape )
- Click on paper margin and set the margin of the page(left, right, top and bottom)
- Click on Header and footer and select custom header and custom footer from the dialog box and specify the header and footer.
- At last click on Ok button.
Note : - You can
create header and footer from the view menu also.
Print Area
Being a large work area of the sheet, you have to print only
required data from the sheet, To do this Select your required data and set the
print area.
Steps:
- Select your required data to which you want to print.
- Click on File menu
- Click on Print Area --> Set Print Area
Or, Click on Sheet from the page
setup dialog box and range the required sheet.
Clear the Print Area.
- Select your print area.
- Click on File menu
- Click on Print Area --> Clear Print Area
Custom Views
Creates different views of a worksheet. A view provides an
easy way to see your data with different display options. You can display,
print, and store different views without saving them as separate sheets.
Steps: -
- Prepare your data at any place of the work sheet.
- Click on View menu
- Click on custom views...
- Click on Add and type the custom view name
- Click on Ok button
- To show, that data again click on view menu
- Click on Custom View...
- Select your custom view name and click on Show.
Inserting Rows and Columns
Steps:
1.
Select row or column or position the cell pointer at
required cell
2.
Click on Insert menu
3.
Select Row or column as your need.
Note : - The row will display above of the selected row or
cell. and column will display left of the cell or column.
Deleting Row and Column
Steps:
1.
Select required row or column to which you want to
remove.
2.
Click on Edit
menu
3.
Click on Delete...
4.
Select or tick on entire row or entire column
5.
Click on Ok
button
Conditional Formatting
The conditional formatting command lets you to format the
data according to conditional in the cells. You can specify three conditions
from the dialog box.
Steps:
1.
Select your cells or data
2.
Click on Format menu
3.
Click on Conditional Formatting
4.
Then select the conditions and set the values
5.
Click on Format...
6.
Specify the font, font color, Style , border and
pattern etc.
7.
Click on Ok button.
8.
For next condition Click on ADD and again give the
condition and Click on Format
9.
repeat steps 6, 7 and 8 and at last click on Ok button.
Increase or decrease the Height of the Row
Steps:
1.
Select required row or rows
2.
Click on Format menu
3.
Click on
Row--> Height
4.
Type the value
for the height of the row in Pixel.
5.
Click on Ok button.
Or, Place the mouse pointer at row
number and drag and drop.
Increase or decrease
the width of column
Steps:
1.
Select required column
2.
Click on Format menu
3.
Click on Column -->Width
4.
Type the value for column width
5.
At last click on Ok button.
or, Drag and drop mouse from
column heading
Auto Fit Selection
The autofit selection command lets
you to automatically fit the columns as text.
Steps:
1.
Select your data
or text.
2.
Click on Format menu
3.
Click on Auto fit selection
Note : -You can hide or un
hide row and column by using hide
column and row.
Formatting Cells
The format cell
command lets you to format the cell or data on the cell (e.g Number, Alignment,
Font, border, pattern etc)
Steps:
1.
Select your required cell or cells
2.
Click on Format menu
3.
Click on Cells...
Or, Right button
click at required selected cell and Click on Format cell
Then a format cell dialog box will
appear.
1.
Click on Number
and set the number format.
2.
Click on Alignment
and set the alignment, Wrap, shrink, merge, text direction etc.
3.
Click on Font
and select the required fonts, styles, size, color etc.
4.
Click on Border
and set the border of the cells.
5.
Click on pattern
and set the pattern color for the background of the cells.
6.
At last click on Ok button.
Fact (Number)
Returns the factorial of a number, equal to 1* 2* 3.......*
number.
Syntax:
=Fact(number)<enter>
PI( )
Returns the value of Pi, 3.14159265358979, accurate to 15
digits.
Syntax:
=PI() <Enter>
=3.14
Power( )
Returns the result of a number raised to a power
Syntax:
=power(number, power) <Enter>
Example,
=power(2,3)
=8
Sqrt ( )
Returns the square root of a number.
Syntax:
=Sqrt(number)<enter>
example,
=sqrt(25)
=5
Degrees()
Converts radians to degrees.
Syntax:
=Degrees(angle) <enter>
Radians(angle)
Converts the degrees to radians.
Odd ( )
Rounds a positive number up and negative number down to the
nearest odd integer.
Syntax:
Odd(number)<enter>
=Even()
Rounds a positive number up and negative number down to the
nearest even integer.
Syntax
=Even(number) <enter>
Example,
=even(5)
=6
2. Statistical Functions
Average ( )
Returns the average (Arithmetic Mean) of its arguments,
which can be numbers or names, arrays or references that contains numbers.
Syntax:
=Average(number1,number2, .....) <enter>
Example,
=average(12,4,2,5)
=
Count ( )
Counts the number of cells that contain numbers and numbers
within the list of arguments.
=Count(value1,value2, ...............) <enter>
Example,
=count(range of cells)
=
Countblank( )
Counts the number of empty cells in a specified range of
cells.
Syntax:
=Countblank(range) <enter>
Example,
=countblank(range of cells)
=
Frequency( )
Calculates how often values occur within a range of values
and then returns a vertical array of numbers having one more element than
Bins_array.
Syntax:
=Frequency(data_array, bins_arry) <enter>
Max ( )
Returns the largest
value in a set of values. Ignores logical values and text.
Syntax:
=Max(number1,number2,...) <enter>
Example,
=MAX(range of cells)
=
Min( )
Returns the smallest number in a set of values. Ignores
logical values and text.
Syntax:
=Min(number1,number2,...) <enter>
=Example,
=Min(range of cells)
=
Median( )
Rerturns the median, or the number in the middle of the set
of given numbers.
Syntax:
=Median(number1, number2,......) <enter>
Example,
=Median(range of cells)
Date and Time Functions
Today()
Returns the current data formatted as a date.
Syntax:
=Today( ) <enter>
Now ( )
Returns the current data and time formatted as a date and
time.
Syntax:
=Now() <enter>
Days360( )
Returns the number of days between two dates based on a 360
- day year (twelve 30 day months).
Syntax:
=Days("Starting_Date", "Ending_date")
<enter>
Example,
=Days("01/01/01",Now())
=
Logical
Functions
And( )
Checks whether all arguments are TRUE, and returns TRUE if
all arguments are TRUE
Syntax:
=AND(Logical1,logical2,..) <enter>
If( )
Checks whether a condition
is met, and returns one vaue if TRUE, and Another value if FALSE.
Syntax:
=IF(logical_text,value_if_true,
value_if_false) <enter>
Financial
Functions : - The
financial function lets you to calculate the financial transactions. In excel there are different
types of financial functions which are given below:
PMT ( )
Calculates the payment for a loan based on constant
payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
Rate is the interest rate
for the loan.
Nper is the total number of
payments for the loan.
Pv is the present value, or
the total amount that a series of future payments is worth now; also known as
the principal.
Fv is the future value, or a
cash balance you want to attain after the last payment is made. If fv is
omitted, it is assumed to be 0 (zero), that is, the future value of a loan is
0.
Type
is the number 0 (zero) or 1 and indicates when payments are due.
|
Set type equal
to
|
If payments are
due
|
|
0 or omitted
|
At the end of the period
|
|
1
|
At the beginning of the period
|
Examples
The following formula returns the monthly payment on
a $10,000 loan at an annual rate of 8 percent that you must pay off in 10
months:
PMT(8%/12, 10,
10000) equals
-$1,037.03
For the same loan, if payments are due at the
beginning of the period, the payment is:
PMT(8%/12, 10,
10000, 0, 1)
equals -$1,030.16
The following formula returns the amount someone
must pay to you each month if you loan that person $5,000 at 12 percent and
want to be paid back in five months:
PMT(12%/12, 5,
-5000) equals
$1,030.20
You can use PMT to determine payments to annuities
other than loans. For example, if you want to save $50,000 in 18 years by
saving a constant amount each month, you can use PMT to determine how much you
must save. If you assume you'll be able to earn 6 percent interest on your savings,
you can use PMT to determine how much to save each month.
PMT(6%/12,
18*12, 0, 50000)
equals -$129.08
If you pay $129.08 into a 6 percent savings account
every month for 18 years, you will have $50,000
PV ( ) : Returns
the present value of an investment. The present value is the total amount that
a series of future payments is worth now. For example, when you borrow money,
the loan amount is the present value to the lender.
Syntax
PV(rate,nper,pmt,fv,type)
FV ( ) : -Returns the future value of an investment
based on periodic, constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,pv,type)
Rate ( ) : - Returns the interest rate per
period of an annuity. RATE is calculated by iteration and can have zero or more
solutions. If the successive results of RATE do not converge to within
0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
=RATE(nper,pmt,pv,fv,type,guess
Nper (
) : - Returns the number of periods for an investment based on periodic,
constant payments and a constant interest rate.
Syntax
NPER(rate, pmt, pv,
fv, type)
Data Table
It has to prepare the table which can find the required values for the cells
according to row input cell and column
input cell.
1. Prepare a data sheet as given below.
2. State or Position the cell pointer at required cell
3. Click on Data menu
4. Click on Table
Then
a table dialog box will appear
5. Select row cell from row input cell.
6. Select column cell from column input cell.
7. At last click on Ok button
Scenario
This option lets you to create and save different sets of
input values on the cell. It saves the different cell values by calculating
according row input cell or column input cells. It is the advanced features of
Goal Seek.
Steps:
i. Create a data and type the formula
ii. Select the required cell which contains the formula
iii. Click on Tools
menu
iv. Click on Scenario
A scenario dialog box will appear
v. Click on Add and
type the scenario name to save the value range the cell to which you want to
change.
vi. Click on Ok and type the value and click
on Add
Vii. Repeat steps v, vi again and again after finish then Click on OK
button.
viii. Select scenario name and click on Show.
Goal
Seek
The goal seek is a powerful command in excel. It can be used
to find required data from the cell by changing another cell value.
Steps:
- Position the cell pointer at required cell which contains the formula or result
- Click on Tools menu
- Click on Goal Seek
Then a goal seek dialog box will appear
- Type the required value at to value box
- range the required cell at by changing cell box
- At last clilck on Ok button and again click on Ok button.
Data Sort
The Sort command lets you to sort the data in ascending (A -
Z or 0 -9) or descending (z - a or 9 -
0) order.
Steps
1. Select your data.
2. Click on Data
menu
3. Click on Sort...
Then select the filed name and tick on at Ascending or
descending
3. At last click on Ok
button
Data Form
Excel has facility that you can make or create a form that
is called the data form. You can enter the records into sheet from the data
form.
Steps:
1. Prepare the fields in each column and type a records, and
give the formula if necessary
2. Click on Data
3. Click on Form
...
5. Click
on New button and enter the records.
6. When finish entering the record then click on Close
Filter
The filter command lets you to show or print the records
according name, address, or any fields.
It will filters the required data. There are two types of filters.
1. Auto Filter : - Automatically filters the records.
Steps:
1. Select the data
2. Click on Data menu
3. Click on Filter
4. Click on Auto
Filter
5. Then select the required column to show or filter the
record.
Data Validation
The data validation command lets you to set the required
value on the cell. If user inputs the invalid data then it will show the
message and don't accept that data. The
data validation command also makes or creates the drop down list.To do this
follow the following steps:
1. Select the required cells
2. Click on Data menu
3. Click on Data Validation
Then a data validation dialog box will appear.
4. Click on Settings and set the validation criteria .
5. Click on Input Message and Type the message and title.
6. Click on Error
Alert and select the Style and type the message.
7. At last click on Ok
button
Pivot Table
The data Pivot Table command lets you to summarize or
analysis the data. It will print the data in row heading and column heading.
Steps:
1. Select required data
2. Click on Data menu
3. Click on Pivot
Table
4. Click on Next --> Next --> Layout
5. Drag and drop the fields
and required row, column headings and data.
6. Click on Ok
button
7. Click on Finish
Get External Data
You can import data form the another database program into
excel. To import the data from another application use the Get External Data
command.
Steps:
1. Click on Data
menu
2. Click on Get
External Data
3. Click on Import
file
4. Select your database file form the dialog box
and Click on Open
5. Click on Ok
button
Freeze Pane
The freeze pane command lets you to show the fields in rows
or fields in columns or required data always when enter the data in work sheet.
Steps:
1. Position the cursor at required place.
2. Click on Windows
menu
3. Click on Freeze
Pane
To Remove Freeze
Pane
1. Click on windows
menu
2. Click on Remove
Freeze Pane
Creating a Chart
In
Excel the chart command lets you to show the data range in diagrammatic and
Graphic Representation form. There are different types of charts in excel. We
use these chart according our data.
Steps:
- Select your data to which you want to show in chart.
- Click on Insert menu
- Click on Chart or click on chart icon from the standard tool bar.
Then a chart dialog box will appear.
- Select the chart type and click on Press and Hold to View Sample button ( you can see the sample of the chart)
- Click on Next and again range the data if necessary and tick on row and column radio button.
- Then a chart option dialog box will appear.
- Click on Title and type the chart title and values for the x axis and y axis.
- Click on Axes and tick on the required primary axis option.
- Click on Gridlines and tick on required option
- Click on Legend and tick on required option to place the legend
- Click on Data label and tick on required option
- Click on Data Table and tick on required option to show the table
- Click on Next and tick on required option
- At last click on Finish button
Formula of MS Excel:-
Total:- sum(c7:r8)@
Result:-
iif(or(nepali<32,English<32,math<32,sciece<32,social<32,health<32),"Fail","Pass")@
Percentage:- total/600*100@
Division:-
iif(and(percent>=80),"Dist",iif(and(percent<=60),"First",iif(and(percent<=45),"Second",iif(and(percent<32),"Third","Fail"))))
Remarks:-
iif(division="Dist","very
excellent",iif(division="First","Excellent",iif(division="second","good",iif(division="Third","satisfactory","tryagain"))))@
Rank:-
rank(percent,percent:last percent)
No comments:
Post a Comment
Rahul-Notes