Spreadsheet Analysis

Unit 7: Spreadsheet Analysis

Lesson 1: Introduction to the Spreadsheet

Learning Objectives

On completion of this lesson you will be able to learn :

• what a spreadsheet is
• a brief history of spreadsheet
• what the spreadsheet is for
• the names of few available spreadsheets package.

The majority of application software developed for business is designed

to meet the information needs of management. Besides decisions are
commonly based on numerical information.

You may be a business person, a student or a teacher, or perhaps you are

an accountant, lawyer, physician, financial analyst, or real estate
investor. In many situations, you have a bunch of numerical data which
you need to organise to assist you in making a decision. You usually
know what formulas to use and you can organise them with pencil, paper
Spreadsheet and a pocket calculator. Sometimes this is very tedious and a change in
one data will require a complete recalculation.

Did you ever wonder if you could afford to buy a computer? If you save
100 taka a month this year and increase the amount by 5% each year,
how much can you save in 5 years? What if you increase the amount by
8% instead? What if you start with 150 taka a month? This work
involves many hours of tedious calculations and recalculations.

This is where an electronic spreadsheet program comes in. It lets the

computer be the pencil, the sheet of paper, and the calculator.

Spreadsheet software has three advantages over pencil, paper and

calculator. First, the speed and accuracy at which the computer can
perform calculations enables the user to review data trends much sooner
than if done by hand. Second, the electronic spreadsheet has built into it
all the basic financial, mathematical, statistical, and scientific formulas.
This greatly enhances the efficiency of the user. Finally, if any of the
data is modified, the electronic spreadsheet recomputes the entire
spreadsheet automatically and almost instantly. Any thing that can be
done with a pencil, a pad of paper, and a calculator can be done faster
and far more accurately with an electronic spreadsheet.

157
 Office Automation and MS Office

A Brief History

Two Harvard Business School students, Daniel Bricklin and Robert

Frankston, developed the first electronic spreadsheet program in 1978.
They named it VisiCalc. It was initially written for Apple II personal
computer, but it has since been converted to run virtually on any micro.

The demand for additional features and sophistication led to the

History development of a new generation of spreadsheet software. This
generation was born with the introduction of Lotus 1-2-3 in 1982. It was
written explicitly for the IBM PC.

Since 1-2-3, another generation of spreadsheets has been introduced.

This includes a word processor, increased the spreadsheet capability,
more graphic functions and a network communication capability.
Framework and Symphony are two of the more popular third generation
spreadsheet programs.

Enable, QuattroPro, Multiplan, PeachCalc, pfs:plan, Smart Spreadsheet,

SuperCalc3, -Microsoft Excel are other examples of spreadsheet
software available. Of all these, Lotus 1-2-3 and Excel are most widely
used in our country.

What the spreadsheet is for

Nowadays electronic spreadsheets are a multifaceted tool. Though they
are mainly intended for business accounting purposes, yet scientific and
engineering, presentation graphics, database management applications
are very common. Here a number of possible applications are described.

Spreadsheets can be used to help automate financial statements, business

forecasting, transaction registers, inventory control, accounts receivable,
accounts payable etc.

Financial models can be created that let you play what-if. By changing
one or more variables, the model recalculates, and a new set of results
can be presented in tabular and/or graphical format.
Electronic spreadsheet (also called worksheets) provides many built-in
statistical, analytical, and scientific formulas. Thus it can be used in
many scientific and engineering environments to crunch numbers and
present findings.
Nowadays spreadsheets support powerful, flexible graphical
presentations. Worksheets can include charts and graphs, and can be
formatted for high impact presentations.
Spreadsheets can also be used as a database management tool. Here
database manipulation is simple. You can perform many database

158
Spreadsheet Analysis

requirements very quickly though it lack certain features found in typical

database management programs.

Electronic spreadsheets can also be used as a powerful application

development tool. You may create serious large scale custom
applications using them.
Exercise

1. Multiple Choice Questions :

a. Which spreadsheet program was developed first?

i) Lotus 1-2-3
ii) PeachCalc
iii) VisiCalc
iv) Microsoft Excel.

b. Which pair of individuals is most closely associated with the

development of the first spreadsheet ?

i) Woznick and Jobs

ii) Bricklin and Frankston
iii) Burns and Allen
iv) Boole and Babbage.

c. An electronic spreadsheet is superior to manual calculations

because:

i) The spreadsheet computes faster

ii) The spreadsheet computes its results more accurately
iii) The spreadsheet automatically recalculates whenever any data are
changed
iv) all of the above.

2. Analytical questions

a. Who are the probable users of spreadsheet software?

b. Name a few spreadsheet software currently available in the
software marketplace.
c. What are the advantages of spreadsheet software over pencil,
paper and calculator?
d. Name a few practical problems that you may solve using
spreadsheet software.

If you cannot answer these questions correctly and confidently, go

through this lesson once again before proceeding to the text.

159
Office Automation and MS Office

Lesson 2: Spreadsheet Fundamentals

Learning Objectives
On completion of this lesson you will be able to learn :

• how to start Microsoft Excel

• how to see its layout and window
• how to move around the worksheet
• how to exit Excel.

Microsoft Excel is a powerful spreadsheet application that you can use

for analysing and charting your data and effective presentations.

Starting Microsoft Excel

To start Ms Excel :

1. At the system prompt (such as C:\>), type excel and press Enter.
2. From the Microsoft Excel/Office window, double click on the
Microsoft Excel program icon.

Note : This process may differ slightly from machine to machine.

The Spreadsheet Window

Before proceeding any further, you should be familiarised with the
spreadsheet window. Refer to figure 2.1. When you have loaded
Microsoft Excel, your computer screen should look like this.
The largest part of the window is called the worksheet. The worksheet
appears as a rectangular grid of rows and columns. Excel worksheets are
256 columns wide and 16,384 rows deep. Columns are identified by
letters and rows are identified by numbers. Thus columns are assigned
labels from A to Z, then continue from AA to AZ, and then BA to BZ
and so on, until the last column IV is reached. Rows are identified by a
number from 1 to 16384. You can see the column labels and row
numbers at the top and to the left of the worksheet.
The boxes created by the intersection of columns and rows are called
cells. Each cell is identified by an address. The address of a cell is the
letter of its column followed by the number of its row. For example, the
cell in the extreme upper left corner of the worksheet is addressed A1.
Similarly, the cell at the intersection of column D and row 12 is
addressed D12.

160
 Spreadsheet Analysis

Microsoft Excel

You will always find a cell surrounded by a bold border. This is the
active cell. Whatever entry you make, it always goes to the active cell.
You can make any cell active at any time. The currently active cell
address always appears in the reference area of the formula bar.
Formula bar is the line just above the worksheet. When you select a cell
(i.e., make it active), the formula bar displays the contents of that cell.
When you make a new entry or edit an existing cell data, the formula bar
becomes active and you can see the entry or modifications in it.
Formula bar also includes an enter box (√ ) and a cancel box (×). You
can complete a cell entry or an edit you have made to an entry by
clicking this enter box as well as by pressing the ENTER key. You can
cancel an entry or edit by clicking the cancel box or by pressing the ESC
key.
The leftmost part of the formula bar is the reference area. It always
displays the address of the active cell. Later when you learn to select a
range of cells, you will see the reference area show the size of the
selected range.
The top two lines of the spreadsheet window are for the menu bar and
the tool bar. The menu bar contains menus and the menus contain
commands. In Microsoft Excel, the menu bar changes slightly depending
on what type of document you are working on. For example, when you
are working with a chart, the menu bar contains a Chart menu and a
Gallery menu.
When you open a menu, it displays a list of available commands. Some
commands have shortcut key combination listed to the right of the
command name. These shortcut keys can be used to activate a command
without first going through the main menu.
Some of the command names may appear dimmed. This indicates that
these commands can not be activated at the current situation.
Note : The toolbar consists of a series of buttons for the most frequently
used spreadsheet commands. Excel supplies a number of toolbars

161
 Office Automation and MS Office

serving different needs of the user. You can make any or all of the
toolbars visible at all times, if required.
Moving Around the Worksheet
In total a worksheet contains more than 4 million available cells. The
portion you can see is a very small part of the whole. You can easily
access the other parts of the worksheet by using the scroll bars.
The Table 2.1 gives the keys or key combinations that you can use to
navigate the worksheet.

Keys to press Active cell moves to

Arrow keys to right, left, up or down one cell at a time.
Moving Around the Ctrl+Arrow keys To right, left, up or down to the edge of the current
Worksheet data region.
PgUp, PgDn up or down one window
Home to the beginning of current row
Ctrl+Home to cell A1
Ctrl+End to the lower right corner of the worksheet
End+Arrow keys by one block of data, within the current row or
column
End+Enter to the last cell in current row
End* to lower right corner of the window
Home* to upper left corner of the window.

*when Scroll Lock is on

Table 2.1: Keys key combinations
In addition to the above keys, you can also use the mouse to move the
active cell pointer. Several things you may do.
• Type the desired address in the reference area in the formula bar
and press Enter.
• If the desired cell is visible in the window, click the cell in the
worksheet. If not visible, use the scroll bar to make the cell
visible and then select the cell.

• Select Goto option from the Edit menu or press F5. Type the cell
address and press Enter.

Quitting Excel

To terminate an Excel session :

• From the File menu, choose Exit.

Excel will give you an opportunity to save any previously unsaved work
before you quit.

162
Spreadsheet Analysis

Exercises

1. Multiple Choice Questions:

a. Which of the following is not a part of the Excel window?

i) Toolbar
ii) Chart
iii) Worksheet
iv) Cell.

b. The Excel worksheet size is

i) 256 row × 8192 column

ii) 256 column × 8192 row
iii) 256 row × 16384 column
iv) 256 column × 16384 row.

c. The column number of the 35th column is

i) Z9
ii) A35
iii) AI
iv) None of the above.

Hands on Practice

To get more familiarised with Excel and the topics covered in this
lesson, perform the following:

1. Start Excel.
2. Identify the various components of Excel window.
3. Move the cell pointer to the following cells.
D5, G35, AD122, H52, A1, IV16384, L384, A384
Try as many key combinations as possible to perform the above
movements.
4. Navigate through the Excel menu and see the commands available.
5. Exit from Excel.

163
 Office Automation and MS Office

Lesson 3: Entering Data

Learning Objectives

On completion of this lesson you will be able to learn :

• how to enter data in a worksheet

• how to cancel an entry
• how to know what kind of data you can enter
• how to fill several cells with a single command
• how to save worksheet
• how to open a existing worksheet.

Entering and Editing Data

Worksheet applications are made by organising information in a
meaningful manner. The spreadsheet is developed one cell at a time by
entering different types of information into the individual cells.

To develop your own spreadsheet, you will need to make entries into the
Entering Data cells of the worksheet. Entering data into cells is simple. Simply move
the cell pointer to where you want to enter the data and type in. You will
see the entry appear in the formula bar. When you finished typing, lock
the entry by pressing the [ENTER] key. The cell pointer will
automatically move to the cell below. You may use any of the following
keys at the end of your entry.

key(s) What will happen

[ENTER] locks and cell pointer moves to the cell below.
[SHIFT]+[ENTER] locks and cell pointer moves to the cell above.
[TAB] locks and cell pointer moves to the cell right.
[SHIFT]+[TAB] locks and cell pointer moves to the cell left.

Table 3.1: Entering and editing data.

Sometimes you may wish to cancel an entry. To abandon an entry, you

have three methods. These are :

• To cancel an entry before it is locked, press the [ESC] key.

• If you have just locked it, choose the Undo Entry option in the
Cancelling entry Edit menu before doing anything else.
• If you have already initiated some other action after an entry or
an edit, you cannot undo in the above manner. For situations like
this, you may remove an entry from a cell by selecting the cell
and choosing the Clear command from the Edit menu.

164
 Spreadsheet Analysis

Sometimes you need to modify an entry in a cell. You may change an

entry by typing a new entry over the old one. Simply select the cell and
type the new value you want. The old value will be erased and the new
value will be inserted. If you press the Esc key in the middle of such a
re-entry process, Excel will abandon the new entry and the old data will
remain. You may also recover the old one by selecting the Undo Entry
option from the Edit menu provided you did not initiate a new action. If
you initiated, then, in no way, you can recover the old entry.

In case of minor modifications in long and complex entries, it is wise to

edit it rather than retyping.

To edit the existing one :

1. Select the cell.

Editing existing entries 2. Activate the formula bar by pressing F2 key or clicking in the
formula bar.
3. Make necessary modifications using standard editing keys.
4. Lock entry by pressing Enter or click the Enter box.

Excel Data Types

The entry in every cell in a spreadsheet falls into one of the two classes:
a Constant or a Formula.

A Constant value is data that you type directly into a cell; it can be a
numeric value, including currency, percentage, fraction or scientific
notation, or it can be text. Constant values do not change unless you
select the cell and edit the value yourself.

A Formula is a sequence of values, cell references, functions or

Excel Data Types operators that produces a new value from existing values. Formulas
always begin with an equal (=) sign. A value that is produced as the
result of a formula can change when other values in the worksheet
change. We shall learn more about formulas in module 4.

To enter a number as a constant value, select the cell and type the
number. Numbers can include numeric digits (0 through 9) and the
following special characters.

165
Office Automation and MS Office

Character Function Examples

0 through Any combination of these numerals 1234567890
9
+ used with E or e to indicate positive +758, 1.286E+5
exponents
- indicates negative number -758, 1.286E-5
() indicates negative numbers (758)
, (comma) Thousands marker 10,000,000
/ fraction indicator 3 1/2
$ currency indicator $19.95
% percentage indicator 10%
. (period) decimal indicator 3.1415
E or e Exponent indicator 1.286E+5,
3.52e-6

Table 3.2 : Characters allowed for numeric entries

You can include commas in numbers, such as 1,000,000. A single period
(.) in a numeric entry is treated as a decimal point. Plus sign (+) entered
before numbers are ignored. Precede negative numbers with a minus sign
(-) or enclose them within parentheses.

The number displayed in a cell may differ from what is entered. Based
on the cell format, the number may be rounded, but Excel stores the
original data up to 15 digits of accuracy. Excel always uses the stored
data in calculation no matter how it is displayed on the screen. You can
always change the way a number is displayed. You will learn more about
formatting cells in module.

If a number is too long to be displayed in a cell, Excel displays a series

of number signs (####). If you widen the column enough to
accommodate the width of the number, the number will be displayed in
the cell.

Text entries are used as labels to identify/clarify data in the worksheet.

Any entry that starts with a non-numeric character is treated as text. It
can be characters only or any combination of characters and numbers. In
fact, any entry that is not a number or formula to Excel is a text. A text
entry can be at most 255 characters long. Text are generally aligned to
the left of the cell. If you wish to enter a number as a text, precede it
with an apostrophe (’ ).

If a text exceeds the width of the cell it is entered, it will overlap into
adjacent cells, provided those cells are empty. Otherwise it will be
truncated for display. This truncation will not affect the actual content of
the cell. You may see the actual entry in the formula bar when you select
the cell.

166
 Spreadsheet Analysis

Saving the Worksheet

Worksheets created using electronic spreadsheet reside in the
computer’s RAM. RAM offers instant availability of information stored
in it. Changes, additions and deletions to information stored in RAM can
be accomplished very quickly. The disadvantage of using RAM storage
is that it gets erased when the computer is turned off.

In most occasions, you will be working on a worksheet for several

sessions. You must save your worksheet as a file in a disk. Disk files are
permanent storage of all information.

a). To save a document :

• From the file menu, choose save or click .

b). To save a new, unnamed document :

1. From the file menu, choose Save As or click .

Then the following dialog box will be displayed :

Saving The Worksheet

Fig. 3.1 : The Save As dialog box

167
 Office Automation and MS Office

2. Do one of the following :

To save the document Do this

On the current drive and current Type a name in File Name box.
directory.

On a different drive and in a Select a different drive or

different directory. directory, or type the complete
location and file name in the File
Name box.
3. Choose OK button.

Note : If any open documents have not been saved before word displays
the Save As dialog box so that you can name then.

Opening Existing Worksheet

If you want to work on a worksheet that you previously saved, you must
open it first.
Opening Existing Worksheet
1. From the File menu choose Open or click . Then the following
Open dialog box will be displayed.

2. In the Open dialog box, type or select the file you want to open and
click OK.
3. If the file is not in the current directory, then select the desired drive
from the Drives list box and directory from the Directories list box.
4. Click OK.

168
Spreadsheet Analysis

Exercise

1. Analytical questions

a. What are the Excel data types? Explain them briefly.

b. Describe briefly how you will save a worksheet.

Hands on Practice

2. a) Load Microsoft Excel.

b) Make the following entries in the assigned cells.

Cell Data Cell Data Cell Data

A1 x B1 y C1 z
A2 10 B2 15 C2 25
A3 30 B3 20 C3 35
A4 40 B4 24.7 C4 55
A5 50 B5 30 C5 65
A6 50.5 B6 35 C6 79
A7 60.3 B7 40 C7 80.5
A8 70 B8 50 C8 90
A9 100 B9 60.7 C9 90.5

c) Save the worksheet as [Link]

d) Exit Excel.

If you cannot answer these questions correctly and confidently, go

through this lesson once again before proceeding to the next.

169
 Office Automation and MS Office

Lesson 4: Formulas and Functions

Learning Objectives

On completion of this lesson you will be able to learn :

• what is a formula
• what operators you may use, their function
• how to write valid formulas
• what a function is
• advantages of uses built in functions
• available functions from Excel
• how you can specify a block of data.

Formula
Formulas are entries that enable you to make calculations based on the
numbers and text in the worksheet. With a formula you can perform
operations, such as addition, multiplication, comparison etc. on
worksheet values. A formula always begins with a equal sign (=).

For example, if you enter =2*5+10/8-3 in a cell, spreadsheet will

calculate and then display 8.25 in the cell. This capacity to compute
makes our work with the spreadsheet much simpler.

A simple formula combines constant values with various operators.

These operators fall broadly into three categories :

• Arithmetic operator,
• Comparison operator,
• Text operator.

Arithmetic operators perform basic mathematical operations like

addition, subtraction etc. They combine numeric values and produce a
Formula numeric result. For example, the formula =20^2*15% raises 20 to the
power of 2 and multiply the result by 0.15 (15% = 0.15) to produce a
result of 60. Similarly, =(2+7)*25 will result in 225.

Comparison operators compare two values and produce the logical value
TRUE or FALSE based on the comparison done. For example, the
formula = 7<25 produces the value TRUE, whereas =5>7 produces
FALSE.

Text operator joins two or more text values in a single combined text
value.

170
 Spreadsheet Analysis

Text Operator
Operator Function
& Concatenates two text values

Arithmetic Operators Comparison Operators

Operator Function Operator Function
+ Addition = Equal to
- Subtraction > Greater than
* Multiplication < Less Than
/ Division >= Greater than or equal
to
^ Exponentiation <= Less than or equal to
% Percentage <> Not equal to

You may combine several operators in a single formula. In these cases,

Operations and its function Excel perform the operations in the following order.

Operator Function Precedence

- Negation Highest
% Percent
^ Exponentiation
*,/ Multiplication, Division
+,- Addition, Subtraction
& Concatenation
= , > , < , >= , <= , <> Logical operations Lowest

We can easily alter this order of evaluation by using parentheses to

group expressions in the formula. You may use as many parentheses as
you wish, but there must be a closing parenthesis for each opening
parenthesis.

Of course, it will not be of much use if you can operate only on numeric
and text constants. Fortunately, Excel allows entering cell
references/addresses in formulas. For example, the formula =B2+B3+B4
will add the contents of the cells B2, B3, B4 and stores the sum in the
cell where the formula has been entered. The most interesting part in
using cell references in the formula is that if any of the entries in the
cells B2, B3, B4 are changed, the spreadsheet will recalculate the sum
automatically. You can use data located in different areas in one formula
and one cell’s value in several formulas.

In lesson 3, you have learned that formulas consist of numeric/text

constants, operators and cell references. Another component that can be
included in a formula is the function.

171
 Office Automation and MS Office

Function
A function is a special prewritten formula that takes a value or a set of
values, performs an operation, and returns a value. Assume your
worksheet has numbers in cells B5 through B12, and you need their sum.
From your knowledge of formulas you may enter
=B5+B6+B7+B8+B9+B10+B11+B12. This method is tiresome when
summing eight cells, but becomes impossible if hundreds of cells need to
be added. Excel relieves us by providing the SUM function. The same
result can be achieved using =SUM (B5:B12).

Functions can be used alone or as building blocks in large formulas.

A function is a special Functions even be nested. That is one function may serve as an argument
prewritten formula that takes for another function.
a value or a set of values
Since functions are formulas and these formulas are designed and tested,
you can have instant reliability when you include them in your
worksheet.

Microsoft Excel comes with hundreds of functions. Table 4.1 lists some
of the functions from Excel.

Function What it does

SUM(list) returns the sum of its arguments
POWER(value,index) returns the result of a number
raised to a power
ABS(number) absolute value of the argument
AVERAGE(list) returns the average of its
arguments
EXP(number) returns e raised to the power of a
given number
FACT(number) returns the factorial of a number
INT(number) returns a number down to the
nearest integer
SQRT(number) returns a positive square root
SIN(number), COS(number), returns sine/cosine/tangent of a
TAN(number) number
LN(number), LOG10(number) returns natural logarithm/base-10
logarithm of a number
MAX(list), MIN(list) returns the maximum/minimum
value in a list of arguments
MOD(number,divisor) returns the remainder from integer
division
PI( ) returns the value of π

172
 Spreadsheet Analysis

Function What it does

FV(rate,nper,pmt,pv,type) returns future value of an
investment
IPMT(rate,per,nper,pv,fv,type) returns the interest payment for an
investment for a given period
IRR(values,guess) returns internal rate of return for a
series of cash flows
NPV(rate,val1,val2,...) returns the net present value of an
investment on the basis of a series
of periodic cash flows and a
discount rate
ISBLANK(value), returns TRUE if the value is
ISNUMBER(value), blank/number/text/cell address/
ISTEXT(value), logical
ISLOGICAL(value)
IF(condition,value if specifies a logical test to perform
TRUE,value if FALSE)
CORREL(list1,list2) returns the correlation coefficient
between two data sets
DDB(cost,salvage value,life, returns depreciation of an asset
period, factor) for a specified period using
double declining balance method
NOW( ) returns current date and time.

Table 4.1 : Lists of the functions from Excel

In many functions you are required to specify a list of arguments. These

arguments may be constants or cell references separated by commas. If
Specifying a block of data the arguments are in adjacent cells, you may specify them as TOP LEFT
CELL ADDRESS : BOTTOM RIGHT CELL ADDRESS. For example,
cells B3,B4,B5,C3,C4,C5,D3,D4,D5 can be specified as a range by
B3:D5.

173
Office Automation and MS Office

Exercises
1. Multiple Choice Questions
a. A formula begins with

i) =
ii) ×
iii) +
iv) -.
b. Arithmetic operators perform basic

i) mathematical operations
ii) logical operation
iii) boolean operation
iv) none of the above.
2. Analytical questions
a. Describe an arithmetic operator
b. Describe a comparison operator
c. Describe a text operator
d. Briefly describe a function
e. Write the names of the function and outline their job.
f. How you can specify a block of data?
Hands on Practice
3. Load Excel
a) Open the worksheet you saved in Lesson 3.
b) Enter the following formulas in the assigned cells.

Cell Formula Cell Formula

D1 =SUM(A2:A9) E2 =MIN( A2:A9)
D2 =AVERAGE(A2:A9) E3 =MOD(611,8)
D3 =NOW( ) E5 =PI( )
D4 =ISTEXT( x) E6 =FACT(3)
D5 =ISNUMBER( x) E7 =SQRT(3)
D6 =ISNUMBER(10) E8 =LN(3)
D7 =POWER( 2,3) A10 =SUM(A2:A9)
D8 =MAX(A2:A9) B10 =SUM(B2:B9)
D9 =MAX(B2:B9) C10 =SUM(C2:C9)
D10 =MIN(B2:B9) E9 =SUM(A2:C9)
D11 =COS(40) E10 =SUM(A3:C3)

c) Save the worksheet [Link].

d) Exit Excel.

If you cannot answer these questions correctly and confidently, go

through this lesson once again before proceeding to the next.

174
 Spreadsheet Analysis

Lesson 5: Advanced Editing, Alignment and Fonts

Learning Objectives
On completion of this lesson you will be able to learn :
• how to copy a block data
• how to move a block of data from one place to another
• how to erase a block of data
• how to insert new rows or columns
• how to align data
• how to change fonts.

Copying Data From one Area to Another

While building a worksheet, you often run into the problem of having to
retype formulas or data that you have already entered on another part of
a worksheet. By copying, you may eliminate this tedious retyping. You
may copy a single cell or a group of cells across the worksheet.
a). Using edit menu or standard toolbar.
To copy a single cell across to several cells :
1. Move to the cell you want to copy.
Copying Data From One
Area To Another. 2. From the Edit menu, choose Copy or click .
3. Select the region where you want it to be copied.

4. From the Edit menu choose the Paste or click .

To copy a group of cells to a different location :

1. Select the cell(s) to copy.

2. From the Edit menu, choose Copy or click .

3. Select the upper left corner cell of the region where the cells will
be copied.
4. Click the Paste tool.
b). Using drag-and-drop editing :
To copy cell(s) by dragging.

1. Select the cells to be moved.

2. Hold down the Ctrl key.
3. Point to any of the borders of the selected range and the mouse
pointer will change to an arrowhead.
4. Hold down the left mouse button, drag to the new location, then
release the mouse button.

175
 Office Automation and MS Office

Moving Data From one Area to Another

Moving data from one region to another will erase the cell contents from
the original location and produce a duplicate in the new location. To
move:

a). Using edit menu or standard toolbox :

1. Select the cell(s) you want to move.

2. From the edit menu, choose Cut or click .

3. Select the region where you want it to be moved.

4. From the edit menu, choose the Paste or click the .

Moving Data From One Area b). Using drag-and-drop editing :
To Another.
To drag the cell to its new location :

1. Select the cells to be moved.

2. Point to any of the borders of the selected range and the mouse
pointer will change to an arrowhead.
3. Hold down the left mouse button and drag the cells to a new
location.
4. Release the mouse button.

Inserting Rows or Columns

When you insert cells, rows, or columns, Microsoft Excel adjusts
references to the shifted cells to reflect their new locations.

To insert rows :
Inserting Rows or Columns
1. Select the desired number of row(s) immediately below where you
want the new row(s).
2. From the Insert menu, click Rows.
To insert column :

1. Select the desired number of column(s) immediately to right of

where you want the new column(s).
2. From the Insert menu, click Columns.

Delete Rows and Columns

When you delete cells, rows, or columns, Microsoft Excel adjusts
references to the shifted cells to reflect their new locations.

176
 Spreadsheet Analysis

To delete rows and columns :

Delete rows and columns
1 Select a row or column.
2 From the Edit menu, click Delete.

Erasing Cell Contents

Cells are either blank or contain values. You can either delete cells or
clear the contents of cells. When you clear cells, you remove the cell
contents, formats, or notes but leave the blank cells on the worksheet.

Clearing a cell removes the contents (formulas and data), formats, notes,
or all three from a cell.
To erase cell contents :
Erasing cell contents
1. Select the desired cells.
2. From the Edit menu, select Clear.
3. Click All, Contents, Formats, or Notes.
Note : Pressing DEL clears contents only.
Aligning Cell Entries
Microsoft Excel automatically aligns text to the left and numbers to the
right. You can change this default alignment of text, numbers, and dates.

a). Using standard toolbar :

To align cell entries :

Aligning Cell Entries
1. Select the cell or cell range.
2. Click one of the following alignment button for the desired
alignment.

To Click
Align text

Centre

Align Right

Centre Across Column

To align cell entries :

1. Select cell or cells.

177
Office Automation and MS Office

2. From the Format menu, choose the Cells command.

3. Click on the Alignment tab.

4. Choose the desired horizontal and vertical alignment and text

orientation options.
The General option button, which is the default, aligns text to
the left and numbers to the right. The Left, Centre, Right,
Justify, and Centre Across Selection option buttons work just
like the alignment tools. The Fill option button repeats the
characters in a cell to fill the entire cell.
If you select the Wrap Text option, text is wrapped within the
cell so that all the text can be displayed in a narrower column
width.
5. Click OK.

Changing Fonts
Font refers to the design of the characters in which text and numbers are
displayed on the screen and printed by a printer. Each font has a name
and comes in various sizes and styles.
You can apply fonts to cells so that all of the characters within the cell
have the same font characteristics. For this
a). Using the format menu :

To change Fonts :

178
Spreadsheet Analysis

1. Select cell(s).

2. From the Format menu, choose Cells.

3. Select the Font tab.

4. Select Font, Style, Size and special effects you like.

5. Click OK.
b). Using standard toolbar :
Alternatively, you may select font, size and style from the toolbar.

In cells containing text, individual characters can use different fonts.

Font name Font Bold Italic Underline

size

Font toolbar

To format characters within a cell :

1. Double click the cell or click in the formula bar

2. Select the characters you wish to change font
3. Change the font by using font toolbar.

179
Office Automation and MS Office

Hands on practice

1. a) Open [Link]
b) Copy the contents of B1
c) Erase B1
d) Select A1
e) Bold the selected text
f) Change the point size to 16.

2. a) Select the cell contents [from B2 to B10]

b) Centre the cell contents
c) Copy all data to the next page.
c) Save [Link] as [Link].

180
 Spreadsheet Analysis

Lesson 6: Formatting Numbers, Adding

Borders and Shades
Learning Objectives
On completion of this lesson you will be able to learn :

• how to assign a built in numeric format

• how to add/remove borders
• how to add shading patterns and colours.

Formatting Numbers
Number formats control how numbers, including date and time , are
displayed. Excel provides many built-in number formats for your
convenience.
To assign a built-in numeric format
1. Select cell or cells.
Formatting Numbers 2. From the Format menu, choose Cells command.

Number format dialog box

3. Select the Number tab.
4. Select a Category of formats.
This will narrow the search for formatting codes.
5. Select a Format Code.

181
 Office Automation and MS Office

6. Click OK.
You may also create custom formats, but this is beyond the scope of this
course.

Adding Borders and Shades

You can use borders and shades to create presentation quality
worksheets. In addition, borders and shades help emphasise important
data. Microsoft Excel offers a wide variety of border types and widths,
patterns, and colours that you can use to create more attractive as well as
effective worksheets.

Adding Borders
You can add seven types of borders to a cell or range of cells and add a
shading pattern (1 of 18 available patterns) to selected cells. To add
borders.
To add borders :

1. Select cell(s).
2. From the Format menu, choose Cells.
3. Select the Border tab to display the Border dialog box.

Adding borders

Border dialog Box

4. From the Style options, select the line style.
5. Under Border, select one or more of the options.
6. Click the OK button.

182
 Spreadsheet Analysis

Removing Borders
To remove borders :
1. Select the cell or cells,
2. From the Format menu, choose Cells.
Removing borders
3. Click on the Border tab.
4. In the Border dialog box, clear one or more options under
Border.
Adjoining cells share borders. For example, putting a bottom border on
one cell produces the same effect as putting a top border on the cell
below it. Sometimes when you remove a border, the border doesn’t
disappear from the cell. This happens because there are two borders
applied to the gridline, and you must remove both.

Adding Shades and Colours

To add shades and patterns :

1. Select the cell or cells

2. From the Format menu, choose Cells.

Adding shades and colours

Patterns dialog box

3. Select the Patterns tab.
4. Select the background colour.
5. Select a pattern and its colour from the Pattern drop down
box.
6. Click OK.

183
Office Automation and MS Office

Hands on Practice :

1. a) Select the Format Code under Currency Tk #,##0 and type

4896.3478
b) Select the Format Code under Currency #,##0.00 and type
8973.3395.

2. a) Select the Format Code under Number 0.00 and type 385.
b) Select the Format Code under Number as ###0.00 and type 8592.
3. a) Open [Link] [unit 7, lesson 5]
b) Select the cells A, B, C, D.
c) Apply Border
d) Select A1 and C1
e) Apply pattern and color.

184
 Spreadsheet Analysis

Lesson 7: Changing Cell Size and Page Setup

Learning Objectives
On completion of this lesson you will be able to learn :

• how to change column width

• how to change row height
• how to define paper size, paper orientation
• how to set margins
• how to add/remove header and footers.

You can format your worksheet by increasing or decreasing the column

width and row height of selected rows. It is specially important when the
cell contents are too large or too small for the default cell size. You have
already seen that text entries are truncated when they do not fit in the
cell and adjacent cells are not empty. Similarly, a series of #### signs
are shown when a numeric entry do not fit in the cell. To avoid this
dilemma, you will have to change the cell size.

Changing Column Width

You may manually set the width or ask Excel to widen the cell(s) so that
all entries in that column fit within their respective cells.
To change column width automatically :

1. Select at least one cell in each column you want to change the
width.
2. From the Format menu, choose the Column.
3. Choose the Autofit Selection option or double-click the column
boundary to the right of the column heading.
To fix up the column width manually, do either of the following :

a) Using format menu

Changing Column Width
1. Select the column.
2. From the Format menu, choose Column.
3. Select Width.
4. Type the desired width.
5. Click OK.

Note : Widths are specified in number of characters and you can specify
a width from 0 to 255 characters.
b) By using mouse
1. Point to the column boundary to the right of the column heading,
2. Drag it to the width you want.
Note: The mouse pointer will change to when you point to the
boundary.

185
 Office Automation and MS Office

Changing the Row Height

To fix the height of a row automatically

To change row width automatically :

1. Select at least one cell in each row you want to change the
width.
2. From the Format menu, choose the Row.
3. Choose the Autofit Selection option or double-click the row
boundary to the right of the row heading.

To change the Height manually, do either of the following.

Changing the row
a) Using format menu

1. Select the row(s).

2. From the Format menu, choose Row.
3. Select Height.
4. Type the desired height.
5. Click OK.

Note : In this case the heights are specified in points instead of

characters. (72 points = 1 inch).

b) By using mouse

1. Point to the column boundary to the right of the column heading,

2. Drag it to the width you want.

Note : The mouse pointer will change to when you point to the
boundary.

Page Setup
You use the Page Setup to change printer settings for a single document.
In this way, you can add headers and footers, change margins, etc. You
can also specify settings such as paper orientation and paper size.
The Page Setup command in the File menu displays a tabbed dialog box
which provides access to most print related settings. The Four tabs are :
Page, Margins, Header/Footer, and Sheet.
To define paper size and paper orientation :
1. Position the insertion font.
2. From the file menu, choose Page Setup.
3. Select Page tab.

186
 Spreadsheet Analysis

Defining paper size and

paper orientation.

4. Select the paper size from the Paper Size and paper orientation
from the Orientation box.
5. Select Scaling option and/or First Page Number, if necessary.
6. Click OK.

Setting margins
To set margin by using Page Setup dialog box.

1. Select the text or position the insertion point.

2. From the File menu, choose Page Setup.
3. Select margins tab.

Setting margins

4. Type or select the measurement for margin to adjust in the Top,

Bottom, Left, Right box.

187
Office Automation and MS Office

5. Specify the Header/Footer position in the From Edge option.

Note : This value should be less than top/bottom margin.

6. Check the Horizontally/Vertically checkbox to position the

printout centred on page.
7. Click OK.

You can specify headers or footers to print at the top or bottom of your
document. The Header/Footer tab is where page headers and page
footers are entered and formatted. Headers and footers work exactly
alike -- you may choose a built in header/footer, or define a custom one.

To use a built-in header/footer :

1. From the Page Setup dialog box, select Header/Footer tab.

2. Select from a variety of headers and footers using the two drop
down lists.

3. Click OK.

Creating a Custom Header/Footer

The custom header/footer window contains three sections. The left
section is left justified, the centre section is centred on the page and the
right section is right justified.

188
 Spreadsheet Analysis

To create a Custom Header/Footer :

1. From the Page Setup dialog box, select Header/Footer tab.
2. Select Custom Header or Custom Footer.

3. Click on the desired section (Left, Centre, or Right)

4. Type text or press Alt + Enter for a new line, if necessary.
5. Do one or more of the following and click OK.
Creating a custom header / To Click
footer Insert Text
.
Insert Page Number
.
Insert Total Number of Page
.
Insert Date
.
Insert Time
.
Insert File Name
.
Insert Tab Name
.

6. Click OK.

189
Office Automation and MS Office

Exercise

A. Multiple choice questions

a. The Custom Header/Footer window contains

i) 2 sections
ii) 3 sections
iii) 4 sections
iv) none of the above.

b. Which of the following are shown when a numeric entry do not fit in
the cell?

i) a series of $ $ $
ii) a series of # # #
iii) a series of % % %
iv) none of the above.

B. Hands on practice
1.
a) Open [Link].
b) Copy all data to the next page.
1
c) Set paper size as letter 8 × 11 in and page orientation as lands
2
cape.

2.
a) Set left and right margin as 1.5”.
b) Set top and bottom margins as 1”.

3.
a) Display custom header dialog box.
b) Type Bangladesh Open University.
c) Format the typed text.
d) Insert page Number as the following format.

e) Display custom Footer dialog box.

f) Insert Filename and Date.
g) Close [Link].

4.
a) Open [Link].
b) Set colum width as 20,8.
c) Set row height as 6,5.

190
 Spreadsheet Analysis

Lesson 8: Printing a Worksheet

Learning Objectives

On completion of this lesson you will be able to learn :

• how to select a printer

• how to print part of a worksheet
• how to remove gridlines, include row column headings in the
printout
• how to insert/remove manual page breaks.

Printing a worksheet involves the following steps :

• Page setup
• Printer selection and setup
• Print Preview
• Print.

You have already learned how to define paper size, margins etc. in your
last lesson. We shall learn the remaining steps involved in printing in
this lesson.

Printer Selection
To select the printer :

1. Choose the Print command from the File menu.

2. In the Print dialog box, click the Printer Setup button.
3. Select a printer from the list in the Printer Setup dialog box.
Printer Selection 4. Click OK.

If you do not find the printer you want in the list, you will have to use
the Microsoft Windows Control Panel to install the printer first. Once
installed, you will find the printer listed in the above dialog box.

191
Office Automation and MS Office

Print Preview
By previewing your sheet, you can see each page exactly as it will print,
with the correct margins and page breaks, and the headers and footers in
place. Previewing a sheet can save you time and printer life.

To preview your sheet :

1. From the File menu, choose Print Preview.

2. Do one or more of following :

To Do this
Display next page Click Next

Display the Previous page Click Previous

Switch between a magnified view Click Zoom click on display sheet

and a full-page view of a sheet

Set Printing options to print Click Print

selected sheet

Set options that control the Click Setup

appearance of printed sheet

3. Click the close button.

Print
Before printing, you may specify how the worksheet will be printed,
what part of the sheet to print, whether gridlines and row/column
heading are to be printed etc. The Sheet tab in the Page Setup dialog box
allows you to specify a print area, print titles, and several of these print
options.

To Print from Page Setup dialog box of File menu :

1. From the Page Setup dialog box, choose Sheet tab.

192
Spreadsheet Analysis

2. Do one or more of the following from the option table, it necessary :

To Do this
Set Print area. Type or specify a range of cells in
the Print Area edit box.
Print titles for the selected Select rows and columns under
worksheet in case of multiple page Print Titles options.
document.
Control the order in which your Specify the print order under page
data is numbered. order option.
Print gridlines/row column Select corresponding the check
headings/note etc. box from print option.

3. Click OK or Clock print to display print dialog box.

To initiate Printing :

193
Office Automation and MS Office

1. From the file menu, choose Print or click .

The Print dialog will be displayed.

2. Choose the desired options from the following :

To Do this
Print only selected cells in the Select Selection.
selected sheets.
Prints the print area of each of the Select Sheets.
currently selected sheets.
Prints the entire print area all Select Entire Workbook.
sheets in the active workbook.
Specify the number of copies. Type or select copies.
Print all pages in selected sheet Select All Type or select Pages(s).
print the range of pages.

3. Click OK.

Page Breaking
If a sheet is larger than one page, Excel divides it into pages for printing
by automatically inserting page breaks where needed. These page breaks
are based on the paper size, margin settings, and scaling options. If
automatic page breaks cause a page break to occur in an undesirable

194
 Spreadsheet Analysis

place on the worksheet, you can insert manual page breaks. Whenever
you set a manual page break, Excel adjusts the automatic page breaks in
Printing
the rest of the sheet.

To Insert horizontal page breaks only :

1. Select the row below the row where you want the page break to
appear.
2. From the Insert menu, click Page Break.

To Insert vertical Page breaks only :

Inserting or removing page 1. Select the column to the right of the column to start new page.
break. 2. From the Insert menu, click Page Break.

To include both vertical and horizontal page break :

1. Select the cell right and bottom of the page break.

2. From the insert menu, choose Page Breaks.

To remove a page break :

1. Select the desired cell(s)

2. From the insert menu, choose Remove Page Break.

195
Office Automation and MS Office

Hands on Practice :
1. a) Open [Link].
b) Copy all the contents of 1st page to the 2nd page.
c) Preview the worksheet.
d) Display the next page.
e) Close preview.
f) Insert horizontal page break from cell B3.

2. a) Select the printer as Hp LaserJet 5p/5mp on Lp1.

b) Print the carrent page of the worksheet.
c) Select the cell contents from A1 to B5.
d) Print the selected cell.
e) Print 2nd page of the worksheet.

196
 Spreadsheet Analysis

Lesson 9: Creating and Modifying a Chart

Learning Objectives

On completion of this lesson you will be able to learn :

• what a chart is
• what a ChartWizard is
• how to create a chart in Excel
• how to change chart type
• how to add more data in the chart
• how to delete data from chart
• how to change data series
• how to format chart
• how to change worksheet image plotted in a chart

Chart
A chart is a graphic representation of worksheet data. Excel 5.0 offers 15
types of charts to choose from --- 9 of them are two dimensional chart
A chart is a graphic types and the remaining 6 are three dimensional chart types. You may
representation of worksheet plot a chart directly on your worksheet or as a separate document. Charts
data. and related data are interlinked. Therefore, charts are automatically
updated whenever you make modifications in the chart data.

Data Marker Chart Plot Area Grid line

Title

Chart Area

Y axis
title

Y-Axis (Value axis) Chart Data X axis title X-axis (Category axis) Legend
series

197
 Office Automation and MS Office

Creating a chart
Creating chart is simple in Excel. It comes with a ChartWizard tool. The
ChartWizard guides you through the steps required to create a new chart
or modify settings for an existing chart.

To create a new chart :

Creating a Chart 1. Select the range of worksheet cells that contain the data you want to
plot.
Note : You may select non-contiguous ranges if you wish. Do not select
empty cells outside the rows and columns you want plotted.
2. From the Insert menu, select the Chart or click .

3. Point to where you want top-left corner of the chart located and drag
and drop it onto the desired position.

Then the following ChartWizard (Step 1 of 5) dialog box will be

displayed.

Note : To plot a chart in a perfect square, hold down the Shift key while
you drag. To align the chart to cell grid, hold down the Alt key while you
drag.

4. a) Specify a worksheet range in Step 1 of 5.

b) Click Next, if the range is correct.

Then the Step 2 of 5 ChartWizard dialog box will be displayed.

198
Spreadsheet Analysis

5. a) Select a chart type.

b) Click Next. Then the ChartWizard (Step 3 of 5) will be
displayed.

6. Select the chart format and click Next.

Then the Step 4 of 5 ChartWizard dialog box will be displayed.

7. In Step 4 of 5 dialog box, you will find a preview of the chart, and it
asks you to specify how the data on the chart is oriented i.e whether
data series are in rows or columns. The options of this step are as
follows :

Data Series in : Rows This means that each data series is

plotted from a row of data.
Columns This means that each data series is
plotted from a column of data.
Use First X row(s) This setting lets you specify how many
top rows contain X-axis labels.
Use First X column(s) This setting lets you specify how many
left-hand columns contain the legend
entries.

199
 Office Automation and MS Office

After making the required modifications/entries, click Next. Then the

Step 5 of 5 ChartWizard dialog box will be displayed.

8. a) Indicate whether you want to add a legend.

b) Type a chart title, if you want one.
c) Type a title for each axis, if you want one.
d) Click Finish.

Note : You may modify any of the selections made so far by clicking the
Back push button up to the relevant dialog box and change the
selections. Once you are decided, click the Finish button.

Modifying Chart
To Change the worksheet image plotted in a chart :

1. Open the worksheet containing the range plotted in the chart.

2. Activate the chart.
3. Click the ChartWizard button.
Modifying chart 4. In the Range box, type or select the new range you want and
click Next. Then the following dialog box will be displayed.

200
Spreadsheet Analysis

5. In step 2 of 2 ChartWizard dialog box, make any changes you

want.
Check the sample in Sample Chart box to verify that it is plotted
the way you want.
6. Choose OK button.
There are several other ways to modify a chart.

Changing the Chart Type

• Method 1
1. Activate the chart.
2. From the Format menu, choose the Chart Type.

4. Select a new chart type.

5. Click OK.

• Method 2
1. Activate the chart.

2. Click the Chart type tool .

3. Select a chart type from the drop-down palette of chart types.

• Method 3
1. Activate the chart.
2. From the Format menu, choose Auto Format.

201
Office Automation and MS Office

3. Select a Type from the Galleries list.

4. From the Formats options, choose a format.
5. If you are not satisfied, undo the format or select a new format.
6. OK.

Adding More Data

Once you have created a chart, you can add more data series in the chart.
You have three methods :

• Method 1

1. Select the cells containing the data.

2. Drag and drop it onto the chart.

Excel will display a dialog box to let you specify the type of data being
added.

• Method 2

1. Select the data you are adding to the chart.

2. From the Edit menu, choose the Copy or click .

3. Activate the chart.

4. From the Edit menu, choose the Paste or click .

Method 3
1. Activate the chart.
2. From the Insert menu, choose the New Data.

202
Spreadsheet Analysis

3. In the New Data dialog box, enter the range.

4. Click OK.

Deleting Data from Chart

A data series can be deleted directly from a chart, and there is no impact
on the underlying worksheet data. To remove a data series from a chart,

1. Activate the chart.

2. Select the series you want to delete by clicking a data marker
within the series.
3. From the Edit menu, choose Edit.
4. Choose clear and choose series or press Delete key.

Changing Data Series

After creating a chart, you may want to change the source for one of the
data series.

To change data series :

1. Activate the chart.

2. Select the data series by clicking any data point in the series.
3. From the Format menu, choose Selected Data Series.

203
Office Automation and MS Office

4. Click the Name and Values tab.

5. Then the following dialog box will be displayed.
6. Change the Range accordingly.
7. Click OK.

Changing Data Orientation

To change the orientation of the chart :

1. Activate the chart and click the ChartWizard tool.

2. Click Next on the Step 1 of 2 dialog box.

3. Use the Data Series In options to specify the orientation.

4. Click OK.

Formatting the Chart

To format individual objects in the chart :

1. Click on the object.

2. From the Format menu, choose the Object or double-click on the
object.
3. In the Format object dialog box, select the desired options.

204
Spreadsheet Analysis

Exercise

1. Multiple Choice Questions

a. A chart is a

i) Graphic representation of worksheet data.

ii) Bitmap representation of worksheet data.
iii) Character representation of worksheet data.
iv) None of the above.

b. How many types of chart are there in Excel?

i) 6
ii) 9
iii) 15
iv) 25.

2. Analytical questions

a. What is a chart?
b. How to plot a chart in Excel?
c. How to modify the data used for plotting?

3. Hands on Practice

a) Open [Link].
b) Select cell from A2 to A9 and from B2 to B9.
c) Create a chart using the selected data.
d) Change the chart type.
e) Change data orientation.

Note : If you cannot answer these questions correctly and confidently,

go through this lesson once again before proceeding to the next.

205