RIFT VALLEY UNIVERSITY

WEB DEVELOPMENT AND

DATABASE ADMINISTRATION
Level – I
Based on March 2022, Curriculum Version 1

Module Title: - Operate Spreadsheet applications

Module code: EIS WDDBA1 M07 0322
Nominal duration: 48Hour

Prepared by: Ministry of Labour and Skill

Page 1 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Contents

September, 2022
Addis Ababa, Ethiopia
Table of content

Acknowledgment 4

Acronyms Error! Bookmark not defined.

Introduction to the module 6

This Module covers the unit 7

Learning Objective of the Module 7

Module Instruction7

Unit one: Create spreadsheets 8

1.1 Purpose and Function of spreadsheet 9
1.2 Open, create spreadsheet files and enter numbers, text and symbols into cells
10
1.3 Enter simple formula and function use cell reference 17
1.4 Correct formulas 28
1.5 Edite column and row within the spreadsheet34
1.6 use AutoFill function to increment data in Excel 35
1.7 Save spreadsheet 36
Self check-1 38
Operation title 1: 39

Unit Two: Customize basic settings 42

2.1 Adjust page layout 43
2.2 Open and view different toolbars 43
2.3 Change font settings 45
2.4 Change alignment and line spacing Text alignment 49
2.5 Modify margin sizes 50
2.6 View multiple spreadsheets concurrently 52
Self -check 2 54

Unit Three: Format spreadsheet 55

3.1 Use format features and tools 56
3.2 Insert headers and footers 57
Operation title 3 62

Page 2 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Unit Four: Incorporate object and chart in spreadsheet 65
4.1 Import an object into spreadsheet 66
4.2 Create a chart using selected data in the spreadsheet68
Self-check-4 79
Operation title 4 import an object into spreadsheet 81

Unit Five: Print spreadsheet 83

5.1 Previewing spreadsheet 83
5.2 Selecting basic printer options to printing spreadsheet 85
Self check-5 87
Reference 88

Page 3 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Acknowledgment

Ministry of Labor and Skills wish to extend thanks and appreciation to the many
representatives of TVET instructors and respective industry experts who donated their time and
expertise to the development of this Teaching, Training and Learning Materials (TTLM).

Page 4 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Acronyms

1. MS-Excel Microsoft Excel

2. PC Personal computer
3. CD-R Compact disc-recordable
4. USB Universal Serial Bus
5. ICT Information communication technology
6. WDDA Web development and database
administration

Teaching, Training and Learning

7. TTLM Materials

Page 5 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Introduction to the module

A spreadsheet or worksheet is a file made of rows and columns that help sort, organize, and
arrange data efficiently, and calculate numerical data. What makes a spreadsheet software
program unique is its ability to calculate values using mathematical formulas and the data in
cells. An example of how a spreadsheet may be utilized is creating an overview of your bank's
balance. spreadsheet and worksheet mean the same thing. However, most people only refer to the
program as a spreadsheet program and the files it creates as spreadsheet files or worksheets.

Page 6 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
Module Instruction

This Module covers the unit

 Create spreadsheets
 Customize basic settings
 Format spreadsheet
 Insert chart in spreadsheet
Learning Objective of the Module

 Print spreadsheet
 Can Create spreadsheets
 Apply Customize basic settings on spreadsheet
 Format spreadsheet
 Insert chart in spreadsheet
 Can Print spreadsheet

For effectively use these modules trainees are expected to follow the following module
instruction:
1. Read the specific objectives of this Learning Guide.
2. Follow the instructions described below.
3. Read the information written in the information in respectively.
4. Accomplish the “Self-check 1, Self-check 2, Self-check 3 and Self-check 4, Self-check 5,
Self-check 6 and self-check7” respectively.
5. If you earned a satisfactory evaluation from the “Self-check” proceed to “Operation Sheet.
6. Do the “LAP test” at the end page (if you are ready).

Unit one: Create spreadsheets

This learning unit is developed to provide the trainees the necessary information regarding the

Page 7 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
following content coverage and topics:
 Purpose and function of spreadsheet
 Open spreadsheet application, create spreadsheet files and enter numbers, text and
symbols into cells.
 Enter simple formulas and functions use cell reference
 Correct formulas
 Edit columns and rows within the spreadsheet
 Use the auto-fill function to increment data
 Save spreadsheet to directory or folder

This unit will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Open spreadsheet application, create spreadsheet files and enter numbers, text and
symbols into cells according to information requirements
 Enter simple formulas and functions using cell referencing where required
 Correct formulas when error messages occur
 Edit columns and rows within the spreadsheet
 Use the auto-fill function to increment data where required
 Save spreadsheet to directory or folder

Page 8 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
1.1 Purpose and Function of spreadsheet
1.1.1 Purpose of spread sheet

The purpose of a spreadsheet is to organize and calculate information. Spreadsheets are utilized
by many different businesses and organizations to perform a variety of tasks. The most commons
tasks performed by spreadsheet software include functions, computations, statistical analysis,
and formatting. This text will focus on excel when discussing spreadsheets.
Spreadsheets are an essential business and accounting tool. They can vary in complexity and can
be used for various reasons, but their primary purpose is to organize and categorize data into a
logical format. Once this data is entered into the spreadsheet, you can use it to help organize and
grow your business.

1.2. 1 Function of spread sheet

A spreadsheet is a file that exists of cells in rows and columns and can help arrange, calculate
and sort data. Data in a spreadsheet can be numeric values, as well as
text, formulas, references and functions.
A spreadsheet is a computer program that can capture, display and manipulate data arranged in
rows and columns. Spreadsheets are one of the most popular tools available with personal
computers. A spreadsheet is generally designed to hold numerical data and short text strings. In a
spreadsheet program, spaces that hold items of data are called spreadsheet cells. These can be
renamed to better reflect the data they hold and can be cross-referenced through row numbers
and column letters.
Below are some other popular uses of spreadsheets.
A. Finance
Spreadsheets are ideal for financial data, such as your checking account information, budgets,
taxes, transactions, billing, invoices, receipts, forecasts, and any payment system.
B. Forms
Form templates can be created to handle inventory, evaluations, performance reviews, quizzes,
time sheets, patient information, and surveys.
C. School and grades

Page 9 of 89 Ministry of Labor and Operate spreadsheet application Version -1

Skills September, 2022
Author/Copyright
 Teachers can use spreadsheets to track students, calculate grades, and identify relevant data, such
as high and low scores, missing tests, and students who are struggling.
D. Lists
Managing a list in a spreadsheet is a great example of data that does not contain numbers, but
still can be used in a spreadsheet. Great examples of spreadsheet lists include telephone, to-do,
and grocery lists.
E. Sports
Spreadsheets can keep track of your favorite player stats or stats on the whole team. With the
collected data, you can also find averages, high scores, and statistical data. Spreadsheets can
even be used to create tournament brackets.
1.2 Open, create spreadsheet files and enter numbers, text and symbols into
cells
1.2.1 Open spreadsheet
Excel is a spreadsheet program that allows you to store, organize, and analyze information.
While you may believe Excel is only used by certain people to process complicated data, anyone
can learn how to take advantage of the program's powerful features. Whether you're keeping a
budget, organizing a training log, or creating an invoice, Excel makes it easy to work with
different types of data.
Start All ApplicationMicrosoft OfficeMicrosoft Excel

Page 10 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig1.1 window taskbar
When you open Excel 2016 for the first time, the Excel Start Screen will appear. From here,
you'll be able to create a new workbook, choose a template, and access
your recently edited workbooks.
From the Excel Start Screen, locate and select Blank workbook to access the Excel interface.

Fig1.2 Excel Start Screen

Click the buttons in the elbow to become familiar with the Excel interface.

Page 11 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.3 excels interface

• Name Box: Displays the currently selected sell.

• Formula Bar: Displays the number, text, or formula that is in the currently selected
cell, and allows you to edit it. It behaves just like a text box.
• Selected Cell: The selected cell has a dark border around it.
• Column: Columns run vertically (top to bottom).
• Column Label: Identifies each column with a letter. Clicking on a column label
selects the entire column.
• Row: Rows run horizontally (left to right).
• Row Label: Identifies each row with a number. Clicking on a row label selects the
entire row.
• Cell: The intersection of a row and column.
• Worksheets: The worksheets contained in the workbook are displayed at the bottom-
left of the screen. Click on a worksheet to view it.
• Scroll Bars: Used to view other parts of a worksheet when the entire worksheet cannot
fit on the screen.
• View Tools: See Status Bar next

Page 12 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1.2.2 Open spreadsheet To create a new blank workbook:
1. Select the File tab. Backstage view will appear.

Fig1.4 file tab

2. Select New, then click Blank workbook.

Fig 1.5 Blank workbook

3. A new blank workbook will appear.

Page 13 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 To open an existing workbook:
In addition to creating new workbooks, you'll often need to open a workbook that was
previously saved.
1. Navigate to Backstage view, then click Open.

Fig 1.6 backstage view

2. Select Computer, then click Browse. Alternatively, you can choose OneDrive to open
files stored on your OneDrive.

Fig 1.7 save location

3. The Open dialog box will appear. Locate and select your workbook, then click Open.

Page 14 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.8 open dialog box
1.2.3 Enter numbers, text and symbols into cells
1. Click cell location A2 on the worksheet.
2. Type the word Month.
3. Press the RIGHT ARROW key. This will enter the word into cell A2 and activate the
next cell to the right.
4. Type Unit Sales and press the RIGHT ARROW key.
5. Repeat step 4 for the words Average Price and then again for Sales Dollars

Figure 1.9 Entering Column Headings into a Worksheet

1. Click cell location B3.

Page 15 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2. Type the number 2670 and press the ENTER key. After you press the ENTER key, cell
B4 will be activated. Using the ENTER key is an efficient way to enter data vertically
down a column.
3. Enter the following numbers in cells B4 through
B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.
4. Click cell location C3.
5. Type the number 9.99 and press the ENTER key.
6. Enter the following numbers in cells C4 through
C14: 12.49, 14.99, 17.49, 14.99, 12.49, 9.99, 19.99, 19.99, 19.99, 17.49, and 14.99.
7. Activate cell location D3.
8. Type the number 26685 and press the ENTER key.
9. Enter the following numbers in cells D4 through
D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416,
and 53370.
10. When finished, check that the data you entered matches

Fig 1.10 Enter data

Page 16 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1.3 Enter simple formula and function use cell reference
Create formulas
Formulas are one of the most commonly used features of Excel. They can be used to carry out
simple addition and subtraction or far more complex mathematical calculations
Mathematical operators
Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for
subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^)
for exponents.

All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is
equal to, the formula and the value it calculates.
The order of operations
Excel calculates formulas based on the following order of operations:
1. Operations enclosed in parentheses
2. Exponential calculations (3^2, for example)
3. Multiplication and division, whichever comes first
4. Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is PEMDAS,
or Please Excuse My Dear Aunt Sally.
Click the arrows in the slideshow below to learn how the order of operations is used to calculate
formulas in Excel.

Page 17 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Operators – Operator is a sign or symbol that specifies the type of calculation such as; Addition
(+), Subtraction (-) and Multiplication (*).

Arithmetic Operator

Text Operator
Operators

Comparison (Logical) Operators

Reference Operator
Arithmetic Operators
Operators Symbol Priority
Bracket operator () 1st
Exponential ^ 2nd
Multiplication Division *, / 3rd
Addition, Subtraction +, - 4th

Text Operators (&) :-is an ampersand symbol that used to concatenate or connect two pieces of
text values in to a single combined text value. i.e the two text operands should be enclosed with
double quotation marks.
Example “Micro” & “Soft” produce the concatenate word “Microsoft”
Comparison or Logical Operators is using comparison operators you can compare two or more
operands and produce the logical value True or False.

Page 18 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Operators Symbols
- Equal to------------------------------------------------------------------ =
- Grater than--------------------------------------------------------------- >
- Less than---------------------------------------------------------------- <
- Greater than or equal than-------------------------------------------- >=
- Less than or equal to ------------------------------------------------- <=
- Not equal to ----------------------------------------------------------- < >
Reference Operator - References are actual cell address in the worksheet.
The basic MS-Excel reference operators are;
: (Colon) – a range reference that refers all cells or sheets between and including the two
references specified.
, (Comma) – a union operator that combines multiple non-adjacent references in to one
references.
Single Space – an intersection operator that produces one reference from the cells or ranges that
have references in common.
!(Exclamation Mark) – Used to separate sheet names from cell references.

There are two types of cell references: relative and absolute. Relative and absolute references
behave differently when copied and filled to other cells. Relative references change when a
formula is copied to another cell. Absolute references, on the other hand, remain constant no
matter where they are copied.
A. To create and copy a formula using relative references:
In the following example, we want to create a formula that will multiply each item's price by
the quantity. Instead of creating a new formula for each row, we can create a single formula in
cell D4 and then copy it to the other rows. We'll use relative references so the formula calculates
the total for each item correctly.

Page 19 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1. Select the cell that will contain the formula. In our example, we'll select cell D4.

Fig1.11 select cell

2. Enter the formula to calculate the desired value. In our example, we'll type =B4*C4.

Fig 1.12 enter formula

3. Press Enter on your keyboard. The formula will be calculated, and the result will be
displayed in the cell.
4. Locate the fill handle in the bottom-right corner of the desired cell. In our example, we'll
locate the fill handle for cell D4.

Fig 1.13 Fill handle

5. Click and drag the fill handle over the cells you want to fill. In our example, we'll select
cells D5:D13.

Page 20 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig1.14 formula will be copy
6. Release the mouse. The formula will be copied to the selected cells with relative
references, displaying the result in each cell.

Fig 1.15 fill cells

You can double-click the filled cells to check their formulas for accuracy. The relative cell
references should be different for each cell, depending on their rows.

Fig 1.16 check formula accuracy

Page 21 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 B. Absolute references
There may be times when you do not want a cell reference to change when filling cells. Unlike
relative references, absolute references do not change when copied or filled. You can use an
absolute reference to keep a row and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($) before the
column and row. If it precedes the column or row (but not both), it's known as a mixed
reference.

You will use the relative (A2) and absolute ($A$2) formats in most formulas. Mixed references
are used less frequently.
When writing a formula in Microsoft Excel, you can press the F4 key on your keyboard to
switch between relative, absolute, and mixed cell references. This is an easy way to quickly
insert an absolute reference.
To create and copy a formula using absolute references:
In the example below, we're going to use cell E2 (which contains the tax rate at 7.5%) to
calculate the sales tax for each item in column D. To make sure the reference to the tax rate stays
constant—even when the formula is copied and filled to other cells—we'll need to make
cell $E$2 an absolute reference.
1. Select the cell that will contain the formula. In our example, we'll select cell D4.

Fig 1.17 select cell in absolute reference

Page 22 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2. Enter the formula to calculate the desired value. In our example, we'll type
=(B4*C4)*$E$2, making $E$2 an absolute reference.

Fig 1.18 enter formula

3. Press Enter on your keyboard. The formula will calculate, and the result will display in
the cell.
4. Locate the fill handle in the bottom-right corner of the desired cell. In our example, we'll
locate the fill handle for cell D4.

Fig 1.19 fill handle

5.Click and drag the fill handle over the cells you want to fill (cells D5:D13 in our example).

Fig 1.20 drag fill handle

Page 23 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 6.Release the mouse. The formula will be copied to the selected cells with
an absolute reference, and the values will be calculated in each cell.

Fig 1.21 calculated value

You can double-click the filled cells to check their formulas for accuracy. The absolute reference
should be the same for each cell, while the other references are relative to the cell's row.

Fig 1.22check the formula

Be sure to include the dollar sign ($) whenever you're making an absolute reference across
multiple cells. The dollar signs were omitted in the example below. This caused Excel to
interpret it as a relative reference, producing an incorrect result when copied to other cells.

Fig 1.23 dollar sign omitted

Page 24 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1.3.2 Function
A function is a predefined formula that performs calculations using specific values in a
particular order. Excel includes many common functions that can be used to quickly find
the sum, average, count, maximum value, and minimum value for a range of cells. In order to
use functions correctly, you'll need to understand the different parts of a function and how to
create arguments to calculate values and cell references.

In order to work correctly, a function must be written a specific way, which is called the syntax.
The basic syntax for a function is the equals sign (=), the function name (SUM, for example),
and one or more arguments. Arguments contain the information you want to calculate. The
function in the example below would add the values of the cell range A1:A20.

Arguments can refer to both individual cells and cell ranges and must be enclosed
within parentheses. You can include one argument or multiple arguments, depending on the
syntax required for the function.
For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the
cell range B1:B9. This function contains only one argument.

Fig 1.24 one argument

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3,
C1:C2, E1) will add the values of all of the cells in the three arguments.
Page 25 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.25 multiple argument
Creating a function
There are a variety of functions available in Excel. Here are some of the most common functions
you'll use:
 SUM: This function adds all of the values of the cells in the argument.
 AVERAGE: This function determines the average of the values included in the
argument. It calculates the sum of the cells and then divides that value by the number of
cells in the argument.
 COUNT: This function counts the number of cells with numerical data in the argument.
This function is useful for quickly counting items in a cell range.
 MAX: This function determines the highest cell value included in the argument.
 MIN: This function determines the lowest cell value included in the argument.
To create a function using the AutoSum command:
The AutoSum command allows you to automatically insert the most common functions into
your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In the example below,
we'll use the SUM function to calculate the total cost for a list of recently ordered items.
1. Select the cell that will contain the function. In our example, we'll select cell D13.

Fig 1.26 select cell

Page 26 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2. In the Editing group on the Home tab, click the arrow next to the AutoSum command.
Next, choose the desired function from the drop-down menu. In our example, we'll
select Sum.

Fig 2.27 edit group

3. Excel will place the function in the cell and automatically select a cell range for the
argument. In our example, cells D3:D12 were selected automatically; their values will
be added to calculate the total cost. If Excel selects the wrong cell range, you can
manually enter the desired cells into the argument.

Fig 1.28 automatically cell rang

4. Press Enter on your keyboard. The function will be calculated, and the result will
appear in the cell. In our example, the sum of D3:D12 is $765.29.

Page 27 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.29 Result
The AutoSum command can also be accessed from the Formulas tab on the Ribbon.

Fig 1.30 Auto sum

You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To use this
shortcut, hold down the Alt key and then press the equals sign.

1.4 Correct formulas

Sometimes Excel comes across a formula that it cannot calculate. When this happens, it displays
an error value. Error values occur because of incorrectly written formulas, referencing cells or
data that don’t exist, or breaking the fundamental laws of mathematics.
#### Error
The #### error occurs when the column isn't wide enough to fit the cell data.
1. Double-click the line to the right of the column letter for the column containing the error.

Page 28 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.31 column narrow width

The column width automatically resizes to fit the widest string of text in the column, thus fixing
the error.
To resize all columns in the sheet at once, click the Select All button in the upper-left of the
worksheet before resizing a column’s width.
NAME Error
You'll see the #NAME? error when the text in the formula isn't recognized. Sometimes it's easy
to figure out the error, but other times you'll need help to determine what’s happening. For this
example, you’ll use Excel’s Error Checking feature to help fix the problem.
1. Select the cell with the #NAME? error.
2. Click the Formulas tab.
3. Click the Error Checking button.

Page 29 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.32 error check button
The Error Checking dialog box opens. The left side of the dialog box shows the formula that’s
causing the error and gives a description of what’s happening.
4. Select an error checking option at the right and fix the error.
 Help on This Error: Displays information specific to the error type.
 Show Calculation Steps: Demonstrates all steps leading to the error.
 Ignore Error: Allows you to accept the formula as entered, without Excel
displaying the Error Checking Options smart tag.
 Edit in Formula Bar: Allows you to edit the formula that is generating the error
in the Formula Bar.
2. Close the dialog box.

Page 30 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.33 close dialog box

The #NAME? error in the cell is replaced with the corrected formula.
VALUE! Error
The #VALUE! error tells you there’s something wrong with the cells you’re referencing or with
the way the formula is typed. This is a very general error and it can be tricky to pinpoint the
cause of it. This example uses the Trace Precedents feature to help fix the error.
1. Select the cell with the #VALUE! error.
2. Click the Trace Precedents button on the Formulas tab.

Fig 1.34 click trace precedents

Page 31 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Trace Precedents shows dots that indicate which cells affect the value of the currently selected
cell. This helps to visually locate the error.
3. Locate the cell that’s causing the error.
4. Correct the formula in the formula bar.
5. Click or press Enter.

Fig 1.35 precedence show

The formula updates to display the correct result and the #VALUE! error disappears.
DIV/0! Error
You will see the #DIV/0! Error any time a number is divided by zero. This includes typing “/0”
in a formula or referencing a cell to divide by that contains 0 or is blank.
1. Select the cell with the error.
2. Click in the formula bar and fix the error.
3. Click or press Enter.

Page 32 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.36 click enter
The cell updates to the correct result and the #DIV/0! Error is fixed.
REF! Error
You will get the #REF! error when a formula references a cell that’s not valid. This often
happens when referenced cells get deleted or pasted over.
1. Select the cell with the #REF! error.
2. Click in the formula bar and fix the error.
3. Click or press Enter.

Fig 1.37 formula invalid

The cell reference is now valid and the #REF! error no longer displays.

Page 33 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1.5 Edite column and row within the spreadsheet
By default, every row and column of a new workbook is set to the same height and width. Excel
allows you to modify column width and row height in different ways, including wrapping
text and merging cells.
To modify column width:
In our example below, column C is too narrow to display all of the content in these cells. We can
make all of this content visible by changing the width of column C.
1. Position the mouse over the column line in the column heading so the cursor becomes
a double arrow.

Fig 1.38 mouse position

2. Click and drag the mouse to increase or decrease the column width.

Fig 1.39 click and drag mouse

3. Release the mouse. The column width will be changed.

Page 34 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.40 column width change
With numerical data, the cell will display pound signs (#######) if the column is too narrow.
Simply increase the column width to make the data visible.
1.6 use AutoFill function to increment data in Excel

You will learn how to fill down series of numbers, dates and other data, create and use
custom lists in Excel.

Fig 1.41 fill down series

Page 35 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 1.7 Save spreadsheet
It's important to save your workbook whenever you start a new project or make changes to an
existing one. Saving early and often can prevent your work from being lost.
1. Locate and select the Save command on the Quick Access Toolbar.

Fig 1.43 file tab

2. If you're saving the file for the first time, the Save As pane will appear
in Backstage view.
3. You'll then need to choose where to save the file and give it a file name. To save the
workbook to your computer, select Computer, then click Browse. Alternatively, you can
click OneDrive to save the file to your OneDrive.

Fig1.42 save as pan

4. The Save As dialog box will appear. Select the location where you want to save the
workbook.
5. Enter a file name for the workbook, then click Save.

Page 36 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 1.44 dialog box
6. The workbook will be saved. You can click the Save command again to save your
changes as you modify the workbook.
You can also access the Save command by pressing Ctrl+S on your keyboard.

Page 37 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Self-check-1
Test-I Multiple choices.
Instruction: Answer the following questions and put your answer.
I. Choose the correct answer from the given alternatives
1. Star Office Calc has a variety of
A. Buttons C. Options
B. Functions D. All
2. The cells are addressed in terms of
A. Row and column labels C. Rows
B. Row labels D. Columns
3. Which of the following data can be typed into a Spreadsheet cell?
(A) Formulae (C) Numbers
(B) Text (D) All of these
4. Which bar is used to display options?
(A) Menu (C) Formula
(B) Function (D) Status
5. The intersection of rows and columns creates
(A) Cells
(B) Worksheets
(C) Spreadsheets
(D) None of these
6.A group of cells is called a ________.
A
. cell cluster B. multicell
C. chart D. cell range
7.The intersection of a row and a column is called a ________.
A.
chart B. worksheet
C. sum D. cell

Page 38 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Operation title 1: Create spreadsheet
Purpose: -
 To familiarize with Microsoft excel 2016 environment.
 To know how to work formula, function, edit column and row and enter data into cell.
Instruction: The Given necessary equipment, tools and materials you are follow the
necessary steps and use the given figure below (1.44), operate each task. You have given
1:30 hour for the task and you are expected to write the answer task.

Fig 1.44 payroll

Task1: Open a new workbook and save the file with the name “Payroll”.
Task2: Enter the column/labels and values in the exact cells locations as desired.
Task3: Use AutoFill to put the Employee Numbers into cells A6:A8.
Task4: Set the columns width and rows height appropriately.
Task5: Set labels alignment appropriately.
Task6: Use warp text and merge cells as desired.
Task7: Apply borders, gridlines and shading to the table as desired.
Task8: Format cell B2 to Short Date format.
Task9: Format cells E4:G8 to include dollar sign with two decimal places.
Task10: Calculate the Gross Pay for employee; enter a formula in cell E4 to multiply Hourly
Rate by Hours Worked.

Page 39 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Task11: Calculate the Social Security Tax (S.S Tax), which is 6% of the Gross Pay; enter
a formula in cell F4 to multiply Gross Pay by 6%.
Task12: Calculate the Net Pay; enter a formula in cell G4 to subtract Social Security Tax from
Gross Pay.
13. Set the work sheet vertically and horizontally on the page.
14. Save your work.

Tools and requirement: - ICT room, computer, Printer, A4 paper, Mouse and keyboard,
Monitor, Basic Software, Documents and pen/pencil.
Precautions: Microsoft office is install.
Procedures:-in doing the task
Step-1: Click on start → All Application→Click Microsoft office excel 2016 → click blank
document
Step-2: enter data in to cell
Step-3: based on figure enter data each cell
Step-4: based on figure change format
Step-5: based on figure create formula and function
Step-6: the error is occurred to correct
Step-7: save the file based on the given
Quality Criteria: based on the figure check the task are properly done.

Page 40 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 LAP Test 3 Practical Demonstration
Instruction: The given necessary equipment, tools and materials you are follow the necessary
steps and use the given figure (1.)operate each task. You have given 1:30 hour for the task and
you are expected to write the answer.

Fig 1.45 panda EST

Task1: Create the worksheet shown above.
Task2: Set the column widths as follows: Column A: 8, Column B: 14, Columns C & D: 15,
Columns E & F: 14.
Task3: Enter the formula to find COMMISSION for the first employee.
The commission rate is 2% of sales, COMMISSION = SALES * 2%
Copy the formula to the remaining employees.
Task4: Enter the formula to find TOTAL SALARY for the first employee where:
TOTAL SALARY = SALARY + COMMISSION
Copy the formula to the remaining employees.
Task5: Enter formula to find TOTALS, AVERAGE, HIGHEST, LOWEST, and COUNT
values.
Copy the formula to each column.
Task6: Format numeric data to include commas and two decimal places.
Task7: Align all column title labels horizontally and vertically at the center.
Task8: Create a Header that includes your name in the left section, page number in the

Page 41 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 center section, and your ID number in the right section.
Task9: Create footer with DATE in the left section and TIME in the right section.
Task10: Save the file with name “LAB1”

Unit Two: Customize basic settings

This unit to provide you the necessary information regarding the following content coverage
and topics:
 Adjust page layout
 Open and view different toolbars
 Change font settings
 Change alignment and line spacing
 Modify margin sizes
 View multiple spreadsheets concurrently
This guide will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Adjust page layout to meet user requirements or special needs
 Open and view different toolbars
 Change font settings so that they are appropriate for the purpose of the document
 Change alignment options and line spacing according to spreadsheet formatting features
 Modify margin sizes to suit the purpose of the spreadsheets
 View multiple spreadsheets concurrently

Page 42 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2.1 Adjust page layout
In Microsoft Excel there are several ways you can alter and edit the layout your work sheet.to
begin, it is advisable to view your worksheet page layout view as this shows the margin and ruler
as well as how the worksheet will appear when printed.

Fig 2.1 page layout tab

2.2 Open and view different toolbars

Fig 2.2 toolbar interface

The Toolbar is an area where you can add different commands or tools associated with excel. By
default, it is located above the ribbon with different tools and visible in the Excel window’s
upper right corner. To increase customer friendliness, toolbars have become customizable

Page 43 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 according to the frequent use of different tools. Instead of a set of tools, excel gives us the option
to select and build a Quick Access Toolbar

It is a symbolical representation of built-in options available in Excel. By default, it contains the

below commands.

1. Save: To save the created workbook.

2. Undo: To return or step back one level of an immediate action performed.

3. Redo: Repeat the last action.

Use the Toolbar in Excel

The Toolbar in Excel is a shortcut tool to avoid searching for the commands you often use in the
worksheet. Using Toolbar in Excel is easy, and it helps us simplify access to the document’s
commands. Let’s understand the working of the Toolbar in Excel by some examples given
below.

Adding Commands to the Toolbar in Excel

To get more tools, you have the option to customize the Quick Access Toolbar simply by adding
the commands.

 Click on the downward-facing arrow at the end of the Toolbar in Excel. A pop up will be
shown as Customize Quick Access Toolbar.

Fig 2.3 custom quick access toolbar

 From the dropdown, you will get a list of commonly used commands. Click any of the
options that you want, and it will be added to the toolbar.

Page 44 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 2.4 custom quick access list

 A new command is selected, and this will be added to the toolbar highlighted as the
command is added with already available tools.

In a similar way, you can add the tools which you want to access quickly. So instead of clicking
and finding the tools from the multiple hierarchies, you can access the option within a single
click.

2.3 Change font settings

In Microsoft Excel, a user can change the properties of text in any cell, including font type, size,
color, and make it bold, italic, or underlined. They can also change the color of a cell's
background and the border around a cell. The following picture is a graphic illustration of the
font and cell format bar in Excel with a description of each option.

Font is a general computer term and refers to the style, size and colour of the text and numbers
in your worksheet.

Page 45 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2.3.1 Changing font type

Fig 2.5 font and cell format

To change the text font in a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the font field on the format bar. (If you want to change the
font to bold, italic, or underlined, click the B, I, or U on the format bar.)
3. After clicking the down arrow for the font, select from each of the installed fonts on your
computer. Click the font you want to use, and the text in the selected cell changes.

Fig 2.6 font type

2.3.2 Changing font size
To change the text size in a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the size box on the format bar. Usually, the default size is
11 or 12, as shown in the image.
3. After clicking the down arrow for the size, there is a selection of different sizes to choose.
Some fonts may not scale properly, so they may have limited size options.
Page 46 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 2.7 font size
2.3.3 Changing font color
To change the text color in a Microsoft Excel spreadsheet, follow the steps below.
1. Select the cell containing the text you want to change.
2. Click the down arrow next to the text color icon. It is usually displayed as the letter "A"
with a red underline, as shown in the image.
3. After clicking the down arrow for the text color, select the color you want to make the
text. If you want a different color than is available in the drop-down menu, click the More
Colors option (indicated by green arrow). Select the desired color in the Colors window,
and click OK.

Fig 2.8 font color

2.3.4 Changing cell background color

To change the cell background color within a Microsoft Excel spreadsheet, follow the
steps below.
1. Click the down arrow next to the cell color icon. It is usually displayed as tipping paint
can with a yellow underline, as shown in the image.

Page 47 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2. After clicking the down arrow for the cell color, select the color you want to make the
cell background. If you want a different color than is available in the drop-down menu,
click the More Colors option (indicated by green arrow). Select the desired color in
the Colors window, and click OK.

Fig 2.9 Background color

Changing cell border
By default, a cell does not have a border. To change the cell border in a Microsoft Excel
spreadsheet, follow the steps below.
1. Select the cell you want to add a border.
2. Click the down arrow next to the cell border icon. It is usually displayed as a four-pane
window, as shown in the image above.
3. After clicking the down arrow for the cell border, select the border type you want to set
for the cell.

Fig 2.10 border

Page 48 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 4. The cell border color, click the Line Color option at the bottom of the drop-down menu.
5. To change the cell border type, click the Line Style option at the bottom of the drop-
down menu
2.4 Change alignment and line spacing Text alignment
By default, any text entered into your worksheet will be aligned to the bottom-left of a
cell, while any numbers will be aligned to the bottom-right. Changing the alignment of
your cell content allows you to choose how the content is displayed in any cell, which
can make your cell content easier to read.

Click the arrows in the slideshow below to learn more about the different text alignment options.

Left Align: Aligns content to the left border of the cell

Center Align: Aligns content an equal distance from the left and right borders of the cell

Right Align: Aligns content to the right border of the cell

Page 49 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Top Align: Aligns content to the top border of the cell

Middle Align: Aligns content an equal distance from the top and bottom borders of the cell

Bottom Align: Aligns content to the bottom border of the cell

 You can apply both vertical and horizontal alignment settings to any cell.

2.5 Modify margin sizes

A margin is the space between your content and the edge of the page. Sometimes you may
need to adjust the margins to make your data fit more comfortably. You can modify page
margins from the Print pane.
1. Navigate to the Print pane.
2. Select the desired margin size from the Page Margins drop-down menu. In our example,
we'll select Narrow Margins.

Page 50 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Fig 2.11 print pane
3. The new page margins will be displayed in the Preview pane.

You can adjust the margins manually by clicking the Show Margins button in the lower-right
corner, then dragging the margin markers in the Preview pane.

Page 51 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2.6 View multiple spreadsheets concurrently
View multiple spreadsheet concurrent
You can also arrange multiple worksheets to view them all at the same time.
View two worksheets in the same workbook side by side
1. On the View tab, in the Window group, click New Window.

2. On the View tab, in the Window group, click View Side by Side .
3. In each workbook window, click the sheet that you want to compare.

4. To scroll both worksheets at the same time, click Synchronous Scrolling in

the Window group on the View tab.
View two worksheets of different workbooks side by side
1. Open both of the workbooks that contain the worksheets that you want to compare.

2. On the View tab, in the Window group, click View Side by Side .

If you have more than two workbooks open, Excel displays the Compare Side by
Side dialog box. In this dialog box, under Compare Side by Side with, click the workbook
that contains the worksheet that you want to compare with your active worksheet, and then
click OK.
3. In each workbook window, click the sheet that you want to compare.

4. To scroll both worksheets at the same time, click Synchronous Scrolling in

the Window group on the View tab.
View multiple worksheets at the same time
1. Open one or more workbooks that contain the worksheets that you want to view at the
same time.

Page 52 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 2. Do one of the following:
 If the worksheets that you want to view are in the same workbook, do the
following:
i. Click a worksheet that you want to view.
ii. On the View tab, in the Window group, click New Window.

iii. Repeat steps 1 and 2 for each sheet that you want to view.
 If the worksheets that you want to view are in different workbooks, continue with
step 3.
3. On the View tab, in the Window group, click Arrange All.
4. Under Arrange, click the option that you want.
5. If the sheets that you want to view are all located in the active workbook, select
the Windows of active workbook check box.

Page 53 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
 Self -check 2
Test-I Multiple choices
Instruction: Answer the following question and put your answer
1.Which bar is used to display options?
A. Menu B. Function
C. Formula D. Status
2.-------------- is the space between your content and the edge of the page.
A. page layout B. margin C. print pane D. none

Page 54 of 89 Ministry of Labor and Skills Operate Spreadsheet Application Date: September 2022
Version: 1
Author/Copyright
Unit Three: Format spreadsheet
This unit to provide you the necessary information regarding the following content coverage
and topics:
 Use format features and tools
 Insert headers and footers
This guide will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Use format features and tools
 Insert headers and footers

3.1 Use format features and tools

 Formatting in Excel means a trick that we can use to modify the data's appearance in a worksheet.
We can format the data in various ways, like we can format the font of the cells or the table with the
help of the styles and format tab present in the Home tab.

Excel formatting is an optional step following data preparation, or all of the data cleansing,
structuring, enriching, and standardizing necessary to prepare the data for analysis

format cells is used to modify the formatting of cell numbers without modifying the actual number.
With the help of the format cells, we can change the number, alignment, font style, Border style,
Fill options, and Protection.

Good formatting will improve our data in various ways:

 With the help of the formatting, we can present our data correctly; for example, formatting
as dates or currency will provide more value to our data.

 Merging and aligning our data is a vital aspect of making our data more readable.

 Formatting our text by increasing the Size, bolding, adding italics, or changing the
fonts will improve the overall appearance of our worksheet.

 Using styles (like table styles) can make our data stand out and helps the reader to focus on
crucial portions of the worksheet.

 Conditional formatting is a useful tool for highlighting crucial portions of our worksheet
graphically or visibly. These are dynamic tools. The Highlighted region changes as our data
changes.

Benefits of Data Formatting in Excel

The following are some of the benefits of Excel data formatting:

 The data appears to be more presentable.

 Data formatting saves a lot of time and effort.

 With the help of the chart, we can analyze the data.

 With the help of the formatting, we can highlight specific data such as profit or loss in
business. Now,
Page 56 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September
Author/Copyright Application 2022
Version 1
 In order to format data in Excel, we'll do the following things:

 The Font size is larger.

 We make the text of the column head bold

 Center aligning the data

 We will apply the outline border with the help of the shortcut that is (Alt+H+B+T),

 After selecting the full table (using Ctrl+A), use the shortcut key (Alt+H+O+I) to adjust the
column width.

 To alter the background, use the 'Fill Color' command in the 'Font' group on 'Home'

3.2 Insert headers and footers

The purpose of Header and Footer in Excel
The purpose is similar to that of hard copy documents or books. The headers and footers in Excel
help meet the standard representation format of the documents and/or worksheets. In addition, they
add a sense of organization to the soft documents and/or worksheets.

Fig 3.1 header and footer tools

3.1.1 Create a Header in Excel

Step1.First, click the worksheet where we want to add or change the header. Then, go to the “Insert
tab” -“Text” group – “Header & Footer.”

Page 57 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 3.2 header and footer
Step2.Clicking on it would open a new window, as shown below.

Fig 3.3 header and footer text

Step3.As shown in the screenshot below, “Header & Footer Tools” has a “Design” tab containing
various text options to put as the header. The default is an empty text box wherein we can enter a
free text, e.g., “This is the header text.” The other options are “Page Number,” “Number of Pages,”
“Current Date,” “Current Time,” “File Path,” “File Name,” “Sheet Name,” “Picture,” etc.

Fig 3.4 header name

Page 58 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 3.1.2 Create Footer in Excel
Step1.We must first click the worksheet where we want to add or change the header. Then, go to the
“Insert” tab -> “Text” group -> “Header & Footer.”

Fig 3.5 header and footer

Step2.Clicking on it would open a new window, as shown. As shown in the screenshot below,
“Header & Footer Tools” has a “Design” tab containing various text options to put as the header.
The default is an empty text box wherein you can enter a free text, e.g., “This is the Footer text.” The
other options are “Page Number,” “Number of Pages,” “Current Date,” “Current Time,” “File Path,”
“File Name,” “Sheet Name,” “Picture,” etc.

Fig 3.6 footer name

Remove Header and Footer in
Step1.We must first launch the “Page Setup” dialog box from the “Page Setup” box under the “Page
Layout” menu.

Page 59 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig3.7 page setup
 Then, go to the “Header/Footer” section.

Fig 3.8 header and footer section

 Select ‘none’ for “Header” and/or “Footer” to remove the respective feature.

Page 60 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Self-check-3

Test I: short Answer writing

Instruction: write short answer for the given question. You are provided 2minutes for each
question and each point has 3 Points.

1.Write the purpose of header and footer in excel?

2.what is formatting in excel?

3.write Benefits of data formatting?

4.write good way of improve formatting?

Page 61 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Operation title 3: Format spreadsheet
Purpose: -
 To familiarize with Microsoft excel 2016 environment.
 To know how to work format cell, enter data and insert header and footer in spreadsheet.
Instruction: The Given necessary equipment, tools and materials you are follow the necessary steps
and operate each task. You have given 1:30 hour for the task and you are expected to write the
answer task.
Task1:Use the given figure below (3.9),and based on the use of following information format
cell(number=General, text alignment=center, font type=caliberia,font styl=Bold,font size=15 and
font color=15).For this operation you have given 1 hour and you are expected to
provide the answer on the given task.
Tools and requirement: - ICT room, computer, Printer, A4 paper, Mouse and keyboard, Monitor,
Basic Software, Documents and pen/pencil.
Precautions: Microsoft office is install.
Procedures:-in doing the task
Step-1: Click on start → All Application→Click Microsoft office excel 2016 → click blank
document
Step-2: enter data in to cell
Step-3: edit column and row
Step-4: To change the format
Step-5: insert header and footer
Step-6: Remove header and footer
Step-7: custom header and footer
Step-8: To use a custom page size.
Step-11: save as you want location and name
Quality Criteria: based on the given information the document is done

Page 62 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 3.9 excel document

Page 63 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 LAP Test 3 Practical Demonstration

Instruction I: Given necessary equipment, tools and materials you are required to perform the
following tasks within 1hours.
Instruction: The given necessary equipment, tools and materials you are follow the necessary steps
and operate each task. You have given 1hour for the task and you are expected to write the answer
Task1: To open MS office excel 2016 then Create a new blank document and save it as name
format_spreadsheet on your desktop. Use of following information column header name
(Id,Tname,sex,age,phone number,address & date of birth),header name=”WDDA” and footer
name=”Next”format cell(number=General, text alignment=cent, font type=caliberia,font
styl=Bold,font size=20 and font color=green) and for column header name use appropriate change
format then based on the given information to prepare the document,Remove header and footer
custom header and footer. For this operation you have given 1:30 hour and you are expected to
provide the answer on the given.

Unit Four: Incorporate object and chart in spreadsheet

This unit to provide you the necessary information regarding the following content coverage
and topics:

Page 64 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
  Import an object into spreadsheet
 Create a chart using selected data in the spreadsheet
 Display selected data in a different chart
 This guide will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Import an object into spreadsheet
 Create a chart using selected data in the spreadsheet
 Display selected data in a different chart

4.1 Import an object into spreadsheet

In Microsoft Excel, the “Object Insert” option allows a user to insert an external object into a
worksheet. Embedding generally means inserting an object from another software (Word, PDF, etc.)
into an Excel worksheet. This option is useful for direct access to files related to your worksheet data
from within your worksheet space. In addition, the inserted object works as a ready information
source or reference for a dataset in an Excel worksheet.

Page 65 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Insert (Embed) an Object in Excel

1. We must first select “Text” from the “Insert” tab and then click “Object.”

Fig 4.1 object tab

2. Then, select “Create New” to embed a new blank file. Remember, only file types are shown
in “Object type” that can be embedded in the Excel worksheet.

Fig 4.2 object type

3. Now, choose a file type to embed. For example, we have selected a “Microsoft Word
Document,” now click “OK.”

Fig 4.3 display as icon

Page 66 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September
Author/Copyright Application 2022
Version 1
 Step 1: Select “Text” from the “Insert” tab and click “Object.”

Fig 4.4 text

Step 2: Select “Create from File” and click “Browse” to embed an existing file into the worksheet.
Remember, only file types are shown in “Object Type” that can be embedded in the Excel
worksheet.

Fig 4.5 browse

Step 3: Now click “OK.” The file icon will be displayed on the worksheet.

Fig 4.6 object icon

Page 67 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 NOTE: If we do not check the “Display as icon” option, it will show the embedded file’s content
instead of the icon. We can double-click on the embedded file icon or content to edit the content of
the embedded file.

Fig 4.7 display icon

4.2 Create a chart using selected data in the spreadsheet

Excel has several different types of charts, allowing you to choose the one that best fits your
data. In order to use charts effectively, you'll need to understand how different charts are used.

Fig 4.8type of chart

Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types
of charts available in Excel.

Page 68 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.9 column chart
Column charts use vertical bars to represent data. They can work with many different types of data, but they're most
frequently used for comparing information.

Fig 4.10 line chart

Line charts are ideal for showing trends. The data points are connected with lines, making it easy
to see whether values are increasing or decreasing over time.

Page 69 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.11 pie chart
Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's
easy to see which values make up the percentage of a whole.

Fig 4.4 bar chart

Bar charts work just like column charts, but they use horizontal rather than vertical bars.

Fig 4.12 area chart

Page 70 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Area charts are similar to line charts, except the areas under the lines are filled in.

Fig 4.13 surface are

Surface charts allow you to display data across a 3D landscape. They work best with large data
sets, allowing you to see a variety of information at the same time.

In addition to chart types, you'll need to understand how to read a chart. Charts contain several
different elements, or parts, that can help you interpret the data.

Fig 4.15 insert data in to worksheet

To insert a chart:
1. Select the cells you want to chart, including the column titles and row labels. These cells will be the source
data for the chart. In our example, we'll select cells A1:F6.

Page 71 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.16 source data
2. From the Insert tab, click the desired Chart command. In our example, we'll select Column.

Fig4. 17 chart command

3. Choose the desired chart type from the drop-down menu.

Fig 4.18 chart type

4. The Selected chart will be inserted into the worksheet.

Fig 4.19 select chart on worksheet

Page 72 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 If you're not sure which type of chart to use, the Recommended Charts command will suggest
several different charts based on the source data.

Fig 4.20 Recommended chart

Chart and layout style

After inserting a chart, there are several things you may want to change about the way your data
is displayed. It's easy to edit a chart's layout and style from the Design tab.
 Excel allows you to add chart elements—such as chart titles, legends, and data labels—to
make your chart easier to read. To add a chart element, click the Add Chart
Element command on the Design tab, then choose the desired element from the drop-down
menu.

Fig 4.21 chart chart layout

To edit a chart element, like a chart title, simply double-click the placeholder and begin typing.

Page 73 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.22 chart element

If you don't want to add chart elements individually, you can use one of Excel's predefined layouts.
Simply click the Quick Layout command, then choose the desired layout from the drop-down
menu.

Fig 4.23 Quick layout

Excel also includes several chart styles, which allow you to quickly modify the look and feel of
your chart. To change the chart style, select the desired style from the Chart styles group. You can
also click the drop-down arrow on the right to see more styles.

Fig 4.24 Chart style

You can also use the chart formatting shortcut buttons to quickly add chart elements, change
the chart style, and filter the chart data.

Page 74 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.25 add chart element
There are many other ways to customize and organize your charts. For example, Excel allows
you to rearrange a chart's data, change the chart type, and even move the chart to a different
location in a workbook.
To switch row and column data:
Sometimes you may want to change the way charts group your data. For example, in the chart
below Book Sales data is grouped by genre, with columns for each month. However, we could
switch the rows and columns so the chart will group the data by month, with columns for each
genre. In both cases, the chart contains the same data—it's just organized differently.

Page 75 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 1. Select the chart you want to modify.
2. From the Design tab, select the Switch Row/Column command.

Fig 4.26 switch row /column

3. The rows and columns will be switched. In our example, the data is now grouped by month,
with columns for each genre.

To change the chart type:

If you find that your data isn't well suited to a certain chart, it's easy to switch to a new chart
type. In our example, we'll change our chart from a column chart to a line chart.
1. From the Design tab, click the Change Chart Type command.

Page 76 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Fig 4.26 chart type command
2. The Change Chart Type dialog box will appear. Select a new chart type and layout, then
click OK. In our example, we'll choose a Line chart.

Fig 4.27 chart type dialog box

3. The selected chart type will appear. In our example, the line chart makes it easier to see
trends in sales data over time.

Page 77 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 To move a chart:
Whenever you insert a new chart, it will appear as an object on the same worksheet that contains
its source data. Alternatively, you can move the chart to a new worksheet to help keep your data
organized.
1. Select the chart you want to move.
2. Click the Design tab, then select the Move Chart command.

Fig 4.28 move chart command

3. The Move Chart dialog box will appear. Select the desired location for the chart. In our
example, we'll choose to move it to a New sheet, which will create a new worksheet.
4. Click OK.

Fig 4.29 move chart

5. The chart will appear in the selected location. In our example, the chart now appears on a
new worksheet.

Fig 4.30 new worksheet chart

Page 78 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
 Self-check-4
Test-I Multiple choices
Instruction: Answer the following questions and put your answer.
1. What do you use to create a chart?
A. Pie Wizard C. Data Wizard
B. Excel Wizard D. Chart Wizard
2. How will you graphically represent expenditure in different departments ?
A. Column Chart C. Pie Chart
B. Line Chart D. Dot Char
3. What type of chart is good for single series of data ?
A. Column Chart C. Pie Chart
B. Line Chart D. Cone Chart
4. What type of chart will you use to compare performance of sales of two products ?
A. Column Chart
B. Line Chart
C. Pie Chart
D. Both A and B

Page 79 of 89 Ministry of Labor and Skills Operate Spreadsheet Date: September

Author/Copyright Application 2022
Version 1
Operation title 4.1 Import an object into spreadsheet
Purpose: -
 To know how to work object import into the worksheet
Instruction: The Given necessary equipment, tools and materials you are follow the necessary
steps and operate each task. You have given 30 mint for the task and you are expected to write
the answer task.
Task1: open a new workbook and save the file with the name “object”to import object into the
excel. For this operation you have given 1 hour and you are expected to provide the answer on
the given task.
Tools and requirement: - ICT room, computer, Printer, A4 paper, Mouse and keyboard, Monitor,
Basic Software, Documents and pen/pencil.
Precautions: import object into excel.
Procedures:-in doing the task
Step-1: Click on start → All Application→Click Microsoft office excel 2016 → click blank
document
Step-2: click insert
Step-3: click text
Step-4: select object create new document or from existing file.
Step-4: display as icon ----ok
Operation title 4 import an object into spreadsheet
Purpose: -
 To familiarize type of chart.
 To know how to work create a chart select data into spreadsheet.
Instruction: The Given necessary equipment, tools and materials you are follow the
necessary steps and operate each task. You have given 1 hour for the task and you are
expected to write the answer task.
Task1:Use the given figure below (3.17) ,open a new workbook and save the file with the
name “chart” to prepare the document then create a chart select data into spreadsheet by all
type of chart. For this operation you have given 1 hour and you are expected to provide the
answer on the given task.
Tools and requirement: - ICT room, computer, Printer, A4 paper, Mouse and keyboard,
Monitor, Basic Software, Documents and pen/pencil.
Precautions: Microsoft office excel is install.
Procedures:-in doing the task
Step-1: Click on start → All Application→Click Microsoft office excel 2016 → click blank
document
Step-2: select the cell you want to chart
Step-3: from insert click chart command
Step-4: choose chart type
Step-5: select chart will be inserted into worksheet

Fig 4.31 excel document

 LAP Test 3 Practical Demonstration

Instruction I: Given necessary equipment, tools and materials you are required to perform
the following tasks within 1hours.
Instruction: The given necessary equipment, tools and materials you are follow the necessary
steps and operate each task. You have given 1hour for the task and you are expected to write
the answer
Task1: To open MS office excel 2016 then Create a new blank document and save it as
name object_chart on your desktop. Use of the following information column name
(Id,Tname,sex,age,phone number,address & date of birth),header name=”WDDA” and footer
name=”Next”format cell(number=General, text alignment=cent, font type=caliberia,font
styl=Bold,font size=20 and font color=green) and for column header name use appropriate
change format then based on the given information to prepare the document,Remove header
and footer custom header and footer. For this operation you have given 1:30 hour and you are
expected to provide the answer on the given.
Task2:based on the task1 information to create a chart by using all type of chart use select
data into spreadsheet.
Task3:Adjust chart layout style
Task3:change column chart into line chart
 Unit Five: Print spreadsheet
This unit to provide you the necessary information regarding the following content coverage
and topics:
 Previewing spreadsheet
 Selecting basic printer options
 Printing spreadsheet
This guide will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Previewing spreadsheet in print preview mode
 Selecting basic printer options
 Printing spreadsheet or selected part of spreadsheet

5.1 Previewing spreadsheet

There may be times when you want to print a workbook to view and share your data offline.
Once you've chosen your page layout settings, it's easy to preview and print a workbook from
Excel using the Print pane.
1. Select the File tab. Backstage view will appear.

Fig 5.1 file tab

2. Select Print. The Print pane will appear.
 Fig 5.2 Backstage
Click the buttons in the interactive below to learn more about using the Print pane.

Fig 5.3 print pan

Show Margins / Zoom to Page
The Zoom to Page button on the right will zoom in and out in the Preview pane.
The Show Margins button on the left will show the margins in the Preview pane.
5.2 Selecting basic printer options to printing spreadsheet
1. Navigate to the Print pane, then select the desired printer.

Fig select printer name

2. Enter the number of copies you want to print.

Fig 5.5 enter number of copy

3. Select any additional settings if needed (see above interactive).

4. Click Print.
Fig 5.6 click print
Self-check-5
Test-I Multiple choices
Instruction: Answer the following questions and put your answer.
1. To print a document, press ___
A. Ctrl+ P
B. Alt + p
C. Tab+ P
D. None of these
2. To see the document before the printout is taken, use
A. Print Preview
B. Format pointer
C. Cut
D. Paste
 Reference
Book (download from google.scholar)
1. QuickStart Guide - From Beginner to Expert (Excel, Microsoft Office Series) by William Fischer, 2016,
1533137951, 9781533137951
2. The Unofficial Guide to Microsoft Office Excel 2007 - Page 55
JuliaKelly, Curt Simmons · 2007
3. Building Financial Models with Microsoft Excel: A Guide for ...
K. Scott Proctor · 2004
4. Excel 2019 Power Programming with VBA - Page 181
Michael Alexander, Dick Kusleika · 2019
5. Excel 2013 Power Programming with VBA - Page 315
John Walkenbach · 2013
6. Excel Formulas and Functions 2020: The Step by Step Excel ...
Adam Ramirez · 2020 ·
Website Link

1. https://spreadsheetdaddy.com/excel/how-to-change-line-spacing
2. https://www.examtiger.com/mcq/excel-sample-mcq-online-gk-test/page/3/
3. https://www.informit.com/articles/article.aspx?p=1326489&seqNum=4
4. https://support.microsoft.com/en-us/office/ways-to-format-a-worksheet-d5efbdb5-b79b-475a-8c56-
99aad944b030
5. https://turbofuture.com/computers/How-to-Format-Spreadsheets-in-Microsoft-Excel
6. https://www.venasolutions.com/blog/financial-planning-analysis/how-to-format-your-excel-spreadsheet-
10-tips
7. https://meritnotes.com/computer-quiz/spreadsheet-mcq/3-633/
8. https://support.microsoft.com/en-us/office/change-line-spacing-8690fcda-1a4c-41fe-bf43-439f7c5d3c7a
 Participants of this Module (training material) preparation

N Name Leve Field of Organization/ Mobile E-mail

o l Study Institution number

1 Abel A Computer MOLS 091177672 Ab.smart99@gmail.com

G/Egziabhe Science 8
r

2 Endalew A IT Debremarkos 091330545 crouchkecho@gmail.com

Kassa PTC 4

3 Frew Atkilt A Network & Bishoftu PTC 091178737 Frew_at@gmail.com

Informatio 4
n Security

3 Getnet B IT Nefasmewuch 092255090 Getnetalemu783@gmail.co

Alemu a PTC 6 m

4 Remedan A ICT Harar PTC 091347893 remedanm77@gmail.com

Mohammed 7