MODULE IN COMP 101: INTRODUCTION TO COMPUTER EDUCATION

UNIT 5: SPREADSHEET
Microsoft Excel
✓ is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS.
✓ is a convenient program because it allows user to create large spreadsheets, reference
information, and it allows for better storage of information.
 used to record and analyze numerical and statistical data.
 provides multiple features to perform various operations like calculations, pivot
tables, graph tools, macro programming, etc.

Getting Started
There are a number of ways to open the Excel program.
First, look for the Excel icon on your desktop and double click it. The Excel
screen should open
for you.

If you cannot find the Excel icon, click the Start button on the bottom left corner of your
desktop to display
the Start menu, then choose:

Programs/All Programs > Microsoft Office > Microsoft Office Excel

Or

The following image shows you how to do this

 Microsoft excel consists of workbooks. Within each workbook, there is an infinite

number of worksheets. Each worksheet contains Columns and Rows. Where a
column and a row intersect is called a cell. For example, cell D5 is located where
column D and row 5 meet. The tabs at the bottom of the screen represent different
worksheets within a workbook. You can use the scrolling buttons on the left to
bring other worksheets into view.
Parts of Excel

The Microsoft Excel worksheet window consists of many parts. Below is a picture of the
worksheet window and all of its component parts. The more commonly used areas are
highlighted.

 File Button – Which contains menus as well as Excel options

 Quick Access Tool Bar – Hold common shortcuts, can be customized
 Ribbon & Tabs – contains all the spreadsheet commands
 Formula Bar – where changes are made to cell contents
 Name Box – Shows the cell reference
 Column & Row Headings – the way to select/resize entire rows or entire
columns. Rows are
numbered (1,2,3, …) and Columns are letters (A, B, C, …)
 Worksheet – current page of spreadsheet
 Help – Microsoft help, available on and offline
 Status Bar – provides information on the current spreadsheet
 Zoom Control – changes the size of the spreadsheet on the screen
  Tabs – help organize items in the ribbon (Home, Insert, Page Layout, etc.)
 Groups – help organize items within a tab (Clipboard, Font, Alignment)
 Dialog Box Launcher – Opens new windows with additional options found in the
bottom right corner of some groups.

Understanding the Ribbon

The ribbon provides shortcuts to commands in Excel. A command is an action that the
user performs. An example of a command is creating a new document, printing a
documenting, etc. The image below shows the ribbon used in Excel 2013.

Ribbon components explained

Ribbon start button – it is used to access commands i.e. creating new documents,
saving existing work, printing, accessing the options for customizing Excel, etc.

Ribbon tabs – the tabs are used to group similar commands together. The home tab is
used for basic commands such as formatting the data to make it more presentable,
sorting and finding specific data within the spreadsheet.

Ribbon bar – the bars are used to group similar commands together. As an example,
the Alignment ribbon bar is used to group all the commands that are used to align data
together.

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

A worksheet is a collection of rows and columns. When a row and a column meet,
they form a cell. Cells are used to record data. Each cell is uniquely identified using a cell
address. Columns are usually labelled with letters while rows are usually numbers.
A workbook is a collection of worksheets. By default, a workbook has three cells in
Excel. You can delete or add more sheets to suit your requirements. By default, the
sheets are named Sheet1, Sheet2 and so on and so forth. You can rename the sheet
names to more meaningful names i.e. Daily Expenses, Monthly Budget, etc.
 Mouse & Keyboard
The mouse pointer in the spreadsheet program takes on many different shapes
depending on where the mouse pointer is within Excel. These shapes are visual clues as
to what you can do the specific position on your display screen.

Excel Cursors

Cursor Name Description In Action

Cross Pointer Used for selecting cells.

A single click will select. To
select a range, click and
drag.

Arrow Pointer Used to select items outside

of worksheet, such as in the
Ribbon.

Resizing Used to change column

Arrows widths.
Move your cursor between
columns to use.
Double clicking will auto-fit.
Click and drag will allow you
to resize manually.

Resizing Used to change row heights

Arrows Move your cursor between
rows to use.
Double clicking will auto-fit.
Click and drag will allow you
to resize manually.

Row Selector Used to select entire rows.

Move your cursor onto a row
number to use.
Single click will select entire
row.
Click and drag will select
multiple rows.

Column Used to select entire

Selector columns
Move your cursor onto a
column letter to use.
 Single click will select entire
column.
Click and drag will select
multiple columns.

Cursor Used to edit cell contents in

Formula Bar or the cell itself.
Sometimes referred to as I
Beam

Fill Handle Used to copy cell contents to

“Auto Fill” adjacent cells.
To use, hover your mouse
over the bottom left corner
of the selected cell or cells.
Click and drag to fill or copy
contents into new cells.

Move Used to move selected items

such as cells.
To use, hover over the dark
boarder of the selected cell
or cells. Click and drag to
move the item.

Special Keys
In addition to the Enter, Tab, Shift, and arrow keys mentioned previously the following
are also special keys used in Excel.

Key Function

Enter In addition to move to the next cell,

enter is used to confirm the new
contents of a cell.

Esc Escape, this key can be used to

cancel out of editing a cell.

Page Up Moves up one screen

Page Down Moves down one screen

F7 Spell Check

F1 Help

Delete Clears contents of entire cell or group

of cells
 Ctrl + A Selects all

Shift In addition to the notes in previous

section, Shift can be used to expand a
selection by holding shift and clicking.
Shift can also be used in combination
with arrows keys to make a selection.

Ctrl Control can be held while clicking to

select unconnected cells or ranges of
cells.

Alt Alt can be pressed or toggled on and

off to allow keyboard selection of
menu items.

How to Do Addition, Subtraction, Multiplication & Division in Excel

ARITHMETIC
S/N FIRST NUMBER SECOND NUMBER RESULT
OPERATOR
1 Addition (+) 13 3 16
2 Subtraction (-) 21 9 12
3 Division (/) 33 12 2.75
4 Multiplication (*) 7 3 21

Let’s now use Microsoft excel to achieve the above results

Step 1) Create an Excel Sheet and Enter the Data

Create a folder on your computer in my documents folder and name
it Exel_(LastName) Excel Tutorials

For this tutorial, we will be using Microsoft Excel 2013. The good news is even if you
have Microsoft Excel 2007 or 2010, you will still be able to follow the tutorial and get the
same result.

Open Excel. You will get a window similar to the one shown below. The outlook of Excel
will depend on your version.

 Enter the data in your worksheet as shown in the image above.

 We will now perform the calculations using the respective arithmetic operators.
When performing calculations in Excel, you should always start with the equal (=)
sign.
  Let’s start with the one for addition. Write the following formula in E2 Excel (Result
column)
 =C2+D2

HERE,

 “=” tells Excel to evaluate whatever follows after the equal sign

 “C2” is the cell address of the first number given by C representing the column
letter and 2 representing the row number

 “D2” is the cell address of the second number given by D representing the column
letter and 2 representing the row number

Press enter key on the keyboard when done. You should get 16 as the result.

Using the knowledge gained in the above example, try to write the formulas for
subtraction, division, and multiplication.

Step 2) Format Data in Microsoft Excel

We all love beautiful things don’t we? Formatting in Excel helps us achieve exactly that.
We can make our spreadsheets more presentable. We will use the data in the arithmetic
operations table. We will make the column names;

 Bold
 Align serial numbers to the left
 Enclose the data in boxes.

Step 3) Make Column Names Bold

 Highlight the cells that have the column names by dragging them.
 Click on the bold button represented by B command.
 Your workbook should now appear as follows

Step 4) Align Data to the Left

  We will align the serial numbers to the left
 Highlight all the data in the S/N column
 Click on align left as shown below

Step 5) Enclose Data in Boxes

Highlight all the columns and rows with data

On the font ribbon bar, click on borders command as shown below.

You will get the following drop-down menu

Select the option “All Borders”.

Your data should now look as follows

Using the knowledge gained above, try to change the font color and try out other options
available on the Home tab.

Step 6) Set the Print Area, Print Preview & Page Layout
The print area is the part of the worksheet that you would like to print out on paper. The
quick and easy way of doing it is by using the following shortcut commands

Ctrl + P

You will get the following print preview.

Press Esc button to exit print preview mode

The page setup ribbon bar has a number of options i.e. orientation, size, etc. Try to apply
the different settings and use Ctrl + P shortcut to preview the effects on the worksheet.
Exercise – Now You
Try the following exercise
1. Open Excel – Using the start menu, search for Excel. From the menu click
Excel to open. This creates a new blank document, usually titled Book1 by
default.

2. Enter the following table into Excel – Using your keyboard enter the
information into Excel. You can use your mouse or arrow keys to move

around.
In cell D1 (Wednesday), the full name does not show. We will work on
resizing to have
this fit shortly.

3. Resize the columns and format the table to match below – This can be done
using the font group found on the Home tab of the ribbon. Adjusting columns
and row sizes can be done in the header (see pointers above for details).

4. Enter Formulas to total each

row – the best way to copy
formulas is to use the auto fill
pointer. Formulas will show
the answer in a worksheet
but the formula in the formula
bar.

5. Add a 2-D Bar Chart of Total Sales – To add a chart first select the data then
use the Insert tab of the ribbon (Charts group). Remember, to select cells not
directly connected use the Ctrl key.

6. Save the workbook – Using the file menu, save the workbook to the desktop
as Weekly Totals
7. Exit Excel
Exercise – Copy Cat
Create the following worksheet in Excel, using the File menu Print Preview to check your
work. Save worksheet to the desktop as Class Schedule once finished. Be sure to adjust
the page layout as needed.
WHAT IS FUNCTION IN EXCEL?

FUNCTION IN EXCEL is a predefined formula that is used for specific values in a

particular order.
Function is used for quick tasks like finding the sum, count, average, maximum value,
and minimum values for a range of cells. For example, cell A3 below contains the SUM
function which calculates the sum of the range A1:A2.

 SUM for summation of a range of numbers

 AVERAGE for calculating the average of a given range of numbers
 COUNT for counting the number of items in a given range

The importance of functions

Functions increase user productivity when working with excel. Let’s say you
would like to get the grand total for the above home supplies budget. To make it simpler,
you can use a formula to get the grand total. Using a formula, you would have to
reference the cells E4 through to E8 one by one. You would have to use the following
formula.

= E4 + E5 + E6 + E7 + E8

With a function, you would write the above formula as

=SUM (E4:E8)

As you can see from the above function used to get the sum of a range of cells, it is
much more efficient to use a function to get the sum than using the formula which will
have to reference a lot of cells.

Common functions
Let’s look at some of the most commonly used functions in MS excel formulas. We will
start with statistical functions.

S/N FUNCTION CATEGORY DESCRIPTION USAGE

01 SUM Math & Trig Adds all the values in a range of cells =SUM(E4:E8)

Finds the minimum value in a range of

02 MIN Statistical =MIN(E4:E8)
cells

Finds the maximum value in a range of

03 MAX Statistical =MAX(E4:E8)
cells

Calculates the average value in a range

04 AVERAGE Statistical =AVERAGE(E4:E8)
of cells

Counts the number of cells in a range

05 COUNT Statistical =COUNT(E4:E8)
of cells

06 LEN Text Returns the number of characters in a =LEN(B7)

S/N FUNCTION CATEGORY DESCRIPTION USAGE

string text

Adds all the values in a range of cells

=SUMIF(D4:D8,”>=1000″,C4:
07 SUMIF Math & Trig that meet a specified criteria.
C8)
=SUMIF(range,criteria,[sum_range])

Calculates the average value in a range

of cells that meet the specified criteria. =AVERAGEIF(F4:F8,”Yes”,E4:
08 AVERAGEIF Statistical
=AVERAGEIF(range,criteria, E8)
[average_range])

Returns the number of days between

09 DAYS Date & Time =DAYS(D4,C4)
two dates

Returns the current system date and

10 NOW Date & Time =NOW()
time

Numeric Functions
As the name suggests, these functions operate on numeric data. The following table
shows some of the common numeric functions.

S/N FUNCTION CATEGORY DESCRIPTION USAGE

Returns True if
the supplied
1 ISNUMBER Information value is numeric =ISNUMBER(A3)
and False if it is
not numeric
Generates a
2 RAND Math & Trig random number =RAND()
between 0 and 1
Rounds off a
decimal value to
3 ROUND Math & Trig the specified =ROUND(3.14455,2)
number of
decimal points
Returns the
number in the
4 MEDIAN Statistical =MEDIAN(3,4,5,2,5)
middle of the set
of given numbers
Returns the
5 PI Math & Trig value of Math =PI()
Function PI(π)
6 POWER Math & Trig Returns the =POWER(2,4)
result of a
number raised to
a power.
POWER( numbe
S/N FUNCTION CATEGORY DESCRIPTION USAGE
r, power )
Returns the
Remainder when
7 MOD Math & Trig =MOD(10,3)
you divide two
numbers
Converts a
8 ROMAN Math & Trig number to roman =ROMAN(1984)
numerals

String functions
These basic excel functions are used to manipulate text data. The following table shows
some of the common string functions.

FUNCTIO COMMEN
S/N CATEGORY DESCRIPTION USAGE
N T
Returns a
number of Left 4
specified Character
1 LEFT Text characters from =LEFT(“GURU99”,4) s of
the start (left- “GURU99
hand side) of a ”
string
Returns a
number of Right 2
specified Character
2 RIGHT Text characters from =RIGHT(“GURU99”,2) s of
the end (right- “GURU99
hand side) of a ”
string
Retrieves a
number of
characters from
the middle of a
string from a Retrieving
3 MID Text specified start =MID(“GURU99”,2,3) Character
position and s 2 to 5
length.
=MID (text,
start_num,
num_chars)
 FUNCTIO COMMEN
S/N CATEGORY DESCRIPTION USAGE
N T
Returns True if
value –
the supplied
4 ISTEXT Information =ISTEXT(value) The value
parameter is
to check.
Text
Returns the
starting position
of a text string
within another
Find oo in
text string. This
5 FIND Text =FIND(“oo”,”Roofing”,1) “Roofing”,
function is case-
Result is 2
sensitive.
=FIND(find_tex
t, within_text,
[start_num])
Replaces part of
a string with
another specified
string. Replace
=REPLACE
6 REPLACE Text =REPLACE “oo” with
(“Roofing”,2,2,”xx”)
(old_text, “xx”
start_num,
num_chars,
new_text)

Date Time Functions

These functions are used to manipulate date values. The following table shows some of
the common date functions

S/N FUNCTION CATEGORY DESCRIPTION USAGE

Returns the number

1 DATE Date & Time that represents the =DATE(2015,2,4)
date in excel code

Find the number of

2 DAYS Date & Time days between two =DAYS(D6,C6)
dates

Returns the month =MONTH(“4/2/2015”

3 MONTH Date & Time
from a date value )

Returns the minutes

4 MINUTE Date & Time =MINUTE(“12:31”)
from a time value

5 YEAR Date & Time Returns the year =YEAR(“04/02/2015”

S/N FUNCTION CATEGORY DESCRIPTION USAGE

from a date value )

VLOOKUP function

The VLOOKUP function is used to perform a vertical look up in the left most column and
return a value in the same row from a column that you specify. Let’s explain this in a
layman’s language. The home supplies budget has a serial number column that uniquely
identifies each item in the budget. Suppose you have the item serial number, and you
would like to know the item description, you can use the VLOOKUP function. Here is how
the VLOOKUP function would work.

=VLOOKUP (C12, A4:B8, 2, FALSE)

HERE,
 "=VLOOKUP" calls the vertical lookup function
 "C12" specifies the value to be looked up in the left most column
 "A4:B8" specifies the table array with the data
 "2" specifies the column number with the row value to be returned by the
VLOOKUP function
 "FALSE," tells the VLOOKUP function that we are looking for an exact match of the
supplied look up value

The animated image below shows this in action

Summary
Excel allows you to manipulate the data using formulas and/or functions. Functions
are generally more productive compared to writing formulas. Functions are also more
accurate compared to formulas because the margin of making mistakes is very
minimum.

Here is a list of important Excel Formula and Function

 SUM function = =SUM(E4:E8)
 MIN function = =MIN(E4:E8)
 MAX function = =MAX(E4:E8)
 AVERAGE function = =AVERAGE(E4:E8)
 COUNT function = =COUNT(E4:E8)
 DAYS function = =DAYS(D4,C4)
 VLOOKUP function = =VLOOKUP (C12, A4:B8, 2, FALSE)
 DATE function = =DATE(2020,2,4)