ECDL Module 4 Notes

Module 4: Spreadsheet
4.1 Using the Application
4.1.1 Working with Spreadsheets
4.1.1.1 Open, close spreadsheet application. Open, close spreadsheets
Opening the Microsoft Excel Application

Click on the Start Button

Click All Programs

Select Microsoft Office

Click on Microsoft Office Excel 2007

To close a Workbook

Click the Microsoft Office Button

and click close, Close.

OR
Otherwise click on the small x button
To close Excel

Click the Microsoft Office Button

and click Exit

.
OR
Otherwise click on the big x button

TCTC

Page 1

ECDL Module 4 Notes

Opening an Existing Spreadsheet

and Click Open, or

Click the Microsoft Office Button

Press CTRL+O (Depress the CTRL key while pressing the O) on the
keyboard

Choose the location where the file is Saved

Click on the file and Click Open

To open several Spreadsheets

Click on the office button and from the menu choose open.

Use the look in drop down menu to select the drive or folder that contains
the files that you want.

Select the first file, and then select the second file while depressing the
Ctrl button. (This \will allow you to choose two or more files)

Then click on open.

3.1.1.2 Create a new spreadsheet based on default template

Create a New Workbook

There are several ways to create new Workbooks, open existing Workbooks and
save workbooks in Excel:

Click the Microsoft Office Button

Click New

OR

Press CTRL+N (Depress the CTRL key while pressing the N) on the
keyboard

TCTC

Page 2

ECDL Module 4 Notes

You will notice that when you
click on the Microsoft Office
Button and Click New, you have
many choices about the types
of spreadsheets you can create.
If you wish to start from a blank
workbook, click Blank. If you
wish to start from a template
you can browse through your choices on the left, see the choices on centre
screen, and preview the selection on the right screen.
4.1.1.3 Save a spreadsheet to a location on a drive. Save a spreadsheet under
another name to a location on a drive.

Saving a Spreadsheet

Click the Microsoft Office Button

Click Save or Save As (remember, if youre sending the document to

someone who does not have Office 2007, you will need to click the Office
Button, click Save As, and choose Word 97-2003 Document),

OR

Press CTRL+S (Depress the CTRL key while

pressing the S) on the keyboard,

OR

Click the Save icon on the Quick Access Toolbar

TCTC

Page 3

ECDL Module 4 Notes

Note:- Remember that when youre saving for the First time you need to
click on save as but if you have already saved the file and you want to save
it again just click on save.
If you want to change the name of the file repeat the same steps like youre doing
the save for the first time and in file name write the new Name and click Save
4.1.1.4 Save a spreadsheet as another file type like: template, text file, software
specific file extension, version number.

To Save document as another file type:

Click the Microsoft Office Button

Save As

Click on the Arrow to the right where you have Save as type

Choose the Type of format example template

4.1.1.5 Switch between open spreadsheets

There are two ways of how you can switch from one open spreadsheet to
another:1. Click on the required spreadsheet icon that will appear on the
taskbar
2. Or click on the View icon on the Menu Bar and
then click on the Switch Windows and Choose
the document required

TCTC

Page 4

ECDL Module 4 Notes

4.1.2 Enhancing Productivity

4.1.2.1 Set basic options/preferences in the application: user name, default folder
to open, save spreadsheets
Update user information
When Microsoft Excel is first installed, you will be asked to provide some basic
information such as your name, initials and address when you are registering the
software. Microsoft Excel uses this information when you create labels and
envelopes and inputs it into the summary information for each spreadsheet. If the
information you initially entered has changed, you can easily update your new
user information, by following these steps:
1.

Click the Microsoft Office Button

2.

Click Popular.

3.

Under Personalize your copy of Office, type the author name in the User

, and then click Excel Options.

name box.

TCTC

Page 5

ECDL Module 4 Notes

Change the default folder / default file location

By default, Excel saves workbooks in the My Documents folder, located in the
root directory.
To change the default folder:

Click the Microsoft Office

Button

, and then click

Excel Options

Click Save

From the default file

location click on browse

Choose the location

Click OK

4.1.2.2 Use available Help functions.

Click the Microsoft Office Help button

in the upper right or the F1 key on
your keyboard to open Help on your
computer.

The Help window will be displayed as illustrated:

Select one of the available help topics, or click
on the Search Box to search for a topic by
entering a keyword.
TCTC

Page 6

ECDL Module 4 Notes

4.1.2.3 Use magnification/zoom tools.

The Zoom feature in Microsoft Excel provides a way for you to shrink or enlarge
the workbook you are working on for viewing purposes. You can zoom out to see
the complete workbook in a small enough size for it to fit in the window.
Conversely, you can zoom in to get a close-up view of a part of the document.
To zoom your documents follow these steps:

Step1

Go to the View menu.

Step2

Select Zoom.

Step3

Choose one of the preset zoom buttons, or select a

more exact zoom percentage in the custom window
below the buttons.

Step 4

Click OK.
4.1.2.4 Display, hide built-in toolbars. Restore, minimize the ribbon.

Display toolbars

Right click on an existing

toolbar
Customize

and

click

quick

on

Access

Toolbar

Choose a tool

TCTC

Page 7

ECDL Module 4 Notes

Click Add

Remove toolbars

Right click on an existing toolbar and click on Customize quick Access

Toolbar

Choose a tool

Click Remove

Minimize the ribbon

Click Customize Quick Access Toolbar .

In the list, click Minimize the Ribbon.

4.2 Cells
4.2.1 Insert, Select
4.2.1.1 Understand that a cell in a worksheet should contain only one element of
data, (for example, first name detail in one cell, surname detail in adjacent cell).
Inserting Data Worksheet cells can hold three kinds of data:

TCTC

Page 8

ECDL Module 4 Notes

Text

Numbers

Formulas

Entering Text
To enter text in excel all you have to do is click on the cell and start writing. When
finished press Enter key. By default Excel aligns text to the left of a cell, and
numbers to the right of a cell.

4.2.1.2 Recognize good practice in creating lists: avoid blank rows and columns
in the main body of list, insert blank row before Total row, ensure cells bordering
list are blank.
It is important that lists regarding the same subject are not discontinuous. For
excel, this means avoiding Blank rows and columns in the main body of the list.
For the total, insert a blank row between the total and the list so as to be able to
distinguish between them.
Ensure that there is no other data bordering the list so as not to mix the data in
the list with the other data

TCTC

Page 9

ECDL Module 4 Notes

4.2.1.3 Enter a number, date, text in a cell.
You can enter numbers, text, dates, or times in worksheet cells.
Enter numbers or text
1. On the worksheet, click a cell.
2. Type the numbers or text that you want, and then press ENTER or TAB.
Notes

By default, pressing ENTER moves the selection down one cell, and
pressing TAB moves the selection one cell to the right.

A cell may display ##### when it contains data that has a number format
that is wider than the column width. To increase the width of the column,
point to Column on the Home tab in Cells Section click Format, and then
click AutoFit Column

To display multiple lines of text inside a cell, select Cells on the Home tab
in Cells Section click Format. Click on Format Cells, click the Alignment
tab, and then select the Wrap text check box. If the text is a single long
word, the characters won't wrap; you can widen the column or decrease
the font size to see all the text. If not all text is visible after you wrap the
text, you may need to adjust the height of the row. Point to Row on the
Home tab in Cells Section click Format, and then click AutoFit Row

Enter dates or times

1. On the worksheet, click a cell.
2. Type a date or time as follows:

For a date, use a slash mark or a hyphen to separate the parts of a date;
for example, type 9/5/2002 or 5-Sep-2002.

TCTC

Page 10

ECDL Module 4 Notes

For a time that is based on the 12-hour clock, type a space, and then type
a or p after the time; for example, 9:00 p. Otherwise, Excel enters the time
as AM.

3. You can format the way in which the date or time appears by selecting the
cell/s,

Click on Home tab in Cells Section click Format

Click Format Cells

Click on the Number tab

Select Date or Time

Then, choose the date or time format from the Type: section in the dialog box.

4.2.1.4 Select a cell, range of adjacent cells, range of non-adjacent cells, entire
worksheet.
Select a Cell
Click on one cell in Excel and it becomes the active cell. The active cell always
has a thick border around it (see example below).The address or name of the
active cell is the letter of the column it is in (letter A in this example) followed by
the number of the row it is in (number 1 in this example). The name of the active
cell always shows here (A1).

TCTC

Page 11

ECDL Module 4 Notes

Select a Range of Cells
Click in the cell in the top left corner of the
range you want to select and holding the left
mouse button down, drag to the bottom
right corner of the range. The diagram below
shows the range B2:C5 selected. Cell B2 is the active cell. Notice how to refer to
a range of cells in Excel the name of the cell in the top left corner, then a colon,
then the name of the cell in the bottom right corner. Dont use B2-C6 as this
means B2 minus C6.
Select Non-Adjacent Ranges
To recreate the example on the
right, click and drag over cells
B2:C7 as described above for
selecting a range. Hold down the
Ctrl Key and drag over the range
E6:E12. Hold down the Ctrl Key
and drag over the range
B12:C15. B12 is the active cell.
Similarly, to select Column A and
Column D (but not the 2 columns
in between) you would select
column A by clicking A at the top
of the column, then holding down the Ctrl Key select Column D by clicking D at
the top of the Column. To select Row 2 and Row 5 (but not the 2 rows in
between) you first select row 2, then holding the Ctrl Key select Row 5 by
clicking 5 at the left of the row.

TCTC

Page 12

ECDL Module 4 Notes

Selecting a Range Using the Shift Key
Click in the cell at the top left corner of the range
you want to select (Cell B2 in the example). Hold
down the Shift Key and click in the bottom right
corner of the range (Cell D9 in this example). Cell
B2 is the active cell. You could go the opposite
way by clicking in D9 and then Shift Clicking in
B2. If you did this, cell D9 would be the active cell.

If you click in a cell, then hold down the Shift Key,

you can use the arrow keys on the keyboard to
expand the selection.

Selecting the Whole Sheet Click on the blue square

between column A and row 1 to select the whole sheet.

The sheet should now looks like this.

A1 is the active cell. Click any cell to
deselect the sheet.

TCTC

Page 13

ECDL Module 4 Notes

4.2.2 Edit, Sort
4.2.2.1 Edit cell content, modify existing cell content.
If you want to completely replace what is written in a cell, select the cell and start
typing. Excel automatically replaces the new text with the existing text in the cell.
If you want to edit part of the text you can either double click on the cell and use
normal text editing techniques, or else you can select the cell and edit its
contents from the formula bar.
4.2.2.2 Use the undo, redo command.

Undo: - From the Quick Access Toolbar choose the Undo command

Redo: - From the Quick Access Toolbar select Redo

4.2.2.3 Use the search command for specific content in a worksheet

Place the insertion point where you want to begin the search

Select the Find and select command from the Home tab.

Select Find

Type the Text you wish

to find in the Find What
box.

Then click on Find Next

button to find the next
occurrence of the text you are looking for

TCTC

Page 14

ECDL Module 4 Notes

4.2.2.4 Use the replace command for specific content in a worksheet.

Place the insertion point where you want to begin the search

Select the Find and select command from the Home tab.

Click on Replace

Type the Text you wish to

find and in the Find what
box

and

type

the

replacement in the Replace

with box.

Depending on the function you wish to perform you can select from the
following:Find Next: - Finds the next occurrence of the selected word or phrase
Replace: - Replaces this instance of the Word, phrase or format
Replace All: - Will replace all occurrences of the selected word, phrase,
and format

4.2.2.5 Sort a cell range by one criterion in ascending, descending numeric

order, ascending, descending alphabetic order.

Sort rows in ascending order (A to Z, or 0 to 9) or descending order (Z to A,

or 9 to 0)
1. Click a cell in the column you would like to sort by.
2. Click Sort Ascending or Sort Descending from the Data tab.

TCTC

Page 15

ECDL Module 4 Notes

Sort rows by two or three criteria (columns) For best results, the range you
sort should have column labels, or headers.

Click a cell in the range you want to sort.

On the Data tab, click Sort.

In the Sort by, click the columns you want to sort, starting with the most
important.

If you have more than one column, click on Add Level and do the same.

Select any other sort options you want from the order box (you can
choose to sort your data in ascending or descending order), and then
click OK.

4.2.3 Copy, Move, Delete

4.2.3.1 Copy the content of a cell, cell range within a worksheet, between
worksheets, between open spreadsheets.
Using the Cut, Copy, and Paste commands in Microsoft Excel, you can move or
copy entire cells or their contents. Note: Excel displays an animated moving

TCTC

Page 16

ECDL Module 4 Notes

border around cells that have been cut or copied. To cancel a moving border,
press ESC.
Move or copy entire cells
When you move or copy a cell, Excel moves or copies the entire cell, including
formulas and their resulting values, comments, and cell formats.
1. Select the cells that you want to move or copy.
2. Do one of the following:

To move cells, click Cut on the Home tab in the clipboard section, or
press CTRL+

To copy cells, click Copy on the Home tab in the clipboard section, press
CTRL+C

3. Select the upper-left cell of the paste area.

4. Click Paste on the Home tab in the clipboard section, press CTRL+V
Move or copy entire cells by using the mouse
By default, drag-and-drop editing is turned on so that you can use the mouse to
move and copy cells
.
1. Select the cells or range of cells that you want to move or copy.
2. Do one of the following:

TCTC

Page 17

ECDL Module 4 Notes

To move a cell or range of cells, point to the border of the selection. When
the pointer becomes a move pointer, drag the cell or range of cells to
another location.

To copy a cell or range of cells, hold down CTRL while you point to the
border of the selection. When the pointer becomes a copy pointer, drag
the cell or range of cells to another location.

4.2.3.3 Move the content of a cell, cell range within a worksheet, between
worksheets, between open spreadsheets.
Insert moved or copied cells between existing cells
1. Select the cell or range of cells that contains the data that you want to move or
copy.
2. Do one of the following:

To move cells, click Cut on the Home tab in the clipboard section, or
press CTRL+

To copy cells, click Copy on the Home tab in the clipboard section, press
CTRL+C

3. Select the upper-left cell of the paste area. Click on Paste from the Home tab
in the clipboard section
Tip
To move or copy a selection to a different worksheet or workbook, click another
worksheet tab or switch to another workbook, and then select the upper-left cell
of the paste area

TCTC

Page 18

ECDL Module 4 Notes

4.2.3.2 Use the auto fill tool/copy handle tool to copy, increment data entries.
Automatically repeat items already entered in the column
If the first few characters that you type in a cell match an existing entry in that
column, Microsoft Excel automatically enters the remaining characters for you.
Excel automatically completes only those entries that contain text or a
combination of text and numbers. Entries that contain only numbers, dates, or
times are not completed.
Do one of the following:

To accept the proposed entry, press ENTER.

The completed entry exactly matches the pattern of uppercase and

lowercase letters of the existing entry.

To replace the automatically entered characters, continue typing.

To delete the automatically entered characters, press BACKSPACE.

Notes

If you don't want entries that you type to be completed automatically, you
can turn this option off.

1. Click the Microsoft Office Button

, and then click Excel Options.

2. Click Advanced, and then under Editing options, select or clear the
Enable AutoComplete for cell values check box to turn this option on
or off.
Use the fill handle to fill data
TCTC

Page 19

ECDL Module 4 Notes

You can have Excel automatically continue a series of numbers, number

and text combinations, dates, or time periods, based on a pattern that you
establish. To quickly fill in several types of data series, you can select cells
and drag the fill handle.

Note: After you drag the fill handle, the Auto Fill Options

button appears so

that you can choose how the selection is filled.

For example, you can choose to fill just cell formats by clicking Fill Formatting
Only, or you can choose to fill just the contents of a cell by clicking Fill Without
Formatting.

1. Click the Microsoft Office Button

, and then click Excel Options.

2. Click Advanced, and then under Editing options, select or clear the
Enable Fill Handle and cell drag and drop check box to turn this
option on or off.
If you don't want to display the Auto Fill Options button every time you drag the
fill handle, you can turn it off.
Fill data into adjacent cells
1. Select the cells that contain the data that you want to fill into adjacent cells.

2. Drag the fill handle

TCTC

across the cells that you want to fill.

Page 20

ECDL Module 4 Notes

3. You can use the Auto Fill Options button

, which appears after you drag

the fill handle, to choose how to fill the selection.

Fill formulas into adjacent cells
1. Select the cell that contains the formula that you want to fill into adjacent cells.

2. Drag the fill handle

across the cells that you want to fill.

Fill in a series of numbers, dates, or other built-in series items

Using the fill handle, you can quickly fill cells in a range with a series of numbers
or dates or with a built-in series for days, weekdays, months, or years.
1. Select the first cell in the range that you want to fill.
2. Type the starting value for the series.
3. Sometimes you may need to type a value in the next cell to establish pattern.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two
cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2,
2, 2..., you can leave the second cell blank.
4. Select the cell or cells that contain the starting values.
5. Drag the fill

handle across the range that you want to fill.

To fill in increasing order, drag down or to the right. To fill in decreasing order,
drag up or to the left.
More examples of series that you can fill

TCTC

Page 21

ECDL Module 4 Notes

4.2.3.4 Delete cell contents.

TCTC

Page 22

ECDL Module 4 Notes

To completely delete the contents of a cell just click on the cell and press the
delete key or backspace key from the keyboard.

4.3 Managing Worksheets

4.3.1 Rows and Columns
4.3.1.1 Select a row, range of adjacent rows, range of non-adjacent rows.
Select a Row
Click on the number at the left of the row. The
diagram shows Row 2 selected. Cell A2 is the
active cell. To select all the rows between 2
and 6 you would click on the number 2 and
holding the left mouse button down, drag over the other numbers until you
reach 6
Select Rows in adjacent format
Click on the number at the left of the row. The
diagram shows Row 2 selected. Cell A2 is the
active cell. To select all the rows between 2
and 6 you would click on the number 2 and
holding the left mouse button down, drag over the other numbers until you
reach 6
Select Rows in non adjacent format
Click on the number at the left of the row. The
diagram shows Row 2 selected. Cell A2 is the
active cell. To select other rows that are not
adjacent to Row 2, press the ctrl button and
TCTC

Page 23

ECDL Module 4 Notes

while continuing pressing the ctrl button select the other Rows

4.3.1.2 Select a column, range of adjacent columns, range of non-adjacent

columns.
Select a Column
Click on the letter at the top of the column. The
diagram shows Column C selected. Cell C1 is
the active cell. To select all the columns
between C and F you would click on the letter C
and holding the left mouse button down,
drag over the other letters until you reach F.
Select Columns in adjacent format
Click on the letter at the top of the column.
The diagram shows Columns C selected. Cell
C1 is the active cell. To select all the columns
between C and A you would click on C and
holding the left mouse button down, drag
over the other columns until you reach A
Select Columns in non adjacent format
Click on the letter at the top of the column.
The diagram shows Columns C selected. Cell
C1 is the active cell. To select other columns
that are not adjacent to Column C, press the
ctrl button and while continuing pressing the
TCTC

Page 24

ECDL Module 4 Notes

ctrl button select the other Columns

4.3.1.3 Insert, delete rows and columns.

Insert rows on a worksheet
1. To insert rows, do one of the following:

To insert a single row, select the row or a cell in the row above which you
want to insert the new row. For example, to insert a new row above row 5,
click a cell in row 5.

To insert multiple rows, select the rows above

which you want to insert rows. Select the
same number of rows as you want to insert.
For example, to insert three new rows, you
need to select three rows.

To insert nonadjacent rows, hold down CTRL

while you select nonadjacent rows

2. On the Home Tab click on the Insert button and

click on insert Rows
Tip You can also right-click the selected rows and then click Insert on the
shortcut menu.
Insert columns on a worksheet
1. To select columns, do one of the following:

To insert a single column, select the column or a cell in the column

immediately to the right of where you want to
insert the new column. For example, to insert a
new column to the left of column B, click a cell in
column B.

To insert multiple columns, select the columns

immediately to the right of where you want to
insert columns. Select the same number of
columns as you want to insert. For example, to
insert three new columns, you need to select
three columns.

TCTC

Page 25

ECDL Module 4 Notes

To insert nonadjacent columns, hold down CTRL while you select

nonadjacent columns.

2. On the Home Tab click on the Insert button and click on insert Columns
Tip You can also right-click the selected cells and then click Insert on the
shortcut menu.
Delete Columns or Rows
1. Select the cells, rows, or columns that you want to
delete.
2. On the Home Tab click on the Delete button and
click on Delete Columns or Rows
Tip You can also right-click on rows, or columns, and
then click Delete on the shortcut menu.
If you are deleting rows or columns, other rows or columns automatically shift up
or to the left.
4.3.1.4 Modify column widths, row heights to a specified value, to optimal width
or height.
Set a column to a specific width
1. Select the column or columns that you want to
change.
2. On the Home Tab click on the Format button and
click on Column Width.
Change the column width to fit the contents
1. Select the column or columns that you want to
change.
2. On the Home Tab click on the Format button and click on Auto Fit Column
Width
Tip To auto fit all columns on the worksheet, click the Select All button and then
double-click any boundary between two column headings.
TCTC

Page 26

ECDL Module 4 Notes

Match the column width to another column

1. Select a cell in the column.

2. Click on Copy from the home tab, and then select
the target column.
3. On the Home tab, click on the arrow under Paste
and click on Paste Special, and then select Column
widths.
Change the default width for all columns on a
worksheet or workbook
The value for the default column width indicates the average number of
characters of the standard font that fit in a cell. You can specify a different
number for the default column width for a worksheet or workbook.
Do one of the following:

To change the default column width for a worksheet, click its sheet
tab.

To change the default column width for the entire workbook, rightclick a sheet tab, and then click
Select All Sheets on the shortcut menu.

On the Home tab, in the Cells group, click Format.

Under Cell Size, click Default Width.

In the Default column width box, type a new

measurement.

Change the width of columns by using the mouse

Do one of the following:

To change the width of one column, drag the boundary on the right side of
the column heading until the column is the width that you want.

TCTC

Page 27

ECDL Module 4 Notes

To change the width of multiple columns, select the columns that you want
to change, and then drag a boundary to the right of a selected column
heading.

To change the width of columns to fit the contents, select the column or
columns that you want to change, and then double-click the boundary to
the right of a selected column heading.

To change the width of all columns on the worksheet, click the Select All
button, and then drag the boundary of any column heading.

Set a row to a specific height

1. Select the row or rows that you want to change.
2. On the Home Tab click on the Format button
and click on Row height
Change the row height to fit the contents
1. Select the row or rows that you want to change.
2. On the Home Tab click on the Format button
and click on Auto Fit Row Height
Change the height of rows by using the mouse
Do one of the following:

To change the row height of one row, drag the boundary below the row
heading until the row is the height that you want.

To change the row height of multiple rows, select the rows that you want
to change, and then drag the boundary below one of the selected row
headings.

To change the row height for all rows on the worksheet, click the Select
All button, and then drag the boundary below any row heading.

To change the row height to fit the contents, double-click the boundary
below the row heading

4.3.1.5 Freeze, unfreeze row and/or column titles.

TCTC

Page 28

ECDL Module 4 Notes

On the View tab, in the Window group, click the arrow below Freeze
Panes.

Do one of the following:

To lock one row only, click Freeze Top Row.

To lock one column only, click Freeze First Column.

To lock more than one row or column, or to lock both rows and
columns at the same time, click Freeze Panes.

When you freeze the top row, first column, or panes, the Freeze Panes option
changes to Unfreeze Panes so that you can unlock any frozen rows or
columns.

4.3.2 Worksheets
4.3.2.1 Switch between worksheets.
To switch from one worksheet to another simply click on the relevant worksheet
tab towards the bottom of your screen.
4.3.2.2 Insert a new worksheet, delete a worksheet.
To insert a worksheet tab, right click on an existing
worksheet tab and click Insert, as illustrated.

A dialog box will be displayed. Click on the OK button.

TCTC

Page 29

ECDL Module 4 Notes

By default the sheet will be named Sheet 4 and it will be placed in front of the
sheet on which you clicked before choosing the Insert command. You can
change the name as well as the position of the worksheet.
To delete a worksheet
Right click on the worksheet tab that you wish to delete. Select Delete from the
popup menu displayed.
4.3.2.3 Recognize good practice in naming worksheets: use meaningful
worksheet names rather than accept default names.
Name worksheets in a meaningful way to you so that when you are looking for a
particular sheet you can find it quickly and easily. This saves time since it is
quicker to locate

TCTC

Page 30

ECDL Module 4 Notes

4.3.2.4 Copy, move, rename a worksheet within a spreadsheet
Renaming a worksheet
To rename a worksheet tab right click on the worksheet tab
that you wish to rename. From the popup menu displayed
select the Rename command. You can then type over the
default worksheet name, which will become highlighted.

To move a worksheet
You can change the position of your worksheets by using drag and drop
techniques with your mouse.
Copy a worksheet
To copy a worksheet tab right click on the worksheet tab
that you wish to copy. From the popup menu displayed
select the Move or copy command. Then select the sheet
where you want the worksheet to be. Tick the Create a copy
box. Then click OK

4.4 Formulas and Functions

4.4.1 Arithmetic Formulas
4.4.1.1 Recognize good practice in formula creation: refer to cell references
rather than type numbers into formulas.
4.4.1.2 Create formulas using cell references and arithmetic operators (addition,
subtraction, multiplication, division).
Writing the Formula

TCTC

Writing Excel formulas is a little different than the way it is done in

math class.
Page 31

ECDL Module 4 Notes

Excel formulas starts with the equal sign ( = ) rather than ending with it.

The equal sign always goes in the cell where you want the formula
answer to appear.

The equal sign informs Excel that what follows is part of a formula, and
not just a name or a
number.

Excel formulas look like this: =3 + 2

rather than:
3+2=

Mathematical Operators
Creating formulas in Microsoft Excel is not difficult. Just combine the cell
references of your data with the correct mathematical operator.

Subtraction - minus sign ( - )

Addition - plus sign ( + )

Division - forward slash ( / )

Multiplication - asterisk (* )

Cell References in Formulas

While the formula in the previous step works, it has one drawback. If
you want to change the data being calculated you need to edit or
rewrite the formula.
A better way would be to write formulas so that you can change the
data without having to change the formulas themselves.
To do this, you need to tell Excel which cell the data is located in. A
cell's location in the spreadsheet is referred to as its cell reference.
To find a cell reference, simply look at the column headings to find
which column the cell is in, and across to find which row it is in.

The cell reference is a

combination of the column letter
and row number -- such as A1,
B3, or Z345. When writing cell
references the column letter
always comes first.
So, instead of writing this formula in cell C1: = 3 + 2 write this instead: = A1+A2
TCTC

Page 32

ECDL Module 4 Notes

Note: When you click on a cell containing a formula in Microsoft Excel, the
formula always appears in the formula bar located above the column letters
(circled in red in the example).
Updating Excel Formulas
When you use cell references in Excel formulas, the formulas will automatically
update whenever the relevant data in the spreadsheet changes.
For example, if you realize that the data in cell A1 should have been an 8 instead
of a 3, you only need to change the contents of cell A1.
Excel updates the answer in cell in cell C1. The formula, itself, doesn't need to
change because it was written using cell references.
Order of Operations
If more than one operator is used in a formula, there is a specific order that Excel
will follow to perform these mathematical operations. This order of operations can
be changed by adding brackets to the equation.
How the Order of Operations Works

Any operation(s) contained in brackets will be carried out first

After that, Excel considers division or multiplication operations to be of
equal importance, and carries out these operations in the order they occur
left to right in the equation.
The same goes for the next two operations addition and subtraction.
They are considered equal in the order of operations. Whichever one
appears first in an equation, either addition or subtraction, is the operation
carried out first.

Let's try a step by step example. We will write a simple formula in Excel to add
the numbers 3 + 2.
Step 1: Entering the data
It's best if you first enter all of your data into the spreadsheet before you begin
creating formulas. This way you will know if there are any layout problems, and it
is less likely that you will need to correct your formula later. For help with this
tutorial refer to the image above.
1. Type 3 in cell A1 and press the ENTER key on the keyboard.
2. Type 2 in cell A2 and press the ENTER key on the keyboard.
TCTC

Page 33

ECDL Module 4 Notes

Step 2: Add the Equal (=) Sign

When creating formulas in Microsoft Excel, you ALWAYS start by typing the
equal sign. You type it in the cell where you want the answer to appear.
1. Click on cell C1 (outlined in black in the image) with your mouse
pointer.
2. Type the equal sign in cell C1

Step 3: Add Cell References Using Pointing

After typing the equal sign in step 2, you have two choices for adding cell
references to the spreadsheet formula.
1. You can type them in or,
2. You can use an Excel
feature called Pointing
Pointing allows you to click with your
mouse on the cell containing your
data to add its cell reference to the
formula.

TCTC

Page 34

ECDL Module 4 Notes

Continuing from step 2 for this example
1. Click on cell A1 with the mouse pointer
2. Type a plus (+) sign
3. Click on cell A2 with the mouse pointer
4. Press the ENTER key on the keyboard
The answer 5 should appear in cell C1.
If you have more than one row or column of data that you need to perform
calculations on, it is often possible to copy the first formula to other cells.
The easiest way to do this is to copy formulas with the fill handle
4.4.1.3 Identify and understand standard error values associated with using
formulas: #NAME?, #DIV/0!, #REF!.
Formula errors can result in error values as well as cause unintended results.
If a formula cannot properly evaluate a result, Microsoft Excel will display an error
value. Each error type has different causes, and different solutions.
##### Occurs when a column is not wide enough, or a negative date or time is
used.
#DIV/0! - Occurs when a number is divided by zero (0).
#NAME? - Occurs when Microsoft Excel doesn't
recognize text in a formula.
#REF! - Occurs when a cell reference is not valid.
Correcting errors in a formula
You can correct your formulas using basic text editing
techniques in the formula bar.

4.4.1.4 Understand and use relative, absolute cell referencing in formulas.

Relative cell referencing

In spreadsheet programs a relative cell address identifies the location of a

cell or group of cells.
Cell addresses are used in formulas, functions, charts, and other
spreadsheet commands.
By default, a spreadsheet cell reference is relative. What this means is
that as a formula or function is copied and pasted to other cells, the cell

TCTC

Page 35

ECDL Module 4 Notes

references in the formula or function change to reflect the function's new

location.
In contrast, an absolute cell address does not change when it a formula is
copied and pasted to other cells.

A relative cell address consists of the column letter and row number that intersect
at the cell's location. An example of a relative cell address would be C4, G15, or
Z2345. Note: When listing a cell address - either relative or absolute, the column
letter is always listed first.
Absolute cell referencing

An absolute cell address consists of the column letter and row number
surrounded by dollar signs ( $ ).
An example of an absolute cell address would be $C$4, $G$15, or
$A$345.

Note: An easy way to add the dollar signs to a cell address is to click on a cell
address and then press the F4 key on the keyboard.

An absolute cell address is used when you want a cell address to stay
fixed on a specific cell.
This means that as a formula or function is copied and pasted to other
cells, the cell addresses in the formula or function do not change.

4.4.2 Functions
4.4.2.1 Use sum, average, minimum, maximum, count, counta, round functions.
The Sum Function
Probably the most popular function in any spreadsheet is the SUM function. The
Sum function takes all of the values in each of the specified cells and totals their
values. The syntax is:
=SUM(first value, second value, etc)
In the first and second spots you can enter any of the following (constant, cell,
range of cells).

Blank cells will return a value of zero to be added to the total.

Text cells cannot be added to a number and will produce an error.

TCTC

Page 36

ECDL Module 4 Notes

Let's use the table here for the discussion that follows: We
will look at several different specific examples that show
how the typical function can be used! Notice that in A4
there is a TEXT entry. This has NO numeric value and
cannot be included in a total.

The Average Function

The average function finds the average of the specified data.
(Simplifies adding all of the indicated cells together and
dividing by the total number of cells.) The syntax is as
follows.
=Average (first value, second value, etc.)
Text fields and blank entries are not included in the
calculations of the Average Function.
Let's use the table here for the discussion that follows: We
will look at several different specific examples that show how
TCTC

Page 37

ECDL Module 4 Notes

the average function can be used!

The Max Function

The next function we will discuss is Max (which stands for
Maximum).
This will return the largest (max) value in the selected range
of cells.

Blank entries are not included in the calculations of

the Max Function.

Text entries are not included in the calculations of

the Max Function.

Let's use the table here for the discussion that follows. We
will look at several different specific examples that show
how the Max functions can be used!

TCTC

Page 38

ECDL Module 4 Notes

The Min Function

The next function we will discuss is Min (which stands for
minimum).
This will return the smallest (Min) value in the selected range of
cells.

Blank entries are not included in the calculations of the

Min Function.

Text entries are not included in the calculations of the

Min Function.

Let's use the table here for the discussion that follows. We will look at several
different specific examples that show how the min functions can be used!

TCTC

Page 39

ECDL Module 4 Notes

The Count Function

The Count function will return the number of entries (actually counts each cell that
contains number data) in the selected range of cells.

Blank entries are not counted.

Text entries are NOT counted.

Let's use the table here for the discussion that follows.
We will look at several different specific examples that
show how the Count functions can be used!

TCTC

Page 40

ECDL Module 4 Notes

The CountA Function

CountA will return the number of entries (actually counts each cell that contains
number data OR text data) in the selected range of cells.

Blank entries are not counted.

Text entries ARE Counted.

Let's use the table here for the discussion that follows. We will
look at several different specific examples that show how the
CountA functions can be used!

TCTC

Page 41

ECDL Module 4 Notes

The Round Function

In Excel, the Round function returns a number rounded to a specified number of
digits. The syntax for the Round function is:
=round( number, digits )
Where:
Number is the number to round digits is the number of digits to round the number
to.
Let's take a look at an example:

TCTC

Page 42

ECDL Module 4 Notes

Based on the Excel
spreadsheet above:
=Round(A1, 0)
=Round(A1, 1)
=Round(A2, -1)
=Round(55.1, -1)
=Round(-23.67, 1)

would return 663

would return 662.8
would return 50
would return 60
would return -23.7

4.4.2.2 Use the logical function if (yielding one of two specific values) with
comparison operator: =, >, <.
How it works:
The Excel IF function checks to see if a certain condition is true or false. If the
condition is true, the function will do one thing, if the condition is false, the
function will do something else.
The IF function we are using in this tutorial asks if the value in column A is
greater than the value in column B. If it is, the IF function will place the statement
"A is larger" in column D. If it is not, the IF function will place the statement "B is
larger" in column D.
Our IF function will be entered into cell D1 and it looks like this:
=IF(A3 > B3,"A is larger","B is larger")
Note: the two text statements "A is larger" and "B is larger" are enclosed in
quotations. In order to add text to an Excel IF Function, it must be enclosed in
quotation marks.

Enter the data for the IF function:

It is always a good idea to enter all your data into a spreadsheet before you
begin writing functions or formulas. This way you will know if there are any layout
problems and it is less likely that you will need to make corrections to your
functions later. As an
example type some data to
appear as in the
diagram below:

TCTC

Page 43

ECDL Module 4 Notes

Starting the IF function

When you write an IF function in Excel, you always start by typing the equal sign
in the cell where you want the answer to appear.

Parts of the Excel IF function

Inside the round brackets there are three parts or arguments to an IF Function.
The Arguments
1. The logical test - in our example it is A3 > B3
2. The value if true - in our example it is "A is larger"
3. The value if false - in our example it is "B is larger"
Between each argument of the IF function, the comma is used as a separator.
The comma is what tells Excel when each argument of IF function ends and the
next section begins.

Adding Separators
TCTC

Page 44

ECDL Module 4 Notes

Therefore, as we complete the IF function, we will add two separators
1. One between the logical test and the value if true arguments
2. One between the value if true and the value if false arguments.

Writing the IF Function Logic Test

The logic test is always a comparison between two amounts. In this tutorial we
are comparing the values in cells A3 and B3. Between the two values we place a
comparison operator. Since we want to know if A3 is greater than B3, we will use
the Greater Than operator " > " between the two cell references.

Comparison Operators
The comparison operators that can be used in the logical test portion of an Excel
IF function are:

Equals (=)

Less than (<)

Less than or equal to (<=)

Greater than (>)

Greater than or equal to (>=)

Not equal to (<>)

TCTC

Page 45

ECDL Module 4 Notes

For our example: type A3 > B3 followed by a comma ","

Adding the "Value if True" argument to the IF Function

Following our comma separator, we add in the value if true argument of the IF
function followed by another comma separator. In this case, if the value in cell A3
is greater than the value in cell B3, we want the function to display the text "A is
larger" in cell D1.

Type "A is larger" followed by a second comma ","

In cell D1 you should see:

=IF(A3 > B3,"A is larger",

TCTC

Page 46

ECDL Module 4 Notes

Adding the "Value if False" argument to the IF Function
The last section to add to the IF function is the value if false" argument of the
function followed by the right or closing round bracket. In this case, if the value in
cell A3 is not greater than the value in cell B3, we want the function to display the
text "B is larger" in cell D1.

Type "B is larger" followed by a right or closing round bracket ")".

In cell D1 you should see the completed IF function:

=IF(A3 > B3,"A is larger","B is larger")

Copying the IF Function to Other Cells

To complete the worksheet, we need to add the IF function to cells D2, D3, and
D4. Since our data is laid out in a regular pattern, we can copy the IF function in
cell D1 to the other three cells. As the function copied, Excel will update the cell
references to reflect the functions new location.
To copy down our function we will use the Fill Handle:
1. Click on cell D1.
2. Place the mouse pointer over the black square in the bottom right corner. The
pointer will change to a plus sign + ".
3. Click the left mouse button and drag the fill handle down to cell D4.
4. Release the mouse button. Cells D2 to D4 will be filled with the IF function.
Notice how the cell references in the IF function have been change in cells D2 to
D4 to reflect the IF functions new location.
TCTC

Page 47

ECDL Module 4 Notes

TIP: If you dont remember the IF function,

Click on Formulas Tab

Click on the button Insert Function
Find the If Function
Click OK

The IF function
window will be
displayed, as
illustrated:

TCTC

Page 48

ECDL Module 4 Notes

Where:
Logical test what are we asking? In this case, lets say our logical test is Is
the value in Column A larger than the value in Column B? (e.g. a1>b1)
Value if true what do we want displayed if the answer to our question is true?
For example, we want the answer to be yes.
Value if false what do we want displayed if the answer is false? For example,
we want the answer to be no.

TCTC

Page 49

ECDL Module 4 Notes

4.5 Formatting
4.5.1 Numbers/Dates
4.5.1.1 Format cells to display numbers to a specific number of decimal places,
to display numbers with, without a separator to indicate thousands.
Format Cells to a specific decimal Place

Click once on the cell that need to be changed to

a lower or bigger decimal place

On the Home tab click on the arrow to the right of

the Number Section

From the Number Tab click on Number

Change

Click OK

the

decimal

place

from

the

right

of

the

window

To display numbers with a separator to indicate thousands in the same number

window tick the box next to use 1000 separator. To remove the separator un-tick
the same box

4.5.1.2 Format cells to display a date style, to display a currency symbol.

Format cells to display a date style

Click once on the cell that need to be changed

On the Home tab click on the arrow to the right of

the Number Section

From the Number Tab click on Date

TCTC

Page 50

ECDL Module 4 Notes

Choose the Location

Then choose the Type

Click OK

Format cells to display a currency symbol.

Click once on the cell that need to be changed

On the Home tab click on the arrow to the right of

the Number Section

From the Number Tab click on Currency

From

the

symbol

section choose the

symbol you need

Click OK

4.5.1.3 Format cells to display numbers as percentages.

Click once on the cell that need to be changed to

On the Home tab click on the arrow to the right of

the Number Section

From

the

Number

Tab

click

on

Percentages

Change the decimal Place

Click OK

TCTC

Page 51

ECDL Module 4 Notes

4.5.2 Contents
4.5.2.1 Change cell content appearance: font sizes, font types
Changing the Font
Arial is the font that is currently being used. Your
computer might show a different font name here.
Click on the drop-down arrow to display the list of
available fonts. Use the
scroll bar to see the full
list. Then, simply click on the font of your choice.

Changing the Font size

Click on the drop down arrow showing the current font size, and select the
font size of your choice.

TCTC

Page 52

ECDL Module 4 Notes

4.5.2.2 Apply formatting to cell contents: bold, italic, underline, double underline.
Make text bold

Select the cell that you want to make bold, and move your pointer to the
Home tab.

Click Bold

Note:- Click Bold

again to unbold the cell that you selected.

Make text Italic

Select the cell that you want to make Italic, and move your pointer to the
Home tab.

Click Italic

Note: - Click Italic

again to remove the Italic the cell that you selected

Make text Underlined

Select the cell that you want to make underlined, and move your pointer
to the Home tab.

Click Underlined

Note: - Click Underlined

again to remove the underlined cell that you

selected
If you need the line to be double click on the arrow
next to underlined and choose double underline

TCTC

Page 53

ECDL Module 4 Notes

4.5.2.3 Apply different colours to cell content, cell background

Apply different colours to cell content

Select the cell that you want to change the colour of the cell content,
and move your pointer to the Home tab.

Click Font colour arrow and choose a colour

Apply different colours to cell backgrounds

Select the cell that you want to change the colour of the cell
background, and move your pointer to the Home tab.

Click Fill colour arrow and choose a colour

4.5.2.4 Copy the formatting from a cell, cell range to another cell, cell range.

1.

Select a cell that has the formatting that you want to copy.

2.

Do one of the following:

To copy the formatting to a single cell or range of cells, click on

the Format Painter

on the Home tab, and then drag the mouse

pointer across the cell or range of cells that you want to format.
To copy the formatting to several cells or ranges of cells,

double-click on Format Painter

from the home tab, and then drag

the mouse pointer across each cell or range of cells that you want to
format. When you're done, either click Format Painter

again or

press ESC to turn it off.

Tip To copy the width of one column to a second column, select the heading of
the first column, click on the Format Painter

from the home tab, and then

click the heading of the column that you want to apply the column width to.
TCTC

Page 54

ECDL Module 4 Notes

4.5.3 Alignment, Border Effects

4.5.3.1 Apply text wrapping to contents within a cell, cell range.

Click once on the cell that need to be changed to

On the Home tab click on the

Select the Wrap text from the Alignment section

Note:
If not all wrapped text is visible, it may be because the row is set to a specific
height. Thus, you may need to adjust the height of the row.

4.5.3.2 Align cell contents: horizontally, vertically. Adjust cell content orientation.

Click once on the cell that need to be

changed to

On the Home tab click on the arrow to

the right of
the
Alignment
Section

From

the

Alignment
Tab

you

can change
the

text

alignment if
Horizontally
or vertically
and

the

orientation

TCTC

Page 55

ECDL Module 4 Notes

to a degrees
4.5.3.3 Merge cells and centre a title in a merged cell.
1. Select the cells that you want to merge.
Note:
The cells that you select must be adjacent.
2. In the Alignment section click on the button Merge and Centre

The cells will be merged in a row or column, and the cell contents will be
centered in the merged cell.
4.5.3.4 Add border effects to a cell, cell range: lines, colours.

1.

On a worksheet, select the cell or range of cells that you want to add a
border to, change the border style on, or remove a border from.

2.

To apply a new or different border style, click the arrow next to Borders
on the Font section, and then choose a border style from the palette.
Tip To apply a custom border style or a diagonal border, click on the arrow
next to border and click More Borders. On the Border tab, click the line style
and color that you want, and then click one or more buttons to indicate the
border placement. Two diagonal border buttons

are available under

Border.

3.

To remove cell borders, click the arrow next to Borders

section, and then click No Border

TCTC

in the font

on the palette.

Page 56

ECDL Module 4 Notes

4.6 Charts
4.6.1 Create
4.6.1.1 Create different types of charts from spreadsheet data: column chart, bar
chart, line chart, pie char
1.

On the worksheet, arrange the data that you want to plot in a chart.
For a column, bar, or line chart, you should arrange the data

in columns or rows.
Data in columns:
Data one

Data two

Data in rows:
Data one

Data two

For a pie chart with only one series of data, you should

arrange the data in one column or row only.

One column of data and one column of data labels:
A

One row of data and one row of data labels:

A

TCTC

Page 57

ECDL Module 4 Notes

2.

Select the cells that contain the data that you want to use for the chart.
TIP If the cells that you want to select for the chart are not in a continuous
range, select the first group of cells that contain the data that you want to
include. Hold down CTRL, and then select any additional cell groups that
you want to include. The nonadjacent selections must form a rectangle.

3 Click on Insert and choose which one you need from the chart section

4. Then choose the type of chart if its 3D or 2D etc

TCTC

Page 58

ECDL Module 4 Notes

4.6.1.3 Change the chart type and 4.6.1.2 Select a chart.

.
1.

Select the chart by clicking on it.

2.

Click on Change Chart Type from the Type Section in the

Design tab

3.

Select the chart type that you wish to use and click OK.

4.6.1.4 Move, resize, delete a chart.

 To move a chart

Click in the chart area and drag the chart to another location.
 To resize the chart

Click once on the chart to select it.

Point to a sizing handle. When the pointer changes to a double-sided arrow,

drag the sizing handle until the chart reaches the size you want.
 To delete the Chart

Select the chart and press delete from the keyboard

TCTC

Page 59

ECDL Module 4 Notes

4.6.2 Edit
4.6.2.1 Add, remove, edit a chart title.

Click the chart to which you want to add a title.

This displays the Chart Tools, adding the Design, Layout, and Format
tabs.

On the Layout tab, in the Labels group,

click Chart Title.

Click Centered Overlay Title or Above

Chart.

In the Chart Title text box that appears in the chart, type the text that
you want.

4.6.2.2 Add data labels to a chart: values/numbers, percentages.

o To add a data label to all data points of all data series, click the
chart area.
o This displays the Chart Tools, adding the Design, Layout, and
Format tabs.

On the Layout tab, in the Labels group,

click Data Labels, and then click the
display option that you want.

NOTE

Depending on the chart type that you used, different data label

options will be available.

TCTC

Page 60

ECDL Module 4 Notes

4.6.2.3 Change chart area background colour, legend fill colour.
Change chart area background colour
To change the chart area background colour,
right click in the chart area and select Format
Chart area.

The Format Chart Area dialog box will appear, as illustrated:

From the fill tab choose solid fill

and from the colours choose one
of the colours given

TCTC

Page 61

ECDL Module 4 Notes

Change legend fill colour

1. Right click on the graph's legend and
choose the format legend option.
2. From the fill tab choose solid fill and from
the colours choose one of the colours
given

4.6.2.4 Change the column, bar, line, pie slice colours in the chart.
Change column colours in a chart
1. Right click on a column and click Format Data
Series.
2. From the fill tab choose solid fill and from the
colours choose one of the colours given
3. The same method applies if you wish to change
the bar, line, or pie slice colours in different chart
types.
TIP:
Note that when you right click on a column / bar / line / pie slice and choose
Format Data series, you will change the colour for
the whole series If you only wish to change the
colour of that particular column / bar / line / pie slice,
first click once on the chart element in question to
select the series, click once again on the single
element from the series (e.g. column / bar / pie
slice), and then right click on it and choose Format
TCTC

Page 62

ECDL Module 4 Notes

Data Point.
4.6.2.5 Change font size and colour of chart title, chart axes, chart legend text.
To apply any formatting changes to a chart title:

Right click on the chart title. and

automatically a copy of the Font and
alignment tab will show up. Change the font
size and the colour of the chart title

To change the font size and colour of the chart axes labels:

Right click on text of x axis or y axis and

automatically a copy of the Font and
alignment tab will show up. Change the
font size and the colour of the chart axes

TCTC

Page 63

ECDL Module 4 Notes

The same, to apply any formatting changes to chart legend text

Right click on Legend and automatically a

copy of the Font and alignment tab will
show up. Change the font size and the
colour of the chart legend

TCTC

Page 64

ECDL Module 4 Notes

4.7 Prepare Outputs
4.7.1 Setup
4.7.1.1 Change worksheet margins: top, bottom, left, right
1. On the Page Layout tab, in the Page Setup group, click
Margins.
2. Click Custom Margins.

Top: - To set the top margin

Bottom: - To set the bottom margin
Left: - To set the left-hand margin
Right: - To set the right-hand margin
4.7.1.2 Change worksheet orientation: portrait, landscape. Change paper size
Change page orientation
1. On the Page Layout tab, in the Page Setup group, click
Orientation
2. Choose between Portrait and Landscape
Change Paper Size
1. On the Page Layout tab, in the Page Setup group, click Size
2. Choose between A4, letter etc

TCTC

Page 65

ECDL Module 4 Notes

4.7.1.3 Adjust page setup to fit worksheet contents on a specified number of
pages.

1. On the Page Layout tab, in the Scale to Fit

group, click on the Right Arrow

2. Tick the box next to fit and write a number in the box next to pages by
wide and another number next to pages tall

4.7.1.4 Add, edit, delete text in headers, footers in a worksheet

From the Insert tab, select the Header and Footer button

Start typing or delete the text with the backspace

In case one needs to go in the footer

Click on Go to Footer in the Design Tab in the Navigation section

4.7.1.5 Insert and delete fields: page numbering information, date, time, file
name, worksheet name into headers, footers.

From the Insert tab, select the Header and Footer button

From the Header and Footer Elements choose what you need

In case one needs to go in the footer

Click on Go to Footer in the Design Tab in the Navigation section

To Return to Normal View

Click on the document, click on view and click Normal

TCTC

Page 66

ECDL Module 4 Notes

4.7.2 Check and Print
4.7.2.1 Check and correct spreadsheet calculations and text.
It is important that you check your spreadsheet for any spelling or grammar
mistakes prior to printing.
This can be done by clicking on the Review tab and selecting
Spelling and Grammar.
The spelling and grammar dialog box will be displayed, as illustrated. For each

You can select one of the following actions:

Ignore Once: Leaves the word as it is.

Ignore All: Will not change any further occurrences of the word.
Add to Dictionary: Adds the word to the dictionary.
Change: Will change the word to the suggested word
Change All: Will change all further occurrences of the word to the
suggested word
TCTC

Page 67

ECDL Module 4 Notes

AutoCorrect: Enables the AutoCorrect feature for any further occurrences
of the word.

Cancel: Exits the Spell check.

Dictionary Language: Allows you to select the language used for spell
checking.

Undo Last: Will undo the previous correction.

Suggestions: A list of suggested corrections is displayed.
Options: Enables you to change the Spell Checking options.

4.7.2.2 Turn on, off display of gridlines, display of row and column headings for
printing purposes.
Gridlines
1. On the Page Layout tab, in the Sheet options group,
Tick the Print under Gridlines so that they will print as
well not seen only on the screen.

Row and Column Headings

1. On the Page Layout tab, in the Sheet options group,

Tick the Print under Headings so that they will print as
well not seen only on the screen.

TCTC

Page 68

ECDL Module 4 Notes

4.7.2.3 Apply automatic title row(s) printing on every page of a printed worksheet.
1. On the Page Layout tab, in the Sheet options group, click on the arrow
at the bottom right hand side
2. Click on the Red box on the right hand side of the Rows to repeat at top

3. Select the Row and click on the Red box again

4. Then click OK

TCTC

Page 69

ECDL Module 4 Notes

4.7.2.4 Preview a worksheet

Print preview lets you see a preview of your worksheet before actually printing
it.

Click on the Office button.

Point to Print.

Click on Print Preview.

Note:
To exit Print Preview and return to Normal View, click on Close Print
Preview.

TCTC

Page 70

ECDL Module 4 Notes

4.7.2.5 Print a selected cell range from a worksheet, an entire worksheet,
number of copies of a worksheet, the entire spreadsheet, a selected chart

Click on the Office Button.

Point to Print, and then click on Print from the sub-menu

The

In the Page range section choose whether you wish to print all the pages

Print

dialog

box

will

be

displayed

as

illustrated

below.

in the workbook or you can choose from which page to which page you
want to print.

In the Copies section specify how many copies of the same document
you wish to print.

TCTC

Page 71

ECDL Module 4 Notes

You can choose to print a selection which you selected already, the entire
workbook or the active sheet that you were using before you clicked on
Print

Finally, click OK.

.
.

TCTC

Page 72