Prepared by:

DONNAH B. EBUENG
Teacher III

0
 Module 1
INTRODUCTION ON SPREADSHEET APPLICATION

GOALS
This module is intended for you to explore the user interface of Microsoft Excel.
1
At the end of the module you are expected to:
1. Identifying tasks that requires using the application.
2. Launching and closing the application.
3. Recognizing the elements of the application window and their uses.

What is it

Introduction
 Excel is a spreadsheet program that allows you to store, organize, and analyze
information. It is a software program that enables you to manipulate
computations. It allows user to electronically create large spreadsheets and
numbers and formulas to quickly perform mathematical, financial, and statistical
charts, perform calculations, and organize data.
TECHNICAL TERMS:
SPREADSHEET - a type of software used for evaluation information.
WORKSHEET – page in a workbook
WORKBOOK – compose of different worksheets Active
Cell – a cell where row and column intersect.
Values – numbers that represent quantity.
Chart – a graphic representation of data.

The Excel Interface

When you open Excel for the first time, the Excel Start Screen will appear. From
here, you will 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.

1
 Parts of MS Excel Interface:
Quick Access Toolbar it will display frequently used commands. It will serve as your
shortcut to different commands.
Ribbon is the default tabs displayed once you open the MS Excel program.
Cell is a box in which you enter your spreadsheet data. There are 17 billion cells in each
worksheet.
Rows are horizontal line of cells. Each row has a number that identifies it. MS Excel has
1 through 1,048,576 rows.
Columns a vertical line of cells. Each column has a letter that identifies it. MS Excel has
16, 384 columns. It holds letter A through XFD.
Cell Address – It is composed of column letter and row number intersecting to determine
its own address. The cell address can be found in the name box. Name box is above the
column A.
Range – It is a rectangular grouping of two or more cells. The range address is given by
the address of the top left cell and address of the bottom right cell.
Example I1:I15
Scroll Bar – The bar used to scroll around the workbook. A workbook contains two –
horizontal and vertical – scroll bars.

2
 Zoom Slider – Allows you to quickly zoom in or zoom out of the worksheet.
Formula Bar – A place where you can enter or view formulas or text.
Sheet Tabs – By default, it is named Sheet1, Sheet2, and Sheet3. A selected worksheet is
an active worksheet.
 Worksheet can be inserted, deleted, renamed, moved, copied, reordered, protected, and
colored.

Working with the Excel environment

The Ribbon and Quick Access Toolbar are where you will find the commands to perform
common tasks in Excel. The Backstage view gives you various options for saving,
opening a file, printing, and sharing your document.

The Ribbon

Excel uses a tabbed Ribbon system instead of traditional menus. The

Ribbon contains multiple tabs, each with several groups of commands. You will use these
tabs to perform the most common tasks in Excel.

• Each tab will have one or more groups.

• Some groups will have an arrow you can click for more options.

• Click a tab to see more commands.

• You can adjust how the Ribbon is displayed with the Ribbon Display
Options.

3
To change the Ribbon Display Options:

The Ribbon is designed to respond to your current task, but you can choose to
minimize it if you find that it takes up too much screen space. Click the Ribbon Display
Options arrow in the upper-right corner of the Ribbon to display the drop-down menu.

There are three modes in the Ribbon Display Options menu:

• Auto-hide displays your workbook in full-screen

Auto-hide Ribbon:
mode and completely hides the Ribbon. To show the Ribbon, click the
Expand Ribbon command at the top of screen.

• Show Tabs: This option hides all command groups when they are not
in use, but tabs will remain visible. To show the Ribbon, simply click a
tab.

• Show Tabs and Commands:This option maximizes the Ribbon. All the
tabs and commands will be visible. This option is selected by default
when you open Excel for the first time.

4
The Quick Access Toolbar

Located just above the Ribbon, the Quick Access Toolbar lets you access common
commands no matter which tab is selected. By default, it includes the Save, Undo,
and Repeat commands. You can add other commands depending on your
preference.
To add commands to the Quick Access Toolbar:
1. Click the drop-down arrow to the right of the Quick Access Toolbar.

2. Select the command you want to add from the drop-down menu. To
choose from more commands, select More Commands.

3. The command will be added to the Quick Access Toolbar.

How to use Tell me:

The Tell me box works like a search bar to help you quickly find tools or commands you
want to use.

1. Type in your own words what you want to do.

5
 2. The results will give you a few relevant options. To use one, click it
like you would a command on the Ribbon.

Worksheet views

These views can be useful for various tasks, especially if you are planning to print the
spreadsheet. To change worksheet views, locate the commands in the bottom-right
corner of the Excel window and select Normal view, Page Layout view, or Page Break view.

• Normal view is the default view for all worksheets in Excel.

• displays how your worksheets will appear when

Page Layout view
printed. You can also add headers and footers in this view.

6
 • allows you to change the location of page breaks,
Page Break view
which is especially helpful when printing a lot of data from Excel.

Backstage view

Backstage view gives you various options for saving, opening a file, printing, and
sharing your workbooks.

To access Backstage view:

1. Click the File tab on the Ribbon. Backstage view will appear.

Challenge!
1. Open Excel any version.
2. Click Blank Workbook to open a new spreadsheet.
3. Change the Ribbon Display Options to Show Tabs.
4. Using the Customize Quick Access Toolbar, click to add New, Quick Print,
and Spelling.
5. In the Tell me bar, type the word Color. Hover over Fill Color and choose
a green. This will fill a cell with a color green.
6. Change the worksheet view to the Page Layout option.

7
 PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft Excel.

Perform the worksheet below.
WORKSHEET 1
Text, Label, Value, Formula

I. OBJECTIVES:
1. Identify text, label, value, and formula.
2. Create a table of inventory.
3. Saving the file correctly.
II. PROCEDURE:
1. Open Microsoft Excel, spreadsheet or google sheets.

2. Open new blank workbook.

3. Encode the following: Filename: Surname&Section SALES

INVENTORY OF CANTEEN 1
NAME OF PER PRICE
ITEM PACK PIECES PER PC TOTAL

1 PIATTOS 189.00 20 12.00 240.00

2 SIOMAI 350.00 100 5.00 500.00
3 BURGER 150.00 36 15.00 540.00
4 BALLPEN 125.00 50 10.00 500.00
5 MENTOS 75.00 100 1.00 100.00

8
III. EVALUATION:
CRITERIA 15 10 5
Content Completely 1-2 lines are 3-5 lines are
done missing missing
Tools used Most of the Some of the Few tools
standard tools tools
Creativity Very creative Apply some Plain
colors
Formula Correct Few errors Incorrect
TOTAL

Note: (Quizzes will be given on www.quizstar.4teachers.org)

Register to take quizzes.

GO ONLINE

https://ncsl.org https://gcflearn.org

For queries: 09178803568

9
 SUMMATIVE TEST
Module 1
Introduction On Spreadsheet Application
TEST I. IDENTIFICATION
Directions: Identify the MS Excel interface components. Write your answers on your answer
sheets.

1. 6.

2. 7.

3. 8.

4. 9.

5. 10.

TEST II. MULTIPLE CHOICE

Directions: Read and understand the statements. Select the best answer and write the letter on
your answer sheets.
_____ 1. It is a spreadsheet program that allows you to store, organize, and analyze information.
a) MS Access b) MS Excel c) MS PowerPoint d) MS Word

10
_____ 2. It is composed of different worksheets.
a) cell b) slide c) web page d) workbook
_____ 3. A box in which you enter your spreadsheet data.
a) cell b) slide c) web page d) workbook
_____ 4. The ____________ lets you access common commands no matter which tab is
selected.
a) Formula Bar b) Status Bar c) Quick Access Toolbar d) Title Bar
_____ 5. This view displays how your worksheets will appear when printed. You can also add
headers and footers in this view.
a) Backstage b) Normal c) Page Break d) Page Layout

11
 Module 2
CREATE WORKBOOK IN DIFFERENT WAYS

2
GOALS
This module is intended for you to create and personalized
workbook.
At the end of the module you are expected to:
1. Selecting and using available and appropriate spreadsheet template.
2. Creating a new blank workbook.

What is it

Excel files are called workbooks. There are several ways to start working with a
workbook in Excel. You can choose to create a new workbook either with a blank
workbook or a predesigned template or open an existing workbook.

To create a new blank workbook:

1. Select the File tab. Backstage view will appear.

2. Select New, then click Blank workbook.

3. A new blank workbook will appear.

Using templates

A template is a predesigned spreadsheet you can use to create a new workbook

quickly. Templates often include custom formatting and predefined formulas, so they
can save you a lot of time and effort when starting a new project.

12
To create a new workbook from a template:
1. Click the File tab to access Backstage view .

2. Select New. Several templates will appear below the Blank workbook
option.
3. Select a template to review it.

4. A preview of the template will appear, along with additional information

on how the template can be used.
5. Click Create to use the selected template.
6. A new workbook will appear with the selected template.

You can also browse templates by category or use the search bar to find something
more specific. Example: Calendar

13
Save and Save As

Excel offers two ways to save a file: Save and Save As. These options work in similar
ways, with a few important differences:

• Save: When you create or edit a workbook, you will use the Save
command to save your changes. After that, you can just click the
Save command to save it with the same name and location or use
the shortcut key Ctrl S.
• Save As: You'll use this command to create a copy of a workbook
while keeping the original. When you use Save As, you will need to
choose a different name and/or location for the copied version.

To save a workbook:

Saving your workbook whenever you start a new project or make changes to an
existing one is a must. Saving early and often can prevent your work from being lost.
You will also need to pay close attention to where you save the workbook so it will be
easy to find later.

1. Locate and select the Save command on the Quick Access Toolbar.

2. If you are saving the file for the first time, the Save As pane will
appear in Backstage view.
3. You will 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.
4. The Save As dialog box will appear. Select the location where you
want to save the workbook.
14
 5. Enter a file name for the workbook, then click Save.
6. The workbook will be saved. You can click the Save command again
to save your changes as you modify the workbook.

Using Save As to make a copy

If you want to save a different version of a workbook while keeping the original, you
can create a copy.
To do this, you will click the Save As command in Backstage view. Just like when
saving a file for the first time, you will need to choose where to save the file and give it
a new file name.

To change the default save location:

If you don't want to use OneDrive, you may be frustrated that OneDrive is selected as
the default location when saving. If you find it inconvenient to select Computer each
time, you can change the default save location so Computer is selected by default.

1. Click the File tab to access Backstage view.

2. Click Options.

15
 3. The Excel Options dialog box will appear. Select Save, check the box
next to Save to Computer by default, then click OK. The default save
location will be changed.
Compatibility Mode

Sometimes you may need to work with workbooks that were created in earlier versions
of Microsoft Excel, such as Excel 2003 or Excel 2000. When you open these types of
workbooks, they will appear in Compatibility Mode.

Compatibility Mode disables certain features, so you will only be able to access
commands found in the program that was used to create the workbook. For example, if
you open a workbook created in Excel 2003, you can only use tabs and commands
found in Excel 2003.

When exiting Compatibility Mode, you will need to convert the workbook to the current
version type. However, if you are collaborating with others who only have access to an
earlier version of Excel, it is best to leave the workbook in Compatibility Mode so the
format will not change.

To convert a workbook:

If you want access to all of the Excel 2016 features, you can convert the workbook to
the 2016 file format.

Note that converting a file may cause some changes to the original layout of the
workbook.

1. Click the File tab to access Backstage view.

2. Locate and select Convert command.

3. The Save As dialog box will appear. Select the location where you
want to save the workbook, enter a file name for the workbook, and
click Save.

16
 4. The workbook will be converted to the newest file type.

Challenge!
1. Open your first worksheet.
2. Create another copy using Save As.
3. Change the filename into Sales_Inventory.

PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft Excel.

Perform the worksheet below.

WORKSHEET 2
Saving a Worksheet

OBJECTIVES:
1. Open a new workbook.
2. Create a new worksheet.
3. Save the worksheet properly. PROCEDURE:
1. Open saved workbook.
2. Click File tab then select New.
3. On search box type Calendar.
4. Choose Calendar template.
5. Design a simple 2021 calendar.
6. Rename your worksheet as calendar.
7. Save your file.

EVALUATION:
CRITERIA 15 10 5
Content Completely done 1-2 lines are missing 3-5 lines are
missing
Tools used Most of the Some of the tools Few tools
standard tools
Creativity Very creative Apply some colors Plain
Format Correct Few errors Incorrect
TOTAL

17
 SUMMATIVE TEST
Module 2
Create Workbook In Different Ways
TEST I. TRUE or FALSE
Directions: Read and understand the statements. Write True if the underlined word/s make/s the
statement correct otherwise False on your answer sheets.
_____ 1. Excel files are called workbooks.
_____ 2. You can choose to create a new workbook either with a blank workbook or a
predesigned template or open an existing workbook.
_____ 3. A template is a predesigned spreadsheet you can use to create a new workbook
quickly.
_____ 4. You will use Save command to create a copy of a workbook while keeping the
original.
_____ 5. Compatibility Mode enables certain features, so you will only be able to access
commands found in the program that was used to create the workbook.

TEST II. FILL-IN THE BLANKS

Directions: Fill the blanks with the correct word/s. Write your answers on your answer sheets.
A. To create a new workbook from a template:
1. Click the ___________ to access Backstage view.
2. Select ___________. Several templates will appear below the Blank workbook
option.
3. Select a ___________ to review it.
4. A ___________ of the template will appear, along with additional information on
how the template can be used.
5. Click ___________ to use the selected template.
6. A new ___________ will appear with the selected template.
B. To convert a workbook the newest Excel file:
1. Click the ___________ tab to access Backstage view.
2. Locate and select ___________ command.
3. The ___________ dialog box will appear. Select the location where you want to save
the workbook, enter a file name for the workbook, and click Save.
4. The workbook will be converted to the ___________ file type.

18
 Module 3
CREATE AND EDIT DATA IN A WORKSHEET

GOALS
3
This module is intended for you to identify and classify data as
label, value, or formula.
At the end of the module you are expected to:
1. Insert data accurately.
2. Modify/Edit inserted data properly.

What is it

Whenever you work with Excel, you will enter information or content into cells.
Cells are the basic building blocks of a worksheet. You will need to learn the
basics of cells and cell content to calculate, analyze, and organize data in Excel.

Understanding cells

Every worksheet is made up of thousands of rectangles, which are called cells. A cell
is the intersection of a row and a column in other words, where a row and column
intersect.

Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2,
3). Each cell has its own name or cell address based on its column and row. In the
example below, the selected cell intersects column C and row 5, so the cell address
is C5.

Note that the cell address also appears in the Name box in the top-left corner, and that
a cell's column and row headings are highlighted when the cell is selected.
19
A group of cells is known as a cell range. Rather than a single cell address, you will
refer to a cell range using the cell addresses of the first and last cells in the cell range,
separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and
A5 would be written as A1:A5.

• Cell range A1:A8

• Cell range A1:F1

• Cell range A1:F8

To select a cell:
1. Click a cell to select it.
2. A border will appear around the selected cell, and the column
heading and row heading will be highlighted. The cell will remain
selected until you click another cell in the worksheet.
20
You can also select cells using the arrow keys on your keyboard.

To select a cell range:

Sometimes you may want to select a larger group of cells, or a cell range.

1. Click and drag the mouse until all the adjoining cells you want to
select are highlighted.
2. Release the mouse to select the desired cell range. The cells will
remain selected until you click another cell in the worksheet.

Cell content

Any information you enter a spreadsheet will be stored in a cell. Each cell can contain
different types of content, including text, formatting, formulas, and functions.

• Text: Cells can contain text, such as letters, numbers, and dates.

• Formatting attributes: Cells can contain formatting attributes that

change the way letters, numbers, and dates are displayed. For
example, percentages can appear as 0.15 or 15%. You can even
change a cell's text or background color.
• Formulas and functions: Cells can contain formulas and functions
that calculate cell values. In our example, SUM(B1:B8) adds the
value of each cell in the cell range B1:B8 and displays the total in cell
B9.

21
To insert content:
1. Click a cell to select it.
2. Type something into the selected cell, then press Enter on your
keyboard. The content will appear in the cell and the formula bar.
You can also input and edit cell content in the formula bar.

To delete (or clear) cell content:

1. Select the cell(s) with content you want to delete.
2. Select the Clear command on the Home tab, then click Clear
Contents.

3. The cell contents will be deleted.

You can also use the Delete key on your keyboard to delete content from multiple
cells at once. The Backspace key will only delete content from one cell at a time.

To delete cells:

There is an important difference between deleting the content of a cell and deleting the
cell itself. If you delete the entire cell, the cells below it will shift to fill in the gaps and
replace the deleted cells.

1. Select the cell(s) you want to delete.

2. Select the Delete command from the Home tab on the Cells group.

3. The cells below will shift up and fill in the gaps.

22
To copy and paste cell content:
1. Select the cell(s) you want to copy.
2. Click the Copy command on the Home tab or press Ctrl C on your
keyboard.

3. Select the cell(s) where you want to paste the content.

4. Click the Paste command on the Home tab or press Ctrl V on your
keyboard.

5. The content will be pasted into the selected cells.

To access more paste options:

You can also access additional paste options, which are especially convenient when
working with cells that contain formulas or formatting. Just click the drop-down arrow on
the Paste command to see these options.

23
Instead of choosing commands from the Ribbon, you can access commands quickly
by right-clicking. Simply select the cell(s) you want to format, then right-click the
mouse.
To cut and paste cell content:

Unlike copying and pasting, which duplicates cell content, cutting allows you
to move content between cells.

1. Select the cell(s) you want to cut.

2. Right-click the mouse and select the Cut command. Alternatively, you
can use the command on the Home tab, or press Ctrl X on your
keyboard.
3. Select the cells where you want to paste the content.
4. Right-click the mouse and select the Paste command. Alternatively,
you can use the command on the Home tab, or press Ctrl V on your
keyboard.
5. The cut content will be removed from the original cells and pasted into
the selected cells.

To drag and drop cells:

Instead of cutting, copying, and pasting, you can drag and drop cells to move their
contents.

1. Select the cell(s) you want to move.

2. Hover the mouse over the border of the selected cell(s) until the
mouse changes to a pointer with four arrows.
3. Click and drag the cells to the desired location.

4. Release the mouse. The cells will be dropped in the selected location.

To use the fill handle:

If you are copying cell content to adjacent cells in the same row or column, the fill
handle is a good alternative to the copy and paste commands.

1. Select the cell(s) containing the content you want to use, then hover
the mouse over the lower-right corner of the cell so the fill handle
appears.
2. Click and drag the fill handle until all the cells you want to fill are
selected.
3. Release the mouse to fill the selected cells.

24
To continue a series with the fill handle:

The fill handle can also be used to continue a series. Whenever the content of a row or
column follows a sequential order, like numbers (1, 2, 3) or days (Monday, Tuesday,
Wednesday), the fill handle can guess what should come next in the series. In most
cases, you will need to select multiple cells before using the fill handle to help Excel
determine the series order. Select the cell range that contains the series you want to
continue.

1. Click and drag the fill handle to continue the series.

2. Release the mouse. If Excel understood the series, it will be
continued in the selected cells.

You can also double-click the fill handle instead of clicking and dragging. This can
be useful with larger spreadsheets, where clicking and dragging may be awkward.

Challenge!
1. Open Calendar workbook.
2. Select cell B3 and type JANUARY.
3. Clear the contents in row 14.
4. Delete column D.
5. Using either cut and paste or drag and drop, move the contents of row 5
to row 10.
6. Use the fill handle to put an X in cells F9:F17.

Note: Perform the challenge to familiarize editing data on Excel.

25
PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft Excel.

Perform the worksheet below.

WORKSHEET 4
Weekly Schedule

OBJECTIVES:
1. Follow format when adding contents in worksheet.
2. Create a weekly schedule following the steps given.
3. Saving the file correctly.
PROCEDURE:
1. Open the Calendar workbook.
2. Add a new sheet. Rename it as Schedule.
3. Copy the format below.

4. Add contents and design.

5. Add title.
6. Save your file.
EVALUATION:
CRITERIA 20 15 10
Content Completely done 1-2 lines are missing 3-5 lines are missing

Creativity More designs Less designs No design

Format Correct format 1-2 mistakes 3-5 mistakes
TOTAL
Total Points

26
 SUMMATIVE TEST
Module 3
Create And Edit Data In A Worksheet
TEST I. MATCHING TYPE
Directions: Match Column A with Column B. Write your answers on your answer sheets.
Column A Column B
_____1. The basic building blocks of a worksheet. a. cells
_____2. It is made up of thousands of rectangles, which b. cell address
are called cells.
c. cell range
_____3. They are identified by letters (A, B, C).
d. columns
_____4. They are identified by numbers (1, 2, 3).
e. rows
_____5. A group of cells is known as a _________.
f. worksheets

TEST II. TRUE or FALSE

Directions: Read and understand the statements. Write True if the underlined word/s make/s the
statement correct otherwise False on your answer sheets.
_____ 1. A cell is the intersection of a row and a column.
_____ 2. Each cell has its own name or cell address based on its column and row.
_____ 3. Each cell can contain different types of content, including text, formatting, formulas,
and functions.
_____ 4. The Backspace key will only delete content from one cell at a time.
_____ 5. Select a cell and then press Ctrl + V key on your keyboard to cut a cell.

27
 Module 4
MODIFY TABLE STRUCTURES AND CELL FORMATS

4
GOALS
This module is intended for you to enhance created worksheet by modifying the table
structure and the format of cells.
At the end of the module you are expected to:
1. Demonstrate resizing row height and column width.
2. Apply borders, shading and background patterns.
3. Change number formats.

What is it
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:

1. Position the mouse over the column line in the column heading so
the cursor becomes a double arrow.
2. Click and drag the mouse to increase or decrease the column width.
3. Release the mouse. The column width will be changed.

Note: 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.

28
To AutoFit column width:

The AutoFit feature will allow you to set a column's width to fit its content automatically.

1. Position the mouse over the column line in the column heading so
the cursor becomes a double arrow.
2. Double-click the mouse. The column width will be changed
automatically to fit the content.

You can also AutoFit the width for several columns at the same time. Simply
select the columns you want to AutoFit, then select the AutoFit Column width
command from the Format drop-down menu on the Home tab. This method can also
be used for row height.

To modify row height:

1. Position the cursor over the row line so the cursor becomes a double
arrow.
2. Click and drag the mouse to increase or decrease the row height.
3. Release the mouse. The height of the selected row will be changed.

To modify all rows or columns:

Instead of resizing rows and columns individually, you can modify the height and width
of every row and column at the same time. This method allows you to set a uniform
size for every row and column in your worksheet.

1. Locate and click the Select All button just below the name box to
select every cell in the worksheet.
2. Position the mouse over a row line so the cursor becomes a double
arrow.
29
 3. Click and drag the mouse to increase or decrease the row height,
then release the mouse when you are satisfied. The row height will
be changed for the entire worksheet.

Inserting, deleting, moving, and hiding

After you have been working with a workbook for a while, you may find that you want
to insert new columns or rows, delete certain rows or columns, move them to a
different location in the worksheet, or even hide them.

To insert rows:
1. Select the row heading below where you want the new row to
appear.
2. Click the Insert command on the Home tab.

3. The new row will appear above the selected row.

When inserting new rows, columns, or cells, you will see a paintbrush icon next to
the inserted cells. This button allows you to choose how Excel formats these cells. By
default, Excel formats inserted rows with the same formatting as the cells in the row
above. To access more options, hover your mouse over the icon, then click the
dropdown arrow.

To insert columns:
1. Select the column heading to the right of where you want the new
column to appear. For example, if you want to insert a column
between columns D and E, select column E.
2. Click the Insert command on the Home tab or right-click select insert.

30
 3. The new column will appear to the left of the selected column.

When inserting rows and columns, make sure you select the entire row or column by
clicking the heading. If you select only a cell in the row or column, the Insert command
will only insert a new cell.

To delete a row or column:

1. Select the row you want to delete.
2. Click the Delete command on the Home tab.

3. The selected row will be deleted, and those around it will shift.

To move a row or column:

Sometimes you may want to move a column or row to rearrange the content of your
worksheet. In our example we will move a column, but you can move a row in the
same way.

1. Select the desired column heading for the column you want to move.
2. Click the Cut command on the Home tab or press Ctrl X on your
keyboard.

3. Select the column heading to the right of where you want to move the
column. For example, if you want to move a column between
columns E and F, select column F.

31
 4. Click the Insert command on the Home tab, then select Insert Cut
Cells from the drop-down menu.

5. The column will be moved to the selected location, and the columns
around it will shift.

You can also access the Cut and Insert commands by right clicking the mouse and
selecting the desired commands from the drop-down menu.

To hide and unhide a row or column:

1. Select the columns you want to hide, right-click the mouse, then
select Hide from the formatting menu.
2. The columns will be hidden. The green column line indicates the
location of the hidden columns.
3. To unhide the columns, select the columns on both sides of the
hidden columns. In our example, we will select columns B and F.
Then right-click the mouse and select Unhide from the formatting
menu.
4. The hidden columns will reappear.

Wrapping text and merging cells

Whenever you have too much cell content to be displayed in a single cell, you may
decide to wrap the text or merge the cell rather than resize a column. Wrapping the text

32
will automatically modify a cell's row height, allowing cell contents to be displayed on
multiple lines. Merging allows you to combine a cell with adjacent empty cells to create
one large cell.

To wrap text in cells:

1. Select the cells you want to wrap.
2. Click the Wrap Text command on the Home tab.
3. The text in the selected cells will be wrapped.

To merge cells using the Merge & Center command:

1. Select the cell range you want to merge.
2. Click the Merge & Center command on the Home tab.
3. The selected cells will be merged, and the text will be centered.

33
From here, you can choose to:

• Merge & Center merges the selected cells into one cell and centers the
text
• Merge Across:
merges the selected cells into larger cells while keeping
each row separate
• Merge Cells: merges the selected cells into one cell but does not center
the text
• Unmerge Cells unmerges selected cells

Challenge!
1. Open Weekly Schedule worksheet.
2. Autofit Column Width for the entire workbook.
3. Modify the row height for rows 3 to 14 to 22.5 (30 pixels).
4. Delete row 10.
5. Insert a column to the left of column C. Type REMARKS(done/on-going) in
cell C2.
6. Make sure cell C2 is still selected and choose Wrap Text.
7. Merge and Center cells A1:H1.

To change the font size:

1. Select the cell(s) you want to modify.
2. On the Home tab, click the Font Size command, then select the desired
font size.

3. The text will change to the selected font size.

You can also use the Increase Font Size and Decrease Font Size commands or enter
a custom font size using your keyboard.

34
To change the font:

By default, the font of each new workbook is set to Calibri. However, Excel provides
many other fonts you can use to customize your cell text.

1. Select the cell(s) you want to modify.

2. On the Home tab, click the drop-down arrow next to the Font command,
then select the desired font.

3. The text will change to the selected font.

To change the font color:

1. Select the cell(s) you want to modify.
2. On the Home tab, click the drop-down arrow next to the Font Color
command, then select the desired color.

3. The text will change to the selected font color.

Select More Colors at the bottom of the menu to access additional color options.

35
To use the Bold, Italic, and Underline commands:
1. Select the cell(s) you want to modify.
2. Click the Bold (B), Italic (I), or Underline (U) command on the Home
tab.

3. The selected style will be applied to the text.

Cell borders and fill colors

Cell borders and fill colors allow you to create clear and defined boundaries for different
sections of your worksheet.

To add a fill color:

1. Select the cell(s) you want to modify.

2. On the Home tab, click the drop-down arrow next to the Fill Color
command, then select the fill color you want to use.

36
 3. The selected fill color will appear in the selected cells.

To add a border:
1. Select the cell(s) you want to modify.
2. On the Home tab, click the drop-down arrow next to the Borders
command, then select the border style you want.

3. The selected border style will appear.

You can draw borders and change the line style and color of borders with the
Draw Borders tools at the bottom of the Borders drop-down menu.

37
Cell styles

Instead of formatting cells manually, you can use Excel's predesigned cell styles. Cell
styles are a quick way to include professional formatting for different parts of your
workbook, such as titles and headers.

To apply a cell style:

1. Select the cell(s) you want to modify.
2. Click the Cell Styles command on the Home tab, then choose the desired
style from the drop-down menu.

3. The selected cell style will appear.

Text alignment

By default, any text entered 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
38
cell content allows you to choose how the content is displayed in any cell, which can
make your cell content easier to read.

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

Right Align: Aligns content to the right border of the cell
Center Align: Aligns content at the center border of the cell

To change horizontal text alignment:

1. Select the cell(s) you want to modify.
2. Select one of the three horizontal alignment commands on the Home tab.

3. The text will realign.

To change vertical text alignment:

1. Select the cell(s) you want to modify.
2. Select one of the three vertical alignment commands on the Home tab. I

3. The text will realign.

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

If you want to copy formatting from one cell to another, you can use the Format Painter
command on the Home tab. When you click the Format Painter, it will copy all the

39
formatting from the selected cell. You can then click and drag over any cells you want
to paste the formatting to.

Challenge!
1. Open weekly schedule worksheet.
2. Change the cell style in cells A2:H2 to Accent 3 or any style.
3. Change the font size of row 1 to 8 and the font size for the rest of the
rows to 18.
4. Bold and underline the text in row 2.
5. Change the font of row 1 to a font of your choice.
6. Change the font of the rest of the rows to a different font of your
choice.
7. Change the font color of row 1 to a color of your choice.
8. Select all the text in the worksheet and change the horizontal
alignment to center align and the vertical alignment to middle align.

40
PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft

Excel. Perform the worksheet below.

WORKSHEET 4
Number Formats

OBJECTIVES:
1. Enumerate the different number formats in Excel.
2. Apply the different number formats in making a calendar.
3. Create a very creative but simple calendar.
PROCEDURE:
1. Open the saved workbook.
2. Add a new sheet.
3. Apply different number formats by making a calendar (Birth month).
4. Design a simple calendar.
5. Rename your worksheet as calendar month.
6. Save your file.
7. Print using a typewriting paper, size: 8 ½ x 11.

EVALUATION:
CRITERIA 15 10 5
Content Completely 1-2 lines are 3-5 lines are
done missing missing
Tools used Most of the Some of the Few tools
standard tools tools
Creativity Very creative Apply some Plain
colors
Format Correct Few errors Incorrect
TOTAL

41
 SUMMATIVE TEST
Module 4
Modify Table Structures And Cell Formats
TEST I. FILL-IN THE BLANKS
Directions: Fill the blanks with the correct word/s. Choose the best answer inside the box and
write your answers on your answer sheets.
AutoFit Borders Bottom Calibri Home
Increase Menu Modify One Pound
Same Separate Titles Unmerges Wrapping
1. By default, every row and column of a new workbook is set to the __________ height
and width.
2. Excel allows you to __________ column width and row height in different ways,
including wrapping text and merging cells.
3. With numerical data, the cell will display __________ signs (#######) if the column is
too narrow.
4. The __________ feature will allow you to set a column's width to fit its content
automatically.
5. __________ the text will automatically modify a cell's row height, allowing cell
contents to be displayed on multiple lines.
6. Merge & Center merges the selected cells into __________ cell and centers the text.
7. Merge Across merges the selected cells into larger cells while keeping each row
__________.
8. Unmerge Cells __________ selected cells.
9. You can also use the __________ Font Size and Decrease Font Size commands or
enter a custom font size using your keyboard.
10. By default, the font of each new workbook is set to __________.
11. Cell __________ and fill colors allow you to create clear and defined boundaries for
different sections of your worksheet.
12. You can draw borders and change the line style and color of borders with the Draw
Borders tools at the bottom of the Borders drop-down __________.
13. Cell styles are a quick way to include professional formatting for different parts of your
workbook, such as __________ and headers.
14. By default, any text entered your worksheet will be aligned to the __________-left of a
cell, while any numbers will be aligned to the bottom-right.
15. If you want to copy formatting from one cell to another, you can use the Format
Painter command on the __________ tab.

42
 Module 5
MANIPULATE DATA USING FORMULAS AND WORKSHEET
FUNCTIONS

GOALS 5
This module is intended for you to apply mathematical equations
into spreadsheet formulas.
At the end of the module you are expected to:
1. Use and apply simple formula.
2. Select and apply appropriate worksheet function for a specific task.

What is it

One of the most powerful features in Excel is the ability to calculate numerical
information using formulas. Just like a calculator, Excel can add, subtract, multiply,
and divide. In this lesson, we will show you how to use cell references to create
simple formulas.

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.

43
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.
Understanding cell references

While you can create simple formulas in Excel using numbers (for example, =2+2 or
=5*5), most of the time you will use cell addresses to create a formula. This is known as
making a cell reference. Using cell references will ensure that your formulas are
always accurate because you can change the value of referenced cells without
having to rewrite the formula.

In the formula below, cell A3 adds the values of cells A1 and A2 by making cell
references:

When you press Enter, the formula calculates and displays the answer in cell A3:

If the values in the referenced cells change, the formula automatically recalculates:

By combining a mathematical operator with cell references, you can create a variety of
simple formulas in Excel. Formulas can also include a combination of cell references
and numbers, as in the examples below:

44
To create a formula:
1. Select the cell that will contain the formula.
2. Type the equals sign (=). Notice how it appears in both the cell and
the formula bar.
3. Type the cell address of the cell you want to reference first in the
formula. A blue border will appear around the referenced cell.
4. Type the mathematical operator you want to use.
5. Type the cell address of the cell you want to reference second in the
formula. A red border will appear around the referenced cell.

FORMULA BAR

First refer ence

6. Press Enter on your keyboard. The formula will be calculated, and

the value will be displayed in the cell. If you select the cell again,
notice that the cell displays the result, while the formula bar displays
the formula.

If the result of a formula is too large to be displayed in a cell, it may appear as pound
signs (#######) instead of a value. This means the column is not wide enough to
display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your
worksheet without having to rewrite formulas. The formula will automatically recalculate
and display the new value in cell.

45
To create a formula using the point-and-click method:

Instead of typing cell addresses manually, you can point and click the cells you want to
include in your formula. This method can save a lot of time and effort when creating
formulas by selecting the cell that will contain the formula.

1. Type the equals sign (=).

2. Select the cell you want to reference first in the formula then cell
address will appear in the formula.
3. Type the mathematical operator you want to use.
4. Select the cell you want to reference second in the formula then the
cell address will appear in the formula.
5. Press Enter on your keyboard. The formula will be calculated, and
the value will be displayed in the cell.

Copying formulas with the fill handle

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot
of time and effort if you need to perform the same calculation multiple times in a
worksheet. The fill handle is the small square at the bottom-right corner of the selected
cell(s).

1. Select the cell containing the formula you want to copy. Click and
drag the fill handle over the cells you want to fill.
2. After you release the mouse, the formula will be copied to the
selected cells.

To edit a formula:
1. Select the cell containing the formula you want to edit.
2. Click the formula bar to edit the formula. You can also doubleclick the
cell to view and edit the formula directly within the cell.
3. A border will appear around any referenced cells.
4. Press Enter on your keyboard or select the Enter command in the
formula bar.
5. The formula will be updated, and the new value will be displayed in
the cell.

If you change your mind, you can press the Esc key on your keyboard or click the
Cancel command in the formula bar to avoid accidentally making changes to your
formula.
46
 PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft

Excel. Perform the worksheet below.

WORKSHEET 5
Arithmetic Operators

OBJECTIVES:
1. Identify the different arithmetic operators.
2. Create a table using arithmetic operators in the formula.
3. Saving the file correctly.
PROCEDURE:
1. Open saved workbook.
2. Add new sheet.
3. Encode the following using arithmetic operators in the formula.
4. Save it and rename the sheet as arithmetic. COMPUTER ACCESSORIES SHOP SALES FOR
THE FIRST QUARTER

CATEGORY JAN FEB MAR TOTAL

1 KEYBOARD 5955.00 5214.00 6754.00

2 MOUSE 4395.00 5679.00 6375.00
3 SPEAKER 6384.00 8285.00 7894.00
4 MONITOR 8765.00 8564.00 8210.00

TOTAL
Average sale of keyboard ___________ Maximum Sale__________
Average sale of mouse _____________ Minimum Sale__________
Average sale of speaker____________
Average sale of monitor____________

IV. EVALUATION:
CRITERIA 20 15 10
Content Completely done 1-2 lines are missing 3-5 lines are missing

Creativity More designs Less designs No design

Formula Correct used 1-2 wrong formula 3-5 wrong formula
formula
TOTAL
Total Points
47
 You may have experience working with formulas that contain only one
operator. More complex formulas can contain several mathematical
operators, such as 6+3*8. When there is more than one operation in a
formula, the order of operations tells Excel which operation to calculate
first. To write formulas that will give you the correct answer, you will need
to understand the order of operations.

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.

While this formula may look complicated, we can use the order of operations step
by step to find the right answer.

TIPS

It is especially important to follow the order of operations when creating a formula. Otherwise,
Excel will not calculate the results accurately. In our example, if the parentheses are not
included, the multiplication is calculated first and the result is incorrect. Parentheses are often
the best way to define which calculations will be performed first in Excel.

48
You can add parentheses to any equation to make it easier to read. While it will not change the
result of the formula in this example, we could enclose the multiplication operations within
parentheses to clarify that they will be calculated before the addition.

PERFORMANCE TASK

Directions: Open on your desktop, laptop, or mobile phone Microsoft

Excel. Perform the worksheet below.

WORKSHEET 6
Simple Formula

OBJECTIVES:
1. Compute for the percentage.
2. Create a class record using percentage.
3. Saving the file correctly.
PROCEDURE:
1. Open a new workbook.
2. Copy the names from the class record sheet.
3. Arrange the names alphabetically. (Sort and Filter) 4. Encode the scores and
compute for the percentage.
Formula: Equivalent = (Total Score / Total no. of Items) *50+50
5. 20% = (Equivalent/100) * 20% 6. Save your file.
CLASS RECORD
No. NAME WRITTEN OUTPUTS TOTAL Equivalent 20%
Total no. of items 15 20 25

1 Juan, Angelina 14 18 22
2 Puno, Brad 8 12 15
3 Dela Cruz, Tom 6 5 8
4 Hidalgo, Katie 12 15 20
5 Agustin, Bea 10 15 17
6 Castro, John 9 13 16
7 Cortez, Anne 7 16 21
8 Molina, Sam 15 10 23
9 Rosario, Marianne 13 17 18
10 De Mesa, Jericho 12 14 16

49
V. EVALUATION:
CRITERIA 20 15 10
Content Completely done 1-2 lines are 3-5 lines are
missing missing
Creativity More designs Less designs No design
Formula Correct used 1-2 wrong 3-5 wrong formula
formula formula
TOTAL
Total Points

GO ONLINE

https://ncsl.org https://gcflearn.org

For queries: 09178803568

50
 SUMMATIVE TEST
Module 5
Manipulate Data Using Formulas And Worksheet Functions
Name: _____________________________________________ Score: _____________
Section: ____________________________________________ Date: ______________

TEST I. TRUE or FALSE

Directions: Read and understand the statements. Write True if the underlined word/s make/s the
statement correct otherwise False on your answer sheets.
_____ 1. One of the most powerful features in Excel is the ability to calculate numerical
information using formulas.
_____ 2. Excel uses standard operators for formulas, such as a plus sign for addition (+), a
minus sign for subtraction (-), an ampersand for multiplication (*), a forward slash for
division (/), and a caret (^) for exponents.
_____ 3. All formulas in Excel must begin with an unequal sign (=).
_____ 4. Using cell references will ensure that your formulas are always accurate because you
can change the value of referenced cells without having to rewrite the formula.
_____ 5. If the values in the referenced cells change, the formula automatically recalculates.
_____ 6. Formulas cannot also include a combination of cell references and numbers.
_____ 7. If the result of a formula is too large to be displayed in a cell, it may appear as dollar
signs (#######) instead of a value.
_____ 8. The true advantage of cell references is that they allow you to update data in your
worksheet without having to rewrite formulas.
_____ 9. Formulas can also be copied to adjacent cells with the fill handle, which can save a lot
of time and effort if you need to perform the same calculation multiple times in a
worksheet.
_____ 10. If you change your mind, you can press the Esc key on your keyboard or click the
Cancel command in the formula bar to avoid accidentally making changes to your
formula.

51