INTRODUCTION TO SPREADSHEETS

Examples of spreadsheets
1. Ms Excel
2. Lotus
3. Open office Calc
4. Quattro pro
5. SuperCalc
6. Symphony

Ms Excel is a program designed for performing calculations.

It is also called a spreadsheet
A spreadsheet is a worksheet for setting out calculations or table of figures
Spreadsheets are divided into columns and rows identified by numbers and letters.
The squares are made up of intersecting rows and columns which are called cells.
Cells are identified by cell reference.
A cell reference is the column position and row position combined eg the address F6 means that the cell is
in column F and row 6.

An active worksheet is the worksheet that is currently open.

How a spreadsheet look like

The shaded cell referencing is C1

Ms Excel environment
1. Title bar – it displays the name of the program and file in use
2. Menu bar – contains commands used by Ms Excel
3. Tool bar- it is a quick way of accessing commands in Ms Excel menu
4. Vertical scroll bar- it is used to move up or down the sheet
5. Horizontal scroll bar- it is used to move right or left of the sheet
6. Formular bar- it shows the contents of the active cell. Values and formulars can also be edited in
the formular bar.

Advantages of using spreadsheets

1. Complex Calculations are done quickly
2. Finished worksheet can be saved for later use
3. Figures may be altered with automatic recalculation
4. Information can be presented using graphs and charts
Applications of spreadsheets
i. Used for preparing budget layouts
ii. Used for presentations of financial statements

1
 iii. Used for loan calculations
iv. Used for statistical analysis
How to open Ms Excel
1. Click on start
2. Click on all programs
3. Click on Ms Office
4. Click on office Excel

Cell contents
 Label is text within a cell, usually describing data in the rows or columns surrounding it. Label
can be used as a title or heading.
 Value is numerical data that can be used in a calculation.
 Formula

Create a simple formula in Excel

A formula is an equation that performs a calculation. Like a calculator, Excel can execute formulas that
add, subtract, multiply, and divide.
You can create a simple formula to add, subtract, multiply or divide values in your worksheet. Simple
formulas always start with an equal sign (=), followed by constants that are numeric values and
calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs.
ALL FORMULAS START WITH AN = SIGN

Examples of formulas to do calculation

=A2+B2 addition
=A2*B2 multiplication
=A2/B2 division
=A2-B2 subtraction
=A2^B2 exponentiation
Moving around a worksheet and entering data

You can move around a worksheet using the mouse and the cursor control keys (arrow keys).

Notice when you type data, it is displayed in the selected cell and in the content box at the top of the
window. The data enters the cell only when the ENTER key or an arrow key is pressed.

Changing or deleting the contents of cell

Steps to edit or delete data in a cell

1. Select the cell

2. Edit the data or delete it
Insert and deleting rows and columns

2
 Insert a row
1. Select the row below the place where you want the new row to go by clicking on the
row number to the left of column A
2. Right click and select Insert
Insert a column
1. Select the column to the left of where you want the new column by clicking on the column
letter above row 1.
2. Right click and select Insert
Delete a row
1. Select the row to be deleted by clicking on the row number.
2. Right click and select Delete
Delete a column
1. Select the column to be deleted by clicking on the column letter.
2. Right click and select Delete
Entering labels, values, formulas
To enter data in a cell in Excel, you select the cell, type the data, and press Enter. Excel moves the cell
cursor down one cell.

In Excel, the worksheet consists of a grid of columns and rows that form cells. You enter three types of
data in cells: labels, values, and formulas.
Labels
(Text) are descriptive pieces of information, such as names, months, or other identifying statistics, and
they usually include alphabetic characters.
Values
(Numbers) are generally raw numbers or dates.
Formulas
These are instructions for to perform calculations

Simple Formula:
Click the cell in which you want the answer (result of the formula) to appear. Press enter once you have
typed the formula.
ALL FORMULAS START WITH AN = SIGN.
Refer to the cell address instead of the value in the cell e.g. =A2+C2 instead of 45+57. That way, if a value
changes in a cell, the answer to the formula changes with it.

+ means add e.g. =A2+C2 Add the value in A2 to the value in C2.

Subtract the value in C2 from the value in

- means subtract e.g. =A2-C2 A2.

* means multiply e.g. =A2*A4 Multiply the value in A2 by value in A4.

/ means divide e.g. =A2/A3 Divide the value in A2 by A3.

Copying formulas

3
Is to move data to another location while it remain on the original location
How to copy a formula
1. Select the cell that you want to copy
2. Click on copy button
3. Click the cell where you want to place the data
4. Click on paste button

You can also copy a formula in an active cell to cells in the same column immediately above or below by
dragging the fill handle.

How Excel aligns the data

1. Label: Excel aligns text to the left side of the cell.
2. Value: If the data is a whole value, such as 34 or 5763, Excel aligns the data to the right side of the
cell.
Functions
A function is a predefined formula that can automatically calculate results, perform actions based on the
information provided.
These formulas are used for working with long lists of numbers. A typical function looks like this:
1. SUM
is a function, that adds up the list of numbers. e.g =SUM(A3:A30)
2. AVERAGE
Is a function used to find the mean of a set of values e.g =AVERAGE(B3:B9)
3. MAX
Is used to identify the highest number in the list e.g. =MAX(E5:E9)
4. MIN
Is used to identify the lowest number in the list e.g =MIN(E2:E7)
Other functions:
There are many functions that you could explore with time, below are a few more:

PRODUCT multiplies the numbers in the list

COUNT counts the number of numerical items in the list

COUNTA counts the number of alphabetical items in the list

counts the number of items that satisfy certain criteria e.g.

=COUNTIF(A4:A20,">50") counts the number of values that are higher
COUNTIF than 50 in the list from A4 to A20

4
IF FUNCTION
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to
FALSE.
Example 1

A
1
Data
2
50

Formula Description (Result)

=IF(A2<=100,"Within If the number above is less than or equal

budget","Over budget") to 100, then the formula displays "Within
budget". Otherwise, the function displays
"Over budget" (Within budget)

=IF(A2=100,SUM(B5:B15),"") If the number above is 100, then the range

B5:B15 is calculated. Otherwise, empty
text ("") is returned ()

Example 2

A
1
Score
2
45
3
90
4
78

Formula Description (Result)

=IF(A2>89,"A",IF(A2>79,"B", Assigns a letter grade to

IF(A2>69,"C",IF(A2>59,"D","F")))) the first score (F)

=IF(A3>89,"A",IF(A3>79,"B", Assigns a letter grade to

IF(A3>69,"C",IF(A3>59,"D","F")))) the second score (A)

5
 =IF(A4>89,"A",IF(A4>79,"B", Assigns a letter grade to
IF(A4>69,"C",IF(A4>59,"D","F")))) the third score (C)

In the preceding example, the second IF statement is also the value_if_false argument to the first
IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF
statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the
first logical_test is FALSE, the second IF statement is evaluated, and so on.

The letter grades are assigned to numbers using the following key.

If Score is Then return

Greater than A
89

From 80 to 89 B

From 70 to 79 C

From 60 to 69 D

Less than 60 F

CHARTS AND GRAPHS

Charts and graphs are used to make information clearer and easier to understand. The most common place
for people to see charts and graphs is in the news. News publishers use graphics all the time to show
comparisons and explain important trends for things such as weather, gas prices, crime rate etc. Charts
and graphs are also critical to engineers, scientists and financial analysts who use them to help visualize
large amounts of information, make better decisions, and communicate their results to other people.

Produce Charts and Graphs from Spreadsheet

You can display Microsoft Excel data graphically in a chart.
Microsoft Excel allows you to create two types of charts:
1. An embedded chart - is an object placed on a worksheet and saved with that worksheet when the
workbook is saved.
2. A chart sheet - is created when a worksheet is chosen to contain a chart only.
How to Move a chart sheet
1. select the chart
2. click Move button
3. select New sheet
4. click on OK

6
How to create a chart
1. Select the data to appear to appear in the chart
2. On Insert tab and click on the relevant chart.

There are many other types of charts and graphs, as well as many other variations on the types listed
above. Examples of charts are as follows:
1. bar chart
2. column chart
3. line chart
4. pie chart
5. area chart
6. scatter chart

1. Bar Graph
A bar graph is a graph that shows you information about two or more discrete objects, events, locations,
groups of people, etc. You can use the bar graph to make comparisons.
2. Column Chart
A column chart is just a bar graph where the bars are displayed as columns. Instead of the labels or
categories listed on the left, they are listed on the bottom.
1. Line Graph
A line graph is a chart that connects a series of data points with a line.
2. Pie Charts
A pie chart is a circular graph where the pieces of the pie are used to represent a percentage of a whole.
3. Area Charts
An area chart or area graph combines some of the characteristics of line graphs with the characteristics of
bar graphs. It looks like a line graph with shading underneath the line.
4. XY Graph (Scatter Plot)
An X-Y graph, also called a coordinate graph or scatter plot, is a graph that shows different ordered pairs
on an X-Y axis (Cartesian coordinate system). X-Y graphs are used to look at and find patterns in sets of
data and to plot mathematical formulas.
Pictograph
A pictograph shows data using a series of pictures, where a picture represents a number.
What is printing?
It is to produce a hardcopy of the text or image in a document/spreadsheet file.

How to print a chart

1. Click the chart you want to print
2. Click on file
3. Click on print

EDIT TITLES OR DATA LABELS IN A CHART

To change chart titles (titles in charts: Descriptive text that is automatically aligned to an axis or
centered at the top of a chart.), axis titles, and data labels (data label: A label that provides
additional information about a data marker, which represents a single data point or value that

7
originates from a worksheet cell.) that are added to data points (data points: Individual values
plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and
various other shapes called data markers. Data markers of the same color constitute a data
series.) on a chart, you can edit their contents. If titles and data labels are not linked to worksheet
data, you can edit them directly on the chart and use rich-text formatting to enhance their
appearance.
How to edit chart title, label axis
1. To edit the contents of a title, click the chart or axis title that you want to change.
2. Click again to place the title or data label in editing mode, drag to select the text that you
want to change, type the new text or value, and then press ENTER.

TEXT ALIGNMENT
Text alignment is a feature that allows users to horizontally align text on a page/document.
It enables the composition of a text document using different text positioning on the whole or selected
part of a page.
Text alignment primarily places the cursor or aligns the text with the different margins of the document.
There are three different types of text alignment features, including:
1. Right alignment: This starts each new line of the document on the right-most margin of the page.
2. Left alignment: As the default alignment in most word processing software, it starts each line on
the left-most margin.
3. Center alignment: This positions and starts each new line/text block in the center/middle margin
on the page.
How to align text
1. Select text you want to align
2. Click on home tab
3. Choose the alignment you want
Merge cells
Merge cell is to combine two or more cells into a single cell
How to merge cell
1. Select two or more cells you want to merge.
2. On Home click Merge & Center
Borders
A Border is an outer edge of a cell.
Steps to add cell borders:
1. Select the cells you want to format.
2. Click the down arrow beside the Borders button in the Font group on the Home tab.
Wrap text
The Excel wrap text feature can help you fully display longer text in a cell without it overflowing to other
cells. Wrapping text means displaying the cell contents on multiple lines, rather than one long line. This
will allow you to avoid the "truncated column" effect, make the text easier to read and better fit for
printing. In addition, it will help you keep the column width consistent throughout the entire worksheet.

8
How to Wrap Text
1. Select the cells you want to wrap text
2. On Home tab, click the Wrap Text
Freezing and unfreezing planes
Freeze or lock rows and columns
You can view two areas of a worksheet and lock rows or columns in one area by freezing or splitting
panes. When you freeze panes, you select specific rows or columns that remain visible when scrolling in
the worksheet.
For example, you would freeze panes to keep row and column labels visible as you scroll, as shown in the
following example.

Worksheet window with row 1 frozen

Freeze panes to lock specific rows
1. Select the row below where you want the split to appear.
2. On the View tab, click Freeze Panes
Freeze panes to lock specific columns
1. Select the column to the right of where you want the split to appear.
2. On the View tab, click Freeze Panes
Freeze panes to lock specific rows and columns
1. Click the cell below and to the right of where you want the split to appear.

2. On the View tab, click Freeze Panes

Sorting in Ascending and Descending order
Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical
order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons.
Sorting data helps you quickly visualize and understand your data better, organize and find the data that
you want, and ultimately make more effective decisions.
Types of sorting
1. Ascending order (A to Z) or (0 to 9)
2. Descending order(Z to A) or (9 to 0)

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates
and times (oldest to newest and newest to oldest) in one or more columns.
How to sort data

9
 1. Select a column of alphanumeric data in a range of cells
2. Click on the Home tab, and then click Sort & Filter.
3. Do one of the following:
 Click Sort A to Z (To sort in ascending alphanumeric order)
 Click Sort Z to A (To sort in descending alphanumeric order)
Adjusting cell width and height
On a worksheet, you can specify a column width of 0 (zero) to 255. This value represents the number of
characters that can be displayed in a cell that is formatted with the standard font. The default column
width is 8.43 characters. If the column width is set to 0, the column is hidden.
You can specify a row height of 0 (zero) to 409. This value represents the height measurement in points
(1 point equals approximately 1/72 inch). The default row height is 12.75 points. If the row height is set
to 0, the row is hidden.
Set a column to a specific width
1. Select the column or columns that you want to change.
2. On the Home tab, click Format.
3. Click Column Width.
4. Type the value that you want.
Set a column to a specific height
1. Select the column or columns that you want to change.
2. On the Home tab, click Format.
3. Click Column height.
4. Type the value that you want.
Change the height of rows by using the mouse
To change the row height of one row, drag the boundary below the row heading until the row is the
height that you want.

DELETE , INSERT ROW OR COLUMN

You can insert blank cells above or to the left of the active cell (active cell: The selected cell in which
data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a
heavy border.) on a worksheet (worksheet: The primary document that you use in Excel to store and work
with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and
rows; a worksheet is always stored in a workbook.), shifting other cells in the same column down or in
the same row to the right. Similarly, you can insert rows above a row and columns to the left of a column.
You can also delete cells, rows, and columns.
How to insert a row
1. Select the position where you want to insert a row

10
 2. Right click the mouse
3. Choose insert
4. Click on entire column
Or
1. select the row or a cell in the row above which you want to insert
2. on Home tab, click insert
3. choose insert sheet rows

How to insert a column

1. Select the position where you want to insert a row
2. Right click the mouse
3. Choose insert
4. Click on entire column
Or
1. select the column or a cell to the right which you want to insert
2. on Home tab, click insert
3. choose insert sheet columns
TEXT ORIENTATION
Suppose you want to align text diagonally facing the north-east direction. Text may be rotated at an angle
counterclockwise, angle clockwise, vertical, rotate text up and rotate text down. You may want to do so to
allow the document to give a more organized and managed look and feel.

How to change text orientation

1. Select the cell with text to be changed orientation.
2. Click Orientation on Home tab.
3. Choose the orientation
NUMBER FORMATS
Excel has many in-built number formats that you can use: currency, accounting, date, time percentages
etc.
How to change number formats
1. Select the cells you want to change format
2. Right click
3. Choose format
4. Choose the format and click ok
DECIMAL PLACES
You can change the decimal places without changing the number itself y selecting options in Excel,
which are:
1. Decrease decimal button

11
 2. Increase decimal button
You can also round a number.

How to change decimal places

1. Select the number you want to increase or decrease decimal places
3. On Home tab, choose Decrease decimal or Increase decimal
FREEZING AND UNFREEZING PANES
If you have a large table of data in Excel, it can be useful to freeze rows and columns. This way you can
keep rows and columns visible while scrolling through the rest of the worksheet.
How to freeze top row
1. On View tab, click Freeze panes
2. Click Freeze top row
How to freeze panes
1. Select row 3
2. On View tab, click Freeze panes
3. Click Freeze panes
All rows above row 3 are frozen
How to unfreeze panes
1. On View tab, click Freeze panes
2. Click Unfreeze panes

Graphs and Chart

Types of graphs
1. Column
2. Line
3. Pie
4. Bar
How to create a chart
1. Select the cells that contain the data that you want to use.
2. On Insert tab, choose the chart you want to use.

Create a simple formula in Excel

A formula is an equation that performs a calculation. Like a calculator, Excel can execute formulas
that add, subtract, multiply, and divide.

12
 You can create a simple formula to add, subtract, multiply or divide values in your worksheet.
Simple formulas always start with an equal sign (=), followed by constants that are numeric values
and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs.

xcel uses standard operators for equations, such as a plus sign for addition (+), minus sign for
subtraction (-), asterisk for multiplication (*), forward slash for division (/), and caret (^) for
exponents.

The key thing to remember when writing formulas for Excel is that all formulas must begin with
an equals sign (=). This is because the cell contains—or is equal to—the formula and its value.

When a formula contains a cell address, it is called a cell reference. Creating a formula with cell
references is useful because you can update data in your worksheet without having to rewrite the
values in the formula.
a label is text within a cell, usually describing data in the rows or columns surrounding it.

For example, to create a formula in cell C2 that multiplies a value entered in cell A2 by a value in
cell B2, enter the following formula in cell C2: =A2*B2
To enter this formula in cell C2, follow these steps:
1. Select cell C2.
2. Type the entire formula =A2*B2 in the cell.
3. Press Enter.
Or
1. Select cell C2.
2. Type = (equal sign).
3. Select cell A2 in the worksheet by using the mouse or the keyboard.

Cut, copy and paste

Saving and printing
Printing a spreadsheet
Alignment
Text orientation
Sort
- What if calculations
- Formular bar
- Name box
- Rows and columns headers which are numbered and lettered respectively
- Worksheets.
- Formulars
- Save as
- Graphs and charts.
- Merge and centre

13
- Wrap text
- Sort and filter
- Clipboard
- Cut and paste.
- Zoom tool
- Scroll bar
- number formatting.
- Title bar
- Ribbon
- Status bar

14