Microsoft

What is a Spreadsheet Program?

A spreadsheet program uses worksheets and workbooks. Spreadsheets allow you to organize information
in rows and tables (which create cells), with the added automatic mathematics. Spreadsheets have been
used for many years in business to keep track of expenses and other calculations. Excel numerical data in
a spreadsheet can be converted easily into a chart for graphical presentation of the data.

Terminologies
Worksheet – Excel’s term for an electronic spreadsheet
Cell address – cells are “containers” that are capable of holding data. They are arranged in a series of
lettered and numbered columns and rows. The cell address is also displayed in the Name box at the left
end of the formula bar.
Workbook – multiple related worksheets.
Active cell – a cell with a dark border (called cell selector) around it. It’s the cell that you have selected to
enter or edit data.
Column heading – the lettered boxes across the top of the Excel’s workspace. Clicking one selects an
entire column of cells, which you can format or move as a whole.
Row heading – the numbered boxes down the side of the Excel’s workspace. Clicking one selects an
entire row of cells, which you can format or move as a whole.
Sheet tabs – click on these to move from one worksheet in a workbook to another.
Name box – the box at the far-left end of the Formula bar that holds the address of the cell or cell range
currently selected in Excel.
Fill handle - the small black square in the corner of the selection. When you point to the fill handle, the
pointer changes to a black cross. To copy contents to adjacent cells or to fill in a series such as dates, drag
the fill handle. To display a shortcut menu that contains fill options, hold down the right mouse button as
you drag the fill handle.

Characteristics of good spreadsheet software

1. Allows presentation of bulky tabular data
2. Contains formulae and other functions that make complex mathematical operations easy to handle
3. Contains database tools that allow links to other applications for information processing.
4. Allows the user to represent information graphically.
5. Allows good organization of information within a given file (workbook).

Launching Your Excel Application

1. Click the Start icon button on the Taskbar at the bottom left corner of your screen.
2. Point to Programs.
3. Click Office from the submenu, and then select Microsoft Excel.

Understanding the Excel Environment

The Workbook Window
The Excel workbook window is shown in Figure 1 (below). Descriptions follow.

1 prepared by PETER WAIRIGU

FIGURE 1. The Excel Workbook Window

Menu Bar. The Menu Bar at the top of the screen gives you access to different commands that are used
for such tasks as opening and closing files, printing documents, formatting data, and other operations.

Toolbars. On the sample window in Figure 1 (above), immediately below the Menu Bar is a row of icon
buttons called the toolbars. There are two toolbars that provides quick access to a number of the most
commonly used Excel features. The first bar is the Standard Toolbar; the second is the Formatting
Toolbar. By positioning the mouse pointer on a toolbar icon (without clicking), a yellow box will appear
next to the icon with a brief description of that icon’s use.

Notes: The example in Figure 1 shows the Standard and Formatting toolbars on separate rows for ease of
viewing; however, on your screen you may see these toolbars on a single row. Whenever only a partial
toolbar is displayed, you can click the double arrows at the end of the toolbar to see additional icon
buttons.
You can control how the Standard and Formatting toolbars are displayed using the Tools menu, as
follows:
1. On the Menu Bar click Tools.
2. Choose (click) Customize from the drop-down menu.
3. Click the Options tab to bring it to the front.
4. Check or uncheck the box next to "Standard and Formatting Toolbar share one row":
 An X in the checkbox means the toolbars will share a single row.
 A blank checkbox means the toolbars will be displayed on separate rows.
5. Click the Close button to return to your workbook window.

2 prepared by PETER WAIRIGU

The Standard Toolbar is illustrated in Figure 2 (below). The buttons, from left to right are: New
Worksheet, Open Worksheet, Save, Print, Print Preview, Spell Check, Cut, Copy, Paste, Format Paint,
Undo, Redo, Insert Hyperlink, Web Toolbar, AutoSum, Function Wizard, Sort Ascending, Sort
Descending, Chart Wizard, Mapping, Drawing, Zoom, and Help. Each of these features can also be
initiated from one of the pull-down menus.

FIGURE 2. Standard Toolbar

The Formatting Toolbar is illustrated in Figure 3 (below). The buttons are, from left to right: Font Type,
Font Size, Bold, Italics, Underline, Margins (left, center, right, merge & center,) Currency Style, Percent
Style, Comma Style, Increase Decimal, Decrease Decimal, Decrease Indent, Increase Indent, Borders, Fill
Color, and Font Color. Each of these features can also be initiated from one of the pull-down menus.

FIGURE 3. Formatting Toolbar

Toolbars are very useful. They give direct access to commands without having to go through the menu
items. Excel offers many toolbars. To see a listing of all the available toolbars, click “View” on the Menu
Bar and select “Toolbars.” A submenu appears displaying the toolbars. To select a toolbar simply click on
it in the submenu. A check mark will appear in front of your selection and will now make that selection
viewable from your worksheet.

Formula (or Function) Bar. The Formula Bar displays the contents of the active cell or the active block.
It includes text, numbers, formulas, and functions.
Active Cell. The active cell is the currently selected cell. A thick border block, called the Cell Pointer,
surrounds the active cell. Data is entered in the active cell.
Active Sheet. Often a file consists of more than one worksheet. Each sheet contains a tab you click to
move from one sheet to another. You can rename sheets to make it easier to remember what each one
contains. You may do so by double-clicking the sheet name, e.g., Sheet1, typing a new name, and tapping
the ENTER key.
Worksheet Window. The worksheet window displays the Excel worksheet. It is comprised of rows and
columns. The columns are labeled with the letters of the alphabet, i.e., A, B, C, etc. The rows are
numbered down the left side. Rows and columns intersect to form cells. Each cell can be referenced via its
column name followed by its row number. For example, the intersection of row 2 and column B is cell B2
(as shown in Figure 1, above).

Navigating in a Workbook
At the bottom of the workbook window (where the sheet tabs are located) are a number of controls you
can use to move from worksheet to worksheet within a workbook. Use the four tab scrolling buttons in the
lower left corner only when you have more worksheets in a workbook than can be displayed at once. You
can drag the tab split box to change the number of the sheet tabs displayed. To reset the tab display,
simply double-click the tab split box.

3 prepared by PETER WAIRIGU

You can also navigate a worksheet using the arrows to the right of your keypad — up, down, left, or
right. You can always use point and click with your mouse.
Selecting a Range (or Block) of Cells
1. Click the first cell you want to include in the range
2. While holding the mouse button down, drag the mouse to include all cells you want to include in the
range.
3. Release the mouse button. The range selected will appear highlighted except for the first cell of the
range.

Entering Data and Using Formulas

Entering a Label (Text) or a Value (Number)
1. Click the cell where you want to enter a label or a value.
2. Type a label (text) or a value (number). A label can include uppercase and lowercase letters, spaces,
punctuation, and numbers. When typing values, do so without commas and dollar signs. You can
format them later using the Format menu.
3. Tap ENTER.

A simple spreadsheet would look like this: -

Creating a Formula
When using a formula in your spreadsheet, (a cell containing a formula that references other cells), the
sum will automatically change as other cell values referenced in the formula change. This feature is very
valuable when editing or adding information to your worksheet. You do not have to remember to update
other cells that rely on that cells information.
A formula always begins with an equal sign (=) followed by some combination of numbers, text, cell
references, and operators. If a formula is entered incorrectly, an ERROR IN FORMULA message will
appear.

This is what a basic spreadsheet may look like, keeping track of the grades for five students. As you'll notice,
numbers automatically align to the right, while text automatically aligns to the left. Room has been allowed at the
top and the left for column and row headings, which have been placed in bold.

Simple Formulas:

4 prepared by PETER WAIRIGU

"92.67" was not entered as the contents for cell "E2." The "formula bar" has the following entered into it:

=(B2+C2+D2)/3

By following the normal order of operations, the contents of the three cells in parenthesis (B2, C2, and D2) are all
added to each other, and then divided by 3. This gives an average of the three grades, which is then shown in the
cell "E2" (where the formula was entered).

If you wanted to do the same for students 2 through 5, you would enter in similar formulas for each cell from "E3"
to "E6" replacing the column and row numbers where appropriate.

An easy method to replicate formulas is to select the cell which contains the original formula ("E2" in this case),
click the bottom right corner of the selection box, and drag down several rows (to "E6" in this example). The
formula will be copied down in each cell, and will change itself to reflect each new row.

Insert Rows & Columns:

You may find that you need to insert a new, blank row where there isn't a blank row any more. 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.

5 prepared by PETER WAIRIGU

Sorting:
One of Excel’s powerful features is its ability to sort, while still retaining the relationships among information. For
example, let’s take our student grade example from above. What if we wanted to sort the grades in descending
order? First, let’s select the information we want to sort.

Now let’s select the “Sort” option from the “Data” menu.

A new window will appear asking how you would like to sort the information. Let’s sort it by the average grade,
which is in Column E; be sure to set by “Descending” order. If there were other criteria you wished to sort by as
secondary measures, you could do so; let’s select “Then by” as “Grade 3” just for the practice of doing so
(“Descending” order, as well).

Excel will sort your information with the specifications you entered. The results should look something like this:

6 prepared by PETER WAIRIGU

Cell Formatting:
You may notice that, by default, Excel will leave as many decimal points as possible within the cell’s width
restraints; as you increase the cell’s width, the number of decimal points increases.

Select “Cells” from the “Format” menu. A new window will appear with a wide variety of ways in which to
customize your spreadsheets.

7 prepared by PETER WAIRIGU

For example, if we wanted to set the percentages fixed to only two decimal points, you can make this selection
under the “Number” category within the “Number” tab. You can also set the formatting for things such as the date,
time, currency, etc.

The “Font” tab will also allow you to change the default font used on the spreadsheet. The other tabs provide even
more ways to customize your spreadsheet and its appearance; experiment with the settings to see what works best
for you.

Calculation Operators
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft
Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or

multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator Meaning (Example)

+ (plus sign) Addition (3+3)
– (minus sign) Subtraction (3–1)
Negation (–1)
* (asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)

Comparison operators You can compare two values with the following operators. When two values are
compared by using these operators, the result is a logical value either TRUE or FALSE.

Comparison operator Meaning (Example)

= (equal sign) Equal to (A1=B1)
> (greater than sign) Greater than (A1>B1)
< (less than sign) Less than (A1<B1)
>= (greater than or equal to sign) Greater than or equal to (A1>=B1)
<= (less than or equal to sign) Less than or equal to (A1<=B1)
<> (not equal to sign) Not equal to (A1<>B1)

Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to
produce a single piece of text.

Text operator Meaning (Example)

& Connects, or concatenates, two values to produce one continuous text value
(ampersand) ("North"&"wind")

8 prepared by PETER WAIRIGU

Reference operators Combine ranges of cells for calculations with the following operators.

Reference Meaning (Example)

operator
: (colon) Range operator, which produces one reference to all the cells between two
references, including the two references (B5:B15)
, (comma) Union operator, which combines multiple references into one reference
(SUM(B5:B15,D5:D15))
(space) Intersection operator, which produces on reference to cells common to the two
references (B7:D7 C6:C8)

The order in which Excel performs operations in formulas

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=).
The equal sign tells Excel that the succeeding characters constitute a formula. Excel calculates the
formula from left to right, according to a specific order for each operator in the formula.

Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For
example, the following formula produces 11 because Excel calculates multiplication before addition. The
formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies
the result by 3 to produce 21.
=(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25
first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(B4+25)/SUM(D5:F5)

How to Enter a Formula

(see Figure 4, below, for an example)
1. Click a cell where you want to enter a formula.
2. Type = (equal sign) to begin the formula.
3. Type the first argument. Remember, an argument can be a number or a cell reference. You can type in
the number or if referencing a cell, you can single click on the cell location to have the cell reference
automatically included in your formula.
4. Next, type an arithmetic operator (see Table 2, above).
5. Next, enter the next argument.
6. Steps 4 and 5 can be repeated as many times as needed to add to the formula.
7. Last, tap the ENTER key. The result of the formula appears in the cell while the formula itself appears
in the Formula Bar.

FIGURE 4. Formula Example

9 prepared by PETER WAIRIGU

Worksheet Functions
Functions are predefined formulas that perform calculations by using specific values, called arguments, in
a particular order, or structure. Functions can be used to perform simple or complex calculations. For
example, instead of typing =A1+A2+A3+A4+A5, you may type =SUM(A1:A5). (The colon indicates the
range from A1 to A5.) In this example, SUM is a function that Excel knows about.
It is important to use the correct syntax and format of a function for correct results.
Structure of a function
The structure of a function begins with an equal sign (=), followed by the function name, an opening
parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
Function name: For a list of available functions, click a cell and press SHIFT+F3.
Argument: Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error
values such as #N/A, or cell references. The argument you designate must produce a valid value
for that argument. Arguments can also be constants, formulas, or other functions.
Argument tooltip: A tooltip with the syntax and arguments appears as you type the function. For
example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.

Entering formulas When you create a formula that contains a function, the Insert Function dialog box
helps you enter worksheet functions. As you enter a function into the formula, the Insert Function dialog
box displays the name of the function, each of its arguments, a description of the function and each
argument, the current result of the function, and the current result of the entire formula.

Adding a Range Using the AutoSum Function

The AutoSum function is a great tool to use when you want to quickly add contents of a range of cells.
1. Click the cell where you want the total to appear (usually the last cell in the column or row of cells
that you want to add. The cell must be blank).
2. Click the AutoSum icon button on the Standard Toolbar. AutoSum inserts a formula that uses the
SUM function. It displays a moving border that looks like scrolling lines around the selected range of
10 prepared by PETER WAIRIGU
 cells. This is called a marquee. Excel puts this around the range of cells it “thinks” you want to add
up, and inserts the range reference in the formula.
3. If this is the correct range, press ENTER. If not, type or highlight the correct range and press ENTER.

Formatting Text and Performing Mathematical Calculations

Choosing a Default Font

Microsoft Excel enables you to choose a default font. The default font is the style of typeface that Excel will use
unless you specify a different style. For the exercises in this lesson, you want your font to be set to Arial, Regular,
and Size 10. To set your font to Arial, Regular, and Size 10:

1. Choose Format > Cells from the menu.

2. Choose the Font tab.
3. In the Font box, choose Arial.
4. In the Font Style box, choose Regular.
5. In the Size box, choose 10.
6. If there is no check mark in the Normal Font box, click to place a check mark there. Your selections are
now the default.
7. Click OK.

Adjusting the Standard Column Width

When you open Microsoft Excel, the width of each cell is set to a default width. This width is called the standard
column width. You need to change the standard column width to complete your exercises. To make the change,
follow these steps:

1. Choose Format > Column > Standard Width from the menu. The Standard Width dialog box opens.
2. Type 25 in the Standard Column Width field. Click OK. The width of every cell on the worksheet should
now be set to 25.
3. Move to cell A1.
4. Type Cathy.
5. Press Enter.

Cell Alignment
The name "Cathy" is aligned with the left side of the cell. You can change the cell alignment.

Centering by Using the Menu

To center the name Cathy, follow these steps:

1. Move the cursor to cell A1.

2. Choose Format > Cells from the menu. The Format Cells dialog box opens.
3. Choose the Alignment tab.
4. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened,
click on Center.
11 prepared by PETER WAIRIGU
 5. Click OK to close the dialog box. The name "Cathy" is centered.

Right-Aligning by Using the Menu

To right-align the name "Cathy," follow these steps:

1. Move the cursor to cell A1.

2. Choose Format > Cells from the menu. The Format Cells dialog box opens.
3. Choose the Alignment tab.
4. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened,
click on Right (Indent).
5. Click OK to close the dialog box. The name "Cathy" is right-aligned.

Left-Aligning by Using the Menu

To left-align the name "Cathy," follow these steps:

1. Move the cursor to cell A1.

2. Choose Format > Cells from the menu. The Format Cells dialog box opens.
3. Choose the Alignment tab.
4. Click to open the drop-down box associated with the Horizontal field. After the drop-down box is opened,
click on Left (Indent).
5. Click OK to close the dialog box. The name "Cathy" is left-aligned.

Alternate Method: Alignment by Using the Formatting Toolbar

Using the Formatting toolbar, you can quickly perform tasks. You can use the Formatting toolbar to change
alignment.
Centering by Using the Toolbar
To center the name "Cathy," follow these steps:

1. Move the cursor to cell A1.

2. Click on the Center icon, which is located on the Formatting toolbar.

The red circle designates the Align Center icon.

Right-Aligning by Using the Toolbar
You can right-align the name "Cathy" by following these steps:

1. Move the cursor to cell A1.

2. Click on the Align Right icon, which is located on the Formatting toolbar.
12 prepared by PETER WAIRIGU
 The red circle designates the Align Right icon.
Left-Aligning by Using the Toolbar
You can left-align the name "Cathy" by following these steps:

1. Move the cursor to cell A1.

2. Click on the Align Left icon, which is located on the Formatting toolbar.

The red circle designates the Align Left icon.

Adding Bold, Underline, and Italic
You can bold, underline, or italicize text in Microsoft Excel. You can also combine these features -- in other words,
you can bold, underline, and italicize a single piece of text.
In the exercises that follow, you will learn three different methods for bolding, italicizing, or underlining text in
Microsoft Excel. You will learn to bold, italicize, and underline by using the menu, the icons, and the shortcut keys.
Adding Bold by Using the Menu

1. Type Bold in cell A2.

2. Click on the check mark located on the Formula bar. Clicking on the check mark is similar to pressing
Enter.

3. Choose Format > Cells from the menu. The Format Cells dialog box opens.
4. Choose the Font tab.

5. Click on Bold in the Font Style box.

6. Click OK. The word "Bold" should now be bolded.

Adding Italic by Using the Menu

1. Type Italic in cell B2.

2. Click on the check mark located on the Formula bar. Clicking on the check mark is similar to pressing
Enter.
3. Choose Format > Cells from the menu. The Format Cells dialog box opens.
4. Click on Italic in the Font style box.
5. Click OK. The word "Italic" is italicized.

Adding Bold by Using the Icon

1. Type Bold in cell A3.

2. Click on the check mark located on the Formula bar.

13 prepared by PETER WAIRIGU

 3. Click on the Bold icon, which is on the Formatting toolbar.
4. Click again on the Bold icon if you wish to remove the bolding.

Adding Italic by Using the Icon

1. Type Italic in cell B3.

2. Click on the check mark located on the Formula bar.

3. Click on the Italic icon, which is on the Formatting toolbar.

4. Click again on the Italic icon if you wish to remove the italics.

Adding Underline by Using the Icon

1. Type Underline in cell C3.

2. Click on the check mark located on the Formula bar.

3. Click on the Underline icon, which is on the Formatting toolbar.

4. Click again on the Underline icon if you wish to remove the underline.

NB: using the keyboard shortcuts,

- Hold down the Ctrl key while pressing "i" (Ctrl-i) to change font to Italics.
- Hold down the Ctrl key while pressing "u" (Ctrl-u) to underline.
- Hold down the Ctrl key while pressing "b" (Ctrl-b) to make it Bold.

Changing the Font, Font Size, and Font Color

You can change the Font, Font Size, and Font Color of the data you enter.
Changing the Font

1. Type Times New Roman in cell A5.

2. Click on the check mark located on the Formula bar.
3. Choose Format > Cells from the menu. The Format Cells dialog box opens.
4. Choose the Font tab. All of the Fonts listed in the Font box are available to you.
5. Find and click on Times New Roman in the Font box.
6. Click OK. The font changes from Arial to Times New Roman.

Changing the Font Size

1. Place the cursor in cell A5.

2. Choose Format > Cells from the menu. The Format Cells dialog box opens.
3. Choose the Font tab.
4. Click on 16 in the Size box.
5. Click OK. The font size changes to 16.
14 prepared by PETER WAIRIGU
Changing the Font Color

1. Place the cursor in cell A5.

2. Choose Format > Cells from the menu. The Format Cells dialog box opens.
3. Choose the Font tab.

4. Click to open the drop-down menu associated with the color field.
5. Click on Blue.
6. Click OK. The color changes to blue.

Alternate Method: Changing the Font Color by Using the Icon

1. Place the cursor in cell A5.

2. Click on the down arrow next to the Font Color icon.

15 prepared by PETER WAIRIGU

 3. Click on Red. Your font changes to red.

Working with Long Text

Whenever you type text that is too long to fit into a cell, Microsoft Excel attempts to display all the text. It left-
aligns the text regardless of the alignment that has been assigned to it, and it borrows space from the blank cells to
the right. However, a long text entry will never write over cells that already contain entries -- instead, the cells that
contain entries cuts off the long text. Do the following exercise to see how this works.

1. Move the cursor to cell A6.

2. Type Now is the time for all good men to go to the aid of their army.
3. Press Enter. Everything that does not fit into cell A6 spills over into the adjacent cell.
4. Move the cursor to cell B6.
5. Type TEST.
6. Press Enter. The entry in cell A6 is cut off.
7. Move the cursor to cell A6.
8. Look at the Formula bar. The text is still in the cell.

Changing a Single Column Width

Earlier you increased the column width of every column on the worksheet. You can also increase individual column
widths. If you increase the column width, you will be able to see the long text.

1. Make sure the cursor is anywhere under column A.

2. Choose Format > Column > Width from the menu. The column width dialog box opens.
3. Type 55 in the Column Width field.
4. Click OK.

Column A is set to a width of 55. You should now be able to see all of the text.
Alternate Method: Changing a Single Column Width by Dragging
You can also change the column width with the cursor.

1. Place the cursor on the line between the B and C column headings. The cursor should look like the one
displayed here, with two arrows.

16 prepared by PETER WAIRIGU

 2. Move your mouse to the right while holding down the left mouse button. The width indicator appears on
the screen.

3. Release the left mouse button when the width indicator shows approximately 40.

Moving to a New Worksheet

In Microsoft Excel, each workbook is made up of several worksheets. Before moving to the next topic, move to a
new worksheet.

1. Click on Sheet2 in the lower left corner of the screen.

Setting the Enter Key Direction

In Microsoft Excel, you can specify which direction the cursor moves when you press the Enter key. You can have
the cursor move up, down, left, right, or not at all. You will now make sure the cursor is set to move down when
you press the Enter key.

1. Choose Tools > Options from the menu. The Options dialog box opens.
2. Choose the Edit tab.
3. Make sure there is a check mark in the "Move Selection after Enter" box.
4. If Down is not selected, click to open the Direction drop-down box. Click on Down.
5. Click OK.

Making Numeric Entries

In Microsoft Excel, you can enter numbers and mathematical formulas into cells. When a number is
entered into a cell, you can perform mathematical calculations such as addition, subtraction,
17 prepared by PETER WAIRIGU
multiplication, and division. When entering a mathematical formula, precede the formula with an equal
sign. Use the following to indicate the type of calculation you wish to perform:
+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponential
Performing Mathematical Calculations
The following exercises demonstrate how to perform mathematical calculations.
Addition

1. Move your cursor to cell A1.

2. Type 1.
3. Press Enter.
4. Type 1 in cell A2.
5. Press Enter.
6. Type =A1+A2 in cell A3.
7. Press Enter. Cell A1 has been added to cell A2, and the result is shown in cell A3.

Place the cursor in cell A3 and look at the Formula bar.

NB You can change the signs to – Subtraction, * Multiplication, / Division or ^ Exponential

The AutoSum Icon

The AutoSum icon on the Standard toolbar automatically adds a column of numbers. The following
illustrates the SUM function:

1. Go to cell F1.
2. Type 3. Press Enter.
3. Type 3. Press Enter.
4. Type 3. Press Enter.
5. Click on the AutoSum button, which is located on the Standard toolbar.

6. F1 to F3 should now be highlighted.

18 prepared by PETER WAIRIGU
 7. Press Enter. Cells F1 through F3 are added.

Absolute Cell Addressing

An absolute cell address refers to the same cell, no matter where you copy the formula. You make a cell
address an absolute cell address by placing a dollar sign in front of both the row and column identifiers.
You can do this automatically by using the F4 key. To illustrate:

1. Move the cursor to cell C10.

2. Type =.
3. Use the up arrow key to move to cell C7.
4. Press F4. Dollar signs should appear before the C and before the 7.
5. Type +.
6. Use the up arrow key to move to cell C8.
7. Press F4.
8. Type +.
9. Use the up arrow key to move to cell C9.
10. Press F4.
11. Press Enter. The formula is recorded in cell C10.

Deleting Columns
You can delete columns from your spreadsheet. To delete columns C and D:

1. Click on column C and drag to column D.

2. Choose Edit > Delete from the menu. Column D is deleted.

3. Click anywhere on the spreadsheet to remove your selection.
19 prepared by PETER WAIRIGU
NB: This can also be done to rows.

Using Microsoft Excel, you can represent numbers in a chart. You can choose from a variety of chart
types. And, as you change your data, your chart will automatically update. You can use Microsoft Excel's
Chart Wizard to take you through the process step-by-step.
Creating a Column Chart

To create the column chart shown above, start by creating the spreadsheet below exactly as shown.

After you have created the spreadsheet, you are ready to create your chart.
Highlight cells A3 to D6. You must highlight all the cells containing the data you want in your chart.
You should also include the data labels.
Choose Insert > Chart from the menu.
Click on Column to select the type of chart you want to create.
In the Chart Sub-type box, choose the Clustered Column icon to select the chart sub-type.

20 prepared by PETER WAIRIGU

 Click on Next.
To place the product names on the x-axis, select the Columns radio button.
Click on Next.
Type Toy Sales in the Chart Title field. Toy Sales will appear as the title of your chart.
Type Products in the Category (X) Axis field. Products will appear as your x-axis title.
Type Units Sold in the Value (Y) Axis field. Units Sold will appear as your y-axis title.
Choose the Data Labels tab.
Select Value in the Labels Contain Frame to display the data labels as values.
Choose the Data Table tab.
Select Show Data Table. The data table will appear below your chart.
Click on Next.
Choose As Object In Sheet1 to make your chart an embedded object and part of the worksheet.
Click on Finish

Your chart will appear on the spreadsheet.

Make Your Worksheets Look good Using AutoFormat

If you need to make a good impression, consider using AutoFormat to make your worksheets look their
best. To apply AutoFormat to your worksheet, perform the following steps:
1. Select the range of cells you want to format
2. Choose format, AutoFormat to display the AutoFormat dialog box.
3. Click the Options button to display a list of formatting elements. Insert and delete the check marks
next to each element to toggle on or off the various formatting options. You can preview these in the
Sample window before applying them to the actual data in your worksheet.
21 prepared by PETER WAIRIGU
4. When you see a result you like, click OK to accept it.

Supplying Headers and Footers for Your Worksheets

Excel comes with a number of preset headers and footers, which should serve the purpose well. To apply
one of these, perform the following steps:
1. Click the worksheet you want to apply the headers or footers.
2. Choose View, Header and Footer from the menu bar.
3. Choose the Header/ Footer tab.
4. In the Header or Footer box, click the drop-down arrow and select the header or footer you want.
5. Click OK to apply the header and/ or footer.

Saving a Worksheet
1. Click the Save icon button on the Standard Toolbar (illustrated above).
2. (Optional) If you want to save the worksheet to a different folder, click File from the menu bar and
click “Save as” box, then select the disk drive and folder that you wish to store your file in.
3. Type the new workbook name in the “File name:” text box.
4. Click Save. The new name appears in the Title Bar. Remember: the title bar is the bar at the very top
of your application window.

Printing
Printing the Entire Worksheet
To print an entire spreadsheet, click the Print button on the Standard Toolbar.

Printing a Section of a Worksheet

1. Highlight the range you wish to print.
2. Click File on the Menu Bar and select Print to display the Print dialog box.
3. Click on Selection in the lower left of the dialog box. Make sure the circle is filled in.
4. Click OK.

Note: You may select other options in the Print window before you print your spreadsheet. For instance,
you may print certain pages of your spreadsheet by filling the “From:” and “To:” boxes in the “Print
range” area. You may also print several copies of your worksheet by selecting the ”Number of copies:”
option in the Print dialog box.

Print Preview

22 prepared by PETER WAIRIGU

You may preview your worksheet before printing. Click the Print Preview button on the Standard
Toolbar (pictured above). After previewing, you may print at this point by clicking the Print button on
the Print Preview toolbar.

23 prepared by PETER WAIRIGU

Exercises:
1. The following worksheet shows the salary calculations for employees in the sales department of a
company.

A B C D E
1
2 SALARY CALCULATIONS
3 EMPLOYEES MARITAL BASIC MONTHLY DEDUCTIONS
NAMES STATUS SALARY SALES
4 Barak M.O. Married 27000 250000 8000
5 Njuguna S. M. Single 23000 234000 3000
6 Odhiambo W. O. Married 28000 170000 4500
7 Macharia R. M. Married 17000 213000 7000
8 Wanjiku E. M. Single 22000 234000 5200
9 Kioo J. M. Single 14000 567000 3200
10 Boit T. Married 18000 123000 5000

a) Open the workbook and save as Third.

b) Enter the data into the first worksheet of the workbook and rename the worksheet as salaries.
c) Calculate the commission for each employee if an employee is entitled to 4% commission on the monthly sales.
d) Calculate the monthly relief, which is 5% of the basic pay for the married employees.
e) House allowance if each employee is entitled to 10% of the basic salary
f) Use an appropriate logical test to determine if an employee is entitles to this relief.
g) Calculate the net pay for each employee.
h) On the second worksheet of the workbook, plot a column chart to compare the basic salaries and the net salaries
of the employees. Rename this sheet as Chart.

2. Maragwa enterprise is a small business, which would like to project the growth of its company over the next
year. The table below shows the budget forecast for the business. It is assumed that the sales figures increase by
1.1 % per month. The overhead costs are to be determined as follows:
a) If the total cost of materials and labour is less than or equal to $18500, overhead is $9000; otherwise overhead
is $8500 plus 1% of the combined material and labour costs.
b) Enter the table into a worksheet and compute the sales and the costs for the remaining months.
c) Use the if function to determine the overhead cost.
d) Calculate the totals and gross profit for the remaining months.
e) Construct a bar chart displaying sales, material, labour and overhead costs.
f) Construct a labeled pie chart displaying Gross profit Vs months.

BUDGET FORECAST

Jan Feb Mar Apr May June Total

Sales $40,000.00

24 prepared by PETER WAIRIGU

Costs. Material $5,000.00 $14,500.00 $10,000.00 $12,300.00 $8,000.00 $14,500.00
Labour. $13,000.00 $4,500.00 $3,200.00 $4,000.00 $12,300.00 $10,000.00
Overhead.

Total costs
Gross
3. COOP has over seven hundred employees. Each employee is deducted 10% of the salary earned
on food, 5% on transport and 2% on PAYE. Each employee who earns above 10,000 gets a
house allowance ofKshs.3000 and those who earn below 10000 gets 2500. (Use the IF function to
determine this}
a) Create a worksheet that contains all these data showing monthly earnings of each employee.
A minimum of 10 employees is required.
b) If each employee was to get 20% of bonus pay for his/her salary, calculate the bonus pay.
c) Plot a pie chart showing this data i.e. employee's bonus pay. Format the chart to look more presentable
and neat.
Chart Title should be included as "COOP EMPLOYEES".
d) Filter the data to show all the employees earning more that Kshs 15000. Copy the filtered data in
separate sheet and rename it as Filtered.
e) Save the workbook as COOP EMPLOYEES.

4. Create the following worksheet

a) The worksheet should be well formatted, font (type, size 12), Bold the text; have borders and
any other formatting to make it more presentable.
b) Calculate a deduction, which includes NSSF that is 5% and NHSF that is 2% of salary.
c) Calculate Net Salary
d) Plot a chart of salary per month. House allowance, Deductions and Net salary.
e) Copy in sheet 2 from sheet 1 the data of salary per month, house allowance, deductions, net
salary. Sort it in ascending order and rename the sheet as "Sorted".
f) Rename sheet 1 as "Salary" and save the workbook as EMP Payslip.
g) The basic salary was increased by 8% in the second year calculate the new PAYE, NSSF and
Marriage Relief (use the if function).
h) All monitory figures are in French francs.
i) Rename the sheet as Malakazi.

Name ID No. PfNo. Salary H.Allowance Net salary

Julie 2346739 550670 15000 8000
Mike 2555723 550714 10000 5000
Joanne 2055117 659100 8000 3000
Nicholas 2186892 500120 24000 12000
Edward 2481311 21243 30000 14000
Jennifer 2064171 374401 14000 1000
Geoffrey 2040056 448866 18000 9000
25 prepared by PETER WAIRIGU
5. Complete the following invoice delivery note and save it as Delivery Note in your Ms Excel
folder.

ITEM NO. TITLE ORDERED SUPPLIED COVER PRICE DISCOUNT EXTENDED

1 Shaka 65 65 679.00
2 Science II 550 550 110.00
3 P Maths 800 800 160.00
4 Our World 320 320 350,00
5 Maths III 430 430 640.00
6 History 755 755 395.00
7 History II 200 200 1500.00
8 P History 490 490 990.00
9 War IT 540 540 1150,00
10 Daily Star 79 79 420.00
11 The People 60 60 750.00

a) 22% discount will be given if the item bought costs Shs. 650 or more.
b) Other items costing less than Shs. 650 get a 13% discount.
c) Extended means the cost of the item purchases after the discounts have been awarded.
d) Insert three more columns namely; Amount, VAT and Net Amount.
e) Amount is calculated as the cost per a supply.
f) VAT is 16% of the Amount.
g) Net Amount is the figure payable after VAT deductions.
h) Format the Cover Price, Discount, Extended, Amount, VAT, and Net Amount into currency with 2
decimal places.
i) Rename the worksheet as Invoice.
6. The following table shows the students' performance in three subjects. Enter the information
in a worksheet and use the formulas and functions to calculate:
a) The total marks of each student
26 prepared by PETER WAIRIGU
b) If a student's average is greater than 80, the student gets a bonus of 3 marks.
c) Each of these units is 3 credit hours. Calculate the GPA of each student. Assume that it's the first
semester. If a student's GPA is less that 3.0 output 'Wake up!' else output 'Bravo'.
d) Also calculate the standard deviation for the complOO marks.

STUDENT COMP100 MATH100 COMP101

1 Wambugu 67 45 89
2 Chao 78 65 56
3 Otieno 67 46 84
4 Karanja 97 45 52
5 Prisca 65 68 62
6 Robert 76 23 65
7 David 56 85 76
8 Sandra 57 28 23
9 John Katana 85 88 76
10 Salome Masinde 100 100 100
11 Dorothy Nkatha 100 100 100

27 prepared by PETER WAIRIGU