Create and Use Spreadsheets

Unit Descriptor
This unit describes the performance outcomes, skills and knowledge required to correctly create
and use spreadsheets and charts through the use of spreadsheet software.

INTRODUCTION
A spreadsheet is a computer program using columns and rows. It allows easy entry of text and
figures, and can be applied to simple budgeting, or complex accounting and financial planning.
“Spreadsheet” is a word used to describe the program, but is also used to describe a sheet with
columns and rows. Excel uses the word ‘worksheet’ to describe a single sheet and the word
‘workbook’ to describe a number of sheets together which form a ‘book’. You can add hundreds
of worksheets to a single workbook but excel opens with only 3. A workbook is saved as one file
“cell” is the word used to describe the intersection between a columns and a row.
The first bar which is blue is called the title bar and contains the program name and which book
you have opened. At first opening it will be book 1. When you save the file, you will give it a
filename and this name will then appear in place of book 1 in the title bar.
The office button and the quick access toolbar are also located on the title bar.
The second bar is called the Ribbon and contain Excel’s functions. To access these, click on the
command tabs, e.g. Home, insert, page layout, and the options will display. You can also roll the
wheel on your mouse to scroll through the various ribbons. Try it!
When you rest your mouse on each function, the function name and purpose will appear in a
small box.
Objectives: dear learner upon successful completion of this competency, you will be able to:
 Create and edit a simple spreadsheet document
 Describe the advantage of using formulas rather than entering values only
 Use copy, cut, and paste to copy and move data including formulas
 Format data in cells including font, bold, underline, italic, and shading
 Print a spreadsheet and adjust the fit
 Identify spreadsheet task requirements and clarify with relevant personnel as required
 Ensure data is entered, checked and amended in accordance with organisational and task
requirements, to maintain consistency of design and layout
 Format spreadsheet using software functions, to adjust page and cell layout to meet
information requirements, in accordance with organisational style and presentation
requirements
 Ensure formulae are used and tested to confirm output meets task requirements, in
consultation with appropriate personnel as required
 Use manuals, user documentation and online help to overcome problems with
spreadsheet design and production
 Create chart using appropriate data range in the spreadsheet
 Modify chart type and layout using formatting features

1
  Ensure data input meets designated time lines and organisational requirements for speed
and accuracy
 Name and store spreadsheet in accordance with organisational requirements and exit the
application without data loss/damage

Identifying and clarifying spreadsheet task requirements

Meeting organizational requirements
Organizations use spreadsheets to store and perform calculations on numerical data. For
example, sales figures and payroll information is usually stored on spreadsheets. Once the data
has been entered, calculations can b e performed on the number to produce a required result,
such as total monthly takings or a calculation on interest payments.
Many organizations have specific requirements in relation to how data is stored and the way it is
displayed and presented. You must be aware of these requirements so your spreadsheet meets
your organization’s standards. These requirements may be in relation to how the data is:
 Entered
 Stored
 Presented
 Output or produced
Requirements for the task also be specified by your supervisor or the person requesting the
information. It is important to follow instructions, as well as clarify requirements. Part of
clarifying requirements is asking questions; it may also include checking information and
presentation of past spreadsheets, as well as showing a draft of the spreadsheet prior to
completion. Organizational and task requirement instructions form a type of check for when you
complete the task.

Spreadsheet task requirements may include:

 Data entry
 Output
 Presentation
 Storage

In accounting, spreadsheet is a large sheet of paper that displays the financial position of a
company. It spreads or shows all financial information (such as costs, income and taxes) on a
single sheet of paper for a manager to look at when making a decision. An electronic spreadsheet
organizes information into columns and row. The data can then be manipulated by a formula to
provide a total (sum). The spreadsheet can present the information in a format to help a decision
maker see the financial ‘big’ picture’ of an organization.

2
All organization need to manage and store numerical data and a spreadsheet is the most
appropriate tool for doing this. The most commonly used spreadsheet software is Microsoft
excel.

Before you begin

Before you start entering data into a spreadsheet you should be familiar with the screen elements
of a spreadsheet. Spreadsheets are made up of worksheets and workbooks. A worksheet is
divided into columns and rows and the intersection of a column and row is called a cell. The
current cell is shown by a highlighted rectangle that is called the ‘active cell’. A workbook is a
collection of worksheet.
The following example is sheet 1 in book 1. A1 is the active cell.
Worksheet

If you look closely at this spreadsheet you will notice there are a number of screen elements. The
following sections explain each of the screen elements.
Title bar

The title bar holds the name of the workbook. It has the standard minimize, restore and close
functions founds all office products.

Ribbons- tools buttons

In Microsoft excel 2007, toolbars have changed significantly. The program now operates by
using a number ‘ribbons’ which provides you with easy access to a wide variety of commonly
used tools. Most tool buttons you will use are available from each of the ribbons.

3
Ribbons are categorized under various heading tabs (such as home and insert). Each category has
a tab so you can easily switch to the different tool buttons that you might need to use.

There are also additional tabs that may appear with groups of tool buttons that are relevant to an
action you are performing. For example when you choose to insert a chart, additional sets of
tools appear under design, layout and format tables.
Each tab has group of tools, available, for example, under the home tab, the tool groups available
relate to:-
 The clipboard
 Formatting- text, numeric and cell
 Editing
 Sorting
You will need to familiarize yourself with the various tools that are available in excel 2007,
however the most common tabs you will use are home, insert, page layout and view.
The formula bar

A worksheet is made up of cell. The formula bar displays the current cell reference and is used to
view and edit a cell’s contents. When entering data in a cell you will see a tick and cross button ×
appear on the formula bar. These are the cancel and enter ticks. Accept your entry by clicking on
the tic, or cancel it by clicking on the cross, or simply press the enter key on the keyboard.
The active sheet

Data may include:

 Numbers
 text
DESIGNING A SPREADSHEET
Good worksheet design depends on;
 Careful consideration of the task to be performed by the worksheet
 Effective pre-planning
 Adherence to basic principles of worksheet design.
The basic principles demand that your worksheet should;
 Reliably provide the solution to the problem
 Use a layout and style that both clarify and enhance the information to be conveyed
 To able to be understood by end users
 Be easily updated
 Be able to be edited by others (if necessary)
 Be protected from unauthorised changes (if necessary)

4
  Be time and cost efficient
 Be memory efficient

The following will help you plan memory-efficient worksheets;

 When you enter data, try to do it in a way that uses more columns than rows (memory
usage is based on the number of rows so it will use less memory if you keep the rows to a
minimum.
 Do not leave blank rows in a worksheet to separate information. Alter the row height
instead. Titles can be made just as effective by increasing the row height.
 When formatting cells with borders or shading, format only the minimum number of
cells.
Planning a Spreadsheet
 What should the main heading be?
 Are subheadings needed? What should they be?
 Are there any fixed values (constants) that are needed in formulas? You need to
remember that any formulas that reference fixed values should do so using absolute cell
references. These are best positioned in an area just under the headings but before the
main body of the spreadsheet.
 What are the names of the items about which you are entering data? The answer to this
question will determine;
 How many rows you will need
 The labels that will be needed for those rows.
Appropriate Appearance
Having developed a clear design and used sound formula construction techniques, the
appearance of your spreadsheet can definitely aid in its usability and readability. Your
spreadsheet can be enhanced with numeric, font, color and border formats.
Some guidelines are important to remember.
 What is the purpose of the spreadsheet? This will set the reason for any formatting.
 Are there any appearance standards or requirements set by the client or workplace? Some
organizations may insist that spreadsheets are prepared following a particular style. These style
requirements may be defined in a Corporate Style Guide.
 Will the formatting actually enhance the readability of the spreadsheet?
 Just because the data is money doesn’t mean all the figures need to be in $ with two decimal
places.
Information that would be organized into rows and columns can be managed easily using a
spreadsheet program.

Open Excel.

5
The Ribbon can be minimized to provide more room in the Excel window. Let’s minimize the
Ribbon and display it again. It may be minimized in your window by default.
 Right-click on the menu to display the short-cut menu as shown in Figure 2.1.
The menu is shown in Figure 2.2.

Figure 2.1 Excel menu short-cut menu.

Click the menu item Minimize the Ribbon. If the Ribbon was visible before,
it is now hidden. If the Ribbon was hidden, it is now visible.
Ensure the Ribbon is visible, as shown in Figure 5.2. If it is hidden, use the
above method to reveal it.
The Excel window contains a grid, toolbars, and sheet tabs as shown in Figure 5.2.
Figure 2.2 The Excel window.

The available worksheet area is quite large. The rectangular areas are called cells. There are
1,048,576 rows and 16,384 columns of cells available in each sheet. We will use only part of one
sheet. Let’s scroll to get a sense of the size of the work area.
Click the left, right, up, and down scroll arrows on the right edge of the window as shown in
Figure 2.2.

6
As you click the down scroll arrow you will notice the row numbers increasing. As you click the
right scroll arrow, you will notice the column letters scrolling. After the alphabet has expired, the
“counting” begins again AA, AB, AC, until the last column, XFD.
Press and hold the Ctrl key while you tap the Home key to return to cell A1.
Moving around the Worksheet
Move the mouse pointer to cell D5.
 Click the left mouse button.
Notice that the cell is now outlined in bold. Only one cell will be outlined in bold. This indicates
the active cell. You have activated cell D5 by clicking it.
The sheet tabs at the bottom of the window indicate different worksheets of the Excel workbook.
Each of these sheets also contains 1,048,576 rows and 16,384 columns of cells. To activate a
different sheet, click the appropriate tab.
 Click the tab labeled Sheet2.
Now you have activated the Sheet2 worksheet.
Click the Sheet1 tab to activate Sheet1 again.
Now, let’s begin entering data. There are essentially three types of data: labels, values, and
formulas. A label is text such as a title, the name of a month, or a street address. A value is a
number and a formula is some kind of calculation.
Data Entry
Activate cell A1.
Type: Hello!
While you type this label, several things are happening on the screen. You may notice that the
label seems to appear in two places simultaneously. It appears in cell A1 and it also appears on
the formula bar. Some symbols have also appeared on the formula bar as shown in Figure 2.3.

Figure 2.3 Excel formula bar.

Click the Cancel button on the Formula Bar as shown in Figure 2.3.
Notice that the input “Hello!” has disappeared. You can cancel any input before it has been
entered by clicking the Cancel button.
Type: Hello!
Click the Enter button on the Formula Bar as shown in Figure 2.3.
This button enters the data in the cell. Notice that the buttons on the Formula bar have
disappeared. Similarly, you could have simply pressed the Enter key. The Insert Function
button is used to select a formula; we will examine this later.
Let’s assume the cell entry is incorrect and we wish to erase it from the cell.

7
Make sure that A1 is the active cell. If not, click cell A1 to activate it.
Press the Delete key on the keyboard. Notice that the cell entry has been erased. There are other
methods of deleting cell contents, but pressing the Delete key is quick and intuitive.
We can also make changes to the contents of a cell after it has been entered.
Activate cell A1 if it is not already active.
Type: Welcome to spreadsheet computing!
Press the Enter key on the keyboard.
The text should be entered into the cell as shown in Figure 2.4.

Figure 2.4 Excel text entered.

You should notice a few things. First, since this label is longer than the width of the column, it
scrolls onto adjacent blank cells. Column widths can be enlarged to accommodate data so cells
B1, C1, and D1 could still contain data later and column A could be enlarged. Second, after
pressing the Enter key, the active cell is now A2. After data is entered, the new active cell will
usually be the cell below the entry.
Entering a Formula

The power of the spreadsheet application is the ability to perform calculations using formulas.
Let’s create a formula that adds the contents of cells A2 and A3.
Activate cell A4 if it is not already active.
Type: =A2+A3
Notice that the formula appears in the cell and in the Formula Bar as shown in Figure 5.5. Also,
the cells are color coded corresponding to the formula in cell A4.
Figure 2.5 Excel entering a formula.

Press the Enter key or click the Enter button on the Formula Bar.
Activate cell A4.
Notice that the formula appears in the Formula Bar, but the result of the formula appears in cell

8
A5, as shown in Figure 2.6.

Figure 2.6 Excel formula results.

The power of a formula is in the cell references. Because the cell references are used in the
formula, Excel updates the results when contents of these cells change.
Activate cell A3.
Type: 1,000
Press the Enter key or click the Enter button on the Formula Bar.
Notice that the results in cell A4 have now changed to reflect the new data. Excel formulas
always begin with an equals = sign. In our example, =A2+A3, the plus sign is called an
operator. Excel formulas can contain the following operators:
Operator Description
^ (Caret symbol) Exponentiation
* (Asterisk symbol) Multiplication
/ (Slash symbol) Division
+ (Plus sign) Addition
– (Dash or minus sign) Subtraction
When we create a more complicated formula, we can use these operators and we can also use the
parentheses ( ). Excel will follow the order of operations for mathematics when calculating
formulas.
Let’s start by entering some of the data into specific cells, as shown in Figure 2.7.
Delete the contents of cells A1, A2, A3, and A4. You can select each cell and press the Delete
key to delete the contents.
Enter the data shown in Figure 2.7. Don’t be concerned if some of the data appears to be “cut
off” when you enter information in adjacent cells. We’ll deal with column widths later. When
you have completed entering all of the data, your spreadsheet should look something like that
shown in Figure 2.8.

9
Figure 2.7 Excel sample data showing all cell content

Figure 2.8 Excel sample data displays normal; some of the cell data appears cut off.
Let’s take the opportunity now to save the file.
Click the Save button and save the file as Sample.xlsx.
Save the file periodically as you work through this exercise.
Activate cell A1 by using the Ctrl-Home combination.
Changing the Column Width
Some of the titles in column A have been cut off because of information in cells in column B.

10
This is not a problem since we can adjust the width of any column. Let’s adjust the width of
column A. The longest label in this column is “Net Surplus/Deficit.” We’ll adjust the width until
the entire label is visible.
Move the mouse pointer to the right border of the column A heading (the line between column A
and B above cell A1) as shown in Figure 2.9. Notice that the pointer changes to a vertical line
between a left and right arrow.

Figure 2.9 Excel sizing a column.

Drag the border to the right until the longest label is visible.
Similarly, a column width can be decreased by dragging the line to the left. Using this method,
any column width can be changed.
Moving Cell Contents
Cell contents can be easily copied or moved to other cells. Let’s do some practicing!
Activate cell B4. Let’s move the income figure to cell C4.
Move the pointer to the bottom edge of the active cell (any edge except the bottom right corner
will do the trick).
You should see the pointer change from a cross icon to a large arrow with a move tool icon as
shown in Figure 5.10. Earlier versions of Excel will show a large arrow without the smaller
move tool icon.

Figure 2.10 Excel mouse pointer for moving a cell.

Drag the pointer to cell C4 until you see a “shadow” appear on cell C4.
Release the left mouse button. It’s that easy! The income figure has been moved to cell C4. In
earlier versions of Excel, you may see an outline for the cell instead of a shadow.

11
Using this method, you can move the contents of any cell to any other cell in the worksheet. An
alternate method is to activate the cell to be moved and use a cut and paste method. Let’s use a
different method to move the income figure back to cell B4.
Activate cell C4.
Click the Cut button on the Ribbon as shown in Figure 5.2.
Click cell B4.
Click the Paste button on the Ribbon as shown in Figure 5.2.
Copying Cell Contents
We can copy information from one cell to another as easily as we can move information from
one cell to another. Let’s copy the income figure in cell B4 to cells C4, D4, and E4.
Activate cell B4.
Notice that the active cell is highlighted in bold and contains a box at the bottom right corner of
the cell. This is called the fill handle.
Move the mouse pointer to the fill handle. The pointer should change to a thin cross as shown in
Figure 2.11.

Figure 2.11 Excel mouse pointer on the fill handle of an active cell.
Drag the pointer to cell E4 and release the left mouse button.
Notice the dotted outline on the cells as you drag the mouse pointer, as shown in Figure 5.12.

Figure 2.12 Excel mouse pointer dragging the fill handle.

You should notice that cells C4, D4, and E4 have been filled with the income figure! We can
also use the copy and paste method to copy cell contents.
Activate cell B7.
Click the Copy button on the Ribbon as shown in Figure 2.12.
Notice that there is a flashing marquee around cell B7.
12
Position the mouse pointer in the middle of cell C7 and drag it to cell E7 to select these cells.
Release the left mouse button, as shown in Figure 2.13.

Figure 2.13 Excel cells selected.

Click the Paste button on the Ribbon to paste a copy of the value from cell B7 into the selected
cells.
Copy the Car (gas) figure in cell B8 to cells C8, D8, and E8 using any method you prefer.
Copy the Car (loan) figure in cell B10 to cells C10, D10, and E10 using any method you prefer.
Copy the Food/household figure in cell B12 to cells C12, D12, and E12 using any method you
prefer.
Your document should look something like that shown in Figure 2.14.

Figure 2.14 Excel cells containing budget figures.

Using the Undo and Redo Features

13
Now is a great time to introduce the Undo feature. This command will undo the effect of the
most recent command.
Click the Undo button on the upper left corner of the Excel window.
Excel cannot undo all commands but it’s ideal for commands such as editing, cut, copy, insert,
delete, formatting and others. You can click the drop-down arrow on the Undo button to see the
most recent tasks that can be “undone.” Excel will allow you to click the Undo button repeatedly
to progressively undo the most recent tasks, or you can choose from the list of tasks to undo a
group of tasks. You will not be able to choose a single task in the middle of the list. When you
select a task in the middle of the list, Excel will undo all of the tasks above the task selected.
Notice that the copied cells have been deleted.
Once the Undo feature has been used, the Redo feature can be used to “redo” the most recently
undone task.
Click the Redo button at the upper left corner of the Excel window.
Notice that the copied cells have reappeared.
Entering Formulas
Most of the figures have been entered and it’s time to enter the formulas. Let’s start with the
Total Expenses formula. We could enter a formula such as =B7+B8+B9+B10+B11+B12+B13,
but this is long and cumbersome. When there is a group of cells to be included in a calculation
that adds all of the data, we can use the SUM function. This will allow us to specify a group of
cells by dragging to select the cells or typing a specific range.
Activate cell B15.
Type: =SUM(
Do not type any spaces in this formula. Be sure to type the left parenthesis because it is part of
the function.
Select the group of cells from B7 to B13 by dragging through them. Notice that there is a
marquee around the block of cells as shown in Figure 2.15.

14
Figure 2.15 Excel Sum function.
Press the Enter key to complete the formula.
Activate cell B15 to see the formula in the formula bar.
Notice that Excel has placed a right parenthesis at the end of the function to end it. The formula
should be =SUM(B7:B13). The end cells are specified, separated by a colon (:) to define the
range of cells. The result of the formula appears in cell B15 but the actual formula appears on the
formula bar.
Copy the formula in cell B15 to the range C15:E15. You can use the dragging method or the
copy and paste method.
Activate each cell (C15, D15, E15) and look at the formula in the formula bar. The formula in
cell C15 is =SUM(C7:C13).
Excel has changed the range in each cell accordingly. This is exactly the effect that we need at
this point, so don’t hesitate to copy formulas.
Now let’s enter the formula for the Net Surplus/Deficit using a pointing method. We will
subtract the Total Expenses figure from the Income figure.
Activate cell B17.
Type: =
Click cell B4.
Notice the marquee around cell B4.
Type: -
Click cell B15.
Notice the marquee around cell B15, as shown in Figure 5.16.

Figure 2.16 Excel entering a formula using the pointing method.

Press the Enter key to complete the formula.
The formula could have been manually entered as =B4-B15, however the cells can also be
selected with the mouse as we have done.
Copy the formula in cell B17 to the range C17:E17 using any method you prefer.

15
Let’s create the Year-To-Date formulas.
Activate cell F4.
Type: =SUM(
Select the range B4:E4 by dragging through this range to select those cells as shown in Figure
2.17.

Figure 2.17 Excel sum function across a row.

Press the Enter key to enter the formula in the cell.
Copy this formula to the appropriate places in column F. You can use the
Edit, Copy command in this case since the cells are not all adjacent. You can also use the drag
and drop technique, and delete contents of the cells that contain “0.” Figures 2.18 and 2.19 show
the budget document as it is displayed and with the formulas.

16
Figure 2.18 Excel budget document with all values.

Figure 2.19 Excel budget document formulas.

Viewing Formulas
You can view the formulas as shown in Figure 5.19. Press and hold the Ctrl key while you tap
the ~ key.
The ~ key is at the upper-left position on your keyboard. Notice that the display reveals all of the
formulas, as shown in Figure 5.19. To return to the display view we will use the same key
combination.
Press and hold the Ctrl key while you tap the ~ key. Notice that the view has returned to the
display view.
Inserting and Deleting Rows and Columns

17
Excel allows us to insert rows and columns of cells into the worksheet and adjusts the formulas
accordingly. Let’s insert a row between the month headings and the income figures.
Activate cell B4. This is the position of the new blank row and activating any cell in the row will
work.
Click the drop-down arrow beside or below the Insert button on the Ribbon as shown in Figure
2.20. This reveals the Insert menu.
Click the menu choice Insert Sheet Rows to add a row at the active cell position.

Figure 2.20 Excel Insert Rows and Columns.

Let’s insert a column, and then delete it.
Activate any cell in column F.
Click the drop-down arrow beside the Insert button on the Ribbon as shown in Figure 2.20.
Click the menu choice Insert Sheet Columns to add a column at the active cell position.
Notice that a blank column has been inserted at the active cell position and any cell references
have been adjusted.
Now that we’ve inserted a column, perhaps we decide that it really isn’t necessary and now we
want to delete it.
Activate any cell in column F.
Click the drop-down arrow beside or below the Delete button on the Ribbon.
Click the menu item Delete Sheet Columns to delete the column.
Printing a Worksheet
You can print an Excel worksheet using the Office button and Print menu command as you
would with any other application program. Worksheets can quickly become wide and long and

18
will print on multiple pages, or can be fit to a single page. When the document is fit to a single
page, the font size is reduced until the data fits on one page.
Click the Office button and click the Print menu item.
Click the Print option as shown in Figure 2.21.
The Print dialog box should appear as shown in Figure 2.21.

Figure 2.21 Excel Print dialog box.

Notice that the Active sheet(s) option is selected. As the workbook can have several sheets, there
is an option to select only the active sheet, or the entire workbook.
If you have a printer available, click the OK button to print the worksheet. If you do not have a
printer available, click the Cancel button to cancel the print.
Changing Alignment in a Cell
To this point, we have been entering data, formulas, and adjusting the worksheet cells. Let’s look
at some formatting options to enhance the document.
By default, all of the labels are left-aligned within the cell and all of the values are right-aligned.
Let’s adjust the month labels so that they are centered in the cell.
Select the cells containing the months by positioning the mouse pointer in the middle of cell B3
and dragging through to cell E3.
Click the Center button on the Ribbon as shown in Figure 2.22.

19
Figure 2.22 Excel Ribbon buttons.
Wrapping Text in a Cell
If the content of a cell is too long, we may prefer to wrap the text within the cell, rather than
increase the column width. First, make sure that the title “Year-to-Date” is extending beyond
column F and into column G. If column F is wide enough for the title, decrease the column width
a bit.
Activate cell F3.
Click the Wrap Text button on the Ribbon as shown in Figure 2.22.
Notice that the Year-To-Date title is now wrapped within the cell. No new row has been added
and the row height has been adjusted to accommodate the label.
Merge and Center
We can center a label across a group of columns easily.
Activate cell A1. This cell contains the label “Budget for Jane Doe.” It’s important that the label
to be centered is entered in the leftmost cell of the range.
Select the range A1:F1 by dragging through the range.
Click the Merge & Center button on the Ribbon as shown in Figure 2.22.
Notice that the title has been centered across columns A through F. The cells A1:F1 have been
merged. When you activate any of these cells the entire selection is activated, as shown in
Figure 2.23.

20
Figure 2.23 Excel Merge and Center title.
Formatting Values
The values in the worksheet do not contain decimals or dollar signs. We can use formatting
options to include these.
Select the range B5:F5 by dragging through the range.
Click the drop-down arrow beside the Accounting Number Format button on the Ribbon as
shown in Figure 2.24.
Click the menu item $English (U.S.).
Now look at the numbers in the worksheet. If numbers have disappeared and have been replaced
with #, this indicates that the number of characters has exceeded the column width. If that
occurs, it’s necessary to increase the width of the columns. If there is more than one column
affected, it may be easiest to increase the widths as a group.
Let’s format some more values.

21
Figure 2.24 Excel values formatted.
Select the range B18:F18 by dragging through the range.
Click the Accounting Number Format button on the Ribbon and select $English (U.S.).
Select the range B8:F18 by dragging through the range. Start dragging at one of the corner
positions and continue to the opposite corner to select all of the cells in the range. Click the ,
(comma) button on the formatting toolbar.
Notice that all of the numbers greater than three digits now have comma separators and all of the
numbers have two decimal places, as shown in Figure 2.24.
Bold, Underline, Italic, and Font Changes
Let’s display all titles in bold so that they stand out.
Select the range A5:A18 by dragging through it.
Click the Bold button on the Ribbon as shown in Figure 2.22.
Notice that the titles in column A are now bold. The bold formatting increases the width of the
characters slightly and you may have to increase the width of column A to view the Net
Surplus/Deficit title.
Using whichever method you prefer, display all of the other titles in bold as well. Note that to
bold the “Budget for Jane Doe” title, you will select the merged cell A1:F4.
Let’s underline the title of the worksheet.

22
Activate cell A1.
Click the Underline button on the Ribbon as shown in Figure 2.22.
Italic print is a slanted print. Let’s change the titles to italic print.
Select the range A1:F3 by dragging through it.
Click the Italic button on the Ribbon as shown in Figure 2.22.
Notice that all of the contents of the cells are slanted to the right. The title at the top of the
worksheet is also underlined.
Let’s adjust the font for the worksheet title in cell A1. Here are a few quick definitions:
Font is a character set. That is, a font is the set of characters including the alphabet, numbers,
and all special characters. A font generally has a name such as Courier, Times, Times New
Roman, and so forth.
Font Style is the manner in which the font is displayed. The font may be bold, underlined, or
italicized.
Size is measured in points. A point is 1/72". A good readable text is 10 or 12 point and a
headline text might be 14 or 20 point.
Select cells A1:F18 by dragging through them.
Click the drop-down box for the Font as shown in Figure 5.25.
Notice that the font list is displayed using the font that is named.

Figure 2.25 Excel Font and Font Size indicators.

23
Scroll through the list to get an idea of the fonts that are available. As you scroll through the list
you may see the text change font in the worksheet.
Select the Times New Roman font by clicking the name in the Font box.
Change the font size to 18 by clicking the Font Size drop-down arrow and selecting 18 from the
listing. Notice that the font size has increased and the row height has increased to accommodate
the characters.
If the numbers are replaced with # symbols, increase the column width. The # symbol indicates
the column width is too narrow to display all of the digits.
Let’s change the color of the text in the title.
Make sure cell A1 is selected.
Click the Font Color drop-down arrow as shown in Figure 2.26. Notice that a color palette is
displayed.

Figure 2.26 Excel Font Color drop-down arrow.

Click one of the color swatches to change the color of the title.
When you are setting font color in a financial worksheet you should be aware that when values
(numbers) are colored red, this is interpreted as negative or loss.
We can also add shading to any cell. Let’s shade the Net Surplus/Deficit figures.
Select the range A18:F18 by dragging through it.
Click the Fill Color drop-down arrow as shown in Figure 2.27.

24
Figure 2.27 Excel Fill Color drop-down arrow.
Click one of the very light colors to select the shading.
Your worksheet should look something like that shown in Figure 2.28.

Figure 2.28 Excel completed worksheet.

 Activity
Practice selecting cells and saying the names.
Click on cell C3. Click on cell D5, A15, call B10
Note: when a cell is selected the name of the cell (also known as cell reference )appears at the
extreme left of the formula bar. Can you see it?
Each cell of the spreadsheet can hold figures, text and formulas. The data in each cell is held in
that cell alone. Always put the data only in the cell that has to hold it.

25
To enter data, put your cursor into the selected cell and click. Type the data and press enter. This
moves you to another cell.
Note: you can also use your arrows keys to move around the sheet.
The data appears in the cell as well as in the formula bar (see below)
Selecting cells
You can select or highlight any number of cells –adjacent or non adjacent. To select cells, click
and drag along a number of cells. When you have selected the cells you want selected, let go of
your muse. The selected cells will appear black, except for the first one you have selected. (see
below) note: always select from left to right and from top to bottom.

Practice
Select cells A1-drag t0 C2
Select cells A3 to D6
Select cells D2 to D10
Select cells A1- drag to C2 select cells A3 to D6. Select cells D2 to D10
You can also select a whole row or a column but be careful when you do this. If you format a
whole column for italics, for example, your spreadsheet may print out over 100 pages of blank
sheets. The same applies to selecting a whole row. It is safer to select only the individual cells
you want selected.
Formulas
Formulae may include:
 absolute cell referencing and/or mixed references
 average multiplication
 sum subtraction
 division maximum
 minimum combinations of above

The next section will involve learning how to write formulas.

You will need a pen or pencil to write the answers in this book. You do not need to key these
exercises into a computer.
Formula involves making calculations similar to any business document, but using cell
references instead of the actual amounts.
Look at the following exercise. It is clear that you must multiply the quantity with the price in
order to produce the cost. Normally we would write such a calculation as 36×0.25. for excel to
do such a calculation, you would have to put in = first to indicate that a formula follows, then,
instead of putting in the amount of 36, you would put in the cell reference, that is, A2. To
indicate multiplication, excel uses the asterisk or star, so written in *. Then put in the cell
reference for the 25 cents, i.e. C2. The whole formula should be =A2*C2. Write this formula into
cell D2 on the sheet below in the space provided.
Now write in the formula calculating the cost for rows 3 to 7. The column letters will stay the
same, but the row numbers will change. Don’t forget to put=first.
26
Note: when you are typing the formulas, it is easier to use the * key from the keypad at the right
of your keyboard.

Task 1
A B C D
1 Quantity Description Price Cost
2 36 Soup 0.25 =A2*C2(e.g.)
3 24 Tomatoes 0.35
4 48 Mixed veg 0.34
5 24 Mushrooms 0.59
6 60 Spaghetti 0.61
7 30 Flour, per kg 0.38
8 Total

Did you get the following formulas?

Cost
=A2*C2
=A3*C3
=A4*C4
=A5*C5
=A6*C6
=A7*C7
Now enter the total formula into cell D8. You can total up this column by adding the cells
together containing the subtotal amount ie=D2+D3+D5+D6+D7.
Task 2
Write in the formulas calculating the total pay in the D column (= hourly rate* hours worked)
and write the formula to calculate the total of that column in cell D6.
A B C D
1 Name Hourly rate Hours worked Total pay
2 Smith $12.33 21 =B2*C2(e.g.)
3 Jones $12.33 24
4 Frderick $12.33 28
5 James $12.33 37.5
6 Total

Task 3

27
Calculate the cost per unit. This is calculated by dividing the total cost by the No of items. The
division sign is indicated by a / that is =B2/C2. Put in the totals for columns B.C and D as well.
A B C D
1 Description Total cost No of items Cost per unit
2 AC21 7365 62.5 =B/C2(e.g)
3 TIXE 34 5847 59.0
4 TIO37 6258 67.75
5 IOP45 4251 60.5
6 AER 46 2541 16.75
7 YHE 51 5241 65.25
Total
Functions
A third way of producing a total is to use a function. This is a special in built formula used by
excel. The total function looks like this =SUM (1 st cell:1st cell). Replace 1st cell by writing in the
cell reference for the first cell to be added and replace last cell with the last cell to be added. The
colon allows all the cells in between to be included in the adding. In cell E2, to add all the
Nursing students, you should write=SUM (B2:D2). Please write in the correct SUM functions for
the other schools, including the total in cell E7.
A B C D E
1 Schools 1995 1996 1997 Total
2 Nursing 3891 3889 4012 =SUM (B2:D2) (e.g)
3 Law 5001 5280 5366
4 Music 1098 1300 1298
5 Arts 492 2931 2999
6 Science 2067 1295 2282
Total

 Task 4
Calculate the amount received for the hire of each game (hours booked * cost per hour) and
calculate the total received using the SUM function.
A B C D E
1 Game Court No Total
2 Volley ball 2 3 10.00 =SUM (C3*D3)
3 Racquet ball 1 1 9.50
4 Squash 4 1 9.90
5 Tennis 3 4 10.00
6 Total $

Task 5

28
Calculate the net weight carried kg on each truck (total weight kg- Truck weight kg) and
calculate the total net weight carried.
A B C D
1 Driver Total weight kg Truck weight kg Net weight carried kg
2 Peter Adams 3900 2193 =sum (B2-C2)
3 Jack day 3507 2111
4 Kim the garth 2500 1400
5 Total

Task 6- percentages
Using percentages is one of the most important calculations in excel. Percentages can be inserted
into formulas by writing the actual amount of the percentage.
In the next exercise, certain items are purchased at cost price, a markup of 25% is added and then
the items are sold for the selling price or retail price. Insert a formula for markup. This is
calculated at 25% of the cost price, that is =25%* cost price. Then, insert the selling price which
is = cost price +markup. Also insert the totals for each column.
A B C D
1 Items Cost price Markup Selling price
2 Folders $2.95 =25%*2.95 =B2+C2
3 Paper $5.95
4 Pens $3.00
5 Erasers $1.25
6 Glue $3.20
Total

Task 7
Sometimes, markup is not separately calculated but is included in the selling price formula in one
step. In the exercise below, the markup is 50%
1. To calculate the retail price, you could key in = cost price+(50%* cost price). The portion
inside the brackets calculates the markup which is then added to the cost price to obtain
the retail price.
2. Another way of calculating this is =150% * cost price. Can you see that this has the same
effect?

Write in your choice of formula into the retail price column below

29
 A B C
1 Yummy bread bakery
2 Description Cost price Retail price
3 Vienna loaf $0.75 +B3+(50%*B3) or 150%*B3
4 French loaf $0.95
5 Wholemeal loaf $1.05
6 White bread loaf 40.95

Averages
Normally, to create an average, you add up the amounts that you want to calculate the
average of and divide them by the number of entries you have, eg 2+4+6+3+5=20. There are
five entries so the average is 20 divided by 5=4
Excel makes it very easy- it gives us a function=average
1. Calculate the averaged of all the outlets for all the columns
a. Type average in cell A12 and widen the column to fit. Click in cell B12
b. Click on the down arrow next to the Auto sum button in the toolbar-a number of
different functions appear.
c. Click on average
1. The dotted line appears, but it is around incorrect cells. The cells we want
to find the average of are cells B5 toB8
2. Click and drag cells B5 toB8 only
3. Click on the enter tick. The average of the income column appears. The 3
steps are illustrated bellow.
Fill the formula to the right to calculate the average of all the columns.
Note that the formatting for currency is copied across to the percentage column. Changed cell
E12 to percentage- select the cell, then click on the percentage button on the ribbon
percentage=11%.
Prepare a spreadsheet for printing
1. Click on the home tab, then on the office button, and select print then print preview.
Click on pages setup. A dialogue box displays with four tabs, page, margins, header/
footer and sheet. We will work with each of these to prepare the spreadsheet for printing.
2. Page: select portrait unless the spreadsheet is too wide for the page. Landscape will turn
the page on its side to allow for more width.
Margins: leave the margin settings, but click on vertically and horizontally to center the
spreadsheet on the page.
Header/ footer: click on custom header or custom footer to create headers and footers.
The icons in the header of footer box insert codes for dates, folder names, file names,.Sheet
names, time, etc.
Codes allow the information to be automatically changed if the date, file name etc change when
printing the spreadsheet.

30
Click your cursor in the left, center or right sections, then select the icon you require.
Sheet: click on gridlines to add vertical and horizontal ruler lines to the spreadsheet for easier
reading of data.
Click on ok to set these options and return to print preview
3. Click on zoom to see the full page view.
4. Note that next page and previous age are greyed- out if your spreadsheet fits on one page.
5. Click on print to print the spreadsheet, or
6. Click on close print preview to return to your worksheet save your spreadsheet file
Page break preview
If the spreadsheet has flowed to more than one page, we can remove any unwanted page breaks.
1. Click on the view ribbon tab
2. Select page break preview
3. The spreadsheet will display blue lines where page breaks occur, and a small dialogue
box. Click on ‘do not show this dialog again’, then ok
4. Click and drag the blue lines to the bottom or right of the spreadsheet to remove the page
breaks.
5. Click on normal to return to normally view of the spreadsheet
Copy a spreadsheet to another sheet
1. Copy and paste sheet 1 to sheet 2 as follows:
Look at where sheet 1 is selected at the bottom of the screen.
2. Click on the select all button located above the number 1 and to the left of the letter A the
whole sheet becomes selected.
3. Select the home tab, then click on the copy icon in the clipboard on the ribbon.
4. Click on sheet 2 in cell A1 then click on paste in the clipboard
Note: to copy, you can also use the shortcut keys ctrlC
To paste, you can also use the shortcut keys ctrlv
Turn on formulas
Formulas are turned on by using the combination of the control key and the tilde/ accent key, ie
press and hold the control key, then quickly tap the tilde/ accent key.
The tilde/ accent key is found directly above the tab key. Use a quick tap for the tilde key
because if you hld it down the formulas will very quickly turn on and off producing a flickering
effect. Use the same combination to turn the formulas off.
Turn formulas on in sheet2, you may find that your headings lose their alignment and that the
main heading may be cut off.
Do not worry about that or try to change it. The only thing that is important is that the formulas
are clearly visible.
Prepare worksheet for printing
Because the columns are so much wider now, you need to change the page orientation to
landscape. Prepare the formula worksheet (sheet 2) for printing as you did before but, make only
one change- change orientation to landscape.

31
Note: if you see has hes ### appear in a column, it means that the column is not wide enough to
hold the data. Widen the column until the data shows clearly. Place your cursor on the line
between the C &D or other letters above the columns and double click to autofit.

Handy hint:
By placing your cursor on various locations of the spreadsheet window or ribbons and clicking
the Right mouse button, you can access menus which display useful options, depending where
your mouse is clicked. For example, right mouse clicking on a row number will display options
to cut, copy, paste, insert rows, delete row, etc.
Activity: place your cursor on various areas of the spreadsheet window, such as the tabs, ribbons,
column letters, row numbers and individual cells, then right – mouse click to view the options.
To undo any of the changes, tap ctrlz (undo), or click on undo on the quick access toolbar

 Exercise2- office supply company

Office supply company need a spreadsheet to keep track of prices, markups and retail values of
their stationery list.
A B C D
1 Office supply company
2 Price list
3 Cost price Markup Selling price
4 Item 17.50
5 USB drivers 1 GB 29.00
6 USB drivers 2 GB 7.50
7 Plastic pockets A4(box 100) 1.20
8 Pens- whiteboard 1.50
9 Pens-Felt tip, fine 5.30
1 Folders A4 various colours 2.50
0
1 Folders A4 lever arch 0.50
1
1 Exercise book- 128 pages 0.50
2
1 MP3 storage 2 GB 29.50
3
1 Archive boxed –large 7.50
4
Note: consult your summary notes if you are not sure of a task.
1. Open a new spreadsheet file and key in the data as above. Hint: click on the office button
to get started.
2. TEXT WRAP to put the double line headings into one cell
1. Use text wrap method 1 for the cost price column heading:
1. Type cost in cell B4
2. Press Alt+ Enter
32
 3. Type price, press enter
2. Use text wrap method 2 for the selling price column heading
1. Click in the, key in selling price
2. Click on home tab, then click on the wrap text button (Alignment group)
3. To unwrap, click on the wrap text button again
Calculate the mark up for each of the items. (mark up is the amount that is added by the
proprietor to the cost price. This amount represents his/her profit)
4. The mark up formula is =25% * cost price. Use the click cell method:
1. Click on the first cell under the heading (C5), key in =25%*
2. Click cell B5
3. Click on the blue enter tick
3. Calculate the selling price
1. The selling price formula= cost price+ markup
1. Key in the =sign and use the click cell method to put in the cell references for the remainder of
the formula.
4. Fill down both the markup and the selling price at the same time, ie.
1. Select the two cells containg the formulas, put your cursor on the selling price fill handle and
dill down to the 14th row.
1. Save your spreadsheet as stationery
2. What is the formula in cell C5? D5?-----------------------------------
3 CREATING CHARTS BASED ON THE SPREADSHEET DATA
The concept of using charts
A spreadsheet user can benefit from charts based on the numbers stored in its rows and columns
of cells. Charts enable the user to visualize the data. "A picture," as you know, "is worth a
thousand words." By the same token, a well-designed chart can help you make sense of a
thousand numbers. Charts are also useful to increase the impact of any oral or written
presentation.
For this exercise you’ll open a new spreadsheet document so you can practice creating charts.
Open the document Charts which is in the Other folder on your Work Disk
Your spreadsheet should look like Fig. 3.1 .

Fig 3.1 Data for Charts document

The Excel charting capability

33
Charts are easy to create using the Excel spreadsheet. You may create a chart from information
gathered from most any spreadsheet. There are dozens of different types of charts that you can
create.
Creating a bar chart
The values represented in a chart are called a data series. In the chart you are about to create, the
number of students in the various grade ranges will be represented by bars. The chart may have a
title and a legend which has the names related to the data series. Lesson 5: More on the Use of
the Spreadsheet

34
Most charts, except pie charts, have axes which are the perpendicular lines
along which the data is plotted or displayed. The Y axis is the vertical axis,
which shows the number of students in each grade range. The X axis is the
horizontal axis, which shows the grade range. First you have to designate the
part of the spreadsheet that you want included in the chart. This is called the
chart range.
Select by dragging and highlighting from cell A1 through F2, then click in
the Standard tool bar on the Chart Wizard button (Fig. 3.2)

Fig. 3.2 The Chart Wizard button in the Standard tool bar The Chart Wizard
dialog box is displayed (Fig. 3.2).

Fig.3.3 The Chart Wizard dialog box

Take a few moments to look over the variety of charts and options that Excel offers.
Click on several of the chart types (Bar, Line, Pie, etc.)
To see how a chart will eventually look, click on the button that tells you to "Press and
hold to view sample"

35
 When you have checked out several of the chart types, select the first Chart type: called a
Column chart
In the Chart sub-type section of the dialog box, click on the fourth of the seven samples
(the clustered column with a 3-D visual effect—see Fig. 3.3 above), then click on the
Next> button
Step 2 in the Chart Wizard prompts you for the Data Range (the set of cells that contain the data
you want to use for the chart). You have already selected the data range by dragging across the
set of cells containing the data for the chart.
Click on the Next> button
Step 3 allows you to enter a title for the chart, as well as descriptions for each axis of the chart.
These are your next tasks.
For the chart title enter: Grades for Semester 1, Grade 4
In the X axis box enter: Grades
In the Z axis box (it’s a 3-D chart) enter: Number of Students
Click on Next> to proceed to Step 4 of the Chart Wizard
Finally, click on the Finish button to accept the default of saving the chart as part of the
Charts worksheet
Immediately Excel creates the chart (Fig. 3.3) and displays it on the screen.

You can easily make changes to the chart if you want. Try this now.
Slide the Chart toolbar off to the side if it’s sitting on top of your chart

36
Right click on any piece of the chart (title, any other text, the bars, the chart background, the
gridlines, the chart area as a whole, the legend, the axis numbers or letters)
Notice the handles that immediately appear around the object you've selected. Also notice that each
part of the chart has different options in its popup menu.
Select an option from the menu that pops up and interact with the dialog box to change fonts, colors
and so forth
Try several variations on the look and feel of the chart—experiment; you can always undo anything
you don't like
When you're done practicing, save a copy of the bar chart (use Save As) with the name Bar Chart

Creating a pie chart

Left click near the edge of the Bar chart to select it, then from the Edit menu select Cut
(Ctrl-x) or hit the Del(ete) key
Creating a pie chart will be straightforward if you just successfully completed the bar chart. You
will use the same set of data for the next exercise.
Drag again from cell A1 through F2
In the Standard tool bar click on the Chart Wizard button again
Select Pie for the Chart type: in the Chart Wizard dialog box
Now select the Second of the Pie chart sub-types (Fig. 3. 4), and click on Next>

Fig.3.4 Pie Chart sub-types

You already selected the chart source data so click on Next> again
Enter the chart title: Class Grades and click on Next>

37
There’s nothing to change in Step 4, since you want the chart to be saved with the spreadsheet.
Click on Finish and click in cell A1 to clean up the screen
If all went well, your pie chart is completed and should like the one in Fig. 3.5.

Fig. 3. 5 Completed Pie Chart of grade range

As before with the bar chart, right click on any part of the chart and try some different
fonts or colors and so forth.
When you're done experimenting, save a copy of the pie chart with the name Pie Chart,
then close the spreadsheet by selecting Close from the File menu
3.2 PRINTING THE UPDATED SPREADSHEET
Selecting a section of the spreadsheet for printing
From the Window menu select the spreadsheet document Grade book
You're going to print out the grade book twice. When you first print out your Grade book, you do
not want the LOOKUP Table to appear on the printed report. You want to print only that block
of cells that contains the data pertaining to student grades for the semester. This includes
columns A through N and Rows 1 through 27. The steps that follow will show you how to select
just this block of cells for printing.
Make sure the printer is turned on, and the paper set up correctly
Next you must select (highlight) the range of cells that you want to print.
Drag down diagonally from cell A1 to cell N27 (this will include the Average, Max Score,
and Min Score summary lines at the bottom of the Gradebook), but not the cells with the
LOOKUP table
From the File menu select Page Setup and make sure you have the option for Landscape
orientation selected for the spreadsheet and click OK
From the File menu select Print
Your spreadsheet has just one page. Here are the steps to print just that section of the
spreadsheet that you have selected.
In the Print what section, click on the radio button next to Selection
If you wanted to check on how many pages there are in a spreadsheet before printing, you would
select Page View from the Window menu, and zoom out as you did earlier in this tutorial by
clicking the zoom tool in the Standard toolbar. This will enable you to figure how many pages
you need to select in the Print dialog box.

38
Notice also that in the Print dialog box you can opt to Preview the worksheet before printing.
Click on Preview now
The printed spreadsheet will look better if you remove column and row headers as well as the
cell gridlines.
Click on Setup... at the top of the Preview window, and then click on the Sheet tab
Click to remove the check mark from the boxes next to Gridlines and Row and column
headers, and then click on OK
Save your grade book one more time, then click on the Print button at the top of the
Preview window to print out a hardcopy of your grade book
Now print a second copy of the Grade book document, but this time include the Lookup
Table, along with the gridlines and row and column headers
If you are completing the lesson for a class at school, hand in both copies of the
spreadsheet to your instructor

3.3 MAKING A BACKUP COPY OF YOUR DOCUMENTS

Your last task before completing this session at the computer is to make a backup of your
documents on your Work Disk Backup. The Grade book and Grades Template spreadsheet
documents are saved on your Work Disk, which is in the disk drive.

Close the Excel spreadsheet program

Close or minimize any other windows that may be open on your desktop to make it easier
for you to see what you’re doing
Double click to open the My Computer icon, then double click to open the disk drive that
contains your Work Disk
Drag the Other folder from your Work Disk to the Desktop and drop it there, then drag
the Templates folder to the desktop and drop it there
Watch while Windows makes a copy of your folders on to the desktop, then close the
Window on your Work Disk
Remove your Work Disk from the disk drive, replace it with your Work Disk Backup
and double click on the disk drive icon
Now drag the Other folder and the Templates folder from the desktop to your Work Disk
Backup

39
 Final assessment
1. Enter the following data into a worksheet. Check your worksheet for accuracy
A B
1
2 Monthly sales
3
4 Sales person
5 Lee 12000
6 Mary 15000
7 Trisha 20000
8 Franky 25000
9 Melanie 5000
10 Peter 15000
11 Mathew 10000
12 Anna 30000
13
14 Total
15 Average
2. Format your worksheet change the font type and size, add bolding and adjust the width of
columns. Format the number values as currency.
3. Add a header and footer to your worksheet. Add the date, time and page number
4. Calculate the total of all sales made. Calculate the average sales figure. Give the total and
average calculations absolute cell references.
5. Proof read your worksheet and tests your calculation. Print out your worksheet
previewing it before you print.
6. Select the data from A5 toB12 and select an appropriate chart type. Insert the chart as a
separate worksheet.
7. Format your chart so that values are displayed and all text is clear. Add colors, patterns or
textures to your chart.
8. Preview and adjust your chart before printing it out.
9. Save the file to an appropriate location and name it monthly sales
10. Briefly explain what you did to adjust your workstation to make it easier for you to
complete the task. Briefly state any ergonomically- friendly workplace equipment that
could have been used to assist in preventing injury while working (e.g. document holder).
List any techniques you used to vary your activities while working (for example, rest
breaks) word process your answers.
11. Briefly describe what you can do to conserve energy and paper in the workplace word
process your answers.
12. Briefly describe what organizational and task requirements are. Word processes your
answers.

40
13. Briefly explain what you would do if you experienced a hardware problem (for example,
monitor stopped working ) when completing the task. Word processes your answers.

41