COUNCIL FOR

TECHNICAL AND VOCATIONAL EDUCATION

AND TRAINING
MODULAR PROGRAMME IN

DATA OPERATIONS

LEVEL 1

OPERATE A SPREADSHEET
APPLICATION (BASIC)

1
 COURSE

OCCUPATIONAL TITLE: DATA OPERATIONS

COMPETENCE: OPERATE A SPREADSHEET

APPLICATION (BASIC)

LEVEL 1

MODULE NUMBER: ITIDAT0171A

MODULE TITLE: OPERATE A SPREADSHEET

APPLICATION (BASIC)

TRAINING GUIDE

DEVELOPED BY: NCERD

DEVELOPED AT: CTVET

DATED: NOVEMBER 2012

2
 INTRODUCTION
In this training guide you will learn about the physical layout and types of data used in a
spreadsheet; how to: - create and save a file;
- enter data;
- customize basic settings to a document by selecting and applying appropriate
modifications to font, alignment and styles to both data and cell ranges, according to
spreadsheet requirements;
- enhance a spreadsheet and correct procedures of saving such files to disk/storage; and
- prepare a worksheet for printing, choose the print options and print a document.

The average time to complete this Training Guide is 20 hours, that is 4 – 5 hour sessions in
the laboratory.

TERMINAL PERFORMANCE OBJECTIVE

On completion of this module you will be able to create, customize, format and print
spreadsheets.

ELEMENTS OF COMPETENCY

1. Create spreadsheet
2. Customise basic settings
3. Format spreadsheet
4. Print spreadsheets

ELEMENT

1. Create Spreadsheet

ENABLING OBJECTIVES

1.1 Open a spreadsheet a spreadsheet document and enter data into cells according to
information requirements
1.2 Add, select, copy, delete or move columns and rows as desired
1.3 Apply simple mathematical functions and formulas for calculation

There are 3 sets of procedures to follow in order to master this TPO. After completing
each set of procedures go to the Self-Check for that particular set and answer the questions
or carryout the activity. Model answers to the questions can be found at the back. For
Self-Check activities a reference is given to a particular checklist to apply to your work.

3
Checklist for the Final Assessment of the Unit : OPERATE A SPREADSHEET APPLICATION (BASIC).

PERFORMANCE STANDARDS
Item Yes No
FOR ACCEPTABLE ACHIEVEMENT, ALL ITEMS SHOULD RECEIVED “YES” RESPONSES.
No.
The was able to:
1. Create spreadsheets
2. Format and modify worksheets
3. Apply simple mathematical functions
4. Apply printing procedures

4
 COURSE
OCCUPATIONAL TITLE: DATA OPERATIONS

COMPETENCE: OPERATE A SPREADSHEET

APPLICATION(BASIC)

LEVEL 1
MODULE NUMBER: ITIDAT0171A

MODULE TITLE: CREATING A SPREADSHEET

TRAINING GUIDE

DEVELOPED BY: A CONWAY

DEVELOPED AT: CTVET

DATED: 2011

5
 INTRODUCTION
In this training guide you will learn about the physical layout and types of data used in a
spreadsheet; how to create a file, save a file, and enter data.

The average time to complete this Training guide is 20 hours. That is, 10 – 2 hours sessions
in the computer laboratory

TERMINAL PERFORMANCE OBJECTIVES

Upon completion of this module you will be able to create, modify layout, enter data and save a
spreadsheet file

ENABLING OBJECTIVES
1. Open a spreadsheet a spreadsheet document and enter data into cells according to
information requirements
2. Add, select, copy, delete or move columns and rows as desired

3. Apply simple mathematical functions and formulas for calculation

There are 3 sets of procedures to follow in order to master this TPO. After completing each
set of procedures go to the ‘Self-Check’ for that particular set and answer the questions or
carry out the activity. Model answers to the questions can be found in the answer to ‘Self-
Check’. For ‘Self-Check’ activities, a reference is given to a particular checklist to apply to
work.

6
EO1. Open a spreadsheet a spreadsheet document and enter data into cells according to information
requirements
PROCEDURES RESOURCES AVERAGE
1. Study information in this module on opening  Personal TIME
and entering data in a spreadsheet. This Computer(monitor, mouse,
includes: keyboard, system unit)
 Multimedia kit
a) What is a spreadsheet program?  Spreadsheet program
b) Opening/accessing a spreadsheet
program
c) Physical layout of a spreadsheet
d) The Microsoft Excel Window
e) Types of data used in a spreadsheet
f) Moving around a spreadsheet
g) Selecting cells
h) Cut, copy and paste cell contents
i) Filling/copying cells
2. Follow the steps shown on pages 1-10
3. Ask questions for clarification.
4. Answer self-check No. 1

EO2. Add, select, copy, delete or move columns and rows as desired

PROCEDURES RESOURCES AVERAGE

1. Study information in this module on modifying  Personal TIME
columns and rows. This includes: Computer(monitor,
a) Adding/deleting rows/columns mouse, keyboard, system H
b) Selecting rows/columns unit)
 Multimedia kit
c) Copying/moving cells or ranges
 Spreadsheet program
2. Follow the steps explained on pages 11-12
3. Observe demonstration of modifying column
and row layout
4. Ask questions for clarification
5. Answer self-check No. 2

EO3. Apply simple mathematical functions and formulas for calculation

7
PROCEDURES RESOURCES AVERAGE
1) Study information in the module on simple  Personal TIME
mathematical functions, and their purposes on Computer(monitor,
pages 13-14 mouse, keyboard, system
2) Use formulas(i) and functions for calculation unit)
 Multimedia kit
or automatic recalculation(sum, average, min,
 Spreadsheet Program
max)
 Software templates
3) Ask questions for clarification
4) Input and apply functions/formulas into a
spreadsheet document to compute required
results
5) Answer self-check No. 3

8
 MODULE CONENT
A SPREADSHEET is a general –purpose application program used for the tabulation and
calculation of numeric data.
Results and values can be illustrated using various types of charts and graphs.
Popular Spreadsheet programs include MS Windows Excel, Quatro Pro and Open Office Calc.

OPENING A SPREADSHEET
Do any one of the following to open a MS Office Excel window:
1. Click on the Start Menu > select the program icon
OR
2. Click Start > Programs > MS Office > MS Excel from the menu

PHYSICAL LAYOUT OF A SPREADSHEET

A spreadsheet comprises the following:
a. ROWS there are over 1 million (1,048,576) on a MS Excel 2007 worksheet; these
are numbered – 1,2,3,etc
b. COLUMNS there are 16384 on a MS Excel worksheet, these are lettered – A,B,…
AC,CR, HL, etc
c. CELLS these are formed from the intersection of a row and a column. Each cell
has a unique address or reference, such as B1, H35, etc
d. WORKSHEET this is made up of all the above. A collection of worksheets for a
workbook or file

FEATURES OF THE MS EXCEL WINDOW

The Microsoft Office Button

9
In the upper-left corner of the Excel 2007 window is the Microsoft Office button. When you click
the button, a menu appears. You can use the menu to create a new file, open an existing file, save a
file, and perform many other tasks.

The Quick Access Toolbar

Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar gives
you with access to commands you frequently use. By default, Save, Undo, and Redo appear on the
Quick Access toolbar. You can use Save to save your file, Undo to roll back an action you have
taken, and Redo to reapply an action you have rolled back.

The Title Bar

Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the
name of the workbook you are currently using. At the top of the Excel window, you should see
"Microsoft Excel - Book1" or a similar name.

The Ribbon

You use commands to tell Microsoft Excel what to do. In Microsoft Excel 2007, you use the Ribbon
to issue commands. The Ribbon is located near the top of the Excel window, below the Quick Access
toolbar. At the top of the Ribbon are several tabs; clicking a tab displays several related command
groups. Within each group are related command buttons. You click buttons to issue commands or to
access menus and dialog boxes. You may also find a dialog box launcher in the bottom-right corner
of a group. When you click the dialog box launcher, a dialog box makes additional commands
available.

The Formula Bar

10
 Formula Bar

If the Formula bar is turned on, the cell address of the cell you are in displays in the Name box
which is located on the left side of the Formula bar. Cell entries display on the right side of the
Formula bar. If you do not see the Formula bar in your window, perform the following steps:

1. Choose the View tab.

2. Click Formula Bar in the Show/Hide group. The Formula bar appears.

Note: The current cell address displays on the left side of the Formula bar.

The Status Bar

The Status bar appears at the very bottom of the Excel window and provides such information as
the sum, average, minimum, and maximum value of selected numbers. You can change what displays
on the Status bar by right-clicking on the Status bar and selecting the options you want from the
Customize Status Bar menu. You click a menu item to select it. You click it again to deselect it. A
check mark next to an item means the item is selected.

11
MOVING AROUND A WORKSHEET

By using the arrow keys, you can move around your worksheet. You can use the down arrow key to
move downward one cell at a time. You can use the up arrow key to move upward one cell at a time.
You can use the Tab key to move across the page to the right, one cell at a time. You can hold down
the Shift key and then press the Tab key to move to the left, one cell at a time. You can use the
right and left arrow keys to move right or left one cell at a time. The Page Up and Page Down keys
move up and down one page at a time. If you hold down the Ctrl key and then press the Home key,
you move to the beginning of the worksheet.

The Down Arrow Key

 Press the down arrow key several times. Note that the cursor moves downward one cell at a
time.

The Up Arrow Key

 Press the up arrow key several times. Note that the cursor moves upward one cell at a time.

The Tab Key

1. Move to cell A1.

2. Press the Tab key several times. Note that the cursor moves to the right one cell at a time.

The Shift+Tab Keys

 Hold down the Shift key and then press Tab. Note that the cursor moves to the left one
cell at a time.

The Right and Left Arrow Keys

1. Press the right arrow key several times. Note that the cursor moves to the right.
2. Press the left arrow key several times. Note that the cursor moves to the left.

Page Up and Page Down

1. Press the Page Down key. Note that the cursor moves down one page.
2. Press the Page Up key. Note that the cursor moves up one page.

The Ctrl-Home Key

1. Move the cursor to column J.

2. Stay in column J and move the cursor to row 20.
3. Hold down the Ctrl key while you press the Home key. Excel moves to cell A1.

12
GO TO CELLS QUICKLY

The following are shortcuts for moving quickly from one cell in a worksheet to a cell in a different
part of the worksheet.

Go to -- F5

The F5 function key is the "Go To" key. If you press the F5 key, you are prompted for the cell to
which you wish to go. Enter the cell address, and the cursor jumps to that cell.

1. Press F5. The Go To dialog box opens.

2. Type J3 in the Reference field.
3. Press Enter. Excel moves to cell J3.

Go to -- Ctrl+G

You can also use Ctrl+G to go to a specific cell.

1. Hold down the Ctrl key while you press "g" (Ctrl+g). The Go To dialog box opens.
2. Type C4 in the Reference field.
3. Press Enter. Excel moves to cell C4.

The Name Box

You can also use the Name box to go to a specific cell. Just type the cell you want to go to in the
Name box and then press Enter.

1. Type B10 in the Name box.

2. Press Enter. Excel moves to cell B10.

13
14
 TYPES OF DATA IN A SPREADSHEET
1. LABELS these are headings for rows and columns
2. VALUES these are figures to be calculated
3. SERIES these are either numbers or labels entered in sequence into adjacent
cells (eg. Mon, Tues, …; 102,103, etc)
4. FUNCTIONS these are predefined or built-in methods of calculating for results
5. FORMULAS these are user-defined methods of calculating for results

Month Series

Labels

Values

Functions

Formulas

SELECTING CELLS

If you wish to perform a function on a group of cells, you must first select those cells by
highlighting them. The exercises that follow teach you how to select.

To select cells A1 to E1:

1. Go to cell A1.
2. Press the F8 key. This anchors the cursor.
3. Note that "Extend Selection" appears on the Status bar in the lower-left corner of the
window. You are in the Extend mode.
4. Click in cell E7. Excel highlights cells A1 to E7.
5. Press Esc and click anywhere on the worksheet to clear the highlighting.

Alternative Method: Select Cells by Dragging

You can also select an area by holding down the left mouse button and dragging the mouse over the
area. In addition, you can select noncontiguous areas of the worksheet by doing the following:

15
 1. Go to cell A1.
2. Hold down the Ctrl key. You won't release it until step 9. Holding down the Ctrl key enables
you to select noncontiguous areas of the worksheet.
3. Press the left mouse button.
4. While holding down the left mouse button, use the mouse to move from cell A1 to C5.
5. Continue to hold down the Ctrl key, but release the left mouse button.
6. Using the mouse, place the cursor in cell D7.
7. Press the left mouse button.
8. While holding down the left mouse button, move to cell F10. Release the left mouse button.
9. Release the Ctrl key. Cells A1 to C5 and cells D7 to F10 are selected.

Press Esc and click anywhere on the worksheet to remove the highlighting
COPY, CUT AND PASTE

In Spreadsheet, you can copy data from one area of a worksheet and place the data you copied
anywhere in the same or another worksheet. In other words, after you type information into a
worksheet, if you want to place the same information somewhere else, you do not have to retype
the information. You simple copy it and then paste it in the new location.

These actions can be performed using:

1. The Clipboard Group on the Ribbon

16
2. The pop-up mini menu, when you right-click

FILL CELLS AUTOMATICALLY

You can use Microsoft Excel to fill cells automatically with a series. For example, you can have Excel
automatically fill your worksheet with days of the week, months of the year, years, or other types
of series.

Fill Cells Automatically

The following demonstrates filling the days of the week:

17
1. Click the Sheet2 tab. Excel moves to Sheet2.
2. Move to cell A1.
3. Type Sun.
4. Move to cell B1.
5. Type Sunday.
6. Select cells A1 to B1.
7. Choose the Home tab.
8. Click the Bold button . Excel bolds cells A1 to B1.
9. Find the small black square in the lower-right corner of the selected area. The small black
square is called the fill handle.
10. Grab the fill handle and drag with your mouse to fill cells A1 to B14. Note how the days of
the week fill the cells in a series. Also, note that the Auto Fill Options button appears.

18
Copy Cells

1. Click the Auto Fill Options button. The Auto Fill Options menu appears.
2. Choose the Copy Cells radio button. The entry in cells A1 and B1 are copied to all the
highlighted cells.
3. Click the Auto Fill Options button again.
4. Choose the Fill Series radio button. The cells fill as a series from Sunday to Saturday again.
5. Click the Auto Fill Options button again.
6. Choose the Fill without Formatting radio button. The cells fill as a series from Sunday to
Saturday, but the entries are not bolded.
7. Click the Auto Fill Options button again.
8. Choose the Fill Weekdays radio button. The cells fill as a series from Monday to Friday

19
INSERT ROWS ON A WORKSHEET
1. Do one of the following:

 To insert a single row, select either the whole 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 select three rows.
 To insert nonadjacent rows, hold down CTRL while you select nonadjacent rows.
OR

2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click
Insert Sheet Rows.

OR

3. You can also right-click the selected rows and then click Insert.

INSERT COLUMNS ON A WORKSHEET

1. 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 select three columns.
 To insert nonadjacent columns, hold down CTRL while you select nonadjacent columns.

DELETE CELLS, ROWS, OR COLUMNS

1. Select the cells, rows, or columns that you want to delete.

2. On the Home tab, in the Cells group, do one of the following:

20
  To delete selected cells, click the arrow next to Delete, and then click Delete Cells.
 To delete selected rows, click the arrow next to Delete, and then click Delete Sheet
Rows.
 To delete selected columns, click the arrow next to Delete, and then click Delete
Sheet Columns.

OR

3. You can right-click a selection of cells, click Delete, and then click the option that you
want. You can also right-click a selection of rows or columns and then click Delete.

Using the Keyboard

4. If you are deleting a cell or a range of cells, in the Delete dialog box, click Shift cells left,
Shift cells up, Entire row, or Entire column.

If you are deleting rows or columns, other rows or columns automatically shift up or to the
left.

21
ENTERING SPREADSHEET FUCTIONS AND FORMULAS
A major strength of a spreadsheet is that you can perform mathematical calculations and format
your data. In this lesson, you learn how to perform basic mathematical calculations.

Perform Mathematical Calculations

In Spreadsheet, you can enter numbers and mathematical formulas into cells. Whether you enter a
number or a formula, you can reference the cell when you perform mathematical calculations such
as addition, subtraction, multiplication, or division. When entering a mathematical formula, precede
the formula with an equal sign. Use the following to indicate the type of calculation you wish to
perform:

a) + Addition
b) - Subtraction
c) * Multiplication
d) / Division

BASIC FUNCTIONS
i. =SUM(range) - to find the total
ii. =AVERAGE(RANGE) -to find the mean/average of a range of values
iii. =MIN(range) -to find the least/smallest value
iv. =MAX(range) -to find the highest/largest value in a range

UNDERSTANDING FUNCTIONS

Functions are prewritten formulas. Functions differ from regular formulas in that you supply the
value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add.

When using a function, remember the following:

 Use an equal sign to begin a formula.

 Specify the function name.
 Enclose arguments within parentheses. Arguments are values on which you want to perform
the calculation. For example, arguments specify the numbers or cells you want to add.
 Use a comma to separate arguments.

Here is an example of function:

=SUM(b2:d2, f2)
=average(b6:b10)

In this function:
 The equal sign begins the function.

22
  SUM is the name of the function.
 b2:d2, f2 are the arguments.
 Parentheses enclose the arguments.
 Commas separate the arguments.

After you type the first letter of a function name, the AutoComplete list appears. You can double-
click on an item in the AutoComplete list to complete your entry quickly. Excel will complete the
function name and enter the first parenthesis.

23
 SELF-CHECK NO 1
Perform each of the following tasks. Open a new spreadsheet file

1) Enter the series ‘Monday, Tuesday, Wednesday’ into the range A2:C2
2) Enter the numbers ‘1500, 2350, 1390’ into the range A3:C3
3) Enter the numbers ‘2450, 6320, 1275’ into the range A4:C4
4) Select the ‘D’ column
5) Select rows ‘10’ and ‘11’ simultaneously
6) Save the file as ‘LESSON 1’

SELF-CHECK NO 2
Perform each of the following tasks

1) Retrieve the file ‘LESSON1’ from storage

2) Type the word ‘TOTAL’ into cell A5
3) Insert a column before column ‘A’
4) Delete column ‘D’
5) Insert a row above TOTAL in row 5
6) Move the entire table in range B2:C6 to begin from cell B1
7) Save file to update changes

SELF-CHECK NO 3

Task 1:Retrieve the spreadsheet file, LESSON 1. Complete each of the following calculations:
1) In row 5, use a function to compute the totals for Monday and Tuesday, respectively
2) In rows 7 and 8, use a function to determine the average and least value for each day
3) In row 9, create a formula that will compute what is 15% of each day’s total

Save the file to update changes

Task 2: Enter and complete the worksheet below, according to the notes:
Complete the worksheet below to assist Compton Hughes in calculating how much he has to
spend on items for youth camp.
ITEM No. Needed Cost per item Cost per set
Sneakers 2 4500
Note books 12 120
Pens 4 75
Drawing 12 65
Drawing
Sheets Set 1 3500

To Do:
1. Start the worksheet in cell A3
2. Calculate the cost of each set of items needed

24
3. Find the overall total cost Compton has to spend
4. Show the maximum and minimum cost per item
5. Find the average cost for the various sets of items
6. Show what would be 16% tax of the overall cost of items
7. If tax was added to the overall cost, how much will Compton spend in all?

25
 ANSWERS TO SELF-CHECK NO 1

NB: The trainee student must be able to complete each task without assistance to be successful.

Task Yes No

1) Open a new spreadsheet file

2) Enter the series ‘Monday, Tuesday,

Wednesday’ into the range A2:C2

3) Enter the numbers ‘1500, 2350, 1390’ into

the range A3:C3

4) Enter the numbers ‘2450, 6320, 1275’ into

the range A4:C4

5) Select the ‘D’ column by right clicking

6) Select rows ‘10’ and ‘11’ simultaneously

7) Save the file as ‘LESSON 1’

26
 ANSWERS TO SELF-CHECK NO 2

NB: The trainee student must be able to complete each task without assistance to be successful

Task Yes No

1) Retrieve the file ‘LESSON1’ from

storage

2) Type the word ‘TOTAL’ into cell A5

3) Insert a column before column ‘A’, by

selecting ‘A’ and insert new column

4) Delete column ‘D’, by selecting only

‘D’

5) Insert a row above TOTAL in row 5,

by selecting row 5 and insert above

6) Move the entire table in range A2:C6

to begin from cell A1, by cutting and
paste or by dragging.

7) Save file to update changes

27
 ___________________________________________________________________________
ANSWERS TO SELF-CHECK NO 3
NB: The trainee student must be able to complete the tasks without assistance to be successful
Task 1
i. The functions for the totals, average and least calculations must be as shown
ii. The formula for calculation of 15% of day total may be as shown or “=(c5*15%)”

Task 2
i. The functions and formulas for the completion of the worksheet must be as shown below.
ii. Their placement in correct columns is important.
iii. The inclusion of labels for each calculation is important

28
FINAL ASSESSMENT
This assessment must be completed by the trainee, without assistance, in order to
successfully move on to a subsequent module
The table below concerns salary paid to workers of Porter’s Construction Inc. Complete the
spreadsheet according to the notes below:
NAME Gross Pay Income Tax Insurance Field Net Total
($) Allowance Pay Pay
Kim Jones 44000 2905
Susan Ming 78650 1850
Dane Moses 98450 4500
Jankie Pertab 36270 2250
Movin Sung 51900 1378

a) Create a spreadsheet file with this data. Save file as ‘SALARY’.

b) Income Tax is calculated at 23% of the gross pay.
c) Insurance is 7.5% of Gross Pay.
d) Net Pay = (Gross Pay – (Income Tax + Insurance))
e) Total Pay = (Net Pay + Field Allowance)
f) Calculate overall total Income Tax, Insurance and Field Allowance from the various employees.
g) Calculate minimum Gross Pay and maximum Insurance paid
h) Apply the following modifications to layout:
i. Insert a row above total Income Tax, Insurance and Field Allowance
ii. Delete information pertaining to Dane Moses
i) Save the worksheet to update changes

ANSWER TO FINAL ASSESSMENT

29
 COURSE

OCCUPATIONAL TITLE: DATA OPERATIONS

COMPETENCE: OPERATE A SPREADSHEET

APPLICATION(BASIC)

LEVEL 1

MODULE NUMBER: ITIDAT0171A

MODULE TITLE: CUSTOMIZE BASIC SETTINGS

TRAINING GUIDE

DEVELOPED BY: A CONWAY

DEVELOPED AT: CTVET

DATED: 2011-11-18
INTRODUCTION
In this training guide you will learn how to customize basic settings to a document by selecting
and applying appropriate modifications to font, alignment and styles to both data and cell ranges,
according to spreadsheet requirements
The average time to complete this Training Guide is 10 hrs that is 5-2hrs sessions in the
laboratory/office.

TERMINAL PERFORMANCE OBJECTIVE

Upon completion of this module you will be able to customize basic settings to a spreadsheet
document according to document requirements/purpose.

30
 ELEMENT OF COMPETENCY
2. Customise basic settings

ENABLING OBJECTIVES
2.1 Select font type, size and colour appropriate for the purpose of the document.
2.2 Apply alignment and justification options and line spacing according to spreadsheet
formatting requirements.
2.3 Make appropriate modifications to column width and height to suit spreadsheet
requirements.
2.4 Format cells to display different styles, values and information as required.
2.5 View multiple workbooks/spreadsheets at any one time.

There are 5 sets of procedures to follow in order to master this TPO. After completing each
set of procedures go to the ‘Self-Check’ for that particular set and answer the questions or
carry out the activity. Model answers to the questions can be found in the answer to ‘Self-
Check’. For ‘Self-Check’ activities, a reference is given to a particular checklist to apply to
work.

EO1 Select font type, size and colour appropriate for the purpose of the document.
PROCEDURES RESOURCES AVERAGE
1. Study information in this module on how to  Personal TIME
change THE FONT, FONT SIZE AND Computer(monitor, mouse, 2 hrs
FONT COLOUR. keyboard, system unit)
 Multimedia kit
2. Follow the steps shown on pages 1-4 to  Spreadsheet program
obtain the spreadsheet on page 4.

3. Ask questions for clarification

4. Answer self-check No. 1

EO2. Apply alignment, justification options and line spacing according to spreadsheet formatting
requirements.

31
PROCEDURES RESOURCES AVERAGE
6. Study information in this module on CELL  Personal Computer TIME
ALIGNMENT OPTIONS. (monitor, mouse, 2 hrs
7. Follow the steps explained on pages 5-7. keyboard, system unit)
8. Ask questions for clarification  Multimedia kit
9. Practice cell alignment to satisfaction of the  Spreadsheet program
Lecturer/Instructor.
10. Answer self-check No. 2

EO3. Make appropriate modifications to column width and height to suit spreadsheet requirements.
PROCEDURES RESOURCES AVERAGE
6) Study information in the module on CHANGE  Multimedia kit TIME
COLUMN WIDTH AND HEIGHT.  Spreadsheet program 2hrs
Follow the instructions on pages 8-12.  Spreadsheet templates
7) Ask questions for clarification.
8) Practice changing column width and row
heights to the satisfaction of the
Lecturer/Instructor.
9) Answer self-check No. 3

EO4. Format cells to display different styles, values and information as required.
PROCEDURES RESOURCES AVERAGE
1) Study information in the module on  Multimedia kit TIME
FORMATTING CELL ENTRIES TO  Spreadsheet program 2hrs
DIFFERENT STYLES AND VALUES.  Spreadsheet templates
2) Observe demonstrations on applying formatting
3) Practice applying various formats as specified/
required by lecturer/ Instructor.
4) Answer self-check No. 4

32
EO5. View multiple workbooks/spreadsheets at any one time.
PROCEDURES RESOURCES AVERAGE
1) Study information on how TO VIEW OR  Multimedia kit TIME
SELECT MULTIPLE WORKSHEET on  Spreadsheet program 2 hrs
pages 18-20.  Spreadsheet templates
2) Practice selecting, inserting, deleting and
renaming worksheets as instructed by
Lecturer/Instructor
3) Answer self-check No. 5

33
 CHANGE THE FONT, FONT SIZE, AND FONT COLOR

You can change the Font type, Font Size, Effects, and Font Color of the data you enter into a spreadsheet.

To access the box below, do either one of the following:

 Press Ctrl + D, or
 Select a cell (s) > right click > Format Cells

Effects Allows you to select one of the following formatting effects.

i. Strikethrough: Select this check box to display the text in selected cells as strikethrough (eg 345)
ii. Superscript: Select this check box to display the text selected cells or text as superscript (eg 3 2)
iii. Subscript: Select this check box to display the text in selected cells as subscript (eg Base 2)

Preview: See a sample of text that is displayed with the formatting options that you select.

34
Create the following table to be used in the following tasks. Save as LESSON 2:

Change the Font type

1. Select cells B2 to E3.

2. Choose the Home tab/menu.

3. Click the down arrow next to the Font box. A list of fonts appears. As you scroll down the list of fonts, the
program provides a preview of the font in the cell you selected.
4. Find and click Times New Roman in the Font box.

35
Change the Font Size

1. Select cell B2.

2. Choose the Home tab.
3. Click the down arrow next to the Font Size box. A list of font sizes appears. As you scroll up or down the
list of font sizes, the program provides a preview of the font size in the cell you selected.
4. Click 26. The program changes the font size in cell B2 to 26.

Change the Font Color

1. Select cells B2 to E3.

2. Choose the Home tab/menu.

36
 3. Click the down arrow next to the Font Color button .
4. Click on the color Red. Your font color changes to Red.

Your worksheet should look like the one shown here.

37
CELL ALIGNMENT OPTIONS

Access the format box as in the previous section. Choose the Alignment tab.

Spreadsheet Alignment and Justification Options

Horizontal Select an option in the Horizontal list box to change the horizontal alignment of cell contents. By
default, the program aligns text to the left, numbers to the right, and logical and error values are centered. The
default horizontal alignment is General. Changing the alignment of data does not change the data type.

Vertical Select an option in the Vertical box to change the vertical alignment of cell contents. By default, Excel
aligns text vertically on the bottom of a cell. The default horizontal alignment is General.

Orientation Select an option under Orientation to change the orientation of text in selected cells. Rotation options
may not be available if other alignment options are selected.

38
Degrees/Oblique Sets the amount of text rotation in the selected cell. Use a positive number in the Degree box to
rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to
lower right in the selected cell.

TO DO OBLIQUE ALIGNMENT

In the worksheet created above:

1. Highlight cells b3:e3 – Add to Divide
2. Right-click > Format Cells > Alignment
3. Under Orientation, enter the number for degrees of rotation
4. Click OK

Alternatively, drag the hand of the pointer on the dial to the desired angle

TEXT CONTROL OPTIONS

Wrap text Wraps text into multiple lines in a cell. The number of wrapped lines is dependent on the width of the
column and the length of the cell contents.

39
Shrink to fit Reduces the apparent size of font characters so that all data in a selected cell fits within the column.
The character size is adjusted automatically if you change the column width. The applied font size is not changed.

Merge cells Combines two or more selected cells into a single cell. The cell reference for a merged cell is the
upper-left cell in the original selected range.

Right-to-left

Text direction Select an option in the Text direction box to specify reading order and alignment. The default
setting is Context, but you can change it to Left-to-Right or Right-to-Left.

WRAPPING TEXT IN CELLS

If you want text to appear on multiple lines in a cell, you can format the cell so that the text wraps automatically.

Wrap text automatically

1. In a worksheet, select the cells that you want to format.

2. On the Home tab, in the Alignment group, click Wrap Text .

Text entered in cell A10, extended across cells

All text wrapped within cell A10

40
 Data in the cell wraps to fit the column width. When you change the column width, data wrapping adjusts
automatically.

 If all wrapped text is not visible, it may be because the row is set to a specific height.

41
MERGING CELLS

Sometimes, particularly when you give a title to a section of your worksheet, you will want to merge text over
several columns or rows. The following example shows you how:

1. Go to cell B2 in worksheet LESSON 2

2. Click the check mark on the Formula bar.
3. Select cells B2 to E2.
4. Choose the Home tab.
5. Click the Merge and Center button in the Alignment group. Excel merges cells B2, C2, D2, and E2
and then centers the content.

Note:

1. Merging cells can also be done by

i. Highlighting cells
ii. Right-click > Format Cells>Alignment
iii. In the text control group > select merge cells.

42
To unmerge cells:

i. Select the cell you want to unmerge.

ii. On the Home menu, in the Alignment group.
iii. Click the down arrow next to the Merge and Center button. A menu appears.
iv. Click Unmerge Cells.

43
CHANGE THE COLUMN WIDTH AND ROW HEIGHT

On a worksheet, you can specify a column width of 0 (zero) to 255. This value represents the number of characters
that can be displayed in a cell that is formatted with the standard font. The default column width is 8.43 characters.
If the column width is set to 0, the column is hidden.

You can specify a row height of 0 (zero) to 409. This value represents the height measurement in points (1 point
equals approximately 1/72 inch). The default row height is 12.75 points. If the row height is set to 0, the row is
hidden

TO CHANGE COLUMN WIDTH

You can increase column widths. Increasing the column width enables you to see the long cell content

To set a column to a specific width

1. Select the column or columns that you want to change.

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

3. Under Cell Size, click Column Width.

4. In the Column width box, type the value that you want.

TO CHANGE COLUMN WIDTH BY USING THE MOUSE

Do ANY 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.

44
  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.

What does ##### in a cell means?

A cell may display ##### when it contains data that has a number format that is wider than the column
width. To see all cell contents, you must increase the width of the column.

To set a row to a specific height

1. Select the row or rows that you want to change.

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

3. Under Cell Size, click Row Height.

4. In the Row height box, type the value that you want

CHANGE THE HEIGHT OF ROWS BY USING THE MOUSE

Do ANY ONE of the following:

45
 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.

46
FORMATTING CELL ENTRIES TO DIFFERENT STYLES AND VALUES

Format
Category Description

General This is the default number format that Excel applies when you type a number.

Number This format is used for the general display of numbers. You can specify the number of decimal
places that you want to use, whether you want to use a thousands separator, and how you want
to display negative numbers.

Currency This format is used for general monetary values and displays the default currency symbol with
numbers.

Accounting This format is also used for monetary values, but it aligns the currency symbols and decimal
points of numbers in a column.

Date This format displays date and time serial numbers as date values, according to the type
location that you specify.

Time This format displays date and time serial numbers as time values, according to the type and
locale (location) that you specify.

Percentage This format multiplies the cell value by 100 and displays the result with a percent symbol. You
can specify the number of decimal places that you want to use.

47
 Fraction This format display a number as a fraction, according to the type of fraction that you specify.

Scientific This format displays a number in exponential notation, replacing part of the number with E+n,
where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power.
For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23
times 10 to the 10th power.

Text This format treats the content of a cell as text and displays the content exactly as you type it,
even when numbers are typed.

Special This format displays a number as a postal code (ZIP Code), phone number, or Social Security
number.

Custom This format allows you to modify a copy of an existing number format code. This creates a
custom number format that is added to the list of number format codes.

FORMATTING NUMBERS

You can format the numbers you enter into a spreadsheet. For example, you can add commas to separate thousands,
specify the number of decimal places, place a dollar sign in front of a number, or display a number as a percent.

1. Move to cell B8.

2. Type 1234567.
3. Click the check mark on the Formula bar.

48
4. Choose the Home tab.
5. Click the down arrow next to the Number Format box. A menu appears.
6. Click Number.
7. The program adds two decimal places to the number you typed.

7. Click the Comma Style button . The program separates the thousands with a comma.
8. In the Number group on the ribbon, by clicking the
a) Accounting Number Format
b) Currency Number Format.

49
 The program adds a dollar sign to your number.
NOTE THE DIFFERENCE IN APPEARANCE

Setting decimal places – using the ribbon

1. Click twice on the Increase Decimal button to change the number format to four decimal places.
2. Click the Decrease Decimal button if you wish to decrease the number of decimal places.

Change a number (decimal or integer) to display as a percent.

1. Move to cell B9.

2. Type .35 (note the decimal point).
3. Click the check mark on the formula bar.

4. Choose the Home tab.

50
 5. Click the Percent Style button . The program turns the number to a percent.

The following diagram is a summary of the number formats that are available on the Home tab in the
Number group. To see all available number formats, click the Dialog Box Launcher next to
Number.

NB: ALL OF THE ABOVE FORMATTING OF NUMBERS CAN ALSO BE DONE BY:
i. highlighting the range of cells
ii. right click > Format Cells > Number
iii. click on the Number tab, select the type of formatting

TO VIEW OR SELECT MULTIPLE WORKSHEETS

By clicking the tabs of worksheets (or sheets) at the bottom of the window, you can quickly select a different sheet.
If you want to enter or edit data on several worksheets at the same time, you can group worksheets by selecting
multiple sheets. You can also format or print a selection of sheets at the same time.

To select Do this

A single sheet Click the sheet tab.

If you don't see the tab that you want, click the tab scrolling buttons to display the
tab, and then click the tab.

Two or more adjacent Click the tab for the first sheet. Then hold down SHIFT while you click the tab for
sheets the last sheet that you want to select.

51
Two or more Click the tab for the first sheet. Then hold down CTRL while you click the tabs of the
nonadjacent sheets other sheets that you want to select.

All sheets in a Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
workbook

GROUPING WORKSHEETS

 When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To
cancel a selection of multiple worksheets in a workbook, click any unselected worksheet.

 If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the
shortcut menu.

52
INSERTING AND DELETING WORKSHEETS

By default, the spreadsheet program provides three worksheets in a workbook, but you can insert additional worksheets (and
other types of sheets, such as a chart sheet) or delete them as needed.

Insert a new worksheet

To insert a new worksheet, do either one of the following:

 To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet
tab at the bottom of the screen.

 To insert a new worksheet before an existing worksheet, select that worksheet, and then on the Home
tab, in the Cells group, click Insert, and then click Insert Sheet.

 Alternatively, you can also right-click the tab of an existing worksheet, and then click Insert. On the
General tab, click Worksheet, and then click OK.

Delete a worksheet

 On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Sheet.

 You can also right-click the sheet tab of the worksheet that you want to delete, and then click Delete.

53
Rename a worksheet

1. On the Sheet tab bar, right-click the sheet tab that you want to rename, and then click Rename.

2. Select the current name, and then type the new name

54
 SELF-CHECK NO 1
Create the following spreadsheet table to be used in the following Self Checks:

Go to EO1
In the table above, apply the following font formats:
a) Change the font type in A3:A5 and B2:D2 to ‘Verdana’
b) Change the colour of all the values to orange
c) Change the colour of all the labels to aqua/blue
d) Save the file as SELF CHECK

SELF-CHECK NO 2
Go to EO2
Retrieve the file SELF CHECK and perform the following tasks:
a) Set all labels in row 2 to oblique heading - 50 degrees
b) In cell A1, type the heading ‘TRANSPORTATION COSTS’
c) Merge and centre the heading across the width of the columns A1 to D1
d) Save file to update changes

SELF-CHECK NO 3
Go to EO3
a) Retrieve the file SELF CHECK
b) In cell A5 add the word ‘Transportation’ to the label Total
c) Widen the column to accommodate this label
d) In cell A6 type the label ‘Average Level Transportation’
e) Wrap cell A6 so that the first two words appear in the first line, and ‘Transportation’ is
below. Compute the average for all levels
f) Adjust the row height so that the entire label is visible
g) Save the file to update changes

55
 SELF-CHECK NO 4
Go to EO4
a) Retrieve the file SELF CHECK
b) Comma Format all numbers. State why some numbers do not have a comma
c) Currency (not accounting) format all numbers in Total row
d) Set the Average numbers to 2 decimal places
e) In cell B9, enter the date 2011-09-05. Format to show Day/month/day/year.
f) In cell B10, enter the time 8:15. Format to show hour/min/am/pm
h) Save the file to update changes

SELF-CHECK NO 5
Go to EO5
a) Retrieve the file SELF CHECK
b) Copy the entire table from sheet 1 and paste it to sheet 2
c) Rename the sheets ORIGINAL and BACKUP, respectively
d) Delete any other sheet in the workbook
e) Group the two sheets: increase the widths of columns C and D to 12, in both sheets,
simultaneously
f) Adjust other columns and rows as needed
i) Save the file to update changes.

56
 ANSWERS TO SELF-CHECK NO 1
The completion of each task must be accomplished:

Task
Yes No
a) Created the required spreadsheet

b) Changed the font type in A3:A6 and B2:D2 to

‘Verdana’

c) Changed the colour of all the values to orange

d) Changed the colour of all the labels to aqua/blue

e) Saved the file as SELF CHECK

NB: The trainee student must be able to complete the task without assistance to be successful.

57
 ANSWERS TO SELF-CHECK NO 2
The completion of each task must be accomplished:

Task Yes No

a) Set all labels in row 2 to oblique heading -

50 degrees

b) In cell A1, type the heading

‘TRANSPORTATION COSTS’

c) Merge and centre the heading across the

width of the columns A1 to D1

d) Save file to update changes

58
 ANSWERS TO SELF-CHECK NO 3
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK

b) In cell A5 add the word

‘Transportation’ to the label Total

c) Widen the column to accommodate

this label

d) In cell A6 type the label ‘Average

Level Transportation’

e) Wrap cell A6 so that the first two

words appear in the first line, and
‘Transportation’ is below. Compute
the average for all levels

f) Adjust the row height so that the entire

label is visible

g) Save the file to update changes

59
 ______________________________________________________________________
ANSWERS TO SELF-CHECK NO 4
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK

b) Comma Format all numbers. State why

some numbers do not have a comma

c) Currency (not accounting) format all

numbers in Total row

d) Set the Average numbers to 2 decimal

places

e) In cell B9, enter the date 2011-09-05.

Format to show Day/month/day/year.

f) In cell B10, enter the time 8:15. Format

to show hour/min/am/pm

g) Save the file to update changes

60
 _________________________________________________________________________
ANSWERS TO SELF-CHECK NO 5
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK

b) Copy the entire table from sheet 1

and paste it to sheet 2

c) Rename the sheets ORIGINAL and

BACKUP, respectively

d) Delete any other sheet in the

workbook

e) Group the two sheets: increase the

widths of columns C and D to 12, in
both sheets, simultaneously

f) Adjust other columns and rows as

needed

g) Save the file to update changes.

Completed Document after Self Tests

61
FINAL ASSESSMENT
1. (a) Retrieve the worksheet SALARY, created as final assessment in the previous module.
(b) Apply the various customization techniques learnt in this module to modify the layout of that worksheet:
i. Emphasize the column headings with an appropriate change of font type and size
ii. Wrap any cell containing wide headings
iii. Merge and centre an appropriate title across the width of the table. Increase the height of this row.
iv. Comma format all values for each worker, and apply currency format, zero decimal, places to the
overall totals.
v. Highlight the workers’ names by changing the text colour.
vi. Rename the worksheet ‘SALARY’
(c) Save the file to update changes made.

62
______________________________________________________________________________
___

CHECK LIST FOR FINAL ASSESSMENT

This assessment must be completed by the trainee, without assistance, before advancing to
a subsequent module.

Task Yes No

a) Retrieve the worksheet SALARY, created as final assessment in the

previous module.
b) Apply the various customization techniques:
i. Emphasize the column headings with an appropriate change of font
type and size
ii. Wrap any cell containing wide headings
iii. Merge and centre an appropriate title across the width of the table.
Increase the height of this row.
iv. Comma format all values for each worker
v. Apply currency format, zero decimal, places to the overall totals.
vi. Highlight the workers’ names by changing the text colour.
vii. Rename the worksheet ‘SALARY’
(c) Save the file to update changes made.

63
 COURSE
OCCUPATIONAL TITLE: DATA OPERATIONS

COMPETENCE: OPERATE A SPREADSHEET

APPLICATION (BASIC)

LEVEL 1

MODULE NUMBER: ITIDAT0171A

MODULE TITLE: FORMAT SPREADSHEET

TRAINING GUIDE

DEVELOPED BY: A CONWAY

DEVELOPED AT: CTVET

DATED: 2011-11-15

64
 INTRODUCTION
In this training guide you will learn further techniques of formatting to enhance a spreadsheet
and correct procedures of saving such files to disk/storage.
The average time to complete this Training Guide is 3 hours, that is 3-1 hour sessions in the
laboratory or office.

TERMINAL PERFORMANCE OBJECTIVE

Upon completion of this module you will be able to format a spreadsheet and save the file to
disk/storage.

ELEMENT OF COMPETENCY
3. Format spreadsheet

ENABLING OBJECTIVES

3.1 Use bold, italic, underline and hyphenation for formatting.

3.2 Align information in selected cells.
3.3 Apply borders and shading to cells.
3.4 Close document and save to disk.

There are 4 sets of procedures to follow in order to master this TPO. After completing each
set of procedures go to the ‘Self-Check’ for that particular set and answer the questions or
carry out the activity. Model answers to the questions can be found in the answer to ‘Self-
Check’. For ‘Self-Check’ activities, a reference is given to a particular checklist to apply to
work.

EO1. Use bold, italic, underline and hyphenation for formatting

65
PROCEDURES RESOURCES AVERAGE
5. Study information in this module on  Personal Computer TIME
FORMATING TOOLS USED TO (monitor, mouse, keyboard, 1 hr
ENHANCE OR EMPHASIZE. system unit)
 Multimedia kit
6. Follow the instructions on pages 1-3.  Spreadsheet program

7. Practice the use of formatting tools in a

document identified by the Lecturer/Instructor.
8. Answer self-check No. 1

EO2. Align information in selected cells

PROCEDURES RESOURCES AVERAGE
1. Study information in this module on ALIGN  Personal Computer TIME
CELL ENTRIES OR CONTENTS. (monitor, mouse, 1 hr
keyboard, system unit)
2. Follow instructions on pages 4-5.  Multimedia kit
 Spreadsheet program
3. Observe demonstration of changing cell
alignment.

4. Ask questions for clarification.

9. Practice applying /changing cell alignment as

instructed by Lecturer/Instructor.

10. Answer self-check No. 2

66
EO3. Apply Borders and shading to cells
PROCEDURES RESOURCES AVERAGE
10) Study information in the module on  Multimedia kit TIME
CREATING BORDERS IN A WORKSHEET  Spreadsheet program 1h
and ADDING SHADING/FILL to CELLS.  Spreadsheet Worksheet
2) Ask questions for clarification.
3) Practice applying borders and shading under the
supervision of the lecturer/instructor.
11) Answer self-check No. 3

___________________________________________________________________________________
EO 4. Close document and save to disk/storage
PROCEDURES RESOURCES AVERAGE
1. Study information in this module on CLOSING  Personal TIME
AND SAVING A SPREADSHEET Computer(monitor, mouse, 1h
DOCUMENT. keyboard, system unit)
 Multimedia kit
a) TO SAVE A FILE.  Spreadsheet program
 Spreadsheet file
b) TO SAVE THE DOCUMENT IN A
DIFFERENT FORMAT.

2. Ask questions for clarification

3. Observe the instructions on page 9.
4. Practice saving a file to different locations and in
different formats as directed by the
Lecturer/Instructor.
5. Answer Self-Check No. 4

67
FORMATTING TOOLS USED TO ENHANCE OR EMPHASIZE

When creating worksheet, you may want to emphasize the contents. The following tools are explained in this
module:

1. Bold
2. Italic
3. Underline

Bold with the Ribbon

1. Type Bold in cell A1.

2. Click the check mark located on the Formula bar.
3. Choose the Home tab.
4. Click the Bold button . Excel bolds the contents of the cell.
5. Click the Bold button again if you wish to remove the bold.

Italicize with the Ribbon

68
 1. Type Italic in cell B1.
2. Click the check mark located on the Formula bar.
3. Choose the Home tab.
4. Click the Italic button . Excel italicizes the contents of the cell.
5. Click the Italic button again if you wish to remove the italic.

Underline with the Ribbon

1. Type Underline in cell C1.

2. Click the check mark located on the Formula bar.
3. Choose the Home tab.
4. Click the Underline button . Excel underlines the contents of the cell.
5. Click the Underline button again if you wish to remove the underline.

NB: the following short-cut keys (on keyboard) may be used:

TOOL SHORT-CUT KEYS
Bold Ctrl + B
Italic Ctrl + I
Underline Ctrl + u

69
ALIGN CELL ENTRIES OR CONTENTS
Default Alignment
When you type text into a cell, by default your entry aligns with the left side of the cell. When
you type numbers into a cell, by default your entry aligns with the right side of the cell. You can
change the cell alignment. You can center, left-align, or right-align any cell entry. Look at the
figure above, you’ll see the values (numbers) are all aligned to the right

Center Align

To center cells B3 to E3:

1. Select cells B3 to E3.

2. Choose the Home tab.
3. Click the Center button in the Alignment group. Excel centers each cell's content.

Left-Align

To left-align cells B6 to E6:

70
 1. Select cells B6 to E6.
2. Choose the Home tab/menu.
3. Click the Align Text Left button in the Alignment group. The program left-aligns each cell's content.

Right-Align

To right-align cells A6:

1. Select cell A6.

2. Choose the Home tab/menu.
3. Click the Align Text Right button. The program right-aligns the cell's content.
4. Click anywhere on your worksheet to clear the highlighting.

Note: You can also change the alignment of cells with numbers in them by using the alignment buttons.

71
APPLYING BORDERS IN A WORKSHEET
Without borders, a worksheet will look and prints like the image below. The grid lines are not automatically printed.

By using predefined border styles, you can quickly add a border around cells or ranges of cells.

Creating Borders using the Ribbon

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

2. On the Home tab, in the Font group, do one of the following:

 To apply a new or different border style, click the arrow next to Borders , and then click a
border style.

 To apply a custom border style or a diagonal border, click More Borders. On the Border tab, under
Line and Color, click the line style and color that you want. Under Presets and Border, click one or
more buttons to indicate the border placement. Two diagonal border buttons are available
under Border.

 To remove cell borders, click the arrow next to Borders , and then click No Border .

ALTERNATIVELY:
i. highlight the range of cells to add border
ii. right click > Format Cells > Border (see image below)
iii. select a Line Style, then select outline or inside. Or
iv. choose click on a preset border box
v. when you are finished, click OK

Remove a border by selecting None.

72
APPLYING SHADING/ FILL TO CELLS

You can add shading to cells by filling them with solid colors or specific patterns. You can remove cell shading if
you no longer need it.

To fill/shade cells with a solid colour

1. Select the cells that you want to apply shading to or remove shading from.

2. On the Home tab, in the Font group, do one of the following:

 To fill cells with a solid color, click the arrow next to Fill Color in the Font group on the Home
tab, and then click the color on the palette that you want.

 To apply the most recently selected color, click Fill Color

To Fill cells with patterns

1. Select the cells that you want to fill with a pattern.

2. On the Home tab, click the Dialog Box Launcher next to Font, and then click the Fill tab.

3. Under Background Color, click the background color that you want to use.

4. Do one of the following:

73
  To use a pattern with two colors, click another color in the Pattern Color box, and then click a
pattern style in the Pattern Style box.

 To use a pattern with special effects, click Fill Effects, and then click the options that you want on
the Gradient tab.

To Remove cell shading

1. Select the cells that contain a fill color or fill pattern.

2. On the Home tab, in the Font group, click the arrow next to Fill Color, and then click No Fill.

CLOSING AND SAVING A SPREADSHEET DOCUMENT

When you save a file, you can save it to a folder on your hard disk drive, the desktop, or another storage location (eg
a flash drive or CD). You need to identify the target location in the Save in list. Otherwise, the saving process is the
same, no matter what location you choose.

TO SAVE A FILE:

I. Click the Microsoft Office Button , and then click Save or Save As (to change the file name or location)

II. In the Save in list, click the folder or drive to which you want to save.

74
III. To save the copy in a different folder, click a different drive in the Save in list or a different folder in the
folder list. To save the copy in a new folder, click Create New Folder .

IV. In the File name box, enter a new name for the file.

V. Click Save.

TO SAVE THE DOCUMENT IN A DIFFERENT FORMAT

I. Click the Microsoft Office Button , and then click Save As.

II. On the File menu, click Save As.

III. In the File name box, enter a new name for the file.

IV. In the Save as type list, click the file format that you want to save the file in.

V. Click Save.

75
 SELF-CHECK NO 1
Go to EO1
a) Retrieve the file SELF CHECK, created in the previous Module
b) Format the labels in A3:A6 and B2:D2 to bold
c) Italicize the values in the range B3 to D4
d) Underline the heading in row 1
e) Save and the file to update changes

SELF-CHECK NO 2
Go to EO2
a) Retrieve the file SELF CHECK
b) Centre align all numeric values
c) Right align the labels n the range A3:A6
d) Save the file to update changes

SELF-CHECK NO 3
Go to EO3

a) Retrieve the file SELF CHECK

b) Apply the following borders to the table: a double line to the outline, and single thin
line to inside cells
c) Place diagonal borders in the cells B6:D6
d) Apply a shade of orange to the merged cell in row 1
e) Save the file to update changes

76
 SELF-CHECK NO 4
Go to EO4
You would like to continue working the spreadsheet on another system, but you’re
uncertain as to the version of MS Excel. Thus, the file must be saved to a removable
storage device in a format that can be accessed:

a) Retrieve the file SELF CHECK

b) Save the file in a different format and location as follows:
i. File type - 97-2003 compatible
ii. New filename SELF CHECK2
iii. Save location is your flash drive (or other removable device)
c) Access and verify that the file has been saved correctly

77
 ANSWERS TO SELF-CHECK NO 1
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK, created in

the previous Module
b) Format the labels in A3:A6 and B2:D2 to
bold
c) Italicize the values in the range B3 to D4
d) Underline the heading in row 1
e) Save and the file to update changes

_____________________________________________________________________________________
ANSWERS TO SELF-CHECK NO 2
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK

b) Centre align all numeric values
c) Right align the labels in the range A3:A6
d) Save the file to update changes

78
______________________________________________________________________________
___

ANSWERS TO SELF-CHECK NO 3
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK

b) Apply the following borders to the table: a
double line to the outline, and single thin
line to inside cells
c) Place diagonal borders in the cells B6:D6
d) Apply a shade of orange to the merged cell in
row 1
e) Save the file to update changes

______________________________________________________________________________
___

ANSWERS TO SELF-CHECK NO 4
The completion of each task must be accomplished:

Task Yes No

a) Save the file in a different format and

location as follows:
i. File type - 97-2003 compatible
ii. New filename SELF CHECK2
iii. Save location is your flash drive (or
other removable device)

79
b) Access and verify that the file has been
saved correctly

80
 COMPLETED DOCUMENT FOR SELF CHECKS 1-3

FINAL ASSESSMENT
a) Retrieve the worksheet SALARY, used in the final assessment in the previous module.
b) Apply the various formatting techniques learnt in this module to enhance the presentation of the worksheet:
vii. Bold the names of each worker
viii. Italise the Net Pay and Total Pay labels
ix. Centre align all labels and values for the overall totals, minimum Gross Pay and maximum Insurance
x. Apply borders to the table as follows:
 A thick outline box
 A double vertical border separating Gross Pay from Income Tax
 A double horizontal separating the overall totals from the rows above
c) Apply a light colour shade to the first and last rows of the table.
d) Save the file as a spreadsheet template, with the filename SALARY2,

81
______________________________________________________________________________
___

CHECK LIST FOR FINAL ASSESSMENT

This assessment must be completed by the trainee, without assistance, before advancing to
a subsequent module.

Task Yes No

a) Retrieve the worksheet SALARY, used in the final assessment in the

previous module.
b) Apply the various formatting techniques :
i. Bold the names of each worker
ii. Italise the Net Pay and Total Pay labels
iii. Centre align all labels and values for the overall totals, minimum
Gross Pay and maximum Insurance
iv. Apply borders to the table as follows:
 A thick outline box
 A double vertical border separating Gross Pay from Income Tax
 A double horizontal separating the overall totals from the rows
above
c) Apply a light colour shade to the first and last rows of the table.
d) Save the file as a spreadsheet template,
e) Save file with the filename SALARY2

82
 COURSE
OCCUPATIONAL TITLE: DATA OPERATIONS

COMPETENCE: OPERATE A SPREADSHEET

APPLICATION (BASIC)

LEVEL 1

MODULE NUMBER: ITIDAT0171A

MODULE TITLE: PRINTING SPREADSHEETS

TRAINING GUIDE

DEVELOPED BY: A CONWAY

DEVELOPED AT: CTVET

DATED: 2011-11-15

83
 INTRODUCTION
In this training guide you will learn the techniques involved in preparing a worksheet for
printing, choosing the print options and printing a document.
The average time to complete this Training Guide is 1 hour, that is 1-1 hour session in the
laboratory or office.

TERMINAL PERFORMANCE OBJECTIVE

Upon completion of this module you will be able to prepare and print a spreadsheet document.

ELEMENT OF COMPETENCY
4. Print spreadsheets

ENABLING OBJECTIVES

4.1 Demonstrate the ability to preview a spreadsheet in print preview mode

4.2 Select basic print options
4.3 Print spreadsheet or part of spreadsheet from installed printer

There are 3 sets of procedures to follow in order to master this TPO. After completing each
set of procedures go to the ‘Self-Check’ for that particular set and answer the questions or
carry out the activity. Model answers to the questions can be found in the answer to ‘Self-
Check’. For ‘Self-Check’ activities, a reference is given to a particular checklist to apply to
work.

84
EO1. Demonstrate the ability to preview a spreadsheet in print preview mode
PROCEDURES RESOURCES AVERAGE
11. Study information in this module on  Personal Computer TIME
PREVIEWING A SPREADSHEET IN PRINT (monitor, mouse, keyboard, 15 mins
PREVIEW MODE. system unit)
 Multimedia kit
12. Follow the instructions on pages 1-2.  Spreadsheet program
 Installed printer
13. Practice print previewing in a document
identified by the Lecturer/Instructor, ie the
SELF CHECK and SALARY2 files.
14. Answer self-check No. 1

EO2. Select basic print options

PROCEDURES RESOURCES AVERAGE
 Personal Computer TIME
1. Study information in this module on how to (monitor, mouse, 30 mins
SELECT BASIC PRINT OPTIONS. This keyboard, system unit)
includes:  Multimedia kit
a) Scaling page setup  Spreadsheet program
b) Centring of page before print  Installed printer

2. Follow instructions on pages 1-3.

3. Ask questions for clarification.

4. Practice selecting print options as

instructed by Lecturer/Instructor.

5. Answer self-check No. 2

85
EO3. Print spreadsheet or part of spreadsheet from installed printer
PROCEDURES RESOURCES AVERAGE
 Personal Computer TIME
1. Study information in this module on (monitor, mouse, 15 mins
PRINTING A SPREADSHEET keyboard, system unit)
DOCUMENT:  Multimedia kit
a) Printing a partial/entire worksheet  Spreadsheet program
b) Printing several worksheets  Installed printer
c) Setting a print area

2. Follow instructions on pages 4-5.

3. Ask questions for clarification.

4. Print a document as instructed by

Lecturer/Instructor.

5. Answer self-check No. 3

86
PREVIEWING A SPREADSHEET IN PRINT PREVIEW MODE

When using Print Preview, you can see onscreen how your printed document will look when you print
it.

 If you click the Page Setup button while in Print Preview mode, you can set page settings

such as orientation, scaling and centering your document on the page.

 If your document is several pages long, you can use the Next Page and Previous Page buttons

to move forward and backward through your document.

 If you check the Show Margins check box, you will see margin lines on your document. You

can click and drag the margin markers to increase or decrease the size of your page

margins. It is, however, advisable to set your paper size and orientation during typing of

document

 To return to the document window, click the Close Print Preview button.

87
To Open Print Preview

1. Click the Office button. A menu appears.

2. Highlight Print. The Preview and pane appears.
3. Click Print Preview. The Print Preview window appears, with your document in the center

SELECT BASIC PRINT OPTIONS

Scaling under page setup

Scaling enlarges or reduces the worksheet or selection when you print so that it fits on the
specified number of pages.

 Adjust to: When you select Adjust to, you can enter a percentage in the % normal size
box.
 Fit to: When you select Fit to, you can enter a number in the page(s) wide by box and
the tall box. To fill the paper width and use as many pages as necessary, type 1 in the
pages(s) wide by box and leave the tall box blank.

88
To Center Your Document before print

1. Click the Page Setup button in the Print group. The Page Setup dialog box appears.
2. Choose the Margins tab.
3. Click the ‘Horizontally’ check box. The document is centered horizontally.
4. Click the ‘Vertically’ check box. The document is centered vertically.
5. Click OK. The Page Setup dialog box closes.

NB: Margins can also be adjusted in this dialog box

89
PRINTING A SPREADSHEET

Print a partial or entire worksheet or workbook

1. Do one of the following:

 To print a partial worksheet, click the worksheet, and then select the range of data
that you want to print.

 To print the entire worksheet, click the worksheet to activate it.

 To print a workbook, click any of its worksheets.

2. Click Microsoft Office Button , and then click Print.

Keyboard shortcut = CTRL+ P.

3. Under Print what, select an option to print the selection, the active sheet or sheets, or the
entire workbook

To Print several worksheets at once - Select the worksheets that you want to print by selecting
the worksheet tabs

Set a print area

1. If you print a specific selection on the worksheet frequently, you can define a print area
that includes just that selection. When you print the worksheet after defining a print area,
only the print area will print.

2. You can add cells to expand the print area as needed, and you can clear the print area to
print the entire worksheet again.

3. On the worksheet, select the cells that you want to define as the print area.

4. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set
Print Area.

90
TO SEND THE DOCUMENT TO THE PRINTER

1. Click the Print button. The Print dialog box appears.

2. Click the down arrow next to the name field and select the printer to which you want to
print.
3. Set the print range (All or Pages).
4. If you want to print specific pages, enter the page numbers in the From and To fields.
5. Click OK. The program sends your worksheet to the printer.

91
 SELF-CHECK NO 1
Go to EO1

e) Retrieve the file SELF CHECK, created in the previous module

f) In Print Preview, zoom the window to ensure all table columns are on page 1
g) Close print preview.
h) Close the document window.
i) Repeat steps a) to c) for the file SALARY2
j) Minimize SALARY2 document window

SELF-CHECK NO 2
Go to EO2
a) Restore the SALARY2 document window.
b) Adjust the document within 90% - 95% of normal size.
c) Set top and bottom margins to 1”.
d) Center the document horizontally on the page.
e) Close print preview.
f) Save file to update changes.

SELF-CHECK NO 3
Go to EO3
a) Use the keyboard short-cut keys to access the print box
b) Print one copy of the entire worksheet of SALARY2
c) In this very worksheet, set a print area on the range A1:D7
d) Print this area, also

92
 ANSWERS TO SELF-CHECK NO 1
The completion of each task must be accomplished:

Task Yes No

a) Retrieve the file SELF CHECK, created in the

previous module
b) In Print Preview, zoom the window to ensure all
table columns are on page 1
c) Close print preview.
d) Close the document window.
e) Repeat steps a) to c) for the file SALARY2
f) Minimize SALARY2 document window

93
 ANSWERS TO SELF-CHECK NO 2
The completion of each task must be accomplished:

Task Yes No

a) Restore the SALARY2 document

window.
b) Adjust the document within 90% -
95% of normal size.
c) Set top and bottom margins to 1”.
d) Center the document horizontally
on the page.
e) Close print preview.
f) Save file to update changes.
h)

ANSWERS TO SELF-CHECK NO 3
The completion of each task must be accomplished:

Task Yes No

a) Use the keyboard short-cut keys to

access the print box
b) Print one copy of the entire
worksheet of SALARY2
c) In this very worksheet, set a print
area on the range A1:D7
d) Print this area
.

94
FINAL ASSESSMENT
1. Retrieve the files SELF CHECK and SELF CHECK2; used in the previous module.

2. Centre both documents on the page, horizontally and vertically.

3. Set the orientation of both pages to landscape

4. Adjust both documents to 110% of their respective pages

5. Print.

____________________________________________________________________

CHECK LIST FOR FINAL ASSESSMENT

This assessment must be completed by the trainee, without assistance, before advancing to
a subsequent module. Both documents must be successfully printed.

Task Yes No

1. Retrieve the files SELF CHECK and SELF CHECK2;

used in the previous module.

2. Centre both documents on the page, horizontally and

95
 vertically.

3. Set the orientation of both pages to landscape

4. Adjust both documents to 110% of their respective pages

5. Print documents.

96