Computer Skills 1 MS Excel

Compiled By: M. Patrick © Updated Sept 2015

Chapter 5 – MS Excel 2016

1) What is Excel
Microsoft Excel is a memory based computer programme enabling the user to lay out
data on the screen. It contains Functions, Procedures and Routines.

Spreadsheets have a number of characteristics, eg.

 Individual calculations can be stored, retrieved and duplicated;
 Once the calculation has been done, it can be passed on to other pc users
very easily;
 Different font styles and sizes can be used;
 Spreadsheets are easy to change – and they recalculate automatically if you
change anything;
 Any formula done on a calculator can be done on a spreadsheet – and
more…;
 Data can be copied and moved around the spreadsheet with ease;
 A variety of graphs can be used.

2) The Excel Environment Close

Workbook Title Worksheet
Menu Bar
Application
Icons
COMPUTE
R SKILLS 1
MICROSOFT EXCEL 2016
Sub
Active Section
Address

Active Cell
Formula Bar
Column Headings

Row
Numbers
Scroll
Sheet scroll bar Bars
arrows

Sheet Tabs
Zoom Scroll Bar

Updated May 2018

1 Updated 22 April 2023

Additional
 Shortcut Icons

Computer Applications 1 MS Excel 2016

Sub Section close up

A spreadsheet is referred to as a workbook – it is a file that you work in and store

your data in. It is like a notebook that you write in, with many pages. Each workbook
consists of a number of pages, called sheets. The workbook has 16384 rows and
256 columns (labelled from A to IV) – but you can only see the rows and columns
that appear in the current window.

Looking at the figure on the previous page :

 Close Worksheet button – This will only close the active worksheet, not the
whole programme. It is possible to have a few worksheets open at the same
time.
 Active address – This shows the address of the active cell as data is placed
in the cell.
 Workbook title – This is the name of the workbook and the name of the file
that you have saved it as.
 Formula bar – This shows what is actually inside the active cell: called the
contents. What you see in the cell is not necessarily what is inside it…what
you see is the cell value. Eg, a cell may contain a formula – what you see in
the cell is the answer after Excel worked out the formula. Eg. If you want
Excel to work out 1+1, the formula bar will show the formula but the cell itself
will show the answer.
 Menu bar – Many operations can be done in Excel – they are listed from
menus in the same way as MS-Word.
 Sub Sections – (see picture ‘Sub Section Close Up’ above).
Each Menu is divided up into sub sections relating to its functions. It consists
of icons which are like shortcuts to a function. Additional information is
available when you click on the small arrow on the bottom right corner.
 Scrollbars – They move the image of the workbook allowing you to see the
current area of work since you can’t see it all at once.
 Sheet tabs – This allows a particular sheet to be viewed. If the sheet you
want to look at is not in the window at that time, click on the sheet scrollbar
to bring it into view.
 Sheet Scroll Bar – This allows you to move to those sheets that cannot be
seen as they are outside the view of the Tabs bar.
 Zoom Scroll Bar – By clicking on the + or – you can enlarge or reduce the
page size to suit your application.

2
 Computer Applications 1 MS Excel 2016

A workbook generally has 16 worksheets, however only 1 is available initially,

named Sheet1. You can select any sheet by clicking on its tab. You can also
perform the following functions on the sheets by clicking the right mouse button on
the tab:
 Insert new sheets;
 Delete sheets;
 Rename sheets;
 Move or copy sheets within a workbook or to another workbook;
 Hide sheets.

Task 1

1. Open Excel and notice the Tab at the bottom of the page. This
is ‘Sheet1’. On the right is a sign Click on it for ‘Sheet2’
2. Using your right mouse button, click on Sheet2. The choices
are shown in the box on the right. OR Double Click to “Rename”
it
3. Clicking on any words will activate the command.
4. With the right mouse button, click on ‘sheet1’, now choose
“Rename”. The words will be highlighted in blue with white letters.
5. Immediately type Task 1 and press the “Enter” key. See the
diagram below for the result.

6. To move Task 1 sheet to the right of Sheet2 just click Task 1

and drag it.

3) Rows and Columns

Column widths and Row heights can easily be changed to accommodate your
needs.
Excel provides several different ways to make these changes. Columns can
also be added in the “Home” menu, look for the “Cell” sub-section (far right)
then “Insert”. Also, you can choose to insert Cells, Rows, Columns or sheets.
If you right click the column or row (once selected), similar options are
available. Columns and rows can be hidden by right clicking anywhere on
the selected row/s or column/s and click on ‘Hide’ or ‘Unhide’.

Selecting a column

3
 Computer Applications 1 MS Excel 2016

Changing Column Widths

There are 4 methods by which you can change column widths.

Task 2

Method 1. Place cursor between the columns. The cursor

will change as shown on the right. Click and
drag the column border with the mouse.

Method 2. Go to the Menu “Home”, then sub-section

“Cells” choose “Format”, then Column width
and enter a value.

Method 3. Click the Right mouse button on the column border, choose “Column
Width” and enter a value.

Method 4. Double clicking the right border of a column header will set the width to the
widest data in that column.

Changing Row heights

Row heights are determined automatically by the tallest font size in that row.
You can set the row heights manually just like the columns. The Row or Rows must
be selected first. In the same way as with columns you can Insert, Delete and Hide
Rows.
Task 3

1. Click and drag the lower Row border with the mouse to the desired height.
2. Click the Right mouse button on the Row border, choose “Row height”
and enter a value.
3. Double clicking the bottom border of a Row will set the height automatically.
4. Go to the Menu “Home”, then sub-section “Cells” choose “Format”, then
Row height, and enter a value.

4) The Cell
The intersection of a row and column is called a Cell, eg. Cell B2 is found where
Column B intersects with Row 2.

Cells contain information, and operations can be carried out on individual cells.
Whichever cell is being worked with at the time, will be highlighted by a dark outline,
often with a cursor blinking within it.

Whatever you type in a cell, will appear in the “Formula bar” at the top of the
spreadsheet (See Pg 1). You can use the Backspace key to delete any errors (in

4
Computer Applications 1 MS Excel 2016

that bar) that you may make as you are typing. You can also double click within the
cell.

Once you are happy with the data in that cell, press “Enter” or one of the arrow keys
or use your mouse to click in any other cell. If you press “Enter” the next cell below
will become active. This can be very useful when entering long columns of data.
If you start typing and decide that you no longer want that information, press
“Escape” and the cell will remain unchanged.

The cursor can be moved around in the spreadsheet by using the

cursor movement keys (arrows on the keyboard) or by clicking the
mouse in that cell. Spreadsheets are much larger than 5 rows by 5
columns, but only a portion of the
spreadsheet is visible on the screen.
You can however, enlarge the size of
your screen to view more.
Task 4

1. Choose the cells as shown on the

right and type the cell number in
the cell. Note the information
given in the “Active Address”
box as you change cells.

A group of cells can be selected at one time – this is called a

block. A block of cells can sometimes be called a range.
The range is defined by the addresses of the top left and
bottom right cells in the block.

The example on the right shows the range of cells highlighted,

showing that these are all ready to format data. The range
can either be written B3:E9 or B3..E9.
Select the range A1:E6 by clicking on A1, holding the mouse
button down and dragging to E6. While they are selected
press “Delete” on your keyboard. This will clear all the data you have just typed in.

Task 5

1. Enter the following data

Cell No: B2 B3 B4 B5
Data: 45 63 84 29

Cell No: C2 C3 C4 C5
Data: HOUSE CHAIR HAT TEA

Cell No: D2 D3 D4 D5
Data: 243 526 675 924
2. Select the range B2:D5

5
Computer Applications 1 MS Excel 2016

3. Change the selected area to BOLD, ITALIC and CENTERED.(Home Menu)

Task 6 (Method 1)

1. Select the range again (if not

selected).
2. Using the right mouse button,
click on the selected area. A
menu will appear.
3. Choose “Format Cells”. The
same window arrives as you had in MS Word.
4. Choose “Border” and click on “Outside” and “Inside”. Click OK, then click
anywhere away from the selected area. You will notice that the area you had
selected now has borders around each cell as shown above.
5. (Method 2) Click on the Icon as shown & select ‘All borders’

Cells contain three types of data :

Values, labels and formulas.

Values
These are just numbers entered as figures, eg. 123.456. Obviously you will have to
add a (-) sign for negative values etc. eg. (5) (with brackets) will be considered to be
a negative number, ie. It will be taken as being -5. Typing the symbol % after your
number will immediately divide the number by 100. Excel however can also add the
% symbol automatically if required. You can also tell it to see the numbers as
currency and it will automatically ad the R symbol and the cents after the full stop.

Labels are characters or text – most often they are words. But they may also be
numbers which will be treated as text. If you would like a number to be treated as
text, type it with an apostrophe in front of it, eg. ‘123.45 will be treated as text. These
numbers can still be used as numbers as long as they do not have text included in
them as well. Eg, if you have a label 123.45A – it will be seen only as text and no
calculations can be performed on it.
Labels are usually used for row or column headings or for titles.

Formulas In order for a formula to work, it must have an = sign in front of it in the
“Formula” bar.
Formulas can have a combination of cell references, operators (like +, -, etc) and
constants to calculate values. Eg. =A7*35+22.98…this formula will take the value
found in the cell A7 and multiply it by 35 and then add 22.98 and place the answer in
whichever cell where your cursor was. Formulas will be covered in more detail in
Chapter 5b.

6
 Computer Applications 1 MS Excel 2016

5) Automatic Data fills

It is often necessary to fill cells with constant values or with values forming a series.
Eg, 1,2,3,4,5… or Monday, Tuesday, Wednesday…
Excel allows you to fill the cells automatically as follows :-

Task 7

1. Place the number “1” in cell B2

2. Place the number “2” in cell B3
3. Select B2 and B3 (Drag your cursor over these two
cells). Once selected (Border around both cells), You will
find a small black square at the bottom right of the box –
(see diagram on right). Your large cursor will turn from a big
cross into a small one
4. Now click and drag the box down and notice the numbers
increase in value by one.
5. Place the number “2” in cell C2
6. Place the number “4” in cell C3
Do the same as above and you will notice it now continues with the series.
2, 4, 6, 8 – The basic rule is to start the sequence by selecting both cells.

7. Type “Monday” in cell D2

Drag the box down as you did before and see the results.

8. Type “January” in E2 and repeat the method.

If you wanted the years 2000 to 2010, try this –

9. Type 2000 in F2 – now try the same process. The 2000 gets repeated. This
can also be useful to repeat the same number or text.
10. Type Ctrl+Z to undo the last action (Edit -Undo). Now type the number
2001 below 2000. Try the same process of selecting both cells then dragging
and your series will continue – 2000, 2001, 2003, 2004 etc.

This process of data filling also works by dragging to the right or left along the row.
Copying Formulas using Auto Data fill
What makes spreadsheets so powerful is the fact that they contain formulas which
will automatically be changed to take into account new positions, etc. Eg. If you want
to find the square root of a column of numbers, it is only necessary to write the
formula for the first number in the column, then copy it in a single step for the
remaining numbers. See Task 8 next:-

Task 8
To copy the formula, do the following :-
1. Place the numbers 5 & 6 in A1 and A2 as
shown and use the automatic data fill up to 9.
2. Select the cell where you require the
answer, in this case – B1

7
Computer Applications 1 MS Excel 2016

3. Go to the formula bar and place the ”=” (equal sign) to start a formula.
4. Click on cell A1 (notice A1 in the formula bar). Press the * (star) key
(indicating multiply) then click on the A1 cell again. Your formula bar should
show: =A1*A1 – this, obviously then is the square root of whatever is in cell
A1.
5. Press Enter to confirm your formula, and your answer will appear in B1.
6. To repeat the formula for the rest of the column drag the box corner down and
this will automatically repeat the formula relative to the cell alongside it.

Formula bar

Click on the cell B2…it has changed to accommodate the new formula. In other
words, it is no longer A1*A1, now the formula in Cell B2 shows A2*A2.

Each copy of the formula, is changed by the spreadsheet to apply to the correct cell.
The spreadsheet knows that it must change the formula used in the original formula.
These are known as relative addresses as they are related to the cell alongside.

Relative and Absolute addresses

The formula in the task above changed in comparison with the cell alongside it.
In other words the formula in B1 was relative to the data in A1. When the formula
was dragged to B2 then it was relative to A2. The formula in B3 was relative to the
data in A3 etc. Excel is designed to do this as a standard procedure.

BUT
There is a problem if you actually want the formula to refer to a particular cell,
eg. If you want to multiply a few values by a constant value stored somewhere in the
spreadsheet. Using the example above, we now want to multiply each answer by a
constant value like the value stored in cell D1…9.99 and place it where our original
formula was – now we will have A1*B1*D1 shown in the formula bar.

Formula bar

In other words you want 25 (B1) X 9.99 and 36 (B2) X 9.99 and 49 (B3) X 9.99 etc.

If you try to copy the formula as shown above to the other cells, by dragging the
formula down, it would give an answer relative to the data in D1 then D2 and D3 etc.

Task 9
1. Place the number 9.99 in cell D1.

8
Computer Applications 1 MS Excel 2016

2. Click on B1 to show the formula in the formula bar.

3. Click on the formula bar at the end of the formula.
4. Place a * after the second A1 then click on the cell D1.
5. The formula should be =A1*A1*D1 indicating D1 is added to the formula.
6. Press enter to confirm.
7. Now drag the formula down in Cell B1 as before.

What are the figures in B1 to B5 like now?

B1 is correct since the

formula was placed in that
cell relative to D1.
B2 to B5 all indicate “0”.
Why?
Let us take B2 as an
example. Click on B2 – the
formula is =A2*A2*D2
Which is 6 X 36 X D2, now click on D2 and you will see it is “0”. Answer = 0
Can you see now that the formula is relative to the cells next to it.

To change this, and get this cell (B2) making reference to D1, you have to make the
cell D1 ‘Absolute’. You do this by adding a $ sign in front of the D (Column) and a $
sign in front of the 1 (Row). So, in our example, the column D will become $D and the
row 1 will be $1. So the formula would look like this:-

=A1*A1*$D$1

Once you have made the changes by adding in the $ signs, press enter. You must
now drag the formula down again to re-fresh with the new formula. You will see that
the figures have changed accordingly.

Click on B5 and notice that it

is still referring to D1 now that
the $ sign is there.

A shortcut to placing the $

signs is to place your cursor
to the left, right or centre of
the D1 and press F4.

Just to confirm that this works, change the value in cell D1 to 10 and observe the
automatic change to the other values.

Cut, Copy, Paste & Delete

Since Excel is run on Windows it also has the capability to move data within cells and
from one sheet to another using the Cut, Copy and Paste functions. In the same
way if you select a cell and press the “Delete” key, the contents of that cell will be
deleted although not all formatting, such as borders and colouring will be deleted. To

9
 Computer Applications 1 MS Excel 2016

delete everything including colour and formatting, “Right Click” on the cell and
choose “Delete” choose “Shift cells up” and observe the change.

Task 10

Select the cells A1 to A5. Go to the “Home” menu and click on “Copy”. This now
places your data in RAM memory. Click on the cell C7. Go to the “Home” menu
again and press “Paste”. The same information that was in A1 – A5 now appears in
C7 and below. You could even move to a “Sheet2” and “Paste” the data there. You
will notice then that the formulas make reference to “Sheet1”. Any changes in
Sheet1 will reflect in Sheet2.

Please note that in the “Edit” menu, alongside the words “Cut”, “Copy” and “Paste”
there is a short-cut to that command shown.

e.g. “Cut” = Ctrl + X

“Copy” = Ctrl + C
“Paste” = Ctrl + V

These are standard Windows commands and the keys X,C & V are next to each
other at the bottom left side of the keyboard, making it easier to remember.

Calculating in Excel
6) The Formula
The most important part of a spreadsheet is its ability to calculate formulas. A formula
consists of operators, constants, variables and functions.
Operators are + - * / = etc.

There are 3 types of operators in Excel –

arithmetic, string and comparison operators.

TYPE OF OPERATOR OPERATOR COMMENT

Raising a number to a
ARITHMETIC ^ power
() Brackets
* or / Multiply or divide
+ or - Add or subtract
STRING & Add strings
Compare values equal
to, less than, greater
COMPARISON = < > <= => <> than, less than or equal
to, greater than or equal
to, not equal to.
Arithmetic :

The Rules of Priority (Structure of order)

The order of the operators is shown above and is very important because the ones
higher up in the table are carried out first, regardless of where they fall in your
formula.

10
 Computer Applications 1 MS Excel 2016

For example: 5+2*3 = 11 because the multiplication operator * has priority over
the + sign (in other words, it is dealt with first).

If you had brackets around the 5+2, it would be dealt with first, ie. (5+2)*3 = 21
This is because brackets are dealt with BEFORE any of the operators mentioned
above except the ^.

Comparison operators : They are used to compare values. Eg. 57>56 gives a
result of the logical (referred to as Boolean) value TRUE. Strings can also be used,
Eg. “B”>”A” also gives the result TRUE because in ASCII, B is greater than A.
The function IF is most often used with comparison operators, but that will be dealt
with later. AND, OR, NOT are also Excel functions and will also be dealt with later.

7) The Function
Instead of creating your own formula, Excel has a built in Function option to assist
you to format the data in your sheet.

Your main menus include a tab called ‘Formulas’. Once selected, you will see the
following formula and function options.

The layout on your screen may be slightly different depending on screen settings.
The sub-section ‘Function Library’ gives you category shortcuts to assist you in the
choice of the function you require. For instance, you might be looking for a formula
requiring a Maths or Trig function. Choose the icon for that function and a window
with a full range of choices will appear. Should you require more assistance then go
to the bottom of the menu and click in the button
This button is available on all the shortcut functions and also on the left of the screen.
This ‘Insert Function’ window gives the ‘Function Dialogue Box’ giving you the
option of typing your requirement for a specific need. For instance, type “Square
Root”. Click ‘Go’ and it will find the function ‘SQRT’.

11
Computer Applications 1 MS Excel 2016

The Function Dialogue Box

a)

AutoSum Icon
Excel provides you with a quick short-cut solution to a problem by
providing you with an Icon on your toolbar allowing you to use
favourite functions fast. (This Icon can be found both in the
“Formula” and “Home” tabs)
This Icon not only has AutoSum available
but other favourites such as Average,
Count, Max, Min and more. Clicking on
“More Functions” will bring up the normal
formula dialogue box. The programme
assumes you want to reference the cells
above the cell you clicked on, should this
be incorrect you have the option of
changing the cells requiring the sum.

Task 11 AutoSum
Enter the figures shown above. Click on Cell A7 then click on the AutoSum Icon.
The cells to be totalled will be highlighted. Just press enter to complete the
transaction. (Pressing enter means you are now on the cell below, keep this in mind)

12
Computer Applications 1 MS Excel 2016

Task 12 Average
Delete the information in cell A7 (the total you just placed there). Now click the small
arrow next to the AutoSum Icon. Choose Average and note the figure given.

Task 13 Maximum
Delete the information in cell A7 (the Average you just placed there). Click the small
arrow next to the AutoSum Icon again. Choose Max and note the figure given.
Check the answer visually to see what value the maximum number is.

Task 14 Minimum
Repeat the same as above but use the Min option and view the results.

Task 15 Count
Repeat the same as above but use the Count option. The result is, that only the
cells with numbers in them, are counted. In other words it will not “Add” the numbers
(Sum), but will tell you how many cells have numbers in them. This does not work
with text (letters) or any cell that has been formatted as text.

Cell Settings (Formatting)

Cells can be set for different applications. Within the ‘Home’ task bar you will find
a box as shown on the right. Clicking on the General arrow will pull down a menu
with all the options available for example ‘Currency’ this will automatically
change the figure by adding a Rand sign and a decimal with 2 digits eg R 45.00.
Short cut icons are also available. Be aware that the formatting stays with that
cell even if you delete the contents. The standard setting for a cell is ‘General’.

b) Logical functions
A logical function can only be true or false…
FUNCTION RESULT

AND
IF
OR
FALSE
TRUE

Gives the answer TRUE

13
Computer Applications 1 MS Excel 2016

Some examples :-
Task 16 - IF

Keeping the figures from A1 to A6 in the section above, select B1

since this is where we want our answer to appear. Now go to your
“Insert Function” button and click once. The box on the
right shall appear. In the area “Search for a function”,
type IF and press Go. The IF should now appear in the
“Select a function” area just below it. Click OK and a
new dialogue box appears as shown below.

The Logical test we shall make is that any number “Greater than and equal to 50” in
Cell “A4” must say “Pass” if it is true, and “Fail” if it is false.

Where it says Logical_test, ensure your cursor is in the box then, go to cell A4 and
select it. A4 will appear in the box. Now add to your argument >=50 as shown. In the
‘Value_if _true’ area, type “Pass”. Do the same in the ‘Value_if_false’ area but type
“Fail”. Click OK and see the result. Change the data in A4 to any number less than
50 and note the Fail appears in Cell B4 (the cell which we chose for our answer)

Task 17 - IF

In the cell D1 place the number 1 and in D2 place the number 2.

In the same method as above, click the “Insert function” – select IF then click on cell
D1. Insert the > sign then click on cell D2. The Value_if_true box can state True
and the Value_if_false box can state False. Click OK and see the results. Change
the values around and see the results. Also experiment with changing the words
True and False with some other words. (“Yebo!” and “Eish!”)
You can change these values in the Formula bar if you need to. (see below)

14
Computer Applications 1 MS Excel 2016

Task 18 - COUNTIF

This formula does what it says. Count (how many) numbers or letters exist in a range
if you set a criteria for any range.
Create any range of numbers, maybe 1 – 10. Choose a cell where you want your
answer and find the formula (fx) COUNTIF. Your options are to choose a range
(select the range) then choose your criteria. Lets say <6. Your answer will be 5 since
there are 5 numbers less than 6 when you count from 1 to 10.

Do the same with letters like A B C A B C A B C A– then choose the criteria as “=A”.
Your answer should be 4 since there are 4 A’s in the range.

This process remains standard procedure for all formulas.

Task 19 - OR

Suppose you want to find out if a calculation is correct :

Type the following in any cell…

=OR(3+3=5)
This will give an answer FALSE because 3+3 equals 6!

If you typed the following in a cell…

=OR(4+4=8)
This will give an answer TRUE because 4+4 does equal 8!

If you had the following :

=OR(4+4=8,4+2=7)
The answer would be TRUE because one of the equations is true.

If you had the following :

=OR(4+4=3,2+7=3)
The answer would be FALSE because they are both wrong.

Task 20 - NOT

This will give the answer TRUE or FALSE for logical statements…

=NOT(FALSE) Gives the answer TRUE.

Meaning, if the Data is incorrect then you will get an answer of TRUE.
In other words - The Data is incorrect “NOT correct” (False) – is that true, yes, so the
answer is TRUE.

=NOT(1+1=2) Gives the answer FALSE because 1+1 DOES equal 2!

=NOT(5+5=11) Gives the answer TRUE because 5+5 DOES NOT equal 11!

15
Computer Applications 1 MS Excel 2016

c) Mathematical Functions
Some common mathematical functions…

FUNCTION PURPOSE

SIN Find the sin of an angle

COS Finds the cos of an angle
TAN Finds the tan of an angle
ASIN Finds the arcsin of an angle
ACOS Finds the arccos of an angle
ATAN Finds the arctan of an angle
DEGREES Converts radians to decimals of a degree
RADIANS Converts decimals of a degree to radians
EVEN Rounds the number to the nearest even number
PI Gives the value for PI
PRODUCT Multiplies numbers
SUM Adds numbers
SQRT Finds the square root of a number

The way that you would ask for the square root of a number is as follows : =sqrt(90)
In other words, always start with the = sign and after typing sqrt put the number in
brackets.

The way to square a number is as follows : =5^2 (also see Task 22 below)
This will take the number 5 and raise it to the power of 2, in other words, it will square
it. So, to raise a number to the 4th power : =5^4

To find the sin of an angle : =sin(91.2345)

To convert to radians : =radians(angle)

Task 21 – Square Root

In cell C3, place the number 25. Select the cell C4 (where we want our answer) and
click on the Formula button fx. The “Insert Function” box will arrive where you will
be required to find the formula you are looking for. Find SQRT and press OK. A new
box appears asking for the cell you want to find the Square root of. Click on the cell
C3 and your answer will appear in C4. Again experiment with other numbers.

Task 22 - Raise to power

a) In cell C5, place the number 5. Select cell D5 (where we want our answer)
and then place = in the formula bar. Now click on cell C5 – then place the ^
symbol followed by 2. Your formula is now =C5^2. Press enter and note the
results – experiment.
b) Do the same as above but click on fx then choose “Power” and you are given
a box to place your Number (or click a cell) and another to choose the
Power.

Task 23 – Pi

16
Computer Applications 1 MS Excel 2016

Select any cell, click on Formula button fx. Find Pi and enter it in this cell.
This function only gives you the value of Pi and nothing else.

Task 24 – Product

Select cell A11 and enter 2. Select cell B14 and enter 3. Choose a cell for the
answer.
Click on the Formula button fx. Find the name PRODUCT in the “Insert function”
box. When the “Function arguments” box arrives, click on cell A1 (10). Place the
cursor in the next box [Number2] and click on cell A11 (2). Place the cursor in the
next box [Number3] and click on cell B14 (3). You can multiply 255 numbers.

You now have A1(10) x A11(2) x B14(3) giving you 10x2x3=60 in the cell you chose.

Task 25 – SIN

Place a figure 30 (Degrees) in a suitable vacant cell. Select the cell next to it. Click
on the Formula button fx. Find the SIN function and for the argument number click
on the cell with the 30. You will now have the SIN of 30. Changing the “Angle” will
obviously change the value of the answer.

Task 26 – Tan

Repeat Task 19 but place “TAN” in the cell where you require the answer.

Example 1 Suggested layout

The volume of a cylinder is the area of the circle x height.

If the Diameter of the circle is 20 and the height is 50 then what is
the volume? Find the answer on the spreadsheet using the
necessary formulas.
Use formulas to calculate values

Use PRODUCT to multiply final formula

Click on cells requiring multiplication

PI

R2

Height

17
 Computer Applications 1 MS Excel 2016

Example 2

The Volume of a sphere is 4/3 x PI x r3 . Calculate this on the spreadsheet

when r = 30. Using the above method, attempt this example on your own.

8) Charts
When you are ready to share your data with others it is often better to present your
information in the form of a chart. It has been said that “A picture paints a
thousand words”, in this case data. We will now learn how to create one of many
types of graphs available in MS-Excel.

Choosing the right type of chart best to suit the data being plotted is important. For
example use a pie-chart to compare parts such as regional percentage of a sales
total. Use a column chart to show how the sales regions performed throughout the
year. There is often an overlap between the chart types, each of which is best for
showing data in a particular way.

Task 27

Start with a new workbook and type the information on Sheet 1 as shown in the
diagram below.
Row Lables
Column Lables

We are going to create the graph on Sheet 2 from the data on Sheet 1.

1) Select the range A1:D5 and copy this to the clipboard. (Ctrl + C)
2) Select worksheet 2 (Sheet 2 tag)
3) Select Cell A1 in this worksheet and paste the information on the page. (Ctrl + V)
Note that it paste’s the information exactly as it was copied.
4) Rename the sheet “Chart 1”

Task 28

1) Once the data is in Sheet 2, ensure the

information is selected, then go to the
Chart icon found in the ‘Insert’ menu
and ‘Charts’ sub-section, click once. A
new drop down window appears with a
variety of choices. This window will lead
you through all the steps to create a chart
to suit your needs. Just place your mouse
over any chart shown, and an
explanation will appear to help you
choose.
First you are required to choose a chart type,
followed by a chart sub-type.

18
Computer Applications 1 MS Excel 2016

2) Choose ‘Column’ category followed by the format ‘Stacked column in 3-D’.

A chart as shown should appear near your data.

You will find a new menu titled ‘Design’ open on the toolbar above. (Chart Tools)

The ‘Design’ menu allows you to:-

a) Change type of chart.

b) Change the data.
c) Change the layout.
d) Change the styles.
e) Move the chart around the document.

3) Experiment with the choices and observe the changes (Ctrl + Z will undo them).

Task 29

1. Click on the ‘Layout’ menu (next to ‘Design’) and observe the options.

The ‘Layout’ menu allows you to:-

a) Change current selection.

b) Insert pictures, shapes or a text box.
c) Change chart lables or names.
d) Edit the horizontal or vertical axes.
e) Make background changes.
f) Analysis (not always available).
g) Give the chart a name.

2. Experiment with the choices and observe the changes (Ctrl + Z will undo them).

Task 30

1. Click on the ‘Format’ menu (next to ‘Layout’) and observe the options.

The ‘Format’ menu allows you to:–

a) Change current selection.

b) Change ‘Shape styles’ (fill, outline & effects).
c) Change text to WordArt styles.

19
Computer Applications 1 MS Excel 2016

d) Arrange items.
e) Size the chart accurately.

2. Experiment with the choices and observe the changes (Ctrl + Z

will undo them).

You will notice that both the ‘Layout’ menu and the ‘Format’ menu
both have a sub-section named ‘Current selection’. (far left)

As can be seen the current selection is the ‘Chart Area’

Your choice of selection will now be seen on your chart by the frame appearing
around the chart.

Other choices of selection can be made by clicking on the small

arrow on the right.

The one that you select here will be highlighted showing little dots
around the edges of that item.

Task 31

1. Choose ‘Back Wall’ from the list.

2. Click on the ‘Format’ menu.
3. Go to the ‘Shape Styles’ and click on the small arrows on the
right, giving you a choice of colours.

4. Make your choice and notice the change of the background colour.

Such changes can be more finely tuned by making choosing one of the
options as shown on the right.

 Please Note: this is one method of selecting an item on the

chart for editing. You can select any item on the
chart by clicking on it then make your changes.

 Please Note: If you ‘Right Click’ on any item in the chart you
will open another menu. This menu will offer
choices for that item very similar to the ones
shown above.

 Please Note: There is nothing that can’t be changed or edited

on the chart. We can edit fonts, colours, titles,
lines, backgrounds shapes, sizes, information,
styles . . . . . . and so we can go on.

20
 Computer Applications 1 MS Excel 2016

On the next page are just a few of the items that can be changed.
You will have to experiment a bit to see the full power of this
application.

Change background colour Finally you can even change the Change font and colour
Chart Type and create a new
chart on a new sheet

Change chart
wall settings
Change lable names

Change Text
Rotation

Task 32

1) Go back to ‘Sheet 1’ and click on the chart wizard again.

2) Choose a Pie Chart to suit your needs.
3) Add the same data as before.
4) Whilst in the ‘Design’ tab, go to the far right of the toolbar to ‘Move Chart
location’. you will be given a choice of which sheet you would like to move it to.
Choose Sheet 3 and give it a name of your choice.
5) Once satisfied with your changes press OK.

You should now have a Pie Chart in sheet 3 with the same data. This can be edited
and pulled apart if necessary.

21
 Computer Applications 1 MS Excel 2016

9) Conditional Formatting
This feature allows you to add graphics to data thereby allowing simple visual
pictures or colour to assist in analysing your data.

Task 33

Create a range as an example 1,5,9….. Select the range then choose Conditional
Formatting at the bottom of the window choose ‘Manage Rules’
This window below appears
Go to Edit Rule. If there are no current Rules click the button New Rules,
try some rules and see what they allow to do.

Edit Rules allows changes using the criteria they suggest, you can colour cells within
the chosen range. You can add arrows, you can add ticks and crosses, you can add
Robots Red Orange and Green. There is so much choice that you will have to
browse.

22
 Computer Applications 1 MS Excel 2016

10) Other Features

As with all Microsoft Office features, you can insert pictures, clip-art, shapes, smart
art, text boxes, backgrounds and so on . . . . . Colours and outlines, text fonts, bold,
italic, underline etc are all available.

Merge & Center

Task 34

To Merge cells you are joining the selected cells and making them
one. Select any 3 cells horizontally. Click on and
notice that the three have become one. The text is centred but can
be changed to suit. To un-merge, select the merged cells and re-click
the icon. Selecting the small arrow will give you more options.

11) Sort & Filter

Task 35

Keep the data you had for Task 33, and select it all. Go to the Editing box
in ‘Home’ (Far right side) and choose ‘Sort and filter’. Your options give
you the choice to sort A - Z or Z – A etc. Go to ‘Custom sort’ where you
have more options such as choosing which column has preference in your
choices as well as ‘Sort on’ and what ‘Order’

12) Printing
Printing your document is not as easy as it seems. Remember the computer only
does what it is instructed to do. You have to choose what area of the sheet you wish
to print and the area or data you wish to print.

Task 36

Select the area/data you wish to print. Go to “Page Layout” and choose “Print
Area”. You now have the choice of “Clear Print Area” or “Set Print Area”. Choose
Set Print Area and then print your work. What you have selected will print. Make
sure your printer is set to the correct orientation to suit your requirements.

13) Reduction Icon

When selecting the ‘Range’,
often your formula window is
so big that to cannot see the
range to select it. If you click
on the icon as shown, it will
reduce the window, allowing it
to be moved to a more
convenient position. Clicking
on the icon again will enlarge
the window to its original size.

23