Unit 1: Introduction to Computer Literacy

ICT 1.4: Using Microsoft Excel

Student Support Material

ii ICT 1.4 Using Microsoft Excel

Acknowledgements
Materials written and compiled by Greg Shaw
In consultation with
Peter Seth
Amkat Mai
Geoff Gibaru

Incorporating suggestions from staff of PNG teachers colleges

Printed by EBC Lae, Papua New Guinea, September 2003

Date: 19 September 2003

PASTEP

Primary and Secondary Teacher Education Project

Australian Agency for International Development (AusAID)
GRM International

Papua New Guinea-Australia Development Cooperation Program

Student Support Material

ICT 1.4: Using Microsoft Excel iii

Unit outline

Unit Modules

1.1 Computing Basics

(Core)

1.2 Finding Computer Based Information

Unit 1 (Core)

1.3 Using Microsoft Word

Introduction to
(Core)
Computer
Literacy

1.4 Using Microsoft Excel

(Core)

1.5 Using Microsoft Outlook for email

(Optional)

1.6 Making Presentations Using Microsoft PowerPoint

(Optional)

Icons

Read or Research
Write or Summarise
Activity or Discussion
A computer activity

Student Support Material

iv ICT 1.4 Using Microsoft Excel

Table of contents

Glossary ............................................................................................................. 1
Using Microsoft Excel ......................................................................................... 2
What is Microsoft Excel?................................................................................. 2
Starting up Excel............................................................................................. 2
Moving around a worksheet ............................................................................ 3
Creating a worksheet ...................................................................................... 4
Doing calculations ........................................................................................... 5
Making a chart in Excel ................................................................................... 5
Some more calculations.................................................................................. 7
Adding a graph to our project.......................................................................... 8
The features of Excel .................................................................................... 10

Student Support Material

 ICT 1.4: Using Microsoft Excel 1

Glossary

Cell A box in a spreadsheet where text, numbers or formulae can be

added

Spreadsheet A program that allows us to input, edit and reproduce text and
numbers. In particular it allows us to set up formulas to do
calculations on numbers and to present reports as charts
Microsoft Excel is a spreadsheet program

Work Book This is the data file that open when we start Excel

Worksheet A workbook consists of one or more worksheets that we can

link data or have as separate pages in our workbook.

Student Support Material

 2 ICT 1.4 Using Microsoft Excel

Using Microsoft Excel

What is Microsoft Excel?

Microsoft Excel (or Excel) is an application that allows us to do calculations with

numbers and present reports that often contain numbers. Though Excel is an application
for working with numbers we can also prepare text in Excel. Excel is referred to as a
spreadsheet application. With Excel we can:

• Organize and store data

• Do simple and complex calculations
• Analyze data to understand it
• Format and present reports

Once a structure is set up, with formulas Excel allows us to do calculations for us when
we input new data into the spreadsheet. With Excel we can present data in graphs,
charts and tables and we can take information from Excel and easily place it into a
MSWord document. We will use our project to learn about some of the things that we
can do with Excel.

Starting up Excel

You will need to work with your computer for the following exercises

1.4 Computer Activity One

1. You already know how to turn the computer on. If your computer is not on then
start it up now
2. We can start up MSExcel in several ways. One way is to double click on the
MSExcel icon on the desktop. Another way is to start MSExcel from the
program files in the Start Menu. Start MSExcel now.
3. You should get a screen that looks a little like the one on the next page. Don’t
worry if yours is a little bit different

Student Support Material

ICT 1.4: Using Microsoft Excel 3

What we have on the Excel screen is what looks like a table with rows and columns.
This screen is called a Workbook and the columns and rows that we see are a
Worksheet. On the example above there are three Worksheets (Sheet 1, Sheet 2, and
Sheet 3) as indicated by the small tabs on the bottom of the page. The rows of each
sheet are numbered 1,2,3,4,….. etc while the columns are lettered A,B,C,D,….etc.
Where a row and a column meet we have a little box, which is called a cell. In the
following screen example where row 6 and column D meet the cell is highlighted and
the column D and row 6 headers are highlighted. You will also note that there is a little
box on the tool bar that has D6 in it. This is called the name box and names the current
cell selected.

Figure 1: Typing text and numbers into a spreadsheet

Moving around a worksheet

There are a number of ways to move around in a worksheet. To move within a

worksheet you can:

• To select any cell, click it. For example, click cell A1.
• To move one cell to the right, press TAB, or to move one cell to the left, press
SHIFT+TAB.
• To move one cell down, right, up, or left, use the arrow keys.
• To move to the uppermost-left cell, A1; press CTRL+HOME.
• To move to any cell, on the Edit Menu, click Go To and then type any cell
number (for example, J18).
• To move down in the worksheet, press PAGE DOWN.
• To move up in the worksheet, press PAGE UP.
• To move to the first column of the worksheet, press HOME.

To move to another sheet within a workbook click on the tab for the sheet you want to
move to.

Student Support Material

4 ICT 1.4 Using Microsoft Excel

Creating a worksheet
A worksheet is an effective tool for keeping track of all sorts of data. Creating a
worksheet can help you easily manage information. Let us look at an example. Imagine
that our College has a shop and we want to see how many soft drinks are sold in a
month. We collect the data for a month and make up a table like the one below.

January
Coke 89
Orange Fanta 56
Pineapple Fanta 26
Lemonaide 19

Totals 190

Now let us create an excel worksheet with this data

1.4 Computer Activity Two

Creating a new worksheet

1. Open Microsoft Excel 2000. A new workbook opens.

2. At the top of the worksheet, right-click the A (column header) and click Format
Cells.
3. On the Number tab in the Category box, click Text and then click OK. This
column will list our drink types which are words
4. Click cell B1. Type January – this will be our month column
5. Click on cell A2. Type Coke
6. Use the down arrow to move to cell A3 and type Orange Fanta
7. Keep moving down a cell and input the other drinks just like in our table above
8. Leave a cell space after A5 and type Totals in A6
9. Don’t forget to save your work in your ‘My Documents’ folder. You can name
it ExcelEx1

We should now have a worksheet that looks like the following:

Figure 2: A spreadsheet for soft drink sales

Student Support Material

ICT 1.4: Using Microsoft Excel 5

Doing calculations
What we have done so far is to put some data or information into our worksheet. Now
we will look at how we can do some calculations of that data.

1.4 Computer Activity Three

Doing calculations

Let us set up our worksheet to calculate the total of soft drink sold in January

1. We want our total to appear in our total box (B7) so click in that box
2. Place your curse in the formulae box and type an ‘=’ sign (underneath the ‘+’
sign on your keyboard)
3. Now type ‘B2+B3+B4+B5’. This will now make the contents of our totals cell
(B7) equal to the contents of B2 to B5 added together. You should see 190
displayed in the totals box.
4. Another way to do this is to select the total cell (B7) and then type in the
formulae box =SUM(B2:B6). This means that the value of B7 is equal to the
sum of the values in B2 to B6
5. A final and easiest way to do this is to select the total cell (B7) and click on the
symbol next to the formulae box. A dialogue window will appear with a list of
formulas that you can use. Select SUM then check the default range of cells to
be summed that appear in the window
6. Once we have a formula in place, if we change one or more of the values in the
selected range of cell, so the result in the result cell (in our case B7) will
change. Try this, make the number of Coke 52 and see what happens to the total
result in B7
7. When you are happy with your work then don’t forget to save it!

Making a chart in Excel

Excel allows us the present our data as a worksheet, which is what we did in the
example above. However, Excel also allows us to present information in charts and
graphs. Next, we will prepare a simple graph of our soft drink sales from our shop

1.4 Computer Activity Four

Making a chart from a worksheet table

1. Open your ExcelEx1 worksheet if it is not still open

2. Using your mouse and the left button select all
the cells between A1 and B5. It should look like
the picture here.
3. Make sure you have not selected the Totals
Column
4. Now select the Chart Wizard tool
5. The Chart Wizard dialogue box will appear Figure 3: Highlighting cells
as in the example on the next page

Student Support Material

6 ICT 1.4 Using Microsoft Excel

6. The Chart Wizard has many ways of

presenting our data as charts and graphs.
You can experiment with some of these
later but now we will just select a standard
column graph.
7. Click Next until you come to finish

8. A column graph of our data will be on

your worksheet page as an object on top
of the worksheet. You can select this
object and move it or even copy it into a
Word document

9. Your chart should look like the following

Figure 4: The Chart Wizard

100

90

80

70

60

50 Series1

40

30

20

10

0
Coke Orange Fanta Pineapple Fanta Lemonaide

Figure 5: The finished Chart

Student Support Material

ICT 1.4: Using Microsoft Excel 7

Some more calculations

1.4 Computer Activity Five

Further calculations

Let us set up some more data in our worksheet to calculate the total of soft drink sold
for the whole year.
1. Type in C1 ‘February’, D1 ‘March’, E1 ‘April’ etc…, to ‘December’ in M1
2. Select our total of January cell (B7)
3. Use the Edit menu to Copy the formulae in B7
4. Now, Select the cells C7 to M7 by first selecting C7 and while holding down
the left click mouse button move the mouse to M7 and then let go of the button.
You will notice that the whole selected section of cells becomes highlighted
(make sure you only select these cells, if you make a mistake do it again.
5. Now use the Edit menu to Paste

6. You should now have a ‘0’ in each of the cells that are now our totals. Select
one of these cells, say April (E7), you should now have in the formulae box for
that cell =SUM(E2:E6). Notice that Excel has automatically re allocated the
correct cell to get a total for each of our columns.
7. You can test the calculations and input data for each of the months and you will
see the totals automatically calculated.
8. We can do a calculation for the total amount of soft drinks sold. To do this let us
select a new cell (M9) and type the text ‘Total’ in it.
9. Now select Cell N9 and type in it =SUM(B7:M7). This will total all the
monthly totals

You might end up with something like the table below (I have abbreviated the month
headings)

Figure 6: The finished spreadsheet

Student Support Material

8 ICT 1.4 Using Microsoft Excel

Adding a graph to our project

We can think about how we might use Excel to add a graph to our project.

1.4 Computer Activity Six

Adding a graph to our project

We are going to construct a table of the rural/urban population of Indonesia over some
years. We have helped you to do this by preparing the basic spreadsheet for you.

Open the Excel worksheet titled Indonesian table and type the following information
into the worksheet or open the file excel Indonesia project (check with your lecturer)
Urban/Rural Population of Indonesia Population (in thousands)
Year Urban Rural
1960 14030 82164
1965 16902 90139
1970 20534 99746
1975 26259 109407
1980 33514 117444
1985 42390 124942
1990 53060 131223
1995 65539 135938
2000 79485 138513
2010 109107 136180
2020 141598 129566

1. Now use the chart wizard to produce a graph. This time choose a line graph
2. When you have finished with the wizard copy the graph and paste it into your
Project report

The finished chart should look like this

Student Support Material

ICT 1.4: Using Microsoft Excel 9

160000

140000

120000

100000
Urban
80000 Rural

60000

40000

20000

0
1960 1965 1970 1975 1980 1985 1990 1995 2000 2010 2020
Figure 7: Rural and urban population growth in Indonesia

Student Support Material

 10 ICT 1.4 Using Microsoft Excel

The features of Excel

Here is a summary of the features and commands of Excel

Use these buttons to The Chart The Drawing

add Web links, calculate Wizard helps Tool helps you
an AutoSum, add you create create WordArt
formulas, and sort data. charts. and add shapes
to worksheets.

Merges selected
Use these
cells and then
buttons to format
centers text in
numerical data.
the cell.

The Excel 2000 Standard

and Formatting toolbars
includes buttons for
formatting data and cells.

The Name box indicates

which cell is selected.

The Formula box shows the

data in the cell.

Column and Row numbers

make it easier to know
where you are on the
worksheet.

Use these buttons to keep

track of worksheets in a
workbook. Click the Sheet
buttons to move from one
worksheet to another.

Student Support Material