This
chapter covers the following topics:
Ø
What is an Electronic
Spreadsheet
Ø
How to start Excel
Ø
How to navigate through the
Excel working environment.
Ø
Components to a worksheet
Ø
Exiting Excel
An
electronic spreadsheet is an electronic replacement for the traditional
financial modeling tools i.e. the accountant's columnar pad, pencil, and
calculator. These offer dramatic improvements in creating, editing and
recalculations. Among the various spreadsheet packages available MICROSOFT
EXCEL is a popular choice. It is a part of a bigger package of applications
called MICROSOFT OFFICE. As the name suggests it is a product of MICROSOFT the
maker of WINDOWS operating systems.
Before
you start EXCEL, ensure that EXCEL is installed on your machine. Its icon is
generally found in the PROGRAMS sub menu (inside the START MENU). At times it
may be inside the MICROSOFT office Group inside the PROGRAMS menu. Press enter
or double-click the application named Microsoft Excel
When
you start Excel, two windows appear on the screen, one nested snugly within the
other. The larger of these is called the application window, frames the entire
screen; you use it to communicate with the Excel program. The smaller window,
called the document window, is used to create and edit Excel worksheets and
charts.
The
Excel application window is like a program manager. It provides you with
tools, commands, and status messages to use with your worksheets.
These
elements displayed are as follows:
1.
Title Bar: The bar at the top of the application window. It displays the name
of the application - Microsoft Excel. ** INSER + T reads the title bar.
2.
Menu Bar: The bar below the title bar which is used to choose commands from
Excel's drop-down menu system. The items on the menu bar are as follows:
File Edit View Insert Format Tools
Data Window Help
Menu bar can be accessed by the mouse or
keyboard. To access the menu bar with the keyboard activate the menu using the
ALT key or F10 function key and then use the arrow keys to move to a particular
menu choice. The hot keys for the different menu items can also be used. Note
that the hot key for menu items is the letter underlined in their names as
shown in the name of menu items above.
3.
Standard toolbar: The bar below the menu bar. You click on its tool buttons
to choose frequently used commands and utilities.
4.
Formatting toolbar: The bar below the standard toolbar. You click on its tool
buttons to choose frequently used formatting commands.
5.
Formula bar: The bar below the formatting toolbar. It displays the contents
of the currently active worksheet cell.
5.
Status bar: The bar at the bottom of the application window. It displays
information about the currently selected command and the current status of the
workspace. ** INSERT + PAGE DOWN reads the status bar.
The
Excel document window displays the currently active worksheet and the workbook
in which it is contained. The elements are displayed as follows:
1.
Title bar: The bar at the top of the document window. It displays the name of
the workbook (book 1).
2.
Worksheet tabs: The tabs at the bottom of the document window. You use these
to select a different worksheet within the current workbook. CONTROL + PAGE UP
or PAGE DOWN can be used to scroll between the different worksheets in a
workbook.
3.
Worksheet-tab scroll buttons: The set of four boxes to the left of the
worksheet tabs. You use these to scroll through the worksheet tabs.
4. Scroll bars: The vertical and the horizontal scroll
bars framing the right and lower borders of the document window. You use the
scroll bars, along with the scroll boxes and scroll arrows they contain, to
change which portion of the worksheet is displayed in the document window. The
keyboard options for scrolling on a given worksheet are given in the next
section.
5.
Column headings: The letters at the top of the worksheet column.
6.
Row headings: The number at the left of each worksheet row.
In
Microsoft EXCEL, files are called Workbooks. When you open a new workbook,
you have 3 blank sheets named Sheet1, Sheet 2 & sheet 3. You can enter data
in more than one sheet at a time, switch between sheets. You can switch sheets
with the keyboard use Ctrl+ pageup to
move to previous sheet or Ctrl + Page
down to move to the next sheet.
An
Excel worksheet is an electronic version of a paper spreadsheet. You arrange
your data in rows and columns. A worksheet consists of a grid of 256 columns
and 65,536 rows.
Columns
headings are designated by alphabets - beginning with letter A and continue
through the letter z. After the 26th column, headings become double letters
that is AA through IV.
Rows
are designated by numbers running down the left border. So the first row is called
row 1 and the last row is row 65,536.
The
intersection of a column and row is called a cell. For example, cell A1 is the
intersection of the first column with the first row. Data is entered directly
in the currently active cell. An active cell is the one that is outlined by a
dark border. Only one cell can be active at a time. The name of the active
cell appears in the cell-reference area on the far left of the formula bar. The
screen reader reads out the address of the cell as one moves between cells
using the ARROW keys. The cell address of active cell can be read by the
command **INSERT + C.
Left
one cell -- Left arrow
Right
one cell --- Right arrow
Up
one cell --- Up arrow
Down
one cell -- Down arrow
Up
one screen -- Page up
Down
one screen -- Page down
First
cell in a row that contains data -- Ctrl
+ Left arrow
Last
cell in a row that contains data --- Ctrl
+ Right arrow
Start
of worksheet -- Ctrl + Home
**Move
to prior screen in spreadsheet ALT+PAGE UP
**Move
to next screen in spreadsheet ALT+PAGE DOWN
You
can select several cells at once by holding down the Shift while you click the
mouse on the cells.
To
select several adjacent cells
1.
Select the first cell in the set
2.
Hold down Shift.
3.
Press repeatedly the arrow
key in the direction of the last cell in the set to be selected or simply
select the first cell and drag to the last cell with the mouse.
4.
For selecting entire
columns and rows use-
Select
column CTRL+SPACEBAR
Select
row SHIFT+SPACEBAR
When
you finish an Excel work session and want to exit Excel, you can do either of
the following:
Open
the File menu to display the drop-down menu. In this menu, click or press
ENTER on EXIT.
,,,,,,,,,,,,,,,,,,,
This
chapter covers the following topics:
Ø
Entering data
Ø
Saving Files
Ø
Closing Files
Ø
Opening Files
You
can enter text, numbers, and formulas into any cell on a worksheet. You simply
select a cell and type the data.
Follow these guidelines when entering the
numbers.
You
can include commas in numbers such as 1,000.
A
single period in a numeric entry is treated as a decimal point.
Plus
sign entered before a number are ignored.
Precede
negative numbers with a minus sign or enclose them within parentheses.
Text
can include alphabets, numbers or special characters. To enter numbers as text,
precede the entry with an apostrophe as follows:
'244
entry will be treated as text and not number.
When
you need to perform calculations in Excel, you use a formula. You can create
formulas by preceding the entry with an equal sign (=). This will be discussed
later on.
Excel
allows you to enter date or time. Use the following guidelines when entering date
or time:
If
you want to display time using the 12 hour clock , type am or pm; for example: 3.00 pm or
3,00 am. Unless you type am or pm Excel automatically displays the time using
24 hour clock.
You
can type a date and time in the same cell. Just separate the date and time by
a space.
To
enter dates, use either a / or as separator.
The following keyboard shortcuts can be used to
insert current date or time in the default format.
Date
stamp CTRL+; (SEMICOLON)
Time
stamp CTRL+SHIFT+; (SEMICOLON)
To
save any worksheet , one will have to save the entire workbook in which the
worksheet is contained.
1.
Select the File menu.
2.
Choose the Save As option
3.
Type a file name (let's
call it First) and save it in the desired location. Note that excel provides
the same type of save as dialog box as seen in MS WORD or in any other office
application.
To
close the file, select the file menu and choose the Close command. The keyboard
shortcut is ALT + F4.
To
open an existing worksheet
1.
Click the open button on the tool bar
Or
Select
the open option from the file menu.
2.
Select the appropriate drive and directory from the LOOK IN combo box.
3.
Select the file name from the file list and choose OK.
,,,,,,,,,,,,,,,,,,,,,,,,,
This
chapter covers the following topics:
Ø
Editing Data
Ø
Undoing Changes
Ø
Removing Data
Ø
Copying and Pasting Data
Ø
Moving Data
Ø
Inserting & Deleting
o Rows
o Columns
o Cells
Editing
data is making changes to an existing data. You can edit data in two places on
a sheet.
You
can select the cell and edit data in the formula bar OR you can edit the data
right in the cell.
For
example, let us assume that the cell D6 contains the figure 26,500. To change
it to 25,600 do the following .
1.
Use arrow keys to select
cell D6
2.
Now there are two ways of
editing data in a cell.
3.
The first one is the
OVERWRITE mode in which you type the complete new data and the old data in the
cell gets replaced by it.
4.
The second method is to
make desired changes in the contents of the cell by going into the EDIT MODE.
Press F2, the selected cell then comes into edit mode and then the insertion
pointer can be moved around the data in the cell. Now place the insertion
point at 6. Type 5, and delete the additional 5 after 6.
To
edit the data using mouse, you simply double-click the cell and then move the
insertion point to wherever you need it in the cell. You can then either type
the new information or delete the information that is incorrect.
You
can always correct mistakes which you are typing by pressing the Delete key and
retyping the correct letters or numbers. But what if you select a cell and
clear it or type over the contents of a cell by mistake? You can recover from
such mistakes by-
Using
the Undo command on the Edit menu or its accelerator key CONTROL + Z
OR
Undo
button on the toolbar.
If
you decide that you want to keep the change after all, you can use the Redo
command on the Edit menu (CONTROL + Y) or use the Redo button.
The
name of the Undo command changes to reflect the specific action that can be
undone.
In
some instances, the Undo command is not available example after you issue a
File Save command.
If
data needs to be removed from a cell, you can easily delete the information
and replace it with new information. You can also remove data from a cell by
selecting the cell and pressing Delete.
Data
can be also erased by selecting the Clear command on the Edit menu.
When
you enter data in the cells, you are not locked in to the arrangement. You
can always rearrange data if you need to that is you could copy and paste data
between cells, insert and delete cells, rows and columns, and even move data
between cells.
If
you need to copy information to another place on the worksheet, you can either
Use
the mouse to drag the data to a new location
OR
Use
copy and Paste buttons on the toolbar
OR
Copy
and Paste commands on the Edit menu.
Before
copying data it is essential to select the cells which contain the data to be
copied. After selecting the cells, select the Copy command from the Edit menu.
A moving dotted line, the marquee, surrounds the selected cells.
You
can use keyboard shortcuts for both the Copy and Paste commands.
To
copy data: Hold down Ctrl and Press C.
To
paste data: Hold down Ctrl and press V.
If
you need to copy a given range to multiple locations in a worksheet choose the
paste command or use the Ctrl-V combinations. The marquee remains active,
signaling that you can paste the selected data in as many locations as you
want. To complete the command, press Enter when you paste the data for the
last time, or press Esc. To remove the marquee.
Just
as you can copy data by dragging with the mouse or by using menu commands, you
can also moving data using the Cut and Paste commands.
Using
Cut and Paste.
Cutting
and Paste with commands is much like copying and pasting with a twist: You
use the Cut command instead of the copy command, and you move data rather than
duplicate it. The shortcut command is CONTROL +X.
Excel
gives you the facility to insert blank rows or columns. To insert a new row
above a row , follow these steps:
1.
Select the row above which the new row is to inserted by clicking its header
or pressing SHIFT + SPACE.
2.
Choose the Row command from the Insert menu. The rows move down, leaving a
new, blank row.
Excel
makes it as easy to insert new columns as it is to insert new rows in your
worksheets. The steps followed for inserting a column are the same as that for
inserting a row. Instead of selecting the row header select the column
header to the right of the location where you need the new column.
Alternatively, press CONTROL + SPACE from the keyboard. Then choose the column
command from the Insert menu.
Inserting Cells:
When
you want to insert only a few cells, simply select the same number of cells
below or to the right of the location where you need new cells. Then choose the
Cells command from the Insert menu. Excel shifts other cells in the worksheet
to make room for the new cells. However, you must first choose from the dialog
box, that opens on giving the command between shifting the selected cells to
the right and shifting the selected cells down.
Deleting
rows and columns is as straightforward as inserting them. To delete a column:
1.
Select the column header of the column to be deleted or select that column by
pressing CONTROL + SPACE
2.
Choose the Delete command from the Edit menu.
To
delete a row, follow step 2 after selecting the row.
The
Delete command on the Edit menu works in much the same manner as the Insert
command. To delete the cell range A4 : A 12, follow these steps:
1.
Select the range A4:A12
2.
Choose the Delete command
from the Edit menu.
3.
Confirm whether to Shift
Cells left or to shift cells up in the dialog box that opens.
4.
Click OK, or press Enter.
NOTE: The Insert and Delete commands on the
Edit menu work only with single rectangular selections. You cannot insert or
delete multiple, noncontiguous ranges with one command.
,,,,,,,,,,,,,,,,,,,
This
chapter covers the following topics:
Ø
Using References
Ø
Writing Formulas
Ø
Creating Simple Formulas
Ø
Order of Evaluation
Ø
Using Cell Addresses in
Formulas
Ø
Copying Formulas
Ø
Totaling Cell Addresses
Automatically
With
references, you can identify cells or groups of cells on a worksheet.
Range
(colon): Produces one reference to the cells enclosed between a rectangular
area using the cell addresses of the two cells at the edges. It is written as
(Top Cell Address : Bottom Cell Address)
For example, if you want to refer to cells A1, A2, A3 & A4 then the range
will be written as
(A1:A4)
Similarly,
if cells A1, A2, A3, B1, B2 & B3 are to be referred, the range will be
written as
(A1:B3)
You
can perform calculations with your data using formulas, which are made up of
data operators and often functions. Microsoft Excel
offers
you a variety of functions that makes calculations easier for you.
To
tell Excel that you're entering a formula in a cell, you must begin the entry
with an equal sign (=).
Lets
create a simple formula with adds the value 25 to 35. In a blank cell lets say
A1 enter
=
25 + 35
After
entering the formula, press the Enter key for Excel to accept the formula. The result
60 will appear in A1. The formula however is displayed in the formula bar.
While navigating around in a worksheet, whenever the pointer selects a cell
which has a formula, the screen reader announces this message. The formula can
be read by giving the command **CONTROL + F2.
Your
formulae can use any of the numeric operators : plus (+) for addition, minus
(-) for subtraction, asterisk (*) for multiplication, and slash (/) for
division. Other kinds of numeric
operators
include percent (%) which divides the preceding value by 100, and caret (^)
which multiplies the preceding value by itself the number of times specified by
the following value .
For
example, if you type = 23 ^ 3 in cell A1 and press enter, Excel multiplies 23
by itself there times and displays the result : 12167 in the cell.
Excel
performs a rigid set of rules, known as order of evaluation, when performing
multiple computations in a single formula. Table 4.1 lists the Microsoft Excel
numeric operators in order to evaluate, from first to last. When two operators
have equal precedence (for example, division and multiplication), Excel
performs the computations from left to right
-
Negation
%
Percentage
^
Exponentiation
/,*
Division, Multiplication
+,-
Addition, Subtraction
let's
take a look at this example
84
* 0.7 ^ 2 /24 * 3.26 - 29
Stepwise
evaluation
1.
Exponentiation = 84 * 0.7 ^ 2 /24 * 3.26 - 29
2.
Multiplication = 84 8 0.49 / 24 * 3.26 - 29
3.
Division = 41.16 / 24 * 3.26 - 29
4.
Multiplication = 1.715 * 3.26 - 29
5.
Subtraction = 5.5909 - 29
6.
Result = -23.4091
You
can override Excel's order of evaluation by enclosing parts of the formulae in
parentheses. Excel then performs enclosed computations before proceeding with
the usual order of evaluation.
For
example:
=
(19+2) / 3
The
sequence of evaluation will be as follows :
1.
Addition = (19+2) / 3
2.
Division = (21) / 3
3.
Result = 7
So
far, the formulas we have used consist solely of values and operators. Formulas
can also refer to the other cells. For example, the formula
=
A1 + A2 + A3
returns
the sum of the values in cells A1,A2,A3
Excel
offers a variety of functions that take a lot of the hard work out of creating
and entering formulas. Refer back to the above formula. Instead of typing
=
A1+ A2 + A3
we
could use the SUM function i.e
=
SUM (A1:A3)
for
finding out the maximum value in a range of cells, the following formula should
be written.
=max(Range)
similarly,
for finding the minimum value
=min(Range)
for
evaluating the average
=average(Range)
some
other important formulas are discussed below:
the
IF formula
For
logical purposes like allotting Grades to students on the basis of
percentage/total marks the IF function is used.
=IF(CONDITION,
OPTION 1, OPTION 2)
Here
CONDITION refers to the basis on which distinction will be made and OPTIONS
refer to the grades/remarks to be given.
If
the number of options is more than two then two or more IF arguments are used
in the same formula.
=IF(CONDITION
1, OPTION 1, IF(CONDITION
2, OPTION 2, IF(CONDITION 3, OPTION 3, OPTION 4)))
The number of Ifs USED is ALWAYS one less than
the number of options AVAILABLE.
For
inserting current date and time
=NOW()
For
counting the number of occurrence of certain text in data:
=COUNTIF(RANGE, TEXT)
For
calculating the future value or the maturity value of an investment:
=FV(RATE%, PERIOD,, PRINCIPAL AMOUNT)
For
calculating the payments to be made for repayment of a loan:
=PMT(RATE%, NUMBER OF INSTALMENTS, LOAN AMOUNT)
For
knowing the weekday weekday function is used.
Syntax
=weekday(date of birth)
You
can copy formulas into a range of cells just as you can copy data into a range.
To copy a formula, select the cell containing the formula and use the copy and
paste command as explained earlier.
One
of the tasks that you need to frequently do is total rows and columns. You
could create a new formula every time you needed to total a row or column, but
Microsoft Excel provides an easier way. The AutoSum button on the Standard
toolbar automatically creates a formula to total the rows and columns for you.
To use the AUTOSUM formula
from the keyboard, press
ALT =
in the cell where you want the total. Excel then displays a SUM formula
containing the range for which totaling will be done. If the range is
acceptable, press ENTER. The result of the AUTOSUM formula is displayed in the
cell.
AutoSum
can be used in three ways :
1.
locate and total the rows
and columns in the range nearest to the current cell, or
1.
2.
total any range you select
or
3.
add grand total to a range
containing other totals
To
automatically total the nearest range, you type the AUTOSUM formula or click
AutoSum bottom and press Enter, or double click the AutoSum button.
To
total a specific range, you select a range and then use the AUTOSUM command or
click the AutoSum button.
When
you use the AutoSum button or type in ALT = once, the formula is created, and
then have the option of accepting it (by pressing Enter), or modify it.
Whichever
method you use, be sure that there is a blank row and column around your data
for the cells you want to total.
,,,,,,,,,,,,,,,,,,,,
This
chapter covers the following topics
Ø
Formatting Data
Ø
Changing Column Width and Row
Height
