TOPIC 4

ELECTRONIC SPREADSHEET USING MS EXCEL

INTRODUCTION

This topic will introduce Microsoft Excel. Microsoft Excel is a popular computer
program for spreadsheet application. It features calculation, graphing tools,
pivot tables and a macro programming language called VBA (Visual Basic for
Applications).

LEARNING OBJECTIVES

The objectives of this topic are to:

1. Understand the MS Excel.
2. Apply MS Excel to create and edit spreadsheet application.

LEARNING OUTCOMES

After completing this topic, the students should be able to:

1. Use Ms Excel to create and edit spreadsheet applications.

4.1 GETTING STARTED WITH EXCEL 2003

Microsoft Excel is the most widespread program for creating spreadsheets on

the market today. Spreadsheets allow you to organize information in rows and
tables (which create cells), with the added bonus of automatic mathematics.
Spreadsheets have been used for many, many years in business to keep track
of expenses and other calculations. Excel will keep track of numbers you place
in cells, and if you define cells to refer to each other, any changes made in one
cell will be reflected in these referring cells. It sounds a bit complicated, but
Excel makes it all a breeze.

Microsoft Excel will automatically open with a blank spreadsheet spanning

many columns and rows. You will notice a number of toolbars with many more
options included.

4 -1 DAC 10203
4.1.1 Standard Toolbar:

Figure 4.1 (a) Standard Toolbar

1. New: Create a new, blank spreadsheet

2. Open: Open a previously saved spreadsheet
3. Save: Save your current spreadsheet
4. Permission:
5. Print: Prints the current document.
6. Print Preview: Preview the potential print of the current document.
7. Research: Microsoft has enabled Information Rights Management
(IRM) within the new version of Excel, which can help protect sensitive
documents from being copied or forwarded. Click this for more
information and options.
8. Copy: Copies the current selection to the clipboard, which can then be
pasted elsewhere in the document.
9. Paste: Takes the current clipboard contents and inserts them.
10. Undo: Undoes the last action in the document, reverting back a step
in time.
11. Insert Hyperlink: Inserts a hyperlink to an Internet location.
12. AutoSum: A drop-down menu of available mathematical operations to
perform.
13. Sort Ascending: Sorts the current selection in ascending order.
14. Chart Wizard: Opens the Chart Wizard, which will walk you through
the creation of a chart / diagram using the currently selected
information.
15. Microsoft Excel Help: Brings up the Excel Help window, which will
allow you to type in a key-word for more information, or click anything
on screen to directly bring up further information on that subject.
16. More Options: There are a variety of extra options you can call or add
to the toolbar, such as Spell Check, Sort Descending, Cut, Redo, etc.
By clicking the triangle, you can access these options; at the same
time, you can drag this toolbar outwards more to make more available
space for these options directly on the toolbar.

4 -2 DAC 10203
4.1.2 Formatting Toolbar:

Figure 4.1 (b): Formatting Toolbar

1. Font: Change the font of the selected cell(s)

2. Size: Change the font size of the selection
3. Bold: Put the selection in bold face
4. Italics: Italicize the selection
5. Underline: Underline the selection
6. Align Left: Align the current selection to the left
7. Center: Align the current selection to the center
8. Align Right: Align the current selection to the right
9. Merge & Center: Combine two selected cells into one new cell that
spans the width of both and center the contents of this new cell
10. Currency Style: Change the style in which currency is displayed
11. Percent Style: Change the style in which percents are displayed
12. Decrease Indent: Decrease the indent of a cell by approximately one
character
13. Border: Add or alter the style of borders to format a cell with
14. Fill Color: Select a color to fill the background of a cell with
15. Font Color: Select a color to apply to a selection of text

4.2 WORKING WITH SPREADSHEETS

A spreadsheet is an electronic document that stores various types of data.

There are vertical columns and horizontal rows. A cell is where the column and
row intersect. A cell can contain data and can be used in calculations of data
within the spreadsheet. An Excel spreadsheet can contain workbooks and
worksheets. The workbook is the holder for related worksheets.

4 -3 DAC 10203
Formula Bar

Figure 4.2 (a): Example of new spreadsheet

Entering Data

There are different ways to enter data in Excel: in an active cell or in the
formula bar.
To enter data in an active cell:

Click in the cell where you want the data

Begin typing

Figure 4.2 (b): Click the cell and type the data

To enter data into the formula bar

Click the cell where you would like the data

4 -4 DAC 10203
Place the cursor in the Formula Bar
Type in the data

Figure 4.2(c): Click the Formula Bar And Type The Data

Excel allows you to move, copy, and paste cells and cell content through
cutting and pasting and copying and pasting.

Select Data

To select a cell or data to be copied or cut:

Click the cell

Figure 4.2 (d): Click the cell to copy, cut or edit

Click and drag the cursor to select many cells in a range

Figure 4.2 (e): Click and Drag Cursor To Select Cell

4 -5 DAC 10203
Cut, Copy and Paste

To cut, copy and paste data:

Select the cell(s) that you wish to copy

On the Edit menu bar, click Copy at the menu list
Select the cell(s) where you would like to copy or cut the data
On the Edit menu, click Paste at the menu list

Figure 4.2 (f): Select Cell To Cut, Copy Or Paste

4.2.1 Insert Rows & Columns

To insert a new blank row, place your cursor directly below where you
would like a new row. Select Insert >> Rows.
To insert a new column, place the cursor in a cell directly to the right of
where you would like the column. Select Insert >> Columns.

Figure 4.2 (g): Select Insert Rows

4.2.2. Delete cells

Place the cursor in the cell, row, or column that you want to delete
Click the Edit at the menu bar
Click Delete at the menu list

4 -6 DAC 10203
Figure 4.2.(h): Insert And Delete Rows, Columns

4.2.3 Find and Replace

To find data or find and replace data:

Click the Edit at the menu bar
Choose Find or Replace
Complete the Find What text box
Click on Options for more search options

Figure 4.2(i): Find Function

Figure 4.2(j): Replace Function

4 -7 DAC 10203
4.3 Add a Comment to a Cell

. To add a comment to cell eg. at B1, do the following:

Click on cell B1
From the menu bar, click on Insert
From the drop down menu, click on Comment
A yellow text box will appear to the right of cell B1, as in the image
below:

Figure 4.3(a): To Insert Comment

Excel will add to the comment the name of the person whose computer it is. In
this case it was done on Keys computer. You can delete this name by pressing
the backspace key on your keyboard.

To add your comment, just start typing. You can enlarge the text box by holding
down your left mouse button on one of the white squares and dragging.

Figure 4.3(b): Typing A Comment

Click on any other cell in your spreadsheet and the comment will disappear.
The cell will have a red triangle in the top right corner, indicating that it contains
a comment.

4 -8 DAC 10203
Figure 4.3(c): Cell Contains A Comment

If you move your mouse over cell B1 the comment will pop up.

If you want to get rid of a comment, do the following:

Click inside the cell that contains the comment
Click once with your right mouse button
A menu appears
Click "Delete Comment" with your left mouse button
The comment is deleted

4.4 Entering Data into a Spreadsheet

To begin this part of the course, we'll do something really simply: we'll enter
some text and numbers into some cells.
To enter something into a cell, do the following:
Click on cell egg. A1 with your left hand mouse button

Type the text "Numbers" (without the quotation marks)

Press the Return key on your keyboard

The darker border will jump down one cell to A2

Type a 3 and then press the Return Key on your keyboard

The darker border will jump down one cell to A3

Enter a 6 and a 9 in exactly the same way

4 -9 DAC 10203
Figure 4.4(a): Entering Data in a Cell

4.4.1 Editing data in a cell

If there is already something in a cell, and you tried to type something else, the
old contents would be entirely erased.
You have to edit from the Formula bar.

Figure 4.4(b): Formula Bar

Click on the cell A1. Then click inside the formula bar. You will see your cursor
flashing away.

Figure 4.4(c): Data at Formula Bar

To edit the contents,

Use the backspace key to erase anything you don't want.
Type something new in the formula bar.
Press the Return key

4 -10 DAC 10203

Figure below shows the text "Numbers" has been changed to "Add these
numbers". The formula bar now reads "3" when the Return key is pressed. The
cell A2 is showing in the Name Bar.

Figure 4.4(d): Nama Bar (A2)

4.4.2 Widen Column

To widen a column, do the following:

Move your mouse pointer up to the letter A

The pointer will be in the shape of a white cross, as in the next
image

Figure 4.4(e): White Cross

Move your mouse pointer, the white cross, to the line in between the
A and the B
The mouse pointer will change shape again
The mouse pointer will be in the shape of a black cross with
arrowheads, like the one in the image below:

4 -11 DAC 10203

Figure 4.4(f): Black Cross with Arrowheads

When you mouse pointer changes shape, hold down your left mouse
button
Keep it held down and drag your mouse to the right
Let go of the mouse button when you are satisfied with the width of
your column.

Widening a Column The Result

Figure 4.4(g): Widening the Column

You can widen a row in exactly the same way. You can also double-click this
dividing line to have Excel automatically choose the best width.

4.4.3 Formatting cells

Centering text in a cell comes under the heading of Formatting. Things like
making the text bold, changing the font, and adding some color also come
under Formatting.

4.4.4 Highlighting the Cells.

To highlight cells, do the following:

Position your mouse pointer over cell A1

Make sure the pointer is in the shape of a thick white cross

Hold down your left mouse button

Keep the left mouse button held and drag downwards

4 -12 DAC 10203

Let go of the left mouse button when all four cells are highlighted

Figure 4.4(h): Highliting Cells

4.4.5 Centering Data in Cell

To centre the data in your four highlighted cells, do the following:
Click on the word "Format" on the menu bar
Click on the word "cells" with your left mouse button

Figure 4.4(i): Format Cells Menu Bar

Format Cells dialogue box popping up.

4 -13 DAC 10203

Figure 4.4(j): Format Cells Dialogue Box

Click on the word "Alignment" at the heading Tab Strips

Figure 4.4(k): Alignment Headings

4 -14 DAC 10203

Click the little black down-pointing arrow on the right hand side of the
Horizontal box

Figure 4.4(l): Text Alignment Options - Horizontal

There are a number of options you can select for the Horizontal text alignment.
Click on the word "Center" with your left mouse button.

Change the Vertical alignment in exactly the same way.

Click the OK button at the bottom of the dialogue box.

Figure 4.4(m): Text Centered

4.5 Setting Currency Signs

To get the Currency signs in your spreadsheet, do this:

Enter a number for the price, but enter a zero then a full stop first, then
the numbers
Highlight the cells
With the cells highlighted, click on Format from the menu bar
From the menu that drops down, click on "Cells"

4 -15 DAC 10203

Format Cells dialogue box popping up
Click on "Number", and

Figure 4.5(a): Setting Currency Signs

Click on Currency from the Category section; and make sure the
Decimal Places text box has a 2 in it.
Click the OK button at the bottom.

4.6 Font Formatting

You can change the font of your text and numbers, and make it bold. You can
also change the size of the font and the font color.

To format the text in the cell A1 do this:

Click on cell A1 with your left mouse button

Click on Format from the menu bar
From the menu that drops down, click the word "Cells" with your left
mouse button
The Format dialogue pops up again
Click on the word "Font"

4 -16 DAC 10203

Figure 4.6): Font Headings

From the dialogue box, change the Font Style to Bold

Change the size to 8
Click the OK button at the bottom

Figure 4.6 (b): Text Change to Bold Style

4 -17 DAC 10203

4.7 Changing the cell color

To add a splash of color to your cells, first highlight the cells you want to
change.

Click on Format from the menu bar

From the drop down menu, click on "Cells"
The format dialogue box pops up
Click on the word "Patterns"

Figure 4.7 ( a) : Patterns Heading

Click on any of the colored squares that take your fancy.

If you'd prefer a pattern, click the black down-pointing arrow on the drop
down box to the right of "Pattern".
Select a pattern that takes your fancy.
Click the OK button at the bottom. Or
Click on the Font tab strip first, and change the text to bold.
Then click OK.

When you click OK, the cell color will change from white to the color or pattern
you have chosen. If you don't like it, you click on Edit from the menu bar, then
click "Undo format cell".

Once you have the numbers formatted in a different color, click on the cell A1.

4 -18 DAC 10203

.

Figure 4.7 (b) : Colouring Cells

4.8 Saving your work

To save the spreadsheet do the following:

Click on "File" from the menu bar

From the menu that drops down, click on "Save"
If you have not yet saved your work, the Save As dialogue box will pop
up. Clicking Save otherwise will save the latest version of your work. In
other words, your saved work will be updated
The Save As dialogue box is this one below

4 -19 DAC 10203

Figure 4.8(a) Steps to Save a File

The Save As dialogue box is split into three basic sections: Where do you want
to save it; the files already in that location; and what do you want to call your
file.

Just to the right of the words "Save in" is a drop down box. At the moment it
says "My Documents", and there is a little folder icon next to it. We can change
to other folder by clicking the down-pointing arrow and select the folder at the
big white area.

4 -20 DAC 10203

Figure 4.8 (c): Location to Save

The File name (Book1) is already highlighted. Type a new name for our file. In
box below it- Save as type. The box says "Microsoft Excel Workbook". This is
exactly what we want, so no changes are needed. But if you click the black
down-pointing arrow, you'll see other options: Web page, Template, Text, and
so on.

But click the Save button when you've chosen a File name, and set the Save as
type to "Microsoft Excel Workbook (*.xls)".

Figure 4.8 (d): Naming the File

Your new spreadsheet is saved. Keep clicking File > Save on a regular basis to
keep updating your work. Alternatively, click the Save icon on the toolbar:

4 -21 DAC 10203

EXERCISE 1

The picture below shows a simple spreadsheet. There are no formulas in it, and
nothing is being added up or subtracted (we'll do that in the next section).

For Project Number 1, reproduce the spreadsheet above (the famous junk food
diet!). Your spreadsheet must include the following:

1. Centred text and numbers

2. Widened columns
3. Bold font for the text and numbers
4. Background colours or patterns for the cells
5. The changes to make are: click on Currency from the Category section;
and make sure the Decimal Places text box has a 2 in it. Then click the OK
button at the bottom.

4 -22 DAC 10203

4.9 Constructing Spreadsheet

In this section, we'll construct a spreadsheet and learn about: Auto fill,
inserting rows and columns, adding up numbers in columns, formulas, and the
formula bar.

4.9.1 Merge Cells

Merge cell is to turn two or more cell into one cell.

Click on cell A1 with your left mouse button

Enter the text "My Chocolate Addiction" (without the quotes)
Press the Return key on your keyboard
Highlight the cells A1, B1 and C1
From the menu bar, Click on Format
From the drop down menu, click on "Cells"
Format Cells dialogue box pops up
Click on the Alignment tab strip
Look for the box at the bottom that says "Merge cells"
Put at tick in this box by clicking it with the left mouse button

Figure 4.9.1 : Merging Cells

4.9.2 Use AutoFill

AutoFill is a very handy feature of Excel. It allows you to quickly fill a series of
data, such as days of the week, months, and consecutive numbers. You can
select list from custom list or you can create a custom fill series.

4.9.2.1 Create a custom fill series

If you've already entered the list of items you want to use as a series, select the
list on the worksheet.

4 -23 DAC 10203

On the Tools menu, click Options,
Click the Custom Lists tab.
Do one of the following:
To use the selected list, click Import.
To type a new list, select New list in the Custom lists box,
Type the entries in the List entries box, beginning with the first entry.
Press ENTER after each entry.
When the list is complete, click Add.

Note A custom list can contain text or text mixed with numbers. To
create a custom list that contains only numbers, such as 0 through 100

Select enough empty cells to contain the list.

On the Format menu, click Cells,
Click the Number tab.
Apply the Text format to empty cells,
Type the list of numbers in the formatted cells.
Select the list and
Import the list.

4.9.2.2 Change or delete a custom fill series

You cannot edit or delete the built-in lists for months and days.

On the Tools menu, click Options,

Click the Custom Lists tab.
In the Custom lists box, select the list you want.
Do one of the following:

To edit the list, make the changes you want in the List entries box, and then
click Add.
To delete the list, click Delete.

4.9.2.3 Auto fill cell

Click inside the cell B3 on the spreadsheet, and type the text Monday. Like this
one below

4 -24 DAC 10203

Figure 4.9.2 (a): Insert Text to Auto Fill

Move your cursor to the bottom right of the B3 cell

Your cursor will change shape from a white cross to a black cross, as
in the images below.

Normal Cursor Auto Fill Cursor

Figure 4.9.2 (b) Normal and Aut0 Fill Cursor

When your cursor looks like the Auto Fill one, hold down your left mouse
button and drag your mouse to the right
Drag your mouse to the cell H3, Like the one below
Let go of your left hand mouse button when your cursor is over the H3
cell
Excel will fill in the days of the week for you
Format your cells while they are highlighted (Centre and Bold)

Figure 4.9.2 (c) : Days of Week Filled

4 -25 DAC 10203

4,10 Entering Data into Spreadsheet

So we've got a heading at the top of our spreadsheet, and some days of the
week headings. We can now enter the chocolate bars.
Click on cell A4 and type in the text "Mars Bars" (without the quote
marks).
Next enter Twix, Bounty and Others. In cell A9, enter the text "Day
Totals".

To enter numbers

For the Monday column, click on cell B4

Enter the number 1 then press the Return key on your keyboard
Enter the number 7 then press the Return key on your keyboard
Enter the number 8 then press the Return key on your keyboard
Enter the number 1 then press the Return key on your keyboard

Figure 4.10 (a): Entering numbers

You now need to enter the rest of the numbers. Enter the following figures for
the rest of the week:

Tuesday: 2, 5, 3, 2
Wednesday: 1, 3, 2, 2
Thursday: 3, 2, 3, 2
Friday: 3, 4, 4, 2
Saturday: 2, 2, 1, 1
Sunday: 5, 4, 4, 1

4 -26 DAC 10203

Figure 4.10 (b): Numbers Finished Enter

4.11 Entering Formula into a Spreadsheet

4.11.1 Adding Up Rows or Column

In a spreadsheet, you have to "tell" Excel which cells you want to add up. So
for the Monday column, the 1 is in cell B4, the 7 is in cell B5, the 8 is in cell B6,
and another 1 in cell B7. So we want the answer to B4 + B5 + B6 + B7.

To "tell" Excel to add up, you need an equals sign first. We'll also use the Sum
function.

You put what you want to add up in between the two brackets. So we would
need this:

=Sum( B4 + B5 + B6 + B7)

Figure 4.11 (a): Entering Formula at formula bar

Press the Return key on your keyboard.

4 -27 DAC 10203

Finally, Excel understands what you want to do. It adds up the numbers in the
cells you gave it, and puts the answer in cell B9. Your spreadsheet now looks
like this one:

Figure 4.11 (b): Answer to Numbers Above

To add up a long column or rows:

Type the first cell you want to add up,
Type a colon.
After the colon, type the last cell that you want to add up. It would look
like this:

=Sum(B4: B44)

The colon means: "Add up all the cells between the one on the left and the one
on the right."

Once we have the answer in cell, we can use Auto Fill to get the answers to the
other columns or rows.

Click on cell B9
Move your mouse to the bottom right of the cell
The cursor turns into a black cross,
Hold down your left mouse button
Keep the left mouse button held down and drag your mouse towards
other cells
When your cursor gets to cell J7, let go of the left mouse button.
Excel will Auto Fill the other three cells, inserting a formula based on the
first one.

4 -28 DAC 10203

Figure 4.11(c) : Formula Copied Based on First Cell

4.11.2 Multiply Figures

To multiply rows or column, the asterisk symbol is used. The asterisk symbol is
the one above the number 8 on your (English) keyboard.

Click inside the cell D15

Click inside the Formula Bar at the top
Type this formula: = Reference Cells * Reference or Reference Cells *
Numbers
Then press the Return key on your keyboard
Once we have that formula in place, we can Auto Fill the others.

Figure 4.11(d) : Answer After Reference Cells Multiplied

4 -29 DAC 10203

To multiply a long range of values, use the word Product instead of using the
word Sum.

=Product(B4:H4)

Just like the Sum function, you can add other cells after a comma. Like this:

= Product(A1:E1, A3)

To Sum cell A1 to A4 and cell A10, you'd do it like this:

= Sum(A1:A4) + A10

You can also use the Sum function alone. Like this:

= Sum(A1:A4, A10)

Here we have entered a range of cells in the brackets - A1:A4. But after that,
we added a comma then the final cell we wanted to add up. If we also wanted
to add cells A12 and A14 to our sum, we just add a comma then the cell
reference. Like this:

= Sum(A1:A4, A10, A12, A14)

EXERCISE 2

You have just created a spreadsheet on Chocolate addiction. Time now to reveal your
addiction!

Create a spreadsheet like the one in the last section, but substitute Chocolate
Addiction for something else. Examples might be: drinking, eating out, clothes, and
make-up - in fact, anything that someone might be spending too much money on. (It
doesn't have to be you doing the spending; it can be entirely made up.)

Your spreadsheet must include the following:

Daily totals
Individual totals
Weekly total
Columns for Prices
Columns for Number and Cost
Weekly cost
Annual cost

You can format the spreadsheet any way you like. The color scheme is entirely up to
you. Just make sure that your spreadsheet is easy to follow.
4 -30 DAC 10203
4.11.3 Subtraction

To subtract one value from another, you just use the minus sign in between
your cell references. Like this:

= A1 - A2

Cell A3 is where the answer is displayed, and where we entered the formula.

If you want to subtract more than two cells you can do it like this:

= A1 - B1 - C1

Subtraction is fairly straightforward in spreadsheets, and shouldn't cause you

too many problems.

4.11.4 Division

If you want to divide one number by another the symbol to use is this one:

That's the forward slash, and can be found just to the right of the full stop on
your keyboard. You use it like this:

= A1 / C1

4.12 Combining the Mathematical Operators

There are times when you will want to combine the arithmetic operators in your
calculations. Here are a few examples of combining the operators:

Example 1

Start a new spreadsheet

Enter the number 25 in cell A1
Enter the number 50 in cell A2
Enter the number 2 in cell A3

4 -31 DAC 10203

In cell A5 enter the following formula

=(A1 + A2) * A3

Press the Return key on your keyboard to get the answer

The answer you should have got was 150. Notice the brackets in the formula.
The brackets group part of your sum together. Without them, Excel will
normally calculate from left to right. But it does some calculation before others.
Excel sees multiplication as more important than adding up. To see what
happens without the brackets, do this:

Click on cell A5
Click inside the formula bar at the top
Delete both the brackets from the formula
Press the return key on your keyboard to see the answer

Now the answer is different! This time you should have gotten 125. Here are
the picture versions of both formulas:

With Brackets Without Brackets

Figure 4.12 (a) : Working With Brackets and Without Brackets

You might think the second one is wrong. But it's not. It's just the way Excel
works things out. Because it sees multiplication as more important than adding
up, it will multiply cell A2 by cell A3 first. That gets the answer of 100. Excel will
then add this answer to cell A1, which gives the answer 125.

With the brackets in, you force Excel to work things out your way. You're saying
"Do the sum in brackets first, then multiply". When you do the brackets first, you
get a different answer. A1 + A2 = 75. Multiply 75 by cell A3 and you get the
answer 150.

4 -32 DAC 10203

Example 2

Substitute the asterisk symbol from example 1 with the forward slash. So the
formula will be changed from this:

= (A1 + A2) * A3

To this:
= (A1 + A2) / A3

Press the Return key on your keyboard to reveal the answer. It should be 37.5.
Now take the brackets out and try again. Again, you get a different answer. The
total will now be 50!

Again the same process is at work. Excel sees division as more important than
adding up, so it does that first. So it will divide 50 by 2 to get 25. Then it will add
the contents of cell A1 to get 50. With the brackets, we force Excel to do the
adding up first, then divide by the cell A3.

Example 3

This time, enter the following formula for cell A5:

= (A1 * A2) / A3

Press the Return key on your keyboard to reveal the answer. It should be 625.

Remove the brackets and try again. The answer should be 625 again. This is
because Excel sees Multiplication and division as being of equal importance.
When all things are equal, Excel calculates from left to right.

Likewise, Excel sees addition and subtraction as being of equal importance. So

it will calculate from left to right if you use addition and subtraction in the same
formula. As an example, study this formula below:

Figure 4.12 (b): Multiply and Subtract Cell

4 -33 DAC 10203

EXERCISE 3
Create a spreadsheet using the following information. You are the owner of a fruit
store; you have owned the fruit store for one complete year. Use the data below to
construct a spreadsheet to display the sales figures for the first year of operation of
your fruit store.

1) Enter the raw data below, applying as many presentation Features (Font, Font
Size, Font Color, Number Formats and Color, Cell Shading, Text Rotation, etc) to
it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
7) Create formula's to calculate totals for each month.
8) Create formula's to calculate totals for each fruit item.
9) Setup an appropriate header for this spreadsheet.
10) Setup a page number for this spreadsheet and place it in the footer.

4 -34 DAC 10203

Apples Bananas
January 2298 January 6899.21
February 3512.56 February 6755.33
March 4929.67 March 6541
April 5883 April 6032.79
May 6237.77 May 5822.72
June 6566.78 June 5968
July 6213.88 July 6333.33
August 6001 August 6544.11
September 5799.69 September 6845.45
October 5527 October 7000.01
November 3914.55 November 7216.27
December 2564.99 December 7283

Oranges Kiwifruit
January 4923.88 January 3349.12
February 4444.99 February 3899.87
March 3851 March 4336
April 3399.88 April 4892.01
May 3020.03 May 5217.97
June 3411.89 June 4764.10
July 3567.09 July 4523.22
August 3999.91 August 4501
September 4255.88 September 4219.91
October 4873 October 3877.49
November 5214.95 November 3712.12
December 5521.17 December 3555.56

Pears Grapes
Sorting Data in a ChartJanuary
January 3310 4847.23
February 4524.65 February 5361.58
March
In this section you'll learn how to create a chart from data5899.24
5992.76 March
in a spreadsheet.
April 6961.44 April 6853.01
You'll also learn how to sort data.
May 7447 May 5471.34
June 7583.87 June 4534.22
Before we start this7393
July
section, July
though, you need to create the spreadsheet
4222.83
below.
Type in the data7110.10
August exactly as itAugust
is in this one: 4137
September 6637.96 September 3998
October 6275 October 3556.36
November 4841.71 November 3111.44
December 3456.11 December 2789.74

Peaches Nectarines
January 5902.44 January 5310
February 7234.12 February 7772.71
March 5110 March 8219
April 3521.87 April 6989.33
May 1276.34 May 4535.52
June 1227.30 June 1873.38
July 1199.99 July 1198
August 1242.09 August 1241.63
September 1189.73 September 1187.57
October 1195.42 October 1222.21
November 1213.14 November 1432.43 DAC 10203
4 -35
December 1887.49 December 2137.78
4.13 Sorting data

The spreadsheet below shows the viewing figures for BBC 1 and ITV. The ITV
programmers have already been sorted, but the BBC programmers have not.
We'll sort the BBC programmers now.

Figure 4.13 (a): An Example of Spreadsheet

Highlight the BBC 1 programmers and the figures. Your highlighting should look
like the one in the image below.

Figure 4.13 (b): Cell Selected (Highlighted)

4 -36 DAC 10203

To sort the BBC 1 figures, do the following:

From Excel's menu bar, click Data

From the drop down menu, click Sort
A dialogue box appears

Figure 4.13 (c): Sort Dialogue Box

In the Sort By box it says "Column A". To the right of this there a Ascending
radio button and a Descending radio button. A Descending sort. Is the highest
viewing figures will be at the top and the lowest at the bottom. A Ascending sort
is the other way around, with the lowest first and the highest last.

The Sort By box is actually a drop down list.

Click the word Descending to select this option

Click the black down-pointing arrow on the Sort By box
From the drop down list, click on Millions
Click the OK button right now and the data would be sorted

The "Then By" boxes are for what happens in the event of a draw. You choose
which column to sort on next. But click the OK button when your dialogue box
looks like the one below:

4 -37 DAC 10203

Figure 4.13 (d): Column to be Sorted

Your BBC 1 Viewing figures should now look like this:

Figure 4.13 (e): Column Sorted in Ascending Order

4 -38 DAC 10203

4.14 Create a Bar Chart

To start making your chart, you need to highlight the data that will make up the
chart.

So highlight the BBC1 programmers and the viewing figures

From the menu bar, click on Insert
From the drop down list, click Chart
The Chart wizard appears.

Figure 4.14 (a) : Insert Drop Down List (Chart)

There is a four step process to go through with the wizard. The first step is to
select a chart type. The Chart Type showing in the image is a column chart. To
see what this will look like, click the button "Press and Hold to View Sample".
Click with the left mouse button, and keep the button held down

4 -39 DAC 10203

Figure 4.14 (b) :Chart Wizard Dialogue Box Step 1

Let go of the left mouse button and you are returned to the chart types. The
chart we want is Bar. So click on Bar in the Chart Types list. You'll see some
different Bar types under Chart Sub Types. Make sure the first one is selected.

When you have selected your Chart sub-type, click the Next button at the
bottom, and you'll be taken to Step 2 of the Wizard.

4 -40 DAC 10203

Figure 4.14 (c) : Chart Wizard Dialogue Box Step 2

You can click the Rows radio button and the Columns radio button to set the
data range

The Data Range is the important part of Step 2. This refers to the data that you
want in your spreadsheet. Specify a t range here and press the Next button.

Your are taken to Step 3 of the wizard, and the dialogue box now looks like the
following one:

4 -41 DAC 10203

Figure 4.14 (d) : Chart Wizard Dialogue Box Step 3

There a whole lot for us to do in Step 3 of the Wizard! But we're basically
formatting the chart to how we want it.

Notice the three text boxes: Chart Title, Category (X) Axis, Value (Y) Axis.
Enter the text in text boxes:

From the Tab Strips along the top, click on Legend. Your dialogue box will look
something like this one:

4 -42 DAC 10203

Figure 4.14 (e) : Show Legend Button Untick

Untick the box that says "Shows legend" to get your dialogue box looking like
the one above. Then click the Next button at the bottom. You will be taken to
Step 4 of the wizard, the final step. Your dialogue box will look like the one
below.

Figure 4.14 (f) : Chart Wizard Dialogue Box Step 4

This is where you get to specify the Chart Location.

4 -43 DAC 10203

Click the black down-pointing arrow, just to the right of "As object in: Sheet1".
From the drop down list, select where you to locate the chart Then click the
Finish button at the bottom. The wizard will close down, and the chart will be
located at the location you have stated. Your chart will look something like the
one below.

Figure 4.14 (g) : View of the Chart

4.14.1 Format a Chart

To format the chart you created in the last part, do this:

Click on your chart with your right mouse button

A pop up menu appears
Move your mouse up to "Format Chart Area"

4 -44 DAC 10203

Figure 4.14 (g) :Format Chart List Selected

Click on Format Chart Area with your left mouse button

A dialogue box appears

Figure 4.14 (h): Format Chart Area Dialogue Box

4 -45 DAC 10203

Select the Font tab strip, and change your settings to match those above: Arial,
Regular, Size 8. Click OK when you're done.

Figure 4.14 (i) : Chart Formatted

To move your chart up, do the following:

Move your mouse into the Chart Area, but on a white bit
Hold down your left mouse button
Keep the left mouse button held down, and drag your mouse upwards
Your chart should move up
Let go of the left mouse button when your chart is near the top
In the image below, the top left of the chart has been moved to the cell
B2

4 -46 DAC 10203

Figure 4.14 (j): Chart Moved

4.14.2 Resizing a Chart

To resize your chart, do the following:

Move your mouse pointer to the middle black square on the bottom row
(if you can't see any black squares around your chart, click on the chart
with the left mouse button to select it)
Your mouse pointer will change to the shape of a arrow-headed line, like
the one in the image below:

Figure 4.14 (k) : Arrow Head Mouse Pointer

When you mouse pointer changes to the shape above, hold down your
left mouse button
Keep your left mouse button held down and drag downwards
Let go of the mouse button when the bottom of your chart reaches about
Row 22

4 -47 DAC 10203

Your chart should look like the one in the next picture:

Figure 4.14 (l) : Selecting Chart Area to Resize

You can make your chart wider by moving your mouse over the right middle
black square, and then dragging outwards If you drag too, click Edit from the
menu bar, then click "Undo" from the menu.

We can make some of the Titles bold. To make your titles bold, do the
following:

Click on Viewing Figures, but click with your right mouse button
A small menu pops up
Click on Format Chart Title

Figure 4.14 (m) : To Format Font

4 -48 DAC 10203

Another dialogue box pops up
Select the Font tab strip
Choose a Bold font style and click the OK button
Your title is made Bold
Do the same with the Programmers title and the Millions title

Your Chart should now look something like the one below:

Figure 4.14 (n) : Font Formatted

. To change the highest viewing figures at the top, do this:

Click on Sheet 1 to return to your spreadsheet Data

Make sure the data is still highlighted
Click on Data from the menu bar
From the drop down menu, click on Sort
On the Sort dialogue box, change the two Descending radio buttons to
Ascending
Click the OK button
Click back on Sheet 2 to see your Chart
The Highest programmed should now be at the top, and the lowest at
the bottom

4 -49 DAC 10203

EXERCISE 4

Add the following data to your spreadsheet:

The P1, P2, etc, means Position 1, Position 2, and so on. The highest viewing
figure is under P1 and the lowest under P10

Make a chart from this data.

SUMMARY
In this topic we have studied how to create spreadsheet using MS Word. We
can also insert data and format cells. Work with mathematical calculation and
create chart.

REFERENCES

1. Joe Kraynak (2001). To Complete Idiots Guide to Microsoft Office XP.

Indiana: Alpha Books.
2. Ed Bott & Woody Leonhard (2006). Special Edition using Microsoft
Office. Indiana: Que Publishing.
3. Laurie Ann Ulrich (2003).How to Do Everything with Microsoft Office
2003. New York: McGraw-Hill Professional.
4. http://www.homeandlearn.co.uk/ME/MicrosoftExcel.html

4 -50 DAC 10203