EXCEL NOTES

Q. What is a spreadsheet?
Ans. A spreadsheet is a “number manipulator” i.e., it is the computer
equivalent of a paper ledger sheet. It consists of a grid made from columns
and rows to make the handling of numbers easier. It is an environment that
makes number manipulation easy and convenient.
Spreadsheets are made up of
● columns

● rows

● and their intersections are called cells

In each cell there may be the following types of data
● text (labels)

● number data (constants)

● formulas (mathematical equations that do all the work)

Q. What is a COLUMN?
Ans. In a spreadsheet the COLUMN is defined as the vertical space that is
going up and down the window. Letters (A,B,C through XFD) are used to
designate each COLUMN'S location. After column Z comes column AA,
which is followed by AB, AC and so on. After column AZ comes BA, BB, and
so on. After column ZZ is AAA, AAB, and so on.

Q. What is a ROW?
Ans. In a spreadsheet the ROW is defined as the horizontal space that is going
across the window. Numbers are used to designate each ROW'S location.
Rows are numbered from 1 through 1,048,576

Q. What is a Cell?
Ans. In a spreadsheet the CELL is defined as the space where a specified row
and column intersect. Each CELL is assigned a name according to its
COLUMN letter and ROW number.

In the above diagram the CELL labeled B6 is highlighted. When referencing a

cell, you should put the column first and the row second.

Q. Which are the basic data types in Spreadsheet?

Ans. In a spreadsheet there are three basic types of data that can be entered:-

1
 EXCEL NOTES

●Labels (text) are descriptive pieces of information such as names,

months, or other identifying statistics, and they usually include alphabetic
characters.
●Values (numbers) are generally raw numbers or dates.

●Formulas are instructions for Spreadsheet to perform calculations.

data types examples Descriptions

Name or Wage or anything that is just
LABELS
Days text
VALUES 5 or 3.75 or -7.4 any number
FORMULAS =5+3 or = 8*5+3 math equation

NOTE: ALL formulas MUST begin with an equal sign (=).

Labels are text entries. They do not have a value associated with them. We
typically use labels to identify what we are talking about.
In our first example: the labels were
● computer ledger

● car loan

● interest

● # of payments

● Monthly Pmt.

Values are entries that have a specific fixed value. If someone asks you how old
you are, you would answer with a specific answer. Sure, other people will have
different answers, but it is a fixed value for each person.
Examples: the constants are
● 12,000

● 9.6%

● 60

Formulas are entries that follow = sign in a cell. It performs calculations of

some type and returns a result, which is displayed in the cell. Formulas use a
variety of operators and worksheet functions to work with values and text.
Examples:
● =A1+A2+A3

● =SUM(A1:A3)

● =25*30

Q. What is Spreadsheet AutoComplete feature in Spreadsheet?

Ans. Entering data in a worksheet can be time consuming. One of the tools that
Spreadsheet provides to make entry easier is AutoComplete. When you
start to type something in a cell, Spreadsheet tries to
guess what you are typing and shows a "match" that you can accept simply
by pressing Enter.

2
 EXCEL NOTES

The "matches" that Spreadsheet uses in its "guess" is nothing but the
contents of the cells in the column, above where you are making your
entry. For instance, if you have information in cells A1 through A6 and you
are entering a value in cell A7, Spreadsheet looks at what you are typing. If
the first few characters uniquely match something in any of the six cells
previously entered in the column, then Spreadsheet offers to AutoComplete
A7 with the contents of the cell that matched.
Spreadsheet does not try to match with cells that contain only numbers,
dates, or times. The cells must contain either text or a combination of text
and numbers.

Q. What is the use of AutoCorrect feature?

Ans. AutoCorrect feature is used to automatically correct common typing
mistakes. You can also add to the list some words that Spreadsheet corrects
automatically. You can also use this feature to define short cuts for long
words or phrases. You can use it to capitalize the first letter of a
sentence, capitalize Name of the Days, Correct Accidental use of Caps Lock
Key.

Q. What is the use of Auto Fill option?

Ans. The Auto fill features makes inserting a series of values or text items in a
range of cells easy. It uses the auto fill handle. You can drag the auto fill
handle to copy the cell or automatically complete a series.

Q. What is the use of Auto fit?

Ans. Auto Fit option can be used to adjust row or column height in the selected
cells to
accommodate the tallest item in the row or the widest entry in the column.
You can either auto fit row height or column width of a selected row of cells
or column of cells.
Q. Explain the use of Undo & Redo?
Ans. When you use a spreadsheetl sometimes you might delete some
information or clear cells by mistake, now in this situation you may be
required to bring back this information then you can use the Undo feature.
Undo brings back the information that you thought was gone.
Spreadsheet keeps a record of changes you make to your worksheet, and
you can choose to undo the last one -- or as many as you want -- in reverse
order. Then if you change your mind and want to do the changes again, you
can simply use Redo.
Undo only works on changes that you've made during the current session, so
you can't undo procedures if you closed the worksheet and opened it again.
It also doesn't work on operations that do not result in changes, such as
printing or saving.

Q. What is a Range in Spreadsheet?

Ans. A Group of cells is referred to as a Range in Spreadsheet.

Q. Explain how to select a range of cells in Spreadsheet?

Ans. You may select a range of cells in any one of the following ways.
To select a range of cells,

3
 EXCEL NOTES

1. You can click on the first cell you want to select and then drag the mouse
pointer to include the rest of the cells.
2. You can also select a range of cells by clicking the first cell and then
clicking the last cell while holding down the shift key.
3. Take the cursor to the first cell you want to select and keep the shift key
press and then use arrow keys to select the remaining cells.
4. Take the cursor to the first cell and then press F8 key and then using
arrow keys select the remaining cells.

Q. Explain how to select an entire row or entire column?

Ans. To select either entire row or entire column, just click on row or column
heading. You can also select entire row by pressing Shift + Space bar. You
can also select entire column by pressing Control + Space bar.

Q. Explain how to select the entire worksheet?

Ans. You can select the entire worksheet either using control + A command or
by clicking the Select All button which is located above row heading 1 and
to the left of the column heading A. You can also select the entire worksheet
by pressing Control+ Shift+ Space Bar together.

Q. Explain the meaning of contiguous and non-contiguous range of

cells?
Ans. A single rectangular range of cells selected are called contiguous range of
cells.
Two or more non adjacent rectangular range of cells are referred to as non –
contiguous range of cells.

Q. Explain the two methods of selecting non- contiguous (Multiple

selection) range of cells?
Ans. Suppose you want to select multiple ranges of cells. You may first select
the first range and then press control and then click and drag the mouse to
select additional cells or ranges.
Or From the keyboard select the first range of cells using either F8 or Shift
key and then press Shift + F8 to select another range without cancelling the
previous range.
Or Enter the range address in the name box separating each range address
with a comma and then press Enter key.
Or Press F5 key now GoTo Dialog box appears in the reference box. Enter
the range address separated by comma.

Q. Explain how to copy information in a worksheet from one location to

another.
Ans. You can copy a cell or range of cells using different methods.
You must first select the cell or range of cells to copy the data
1. You can use copy and paste command to copy. Select Home
Button /Clipboard/Copy, transfers a copy of the selected cell or range of
cells to the clipboard. After performing this, select the cell where you want
to copy cell or range of cells and then choose Home/Clipboard/Paste
option. Or you can just press enter key in the destination cell.
2. You can use shortcut menu commands for copying and pasting. After
selecting the cell or range of cells right click the range and choose copy
from the shortcut menu.

4
 EXCEL NOTES

Activate the destination cell right click and choose paste from the shortcut
menu. Or Press the enter key.
3. You can also use the shortcut key Control+ C to Copy and Control +V to
Paste.
4. Drag and Drop method can also be used to copy cells or range of cells.
Select the cell or range of cells keep the Control key pressed and move
the mouse to one of the selection border, now the mouse pointer is
augmented with a small plus sign and then simply drag the selection to its
new location while you continue pressing the control key. The original
selection remains behind and Spreadsheet makes a new copy when you
release the mouse button.

Q. Explain how to move information in a worksheet from one location to

another. ?
Ans. You can move content of a cell or range of cells using different methods.
You must first select the cell or range of cells to move the data
1. You can use cut and paste command to copy. Select Home Button
/Clipboard/CUT, transfers contents of the selected cell or range of cells to
the clipboard. After performing this, select the cell where you want to copy
cell or range of cells and then choose Home/Clipboard/Paste option. Or
you can just press the enter key in the destination cell.
2. You can use shortcut menu commands for cut and paste. After selecting
the cell or range of cells right click the range and choose cut from the
shortcut menu.
Activate the destination cell right click and choose paste from the shortcut
menu. Or Press the enter key.
3. You can also use the shortcut key Control+ X to Cut and Control +V to
Paste.
4. Drag and Drop method can also be used to move contents of a cell or
range of cells. Select the cell or range of cells move the mouse to one of the
selection border, now the mouse pointer is augmented with a Four Headed
Arrow and then simply drag the selection to its new location.

Q. Explain the procedure to copy a cell to an adjacent cells or range of

cells?
Ans. You can copy to adjacent cell or range of cells using the option
Home/Editing /Fill and then Down, Right, Up or Left. Other way of doing this
is Auto Fill option using Fill handle.
Q. Explain how to Copy a Range of Cells to other work sheets?
Ans. First select the range of cells to copy, press control and click the sheet tab
for the worksheet to which you want to copy and the select
Home/Editing/Fill/Across Worksheets, then a dialog box appears to ask
you what you want to copy( all contents or formats), make your choice and
then click on Ok
Q. Explain the procedure to add and remove columns and Rows?
Ans. When working with worksheets, you will often need to make changes to
the original worksheets, such as deleting old information or adding new
information. To make this task easier, you can add new rows and columns
or delete existing rows and columns.
Adding Rows

5
 EXCEL NOTES

1. Select a cell below where you want to add a new row

2. From the Ribbon, select the Home command tab
3. In the Cells group, click the down pointing arrow on the INSERT button
» select Insert Sheet Rows
A new row is added above the selected cell.
Adding Columns
1. Select a cell to the right of where you want to add a new column
2. From the Ribbon, select the Home command tab
3. In the Cells group, click the down pointing arrow on the INSERT button
» select Insert Sheet Columns
A new column is added left of the selected cell.
Deleting Rows
1. To delete a single row, select any cell from the row to be deleted
To delete multiple non-contiguous rows, press [Ctrl] + select the cells
from each row to be deleted
2. From the Ribbon, select the Home command tab
3. In the Cells group, click the down pointing arrow on the DELETE button
» select Delete Sheet Rows
The row(s) are deleted.
Deleting Columns
1. To delete a single column, select any cell from the column to be deleted
To delete multiple non-contiguous columns, press [Ctrl] + select the cells
from each column to be deleted
2. From the Ribbon, select the Home command tab
3. In the Cells group, click the arrow on the DELETE button » select Delete
Sheet Columns
The column(s) are deleted.
Q. Explain the use of find and replace option in a spreadsheet?
Ans. The Find and Replace features are time-saving techniques that allow you
to rapidly change the content of your worksheets. Find and Replace
function will search your documents for specific text, which can then be
highlighted, replaced with different text or formatting, or left as-is. This
function provides many advanced options to help make your search as
specific as necessary to find what you are looking for.

Match entire cell Limit search results to cells where an exact match
contents occurs
EXAMPLE: Smith will locate Smith but not Chris
Smith.

Exercises:

1. Currently you have opened an Spreadsheet workbook here you want to

enter name of week days in cells A1 to A7, Explain how to perform this
using auto fill?

6
 EXCEL NOTES

2. Currently you have opened an Spreadsheet workbook here you want to

enter names of Months starting with Jan in cells A1 to A12, Explain how to
perform this using auto fill?

3. Currently you have opened an Spreadsheet workbook here you want to

enter numbers from 1 to 20 in cells A1 to A20, Explain how to perform this
using auto fill?

4. Currently you have opened an Spreadsheet workbook here you want to

enter numbers 2,4,6,8…50 in cells A1 to A25, Explain how to perform this
using auto fill?

5. Currently you have opened an Spreadsheet workbook, here you want to

create a custom list of Milk, Butter, Yoghurt, Ghee,

Q. What is a template in a spreadsheet?

Ans. A template is a spreadsheet file containing common data and formatting
options that is used as a model for other spreadsheets.
●Formatting can include font and layout changes, conditional formatting,
color changes, and any other available options.
Charts can added to the template file as can formulas, functions, look up tables,
and macros.

Q. What is linking of workbook in spreadsheet?

Ans. When you link worksheets, you connect them together in such a way that
one
depends on the other. The workbook that contains the link formulas (also
known
as external reference formulas) is called the dependent workbook. The
workbook
that contains the information used in the external reference formula is
called the source workbook.

Q. Explain how to add worksheet in Spreadsheet ?

Ans. Adding a new worksheet to your workbook

1)Click the Insert Worksheet control, which is located to the right of the last
sheet tab. This method inserts the new sheet after the last sheet in the
workbook.

2)Press Shift+F11. This method inserts the new sheet before the active sheet.

3) Right-click a sheet tab, choose Insert from the shortcut menu, and click the
General tab of the Insert dialog box. Then select click the Worksheet icon and
click OK. This method inserts the new sheet before the active sheet.

7
 EXCEL NOTES

Q. Explain how to remove or delete worksheets in Spreadsheet ?

If you no longer need a worksheet, or if you want to get rid of an empty

worksheet in a workbook, you can delete it in either of two ways:

1)Right-click the sheet tab and choose Delete from the shortcut menu.

2)Choose Home Cells Delete Sheet. If the worksheet contains any data, Spreadsheet asks you to confirm that you wan

You can delete multiple sheets with a single command by selecting the
sheets that you want to delete. To select multiple sheets, press Ctrl
while you click the sheet tabs that you want to delete. To select a
group of contiguous sheets, click the first sheet tab, press Shift, and
then click the last sheet tab. Then use either method to delete the
selected sheets.

Q. Explain how to rename a worksheet in Spreadsheet ?

The default names Spreadsheet uses for worksheets—Sheet1, Sheet2, and so on.
To change a sheet’s name, double-click the sheet tab. Spreadsheet highlights
the name on the sheet tab so that you can edit the name or replace it with a new
name.

Sheet names can be up to 31 characters, and spaces are allowed. However, you
can’t use the following characters

in sheet names:

: colon

/ slash

\ backslash

? question mark

* asterisk

It is convenient to use shorter sheet names so that you can see these names in
sheet tabs.

Q. Explain how to hide a worksheet in Spreadsheet ?

Hiding and unhiding a worksheet

In some situations, you may want to hide one or more worksheets. Hiding a
sheet may be useful if you don’t want others to see it or if you just want to get it
out of the way. When a sheet is hidden, its sheet tab is also hidden. You can’t
hide all the sheets in a workbook, so at least one sheet must remain visible. To

8
 EXCEL NOTES

hide a worksheet, right-click its sheet tab and choose Hide. The active worksheet
(or selected worksheets) will be hidden from view.

To unhide a hidden worksheet, right-click any sheet tab and choose Unhide.
Spreadsheet opens its Unhide dialog box that lists all hidden sheets. Choose the
sheet that you want to redisplay and click OK. You can’t select multiple sheets
from this dialog box, so you need to repeat the command for each sheet that you
want to unhide.

Q. Explain how to protect a workbook in Spreadsheet ?

To prevent others from unhiding hidden sheets, inserting new sheets, renaming
sheets, copying sheets, or deleting sheets, protect the workbook’s structure:

1. Choose Review Changes Protect Workbook.

2. In the Protect Workbook dialog box, click the Structure option.

3. Provide a password, if you like.

After performing these steps, several commands will no longer be available when
you right-click a sheet tab: Insert, Delete, Rename, Move or Copy, Hide, and
Unhide.

Q. How add header & footer to a workbook?

A header is information that appears at the top of each printed page. A footer is
information that appears at the bottom of each printed page. By default, new
workbooks do not have any headers or footers.

You can specify headers and footers by using the Header/Footer tab of the Page
Setup dialog box. But this task is much easier if you switch to Page Layout View,
where you can click the section labeled Click To Add Header or Click To Add
Footer.

If you’re working in Normal view, you can choose Insert Header &
Footer. Spreadsheet switches to Page Layout View and activates the
center section of the page header.

You can then type the information and apply any type of formatting you like.
Note that headers and footers consist of three sections: left, center, and right.
For example, you can create a header that prints your name at the left margin,
the worksheet name centered in the header, and the page number at the right
margin.

When you activate the header or footer section in Page Layout View, the Ribbon displays a new context tab called Hea

Selecting a predefined header or footer

You can choose from a number of predefined headers or footers by using either of the two drop-down lists in the Head

9
 EXCEL NOTES

Q. Explain the different types of cell references in formulas?

Ans. In most formula reference one or more cells by using the cell or range
address. Cell references come in four styles; the dollar sign differentiates them:

Relative: The reference is fully relative. When the formula is copied, the cell
reference adjusts to its new location.

Example: A1

Absolute: The reference is fully absolute. When the formula is copied, the cell
reference does not change.

Example: $A$1

Row Absolute: The reference is partially absolute. When the formula is copied,
the column part adjusts, but the row part does not change.

Example: A$1

Column Absolute: The reference is partially absolute. When the formula is

copied, the row part adjusts, but the column part does not change.

Example: $A1

When cell reference is either row absolute or column absolute then it is known as
mixed reference

[Link] how to use cell references from a different worksheet.

Referencing cells in other worksheets

To use a reference to a cell in another worksheet in the same workbook, use this
format:

SheetName!CellAddress

In other words, precede the cell address with the worksheet name, followed by
an exclamation point. Here’s an example of a formula that uses a cell on the
Sheet2 worksheet: =A1*Sheet2!A1

This formula multiplies the value in cell A1 on the current worksheet by the value
in cell A1 on Sheet2.

If the worksheet name in the reference includes one or more spaces,

you must enclose it in single quotation marks. (Spreadsheet does that
automatically if you use the point-and-click method.)

For example, here’s a formula that refers to a cell on a sheet named All
Depts:

10
 EXCEL NOTES

=A1*’All Depts’! A1

Referencing cells in other workbooks

To refer to a cell in a different workbook, use this format:

=[WorkbookName]SheetName!CellAddress

In this case, the workbook name (in square brackets), the worksheet name, and
an exclamation point precede the cell address. The following is an example of a
formula that uses a cell reference in the Sheet1 worksheet in a workbook named
Budget:

=[[Link]]Sheet1!A1

If the workbook name in the reference includes one or more spaces, you must
enclose it (and the sheet name) in single quotation marks. For example, here’s a
formula that refers to a cell on Sheet1 in a workbook named Budget For 2008:

=A1*’[Budget For [Link]]Sheet1’!A1

When a formula refers to cells in a different workbook, the other workbook

doesn’t have to be open. If the workbook is closed, however, you must add the
complete path to the reference so that Spreadsheet can find it.

Here’s an example: =A1*’C:\My Documents\[Budget For [Link]]Sheet1’!A1

11
 EXCEL NOTES

Spreadsheet Functions:

FV:

The future value (FV) calculates how much a known quantity of money will be
worth at some point of time in the future. The syntax for the FV function is as
follows

=FV(rate, nper , pmt, pv ,

type )

Here rate is rate of interest, nper is periods, pmt is payment, pv is present

value, and type either 0 or 1 depending upon whether the payment is made
before the period begins or payment is made at the end of each period. If type is
not specified then 0 is assumed. Arguments in bold are required arguments.

Examples:

Calculating Future Values of Payments.

1) Suppose you wants to start saving account for a child’s college education,
starting from the first month of child’s birth and wants to know what would be
the amount after 18 years at a given rate of interest compounded annually,
then he must use FV function.

If you invest Rs.100 every month and the rate of interest is 5% then the
future amount can be computed using the function

= FV(5%/12,18*12,-100,0,0)

This function return 34,920.20 which is the amount returned to you.

In the above example 5% annual percentage rate is converted into monthly

rate and the 18 years is converted in to months. There is no present value
because the account is open just now and the type is 0 because this payment
starts after the first month.

Calculating Future value of a Lump Sum

2) Suppose you want to compute how much your Rs.20,000 will be worth after
15 years when rate is 8%, you can use Future value function as follows

=FV(8%,15,0,-20000,0)

Here the payment (pmt) is 0 because the amount is deposited as lump sum.

This function return 63,443.38 which is the amount returned to you.

Calculating Future value of a Lump Sum and Payments .

12
 EXCEL NOTES

3) Suppose you are going to make a monthly payment of Rs.900 against Rs.1,
50,000 mortgages. And your mortgage interest rate is 5.75%, the following
formula will compute how much you will still owe to your house in 5 years.

=FV(0.0575/12,5*12,-900,150000,0)

This function returns -137,435.10, which is the computed outflow to

pay that money back at the end of 5 years.

Q. Write future value function to find how much money will be accumulated
when you invest Rs.2000 in a year for 40 years and earn 8% interest, When

i) Investment is made at the end of the year.

ii) Investment is made at the beginning of the year.

iii) Start with Rs.30000 and invest at the end of every year.

Ans. i) =FV(8%,40,-2000)

ii) =FV(8%,40,-2000,0,1)

iii) =FV(8%,40,-2000,-30000,0)

Q. You now have Rs.250000/-in the bank. At the end of each of the next 20
years you withdraw Rs. 15000. If you earn 8% per year on this investment,
how to find how much money will be there in 20 years using Spreadsheet?

Ans: Future value of 2,50,000/- in 20 years =fv(8%,20,-250000,0,0)is entered in

cell D2

Future value of annuity for 20 years=fv(8%,20,-15000,0,0) is entered in cell

D3

Money after 20 years =D2-D3 in D4

PV:

The PV function (Present Value function) returns the present value of future cash
flows. We know that money in the future has different value than money today.
This function tells us how much that future money is worth right now.

The syntax is

=PV(rate, nper , pmt, fv ,

type )

13
 EXCEL NOTES

Example:

Calculating Present value of a series of payments:

1) Suppose you want to take car loan of Rs.4,00,000 and a company offers an
installment of Rs.9100 per month for 5 years to pay back this loan, assuming
that interest rate is 12% P.A., the following formula will compute the present
value of the amount.

=PV(12%/12,5*12,-9100)

This function returns 409,090.85 which is the present value of the

amount you are paying.

The PV function is used to determine how much a specific future amount is

worth today.

Calculating Present value of a Lump Sum:

2) Suppose somebody wants to give you Rs.1,00,000 but you can collect it only
after 5 years. Assuming 8% growth rate, you want find much this gift worth
today. The following formula will compute the present value of the amount.

=PV(8%,5,0,100000) since 100000 is the future value.

This function returns 68,058.32. This result means that if you have
Rs. 68.058.32 and you have invested it at 8%, it would be worth Rs.
100000 in 5 years.

Calculating Present value of an annuity with a Lump Sum:

3) Suppose that your friend wants you to invest Rs. 50,000/- in his business. If
you invest this amount he will pay you Rs.200 per month for 5 years and
Rs.60000 at the end of 5 years. At the same time your bank gives you 10%
interest. Now you want to determine which one is good deal. Now you
compute the present value using the formula:

=PV(10%/12,60,200,60000,1)

This function returns Rs. 45,958.83. Therefore you can make more
money by investing in Bank.

Q. Write present value function table for an annuity of Rs.3000 per year for 5
years assuming rate as 12% per year.

i) When payment is made at end of the year.

ii) When the payment is made at the beginning of the year.

14
 EXCEL NOTES

iii) Additional Rs.500 is paid when payment is made at the end of the
year.

Ans. i) =PV(12%,5,-3000,0,0)

ii) =PV(12%,5,-3000,0,1)

iii) =PV(12%,5,-3000,-500,0)

Q. You have just won the lottery. At the end of each of the next 20 years you will
receive a payment of Rs. 50,000. If the cost of the capital is 10% per
year,How to find the present value of your lottery using Spreadsheet?

Ans. =PV(10%,20,50000,0,0)

Q. I deposit Rs.2000 per month (at the end of each month) over the next 10
years. My investment earns 0.8 percent per month. I would like to have Rs
1,000,000/- in 10 years. Write Spreadsheet function to find how much I should
deposit now?

Ans: Future value of Rs. 2000 per month deposit=fv(0.8%,120,-2000,0,0) is

entered in Cell C3

You can find the amount to be deposited now using=Pv(0.8%,120,0,-(1000000-

C3),0)

IN ANY CELL

Q. A player receives Rs. 100000 at the end of each of next 7 years. He gets 6%
per year on his investments. Write Spreadsheet function to find the preset value
of his future revenue.?

Ans:=PV(6%,7,-100000,0,0)

PMT:

The PMT function computes payments required to get a certain balance(PV)

down to Zero or some other value(FV). The syntax is

PMT(Rate, nper , pv , fv,

type)

Examples:

Computing loan Payments:

1) Suppose you want buy a refrigerator worth Rs. 44,000/- and you want to know
how much you have to pay as monthly payments when you are making down

15
 EXCEL NOTES

payment of Rs. 4000/- and the dealer is offering 4% financing for a 2 year
loan.

=PMT(4%/12,2*12,40000,0,0)

This function returns 1,737.00. This is your installment amount.

Computing retirement payments:

2) Suppose Mr. X is Retiring at the age of 60 years and has Rs. 20,00,000 with
him. He wants to take out a fixed monthly amount for 20 more years and still
want Rs. 5,00,000 left to leave to heirs. If he assumes that he will get a
minimum of 4% annual return

=PMT(4%/12,20*12,-2000000,500000,0) This function returns 10,765.37.

He can withdraw Rs 10,765.37 and still have Rs.5,00,000 in the account.

IPMT & PPMT:

IPMT (Interest payment) function can be used to find the interest part of a
payment.

PPMT (Principal payment) function can be used to find the principal part of a
Payment.

These two functions are useful when you need to determine the interest/principal
breakdown of a particular payment.

The syntax of the two functions are as follows:

IPMT(rate, per , nper , pv ,

fv ,type)

PPMT(rate, per , nper , pv ,

fv ,type)

Per: period & nper: no. of periods

Example:

Computing Principal and Interest breakdown of a loan Payment:

Suppose you are borrowing a loan 0f Rs.10,000/- for a period of 2 years and
rate of interest is 13%per year. You want a table showing month, principal
part of payment, interest part of payment and installment amount. You may
enter 1 on cellA2 and using fill handle of this cell drag till A13 and fill series.

Enter the formula

=ppmt(13%/12,A2,24,-10000,0,0) in cell B2

=ipmt(13%/12,a2,24,-10000,0,0) in cell C2

16
 EXCEL NOTES

=B2+C2 in cell D2

Select range of cells B2:D2 ,using fill handle drag up to D13.

Q. Suppose you are borrowing a loan of Rs.500000/- for a period of 10 years at

10% rate of interest per year. Write Spreadsheet function to find principal
part and interest part of loan payment for 20 th and 40th installment.

Ans:

=PPMT(10%/12,20,120,-500000,0,0) in cell A2 and press enter key.

=IPMT(10%/12,20,120,-500000,0,0) in cell B2 and press enter key.

=PPMT(10%/12,40,120,-500000,0,0) in cell A2 and press enter key.

=IPMT(10%/12,40,120,-500000,0,0) in cell B2 and press enter key.

NPER:

The NPER function returns the number of payment periods for a loan, given the
loan’s amount, interest rate, and periodic payment amount.

The syntax for the NPER function is

NPER(rate, pmt, pv, fv,

type)
Example:

1) The following formula calculates the number of payment periods for a

Rs.5,000 loan that has a monthly payment amount of Rs. 117.43. The loan has
a 6 percent annual interest rate.

=NPER(0.06/12,117.43,-5000)

This formula returns 47.997 (that is, 48 months).

2) Suppose you have borrowed Rs.1,00,000 at 8% interest and make payments

of Rs.10000/- per year. And you want know how many years it will take you to
pay back the loan, can be found using:

=NPER(8%,-10000,100000,0,0)

This function displays 20.91.

3) Suppose you have borrowed Rs.5,00,000 at 2% interest and make payments

of Rs.20000/- per year. And you want pay a lump sum of Rs. 50000 in the final
payment period ,and you want know how many years it will take you to pay
back the loan, can be found using:

=NPER(2%,-20000,500000,-50000,0)

17
 EXCEL NOTES

This function displays 32.41

RATE

The Rate function computes the interest or Discount Rate on future cash flows.

The transactions where the interest rate is not specifically stated, the RATE
function can be used to compute the implicit interest rate.

The syntax is: RATE(nper, pmt, pv, fv, type,

guess)

Example:

1) Suppose a money lender lends 30 day loan of Rs 1000/- and asks you to repay
Rs.1250. You want to find the actual interest rate, and then you can use RATE
function as follows:

=RATE(1,0,1000,-1250,0,.1)*365/30 Shows a high interest rate of 304%.

Here nper is 1 because you are repaying the entire amount along with the
interest.

Here PMT is 0 because there is no installment. The function is multiplied by 365

and

divided by 30 to convert rate into annual percentage.

We have entered guess as 0.1 which we assume before computation.

2) Suppose you have a balance of Rs. 40,000 in a bank and every fortnight, you
are depositing Rs. 200/- and after 26 payments it becomes Rs 62,000/- and
you want to know the rate of growth then use the RATE function as follows.

=RATE (26,-200,-40000, 62000,0,0.1)*26 Shows interest rate = 34%.

NPV:

The NPV function returns the sum of a series of cash flows, discounted to the
present day using a single discount rate. The cash flows don’t have to be the
same amount, but they must be at regular intervals. The syntax is:

NPV(rate, value1, value2, . .

.)

Cash inflows are represented as positive values and the cash outflows are
represented as negative values. If the discounted negative flow exceeds the
discounted positive flows the function will return a negative amount and vice
versa. Here the rate argument is the discount rate. If the NPV is positive, this
indicates that the future cash flow provides a better rate of return than the

18
 EXCEL NOTES

specified discount rate. The positive amount returned by NPV is the amount that
the investor could add to the initial cash flow to get the exact rate of return. A
negative NPV indicates that the investor does not get the required discount rate.
Therefore, to achieve the desired rate the investor would have to reduce the
initial cash flow by the amount returned by the negative NPV.

Example:

1) NPV with Initial Investment:

[Link] decides invest Rs.200000 to buy a tractor and now he wants to

know whether his investment would produce a 10% return if he assumes that
he would receive the following cash flow over a period of 10 years.

Year Rent

1 45000

2 32000

3 25000

4 19000

5 41000

6 34000

7 33000

8 21600

9 35600

10 45700

If you assume that Rent values are entered in cells B2 through B11 and your
investment of Rs.200000is entered in cell B1 as negative value, then NPV can
be used to check whether the investment will provide expected return or not
using the formula:

=NPV (10%, B2:B11) +B1

This function will return Rs.3, 493.37, which is positive value. So we may
conclude that the investment returns more than the expected returns.

2) NPV with No Initial Investment:

19
 EXCEL NOTES

Suppose you assume that you are going to get rent of Rs.300000 for a
property during the first year and Rs.350000 during the second year.
Rs.400000 during subsequent 3years and you want to decide how much initial
investment should be made to produce 10% return, here you can use NPV as
follows:

Here since it is a practice in real estate to collect the rent in advance, we shall
enter the first year rent in cell B2 and subsequent years in cells B3 through
B6, enter the formula:

=NPV (10%, B3:B6) +B2 in cell B7

Displays an amount of Rs.1, 249,286.25 (Initial investment)

3) NPV with Terminal Values:

Suppose you want to find out how much money you want to invest on a
property from which you are going to get a rent of 360000 during the first 3
years and 372000 during subsequent 4 years and thereafter you would like to
sell the property for Rs.450000 and still want to make 10% return on
investment. You can use NPV as follows:

Here you enter the values 360000 in cells B2 through B4 and 372000 in cells
B5 through B8 and 822000 in cell B9. The entry in the cell B9 includes the
terminal value of Rs.450000.

Enter the formula:

=NPV (10%, B3:B9) +B2 in cell B10

Displays an amount of Rs.2, 381,146.51 (Initial investment)

IRR:

IRR function returns the discount rate that makes the net present value of an
investment zero. In other words IRR is a special case of NPV.

The Syntax is IRR (range,

guess)

Note: The range argument must not contain an empty cell. Here guess
argument is a random value which acts as a ‘seed’ for the computation
process. One essential requirement of the IRR function is that there must
be both positive and negative income flows.

Example:

20
 EXCEL NOTES

Suppose you have invested a capital of Rs.10, 000 entered in cell A1, during
the first year and your return would be Rs.8000 for the first year, Rs.1500
entered in cells A2 through A6 for subsequent four years and loss of Rs.1500
entered in cell A7 during the last year, then to compute IRR use the formula.

=IRR (A1:A7, 0.1) HERE .1 IS A GUESS VALUE.

Displays rate as 15%.

Using LOOKUP function:

Lookup functions enable to “look up” values from work sheets ranges.
Spreadsheet allows you to perform both vertical lookups (VLOOKUP) and
horizontal lookups (HLOOKUP). In a vertical lookup, the lookup operation starts in
the first column of the worksheet range.

In a horizontal lookup, the operation starts in the first row of a worksheet range.

LOOKUP functions:

This function returns a value either from a row or column or from an array. The
LOOKUP function has the following Syntax

LOOKUP(lookup_value,lookup_vector,result_vect
or)

Where: lookup_value is the value to be looked up in the lookup_vector.

lookup_vector is a single column or a single row range that contains

the values to be looked up. These values must in ascending order.

result_vector is the single column or the single row that contains the
values to be returned. It must be the same size as the lookup_vector.

Example:

1) Suppose Roll no. , Name of [Link] students are entered in cells A5:A1000,
B5:B1000 respectively. And you want search and find name of roll no. say 81.

Assuming that A1 is an input cell for roll no. and you want see the name in cell
B1 then you enter the formula

=LOOKUP (A1, A5:A1000, B5:B1000)

VLOOKUP functions:

21
 EXCEL NOTES

Vertical lookup, searches for a value in the first column of a table and returns a
value in the same row from a column you specify in the table. The lookup table is
arranged vertically. The syntax for the VLOOKUP function is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Where : Lookup_value: The value to be looked up in the first column of the

lookup table.

Table_array: The range that contains the lookup table.

Col_index_num : The column no. within the table from which the
matching value is returned.

Range_lookup: optional. If true or omitted, an approximate match is

returned. If false ,an exact match is returned. If the exact match is not
found, then the function returns #N/A.

Example:

1) Suppose Employee id, Name and basic pay workers are re entered in cells
A5:A24, B5:B24 and C5:C24 respectively. And you want search and find name
and bonus of an employee where bonus is 30% of basic using cell A1 as input
cell for employee id . and name and bonus to be displayed in B1 and C1
respectively, then you enter the formula

=VLOOKUP(A1,$A$24:$C$24,2,FALSE) in cell B1

=VLOOKUP(A1,$A$24:$C$24,3,FALSE)*30% in cell C1

2)The following table shows offers from telecom company based on no. of
connections.

No. of Monthly
connections rentals/connection

1-5 300/-

6-10 250/-

11-20 200/-

Above 20 150/-

To write a formula that yields total cost of purchasing any no. of connection is
as follows: no. of con rate
Enter 0 300

6 250
22
11 200

21 150
 EXCEL NOTES

In cells A3:B7.

Enter the formula

=VLOOKUP(A1,$A$4:$B$7,2,TRUE)*A1 in B1

If you enter no. of connections in cell A1 then total cost is displayed in cell B1.

For example if you enter 13 in cell A1 then 1300 is displayed in cell B1.

3) The following is the Policy of letter Grading:

Marks Grad
e

0-30 E

31-50 D

51-70 C

71-80 B

81- A
100

Suppose in a worksheet roll no. and Marks 100 students are entered in cells
A5:A105, and B5:B104, you may use VLOOKUP function to decide letter Grade
to appear in Cells C5:C104 . Enter the above table in the form shown below in
cells E5:F10

Mark Grad
s e

0 E

31 D

51 C

71 B

Enter the formula 81 A

=VLOOKUP(B5,$E$6:$F$10,2,TRUE) in cell C5 and press enter Key.

Using the fill handle of cell C5 drag up to cell C104.

HLOOKUP functions:

23
 EXCEL NOTES

The HLOOKUP work just like the VLOOKUP function except that the lookup table
is arrange horizontally instead of vertically. The Syntax is similar to VLOOKUP.

HLOOKUP(lookup_value,table_array,row_index_num,range_looku
p)

Where: Lookup_value: The value to be looked up in the first row of the lookup
table.

Table_array: The range that contains the lookup table.

Row_index_num :The row no. within the table from which the matching
value is returned.

Range_lookup: optional. If true or omitted, an approximate match is

returned. If false ,an exact match is returned. If the exact match is not
found, then the function returns #N/A.

Example:

A wholesaler has the following policy of discount

Sales Discount
s

Below 10,000 2%

Above 10,000 And Below 5%

50,000

Above 50,000 and Below 10%

1,00,000

Now in a Above 1,00,000 20% worksheet

customer id and Sales amount
of 20 customers are entered in cells A5:A24 and B5:B24 respectively. Using
HLOOKUP you can calculate discount as follows, enter the following details in
cells A1:E2.

10000
Sales 0 10000 50000 0

Discoun
t 2% 5% 10% 20%
In Cell C5 enter the formula
=HLOOKUP(B5,$B$1:$E$2,2,TRUE)*B5 and press enter key.

Using the fill handle of cell C5 drag up to C24.

IF Function

24
 EXCEL NOTES

It returns one value if a condition you specify evaluates to True and another
value if it evaluates to False.

The Syntax is:

IF(logical_test, value if true, value if
false)

Example:

In a worksheet sales is entered in cells A5: A24 and you want to compute
commission on sales @ 3% of sales when ever sales is less than 10,000 and 5%
otherwise, and you want this appear in cells B5:B24, then enter the formula

= IF(A5<10000,A5*3%,A5*5%) in cell B5 and press enter key, using the fill

handle of this cell drag up to B24

COUNTIF Function

This function is used to count number of cells in a range that meets a given
criterion, the syntax is,
COUNTIF (range, criterion)

Where: Range is the range of cells in which you want to count cells meeting a
given criterion

Criterion is a number, date or expression that determines whether to

count a given cell in the range.

Example:

1) Suppose in Cells A1: A31, Sales of a shop during 31 days of a month has been
entered and you want to find on how many days sales exceeds Rs.2,00,000.
Enter the formula

=COUNTIF(A1:A31,”>200000”) and press enter key.

2) Suppose date of joining of employees are entered in cells D5:D49, and you
want to find no. of employees who have joined before 01/01/2000 in cell D55,
Enter the formula =COUNTIF(D5:D49,”<01/01/2000”) and press enter key.

3) Suppose marks of students are entered in cells say B10:B50 and you want to
find number of cells where mark is exactly 100. Enter the formula

=COUNTIF(B10:B50,100) and press enter key.

SUMIF Function:

25
 EXCEL NOTES

This function is used to find the total of cells in a range that meets a given
criterion, the syntax is,
SUMIF (range, criterion,
[sum_range])

Where: Range is the range of cells in which you want to sum cells meeting a
given criterion

Criterion is a number, date or expression that determines whether to

sum a given cell in the range.

Sum_range is the range of cells that are added, if sum range is omitted it
is assumed to be the same as range.

Example: Suppose Name of Dept and Basic pay are entered in cells C5:C54 and
D5:D54 respectively and you want to find the total of Basic pay of only those in
“SALES” department then enter the formula

=SUMIF(C5:C54,”SALES”,D5:D54) in cell D60.(SAY)

ROUND Function

Round a number to a specified number of digits.

ROUND(number,
Syntax num_digits)

Number is the number you want to round

Num_digits specifies the number of digits to which you want to round number.

If num_digits is greater than 0 (zero), then number is rounded to the specified

number of decimal places.

If num_digits is 0, then number is rounded to the nearest integer.

If num_digits is less than 0, then number is rounded to the left of the decimal
point

Examples

=ROUND(2.15,1) will display 2.2

=ROUND(2.149,1) will display 2.1

=ROUND(-1.475,2) will display -1.48

=ROUND(21.5,-1) will display 20 (nearest 10)

=ROUND(155.89,-2) will display 200 (nearest

100)

=ROUND(155.89,0) will display 156 (nearest integer)

26
 EXCEL NOTES

=ROUND(155.48,0) will display 155 (nearest integer)

ROUNDDOWN Function

Rounds a number down, toward zero.

Syntax ROUNDDOWN(number,num_di
gits)

Number is any real number that you want rounded down.

Num_digits is the number of digits to which you want to round number.

Remarks

ROUNDDOWN behaves like ROUND, except that it always rounds a number

down.

If num_digits is greater than 0 (zero), then number is rounded down to the

specified number of decimal places.

If num_digits is 0, then number is rounded down to the nearest integer.

If num_digits is less than 0, then number is rounded down to the left of the
decimal point.

Example

The example may be easier to understand if you copy it to a blank worksheet.

EXAMPLES

=ROUNDDOWN(3.2, 0) will display 3

=ROUNDDOWN(76.9,0) will display 76

=ROUNDDOWN(3.14159, 3) will display 3.141

=ROUNDDOWN(-3.14159, 1) will display -3.1

=ROUNDDOWN(31415.92654, -2) will display 31400

ROUNDUP Function

Rounds a number up, away from 0 (zero).

27
 EXCEL NOTES

Syntax ROUNDUO(number, num of digits)

Number is any real number that you want rounded up.

Num_digits is the number of digits to which you want to round number.

Remarks

ROUNDUP behaves like ROUND, except that it always rounds a number up.

If num_digits is greater than 0 (zero), then number is rounded up to the specified

number of decimal places.

If num_digits is 0, then number is rounded up to the nearest integer.

If num_digits is less than 0, then number is rounded up to the left of the

decimal point.

Example
=ROUNDUP(3.2,0) will display 4

=ROUNDUP(76.9,0) will display 77

=ROUNDUP(3.14159, 3) will display 3.142

=ROUNDUP(-3.14159, 1) will display -3.2

=ROUNDUP(31415.92654, -2) will display 31500

CEILING Function

Returns number rounded up, away from zero, to the nearest multiple of
significance. For example, if you want to avoid using 25 Paise in your prices and
your product is priced at Rs.4.42, use the formula =CEILING(4.42, 0.5) to round
prices up to the nearest 50 paise.

Syntax

28
 EXCEL NOTES

CEILING ( Number, Significance)

Number is the value you want to round.

Significance is the multiple to which you want to round.

Remarks

If either argument is nonnumeric, CEILING returns the #VALUE! error value.

Regardless of the sign of number, a value is rounded up when adjusted away

from zero. If number is an exact multiple of significance, no rounding occurs.

If number and significance have different signs, CEILING returns the #NUM! error
value.
EXAMPLES

=CEILING(2.5, 1) will display 3

=CEILING(-2.5, -2) will display -4

=CEILING(-2.5, 2) will display #NUM!

=CEILING(1.5, 0.1) will display 1.5

=CEILING(1255,25) will display 1275

FLOOR Function

Rounds number down, toward zero, to the nearest multiple of

significance.

Syntax

FLOOR ( Number, Significance)

Number is the numeric value you want to round.

Significance is the multiple to which you want to round.

Remarks

If either argument is nonnumeric, FLOOR returns the #VALUE! error

value.

If number and significance have different signs, FLOOR returns the

#NUM! error value.

29
 EXCEL NOTES

Regardless of the sign of number, a value is rounded down when

adjusted away from zero. If number is an exact multiple of significance,
no rounding occurs.

Example

=FLOOR(2.5, 1) will display 2

=FLOOR(-2.5, -2) will display -2

=FLOOR(-2.5, 2) will display #NUM!

=FLOOR(1.5, 0.1) will display 1.5

=FLOOR(1255,25) will display 1250

INT Function

Rounds a number down to the nearest lower integer

INT(number
Syntax )

Number…is the real number you want to round down to an integer

Examples

=INT(8.9) will display 8

=INT(8.1) will display 8

=INT(-8.9) will display -9

=INT(-8.1) will display -9

=INT(7) will display 7

=INT(-3) will display -3

MAX Function

Returns the largest value in a set of values

MAX(number1,
number2………)
Syntax

Number1, number2,…… are 1 to 30 numbers for which you want to find the
maximum value. You can specify arguments that are numbers, empty cells,
logical values, or text representations of numbers. Arguments that are error
values or text that cannot be translated into number cause errors.

30
 EXCEL NOTES

If an argument is reference, only numbers in that reference are used. Empty

cells, logical values, or text in the reference are ignored.

If the arguments contain no number, MAX returns 0 (zero)

Examples

If A1:A5 contains the number 10, 7, 9, 27 and 2 then:

=MAX(A1:A5) will display 27

=MAX(A1:A5,30) will display 30

MIN Function

Returns the smallest number in a set of values

MIN(number1, number2,
………)
Syntax

Number1, number2,… are 1 to 30 numbers for which you want to find the
minimum value, you can specify arguments that are number, empty cells, logical
values, or text representation of numbers. Arguments that are error values or
text that cannot be translated into number cause errors.

If an arguments is reference, only number in that array or reference are used.

Empty cells, logical values, or text in the reference are ignored.

If the arguments contain no number, MIN returns 0.

Examples

IF A1:A5 contains the number 10, 7, 9, 27 and 2 then:

=MIN(A1:A5) will display 2

=MIN(A:A5,0) will display 0

MOD Function

Returns the remainder after number is divided by divisor. The result has the
same signs as divisor.

MOD(number,
Syntax divisor)

Number is the number for which you want to find the remainder

31
 EXCEL NOTES

Divisor is the number by which you want to divide number. If divisor is 0, MOD
returns the #DIV/0!

Examples

=MOD(23,5) will display 3

=MOD(23,-5) will display -2

=MOD(-23,5) will display 2

=MOD(-23,-5) will display -3

=MOD(5,23) will display 5

SQRT Function

Returns a positive square root.

SQRT(number
Syntax )

Number is the number for which you want the square root. If number is
negative, SQRT returns the #NUM! error value.

Examples

=SQRT(16) will display 4

=SQRT(-16) will display #NUM!

=SQRT(ABS(-16)) will display 4

ABS Function

Returns the absolute value of a number. The absolute value of a number is the
number without its sign

ABS(numbe
Syntax r)

Number…. Is the real number of which you want the absolute value.

Examples

=ABS(2) will display 2

=ABS(-2) will display 2

32
 EXCEL NOTES

If A1 contains -16 then:

=SQRT(ABS(A1)) will display 4

AVERAGE Function

Returns the average (arithmetic mean) of the arguments

AVERAGE(number1, number2,
Syntax ………..)

Number1, number2…. are 1 to 30 arguments for which you want the average.

The arguments must be either numbers or name or reference that contain

numbers.

If reference argument contains text, logical values, or empty cells, those values
are ignored; however, cell with the value zero are included.

When averaging cells, keep in mind the difference between empty cell and those
containing the value zero. Empty cells are not counted, but zero values are
counted.

If A1:A5 is named scores and contains the numbers 10, 7, 9, 27, and 2 then;

=AVERAGE(A1:A5) will display 11

=AVERAGE (scores) will display 11

=AVERAGE(A1:A5,5) will display 10

=AVERAGE(A1:A5) will display 11

LEFT Function

Returns the first (or leftmost) character or characters in a text string

LEFT(text,
num_chars)
Syntax

Text is the text string that contains the character you want to extract.

Num_chars specifies how many characters you want LEFT to extract.

Num_chars must be greater than or equal to zero

33
 EXCEL NOTES

If num_chars is greater than the length of text, LEFT returns all of text.

If it is omitted, it is assumed to be 1.

Examples

=LEFT(“mathematics”,5) will display mathe

=LEFT(“mathematics”) will display M

=LEFT(“Air India”.5) will display Air I

RIGHT Function

Returns the last (or right most) character or characters in a text string.

RIGHT(text,
Syntax num_chars)

Text is the text string containing the characters you want to extract

Num_chars specifies how many characters you want to extract.

Num_chars must be greater than or equal to zero.

If num_chars is greater than the length of text, RIGHT returns all of text.

If num_chars is omitted, it is assumed to be 1.

Examples

=RIGHT(“sales Price”,5) will display Price

=RIGHT(“sale Price”,4) will display rice

=RIGHT(“sale Price”,3) will display ice

=RIGHT(“sale price”) will display e

=RIGHT(“Stock Number”) will display r

MID Function

Returns a specific number of characters from a text string, starting at the

position you specify

MID(text, start_num,
num_chars)
Syntax

34
 EXCEL NOTES

Start_num is the position of the first character you want to extract in text. The
first character in text has start_num 1, and so on.

If start_num is greater than the length of text, MID returns “” (empty text).

If start_num is less than the length of text, but start_num plus num_chars
exceeds the length of text, MID returns the character up to the end of text.

If start_num is less than 1, MID returns the #VALUE! Error value.

Num_chars specifies how many characters to returns from text. If num_chars is

negative MID returns the #VALUE! Error value.

Examples

=MID(“Ganesha”,3,2) will display “ne”

=MID(“Ganesha”,3,4) will display “nesh”

LEN Function
LEN(text)
Returns the number of characters in a text string

Text is the text whose length you want to find. Space count as character.

Examples

=LEN(“Air India (A)”) will display 13

=LEN(“ABC”) will display 3

=LEN(“”) will display 0

UPPER Function
UPPER(text
Converts all lowercase letters in text string to uppercase. )

Text is the text you want converted to uppercase. Text can be a reference or
text string

Examples

=UPPER(“total”) will display TOTAL

=UPPER(“A Computer”) will display A COMPUTER

LOWER Function LOWER(text)

Converts all uppercase letters in text string to lowercase.

Text is the text you want to convert to lowercase. LOWER does not change
character in text that are not letters. Text cab be a reference or text string

Examples

35
 EXCEL NOTES

=LOWER(“NAMASTE”) will display namaste

=LOWER(“Apt. 2B”) will display apt. 2b

=LOWER(“Deepa”) will display deepa

PROPER Function

Capitalize the first letter in a text string and other letter in text that follow any
character other than a letter. Convert all other letters to lowercase letters.

PROPER(text
Syntax )

Text is the text enclosed in quotation marks, a formula that returns text, or a
reference to a cell containing the text you want to partially capitalize

Examples

=PROPER(“this is a TITLE”) will display This Is A Title

=PROPER(“2-cent’s worth”) will display 2-Cents’s Worth

=PROPER(“76BudGet”) will display 76Budget

TRIM Function

Removes all spaces from text except for single spaces between words. Starting
as well as ending blank space are removed. Except a single all other spaces
between two words are also removed. Syntax
TRIM(text)

Text is text from which you want spaces removed.

Example

=TRIM(“First Quarter Earning”) will display first Quarter Earning

=TRIM(“ TEST MATCH “) will display TEST MATCH

TODAY Function

Returns the current date in American format i.e. mm/dd/yy. (however, it depends
on regional settings of Windows). Syntax
TODAY( )
Example

Assuming that the date is set at 14th December 99

=TODAY() Will display 14/12/99

=TODAY()+1 will display 15/12/99

36
 EXCEL NOTES

=TODAY()-1 will display 13/12/99

NOW( )

Returns the current date and time

Numbers to the right of the decimal point in the serial number represent the
time; number to the left represent the date. For example, the serial number
367.5 represents the date-time combination 12:00 P.M, January 1, 1901

The NOW function changes only when the worksheet is calculated or when a
macro that contains the function is run. It is not updated continuously.

Examples

If your computer’s built-in clock is set to 4:30:00 PM, 1-Aug-1987, then

=NOW() will display 8/1/97 16:30

Ten minutes later

=NOW() will display 08/01/87 16:40

DATE Function

Returns a particular date in American format [Link]/dd/yy, (however, it depends

on regional settings or windows).

DATE(year, month,
Syntax day)

Year is a number from 1900 to 9999

Month is number representing the month of the year. If month is greater than 12,
then month adds that number of months to the first month in the year specified.

Day is a number representing the day of the month. If day is greater than the
number of days in the month specified, then day adds that number of days to the
first day in the month. For example. DATE(91,1,35) returns the serial number
representing February 4, 1991

Examples

=DATE(91,1,) will display 1/1/91

=DATE(1999,12,25) will display 12/25/99

=DATE(2000,4,24) will display 04/24/00

37
 EXCEL NOTES

=DATE(1998,14,25) will display 02/25/99

(month is 14 i.e. Feb of next year)

=DATE(1999,6,31) will display 07/01/99

(June cannot have 31 days)

TIME Function

Returns the particular time in hh:mm AM /PM form

TIME(hour, minutes,
Syntax second)

Hours is a number from 0 (zero) to 23 representing the hour. However, you can
type hour as 25 and it will be taken as 1AM of the next day.

Minute is a number from 0 to 59 representing the minute. However, you can

type minutes >59

Second is a number from 0 to 59 representing the second. However, you can

type seconds >59

Examples

=TIME (12,0,0) will display 12.00 PM

=TIME(6,34,0) will display 6.34 AM

=TIME(18,39,58) will display 6.39 PM

=TIME(25,0,0) will display 1.00 A.M

=TIME(16,66,0) Will display 5.06 PM

=TIME(3,30,70) will display 3.31 AM

DAY Function

Returns the day of the month corresponding to serial number or date-text. The
day is given as an integer ranging from 1 to 31

Syntax DAY(serial_number)

38
 EXCEL NOTES

Serial number is calculated as number of days from 1 st January, 1900 to the

given date.

1 January, 1900 =1

2 January, 1900 =2

31 January,1900 =31

1 February,1900 =32

2 February,1900 =33

31st December,1900= 366

1 January, 1901 = 367 and so on

Examples

=DAY(32) will display 1

=DAY(366) will display 31

=DAY(“12/25/2009”) will display 25

=DAY(“06/05/2009”) will display 6

MONTH Function

Returns the month corresponding to serial number or date text. The month is
given as an integer, ranging from 1 (January) to 12 (December).

MONTH(serial_numb
Syntax er)

Examples

=MONTH(32) will display 2

=MONTH(34) will display 2

=MONTH(“05/06/2009”) will display 5

=MONTH(366) will display 12

=MONTH(367) will display 1

39
 EXCEL NOTES

YEAR Function

Returns the year corresponding to serial number or data text. The year is given
as an integer in the range 1900-9999

YEAR(serial_number)
Syntax

Examples

=YEAR(33) will display 1900

=YEAR(367) will display 1901

=YEAR(“12/25/2005”) will display 2005

WEEKDAY Function

Returns the day of the week corresponding to serial number or date text. The
day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday)

WEEKDAY(serial_number,
Syntax return_type)

Return_type is a number that determines the type of return value

Returns type number returned

1 or omitted number 1 (Sunday) through 7(Saturday)

2 number 1 (Monday) through 7(Sunday)

3 number 0 (Monday) through 6(Sunday)

Examples

=WEEKDAY("06/12/2009",1) will display 6 (Friday)

=WEEKDAY("06/12/2009",2) will display 5 (Friday)

=WEEKDAY("06/12/2009",3) will display 4 (Friday)

DAYS360 Function

40
 EXCEL NOTES

Returns the number of days between two dates based on a 360-day year (twelve
30-day months), which is used in some accounting calculation. Use this function
to help compute payments if your accounting system is based on twelve 30-day
month

DAY360(start_date, end_date, method)

Syntax

Start_date and end_date are the two dates between which you want to know the
number of days. If start_date occurs after end_date, DAYS360 returns a negative
number.

Method is logical value that specifies whether to use the U.S or European method
in the calculation

Method defined

FALSE or omitted U.S method. If the starting date is the 31 st of a month, it

becomes equal to the 30th of the same month. If the ending date is the 31 st
month and the starting date is less than the 30 th of the month, the ending date
becomes equal to the 1st of the next month otherwise the ending date becomes
equal to the 30th of the same month.

TRUE European method. Starting dates or ending dates that occur on the 31 st of
a month becomes equal to the 30th of the same month.

To determine the number of days between two dates in a normal year, you can
use normal subtraction, for example, “12/31/93”-“01/01/93” equal 364

Example

=DAYS360(“05/01/92”, “05/01/93”) will display 360

=DAYS360(“05/01/92”, “05/01/94”) will display 720

=DAYS360("01/01/2009","06/11/2009") will display 160

=DAYS360("06/01/2009","06/11/2009") will display 10

41