1

Tagaran, Cauayan City, Isabela

Telefax: (078) 652-1038
Email: icat_97@[Link]

I. NAME OF PROGRAM Bachelor of Science in Criminology

(BSCrim)
II. COURSE DESCRIPTIVE Software Application (MS Office with COURSE CODE
TITLE / CODE Internet)
Com
III. PRE-REQUISITE / CO- None COURSE CREDIT
REQUISITE UNIT

3
IV. COURSE DESCRIPTION This course introduces the Microsoft
Office. Students will learn about the
features and familiarize Microsoft Word,
Excel and Presentation.

V. MODULE TITLE / Excel Basics and Cell Basics MODULE NUMBER

NUMBER
7

VI. INCLUSIVE WEEK Week 7

VII. COURSE OUTCOMES Effectively use software applications for documents used in
onboard ships. Evaluate computer networks and troubleshoot
computer.

A. Learning Outcome: At the end of the modules, the students should be able to:

1. know the parts of Microsoft Excel window;

2. copy, cut, paste, delete cell content;
3. and drag,drop, modify columns, rows and cells.
VIII. MODULE RESOURCES

Free Excel 2016 Tutorial at GCFGlobal. (2020). [Link].

[Link]
 2

Introduction:

Microsoft Excel is a spreadsheet program that allows you to store, organize,

and analyze information. While you may think 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.

MODULE 7
Excel Basics and Cell Basics

Lesson 1: The parts of the Excel window

Some parts of the Excel window (like
the Ribbon and scroll bars) are standard in
most other Microsoft programs. However,
there are other features that are more specific
to spreadsheets, such as the formula
bar, name box, and worksheet tabs. Name Box
The Name box displays the location,
or name, of a selected cell.

Formula Bar
In the formula bar, you can enter or
edit data, a formula, or a function that will
appear in a specific cell.

The Ribbon
The Ribbon contains all of
the commands you will need to perform
common tasks in Excel. It has multiple tabs,
each with several groups of commands. Column
Excel uses a tabbed Ribbon system instead of A column is a group of cells that runs from
traditional menus. The the top of the page to the bottom. In Excel,
Ribbon contains multiple tabs, each with columns are identified by letters.
several groups of commands. You will use
these tabs to perform the most common
tasks in Excel.

Row
A row is a group of cells that runs from the
left of the page to the right. In Excel, rows are
identified by numbers.
Quick Access Toolbar
The Quick Access Toolbar lets you access
common commands no matter which tab is
selected. You can customize the commands
depending on your preference.
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. Cell
Each rectangle in a workbook is called
a cell. A cell is the intersection of a row and a
column. Simply click to select a cell.
 3

Worksheets
Excel files are called workbooks. Each
workbook holds one or more worksheets. Click
the tabs to switch between them, or right-click
for more options.

Zoom Control
Click and drag the slider to use
the zoom control. The number to the right of
the slider reflects the zoom percentage.

Worksheet View Options

There are three ways to view a
worksheet. Simply click a command to select
the desired view.
Vertical and Horizontal Scroll Bars
The scroll bars allow you to scroll up
and down or side to side. To do this, click and
drag the vertical or horizontal scroll bar.

 Normal view is the default view for all

worksheets in Excel.

Backstage view
Backstage view gives you various options for
saving, opening a file, printing, and sharing
your workbooks.
 Page Layout view displays how your
worksheets will appear when printed. You
can also add headers and footers in this
view.

Lesson 2: Cell Basics

Whenever you work with Excel, you'll
enter information—or content—into cells. Cells
are the basic building blocks of a worksheet.
 Page Break view allows you to change You'll need to learn the basics of cells and cell
the location of page breaks, which is content to calculate, analyze, and organize
especially helpful when printing a lot of data in Excel.
data from Excel. 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, it's where a row and column
meet. Columns are identified by letters (A, B,
 4

C), while rows are identified by numbers (1, 2,

3).

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 of

the adjoining cells you want to select
are highlighted. In our example, we'll select
the cell range B5:C18.
2. Release the mouse to select the desired cell To delete cells:
range. The cells will remain selected until you There is an important difference
click another cell in the worksheet. 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. In our

example, we'll select A10:H10.

Cell content
Any information you enter into a
spreadsheet will be stored in a cell. Each cell
can contain different types of content,
including text, formatting, formulas,
and functions. 2. Select the Delete command from
 Text: Cells can contain text, such as the Home tab on the Ribbon.
letters, numbers, and dates.

 Formatting attributes: Cells can 3. The cells below will shift up and fill in the
contain formatting attributes that change gaps.
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.

To copy and paste cell content:

 Formulas and functions: Cells can 1. Select the cell(s) you want to copy.
contain formulas and functions that 2. Click the Copy command on the Home tab,
calculate cell values. In our or press Ctrl+C on your keyboard.
example, SUM(B2:B8) adds the value of 3. Select the cell(s) where you want
each cell in the cell range B2:B8 and to paste the content.
displays the total in cell B9. 4. Click the Paste command on the Home tab,
or press Ctrl+V on your keyboard.
 5

like numbers (1, 2, 3) or days (Monday,

To cut and paste cell content: Tuesday, Wednesday), the fill handle can
1. Select the cell(s) you want to cut. guess what should come next in the series. In
2. Right-click the mouse and select most cases, you will need to select multiple
the Cut command. You can also use the cells before using the fill handle to help Excel
command on the Home tab, or press Ctrl+X on determine the series order. Let's take a look at
your keyboard. an example:
3. Select the cells where you want to paste the 1. Select the cell range that contains the series
content. you want to continue. In our example, we'll
4. Right-click the mouse and select select E4:G4.
the Paste command. You can also use the 2. Click and drag the fill handle to continue the
command on the Home tab, or press Ctrl+V on series.
your keyboard.

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. In our
example, we'll select H4:H12.
2. Hover the mouse over the border of the
selected cell(s) until the mouse changes to
a pointer with four arrows.
3. Release the mouse. If Excel understood the
series, it will be continued in the selected cells.
In our example, Excel added Part 4, Part 5,
and Part 6 to H4:J4.

3. Click and drag the cells to

the desired location. In our example, we'll
move them to G4:G12.
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.

Lesson 3: Modifying Columns, Rows and

Cells

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
4. Release the mouse. The cells will
the column heading so the cursor becomes
be dropped in the selected location.
a double arrow.

2. Double-click the mouse. The column

To continue a series with the fill handle: width will be changed automatically to fit the
The fill handle can also be used content.
to continue a series. Whenever the content of
a row or column follows a sequential order,
 6

 You can also AutoFit the width for several  When inserting new rows, columns, or
columns at the same time. Simply select cells, you will see a paintbrush icon next to
the columns you want to AutoFit, then the inserted cells. This button allows you
select the AutoFit Column Width command to choose how Excel formats these cells.
from the Format drop-down menu on By default, Excel formats inserted rows
the Home tab. This method can also be with the same formatting as the cells in
used for row height. the row above. To access additional
options, hover your mouse over the icon,
then click the drop-down arrow.

Inserting, deleting, moving, and hiding

After you've been working with a
workbook for a while, you may find that you To insert columns:
want to insert new columns or 1. Select the column heading to the right of
rows, delete certain rows or where you want the new column to appear. For
columns, move them to a different location in example, if you want to insert a column
the worksheet, or even hide them. between columns D and E, select column E.

To insert rows:
1. Select the row heading below where you
want the new row to appear. In this example,
we want to insert a row between rows 4 and 5,
so we'll select row 5.

2. Click the Insert command on the Home tab.

2. Click the Insert command on the Home tab.

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

2. The new row will appear above the selected

row.
 7

 When inserting rows and columns, make 2. Click the Wrap Text command on
sure to select the entire row or column by the Home tab.
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:

It's easy to delete a row or column that
you no longer need. In our example we'll
delete a row, but you can delete a column the
same way.

1. Select the row you want to delete. In our

example, we'll select row 9. 3. The text in the selected cells will
be wrapped.

2. Click the Delete command on the Home tab.

 Click the Wrap Text command again

to unwrap the text.

To merge cells using the Merge & Center

command:
1. Select the cell range you want to merge. In
3. The selected row will be deleted, and those our example, we'll select A1:F1.
around it will shift. In our example, row 10 has 2. Click the Merge & Center command on
moved up, so it's now row 9. the Home tab. In our example, we'll select the
cell range A1:F1.

 It's important to understand the difference

between deleting a row or column and
3. The selected cells will be merged, and the
simply clearing its contents. If you want to
text will be centered.
remove the content from a row or column
without causing others to shift, right-click
a heading, then select Clear Contents from
the drop-down menu.

To access additional merge options:

If you click the drop-down arrow next to
the Merge & Center command on
the Home tab, the Merge drop-down menu will
Wrapping text and merging cells appear.
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
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. In this From here, you can choose to:
example, we'll select the cells in column C.
 8

 Merge & Center: This merges the  Unmerge Cells: This unmerges selected
selected cells into one cell and centers the cells.
text.  Be careful when using this feature. If you
 Merge Across: This merges the selected merge multiple cells that all contain data,
cells into larger cells while keeping Excel will keep only the contents of the
each row separate. upper-left cell and discard everything else.
 Merge Cells: This merges the selected
cells into one cell but does not center the
text.

Tagaran, Cauayan City, Isabela

Telefax: (078) 652-1038
Email: icat_97@[Link]

I. NAME OF PROGRAM Bachelor of Science in Criminology

(BSCrim)
II. COURSE DESCRIPTIVE Software Application (MS Office with COURSE CODE
TITLE / CODE Internet)
Com
III. PRE-REQUISITE / CO- None COURSE CREDIT
REQUISITE UNIT

3
IV. COURSE DESCRIPTION This course introduces the Microsoft
Office. Students will learn about the
features and familiarize Microsoft Word,
Excel and Presentation.

V. MODULE TITLE / Working with Cells and Sheets MODULE NUMBER

NUMBER
8

VI. INCLUSIVE WEEK Week 8

VII. COURSE OUTCOMES Effectively use software applications for documents used in
onboard ships. Evaluate computer networks and troubleshoot
computer.

A. Learning Outcome: At the end of the modules, the students should be able to:

1. format cells and understand number formats;

2. and work with multiple worksheets.
VIII. MODULE RESOURCES

Free Excel 2016 Tutorial at GCFGlobal. (2020). [Link].

[Link]
Prepared by: Checked & Verified by: Approved by:

MARY ROSE L. GLORIA P. BALIMBIN, DR. ESTELITO A.

DERRADA LPT LACAMBRA
Instructor Dept. Head/Coordinator Academic Provost
 9

Introduction:

All cell content uses the same formatting by default, which can make it difficult to read a
workbook with a lot of information. Basic formatting can customize the look and feel of your
workbook, allowing you to draw attention to specific sections and making your content easier to
view and understand.

MODULE 8
Working with Cells and Sheets

Lesson 4: Formatting Cells color to white to make it more readable with

this dark fill color.
Cell borders and fill colors
Cell borders and fill colors allow you to
create clear and defined boundaries for
different sections of your worksheet. Below,
we'll add cell borders and fill color to
our header cells to help distinguish them from
the rest of the worksheet.

To add a fill color:

1. Select the cell(s) you want to modify. 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 Fill Color command, then
select the fill color you want to use. In our 2. On the Home tab, click the drop-down
example, we'll choose a dark gray. arrow next to the Borders command, then
select the border style you want to use. In our
example, we'll choose to display All Borders.

3. The selected fill color will appear in the

selected cells. We've also changed the font
 10

3. The selected border style will appear. 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.

To change horizontal text alignment:

 You can draw borders and change the line In our example below, we'll modify the
style and color of borders with the Draw alignment of our title cell to create a more
Borders tools at the bottom of the Borders polished look and further distinguish it from
drop-down menu. the rest of the worksheet.
1. Select the cell(s) you want to modify.

2. Select one of the three horizontal

alignment commands on the Home tab. In our
Cell styles example, we'll choose Center Align.
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, like titles and headers.

To apply a cell style:

In our example, we'll apply a new cell
style to our existing title and header cells.
1. Select the cell(s) you want to modify.
3. The text will realign.

2. Click the Cell Styles command on

the Home tab, then choose the desired
style from the drop-down menu. To change vertical text alignment:
1. Select the cell(s) you want to modify.

2. Select one of the three vertical

3. The selected cell style will appear. alignment commands on the Home tab. In our
example, we'll choose Middle Align.

 Applying a cell style will replace any

existing cell formatting except for text
alignment. You may not want to use cell
styles if you've already added a lot of
formatting to your workbook.
[Link] text will realign.
Text alignment
 11

 You can apply both vertical and horizontal  You can also select the desired cells and
alignment settings to any cell. press Ctrl+1 on your keyboard to access
additional number-formatting options.
Lesson 5: Understanding Number
Formats In this example, we've applied
What are number formats? the Currency number format, which adds
Whenever you're working with a currency symbols ($) and displays two decimal
spreadsheet, it's a good idea to use places for any numerical values.
appropriate number formats for your data.
Number formats tell your spreadsheet exactly
what type of data you're using, like
percentages (%), currency ($), times, dates,
and so on.

Why use number formats?

Number formats not only make your
spreadsheet easier to read, but they also make
it easier to use. When you apply a number
format, you're telling your spreadsheet
exactly what types of values are stored in a
If you select any cells with number
cell. For example, the date format tells the
formatting, you can see the actual value of
spreadsheet that you're entering specific
the cell in the formula bar. The spreadsheet
calendar dates. This allows the spreadsheet to
will use this value for formulas and other
better understand your data, which can help
calculations.
ensure that your data remains consistent and
that your formulas are calculated correctly.
 If you don't need to use a specific number
format, the spreadsheet will usually apply
the general number format by default.
However, the general format may apply
some small formatting changes to your
data.

Applying number formats

Just like other types of formatting, like
changing the font color, you'll apply number
formats by selecting cells and choosing the Number formatting tips
desired formatting option. There are two main Here are a few tips for getting the best
ways to choose a number format: results with number formatting:
 Go to the Home tab, click the Number  Apply number formatting to an entire
Format drop-down menu in column: If you're planning to use one
the Number group, and select the desired column for a certain type of data, like
format. dates or percentages, you may find it
easiest to select the entire column by
clicking the column letter and applying the
desired number formatting. This way, any
data you add to this column in the future
will already have the correct number
format. Note that the header row usually
won't be affected by number formatting.

 Click one of the quick number-formatting

commands below the drop-down menu.
 12

the cell; instead, they display the value to a

set number of decimal places.

 Double-check your values after

applying number formatting: If you Decreasing the decimal will display the
apply number formatting to existing data, value rounded to that decimal place, but the
you may have unexpected results. For actual value in the cell will still be displayed in
example, applying percentage (%) the formula bar.
formatting to a cell with a value of 5 will
give you 500%, not 5%. In this case, you'd
need to retype the values correctly in each
cell.

 The Increase Decimal and Decrease

Decimal commands don't work with some
number formats, like Date and Fraction.

Lesson 6: Working with Multiple

Worksheets
Every workbook contains at least
one worksheet by default. When working with
a large amount of data, you can
create multiple worksheets to help organize
 If you reference a cell with number your workbook and make it easier to find
formatting in a formula, the spreadsheet content. You can also group worksheets to
may automatically apply the same number quickly add information to multiple worksheets
formatting to the new cell. For example, if at the same time.
you use a value with currency formatting
in a formula, the calculated value will also To insert a new worksheet:
use the currency number format. 1. Locate and select the New sheet button
near the bottom-right corner of the Excel
window.

 If you want your data to appear exactly as

entered, you'll need to use
the text number format. This format is
especially good for numbers you don't
want to perform calculations with, like
phone numbers, zip codes, or numbers
that begin with 0, like 02415. For best
results, you may want to apply the text 2. A new blank worksheet will appear.
number format before entering data into
these cells.

Increase Decimal and Decrease Decimal

The Increase Decimal and Decrease
Decimal commands allow you to control how
many decimal places are displayed in a cell.
These commands don't change the value of
 13

4. The worksheet will be copied. It will have

the same title as the original worksheet, as
well as a version number. In our example, we
copied the November worksheet, so our new
worksheet is named November (2). All content
from the November worksheet has also been
copied to the new worksheet.

 By default, any new workbook you create

in Excel will contain one worksheet,
called Sheet1. To change the default
number of worksheets, navigate
to Backstage view, click Options, then
 You can also copy a worksheet to an
choose the desired number of worksheets
entirely different workbook. You can select
to include in each new workbook.
any workbook that is currently open from
the To book: drop-down menu.
To copy a worksheet:
If you need to duplicate the content of
To rename a worksheet:
one worksheet to another, Excel allows you
1. Right-click the worksheet you want to
to copy an existing worksheet.
rename, then select Rename from the
worksheet menu.
1. Right-click the worksheet you want to copy,
then select Move or Copy from the worksheet
menu.

2. Type the desired name for the worksheet.

3. Click anywhere outside the worksheet tab,

or press Enter on your keyboard. The
2. The Move or Copy dialog box will appear. worksheet will be renamed.
Choose where the sheet will appear in
the Before sheet: field. In our example, we'll
choose (move to end) to place the worksheet
to the right of the existing worksheet.

3. Check the box next to Create a copy, then

click OK.

To move a worksheet:
1. Click and drag the worksheet you want to
move until a small black arrow appears above
the desired location.

2. Release the mouse. The worksheet will be

moved.
 14

2. The worksheet will be deleted from your

workbook.

To delete a worksheet:
1. Right-click the worksheet you want to
delete, then select Delete from the worksheet
menu.

 If you want to prevent specific worksheets

from being edited or deleted, you
can protect them by right-clicking the
desired worksheet and selecting Protect
Sheet from the worksheet menu.

Tagaran, Cauayan City, Isabela

Telefax: (078) 652-1038
Email: icat_97@[Link]

I. NAME OF PROGRAM Bachelor of Science in Criminology

(BSCrim)
II. COURSE DESCRIPTIVE Software Application (MS Office with COURSE CODE
TITLE / CODE Internet)
Com
III. PRE-REQUISITE / CO- None COURSE CREDIT
REQUISITE UNIT

3
IV. COURSE DESCRIPTION This course& Verified
introducesby:the MicrosoftApproved by:
Prepared by: Checked
Office. Students will learn about the
features and familiarize Microsoft Word,
MARY ROSE L. GLORIA
Excel P. BALIMBIN,
and Presentation. DR. ESTELITO A.
DERRADA LPT LACAMBRA
Instructor Dept. Head/Coordinator Academic Provost
 15

V. MODULE TITLE / Formulas and Functions MODULE NUMBER

NUMBER
9

VI. INCLUSIVE WEEK Week 9

VII. COURSE OUTCOMES Effectively use software applications for documents used in
onboard ships. Evaluate computer networks and troubleshoot
computer.

A. Learning Outcome: At the end of the modules, the students should be able to:

1. understand formulas and functions;

2. and creating more complex functions.
VIII. MODULE RESOURCES

Free Excel 2016 Tutorial at GCFGlobal. (2020). [Link].

[Link]

Introduction:

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'll show you how to use cell references to create simple
formulas.

MODULE 9
Formulas and Functions

Lesson 7: Introduction to Formulas

Mathematical operators
Excel uses standard operators for
formulas: a plus sign for addition (+), minus
sign for subtraction (-), asterisk for
multiplication (*), forward slash for division (/),
and 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.
 16

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:

3. Type the cell address of the cell you want to

reference first in the formula: cell D10 in our
example. A blue border will appear around the
referenced cell.

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:

4. Type the mathematical operator you want

to use. In our example, we'll type the addition
sign (+).

5. Type the cell address of the cell you want to

reference second in the formula: cell D11 in
To create a formula: our example. A red border will appear around
In our example below, we'll use a simple the referenced cell.
formula and cell references to calculate a
budget.

1. Select the cell that will contain the formula.

In our example, we'll select cell D12.

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
2. Type the equals sign (=). Notice how it formula bar displays the formula.
appears in both the cell and the formula bar.
 17

4. Type the mathematical operator you want

to use. In our example, we'll type
the multiplication sign (*).
5. Select the cell you want to reference second
in the formula: cell C4 in our example. The cell
address will appear in 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.

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 6. Press Enter on your keyboard. The formula
can save a lot of time and effort when creating will be calculated, and the value will be
formulas. In our example below, we'll create a displayed in the cell.
formula to calculate the cost of ordering
several boxes of plastic silverware.

1. Select the cell that will contain the formula.

In our example, we'll select cell D4.

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
2. Type the equals sign (=). a worksheet. The fill handle is the small square
3. Select the cell you want to reference first in at the bottom-right corner of the selected
the formula: cell B4 in our example. The cell cell(s).
address will appear in the formula. 1. Select the cell containing the formula you
want to copy. Click and drag the fill
handle over the cells you want to fill.
 18

2. After you release the mouse, the formula

will be copied to the selected cells.

3. Double-check your formula for accuracy,

then press Enter on your keyboard. The
formula will calculate and display the result. In
Lesson 8: Creating More Complex our example, the result shows that the
Formulas subtotal for the order is $143.45.
You may have experience working with
formulas that contain only one operator,
like 7+9. More complex formulas can
contain several mathematical operators,
like 5+2*8. When there's 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'll need to
understand the order of operations.

The order of operations

Excel calculates formulas based on the
following order of operations:  You can add parentheses to any equation
1. Operations enclosed in parentheses to make it easier to read. While it won't
2. Exponential calculations (3^2, for example) change the result of the formula in this
3. Multiplication and division, whichever comes example, we could enclose the
first multiplication operations within
4. Addition and subtraction, whichever comes parentheses to clarify that they will be
first calculated before the addition.

To create a complex formula using the

order of operations:
In the example below, we'll use cell
references along with numerical values to
create a complex formula that will calculate
the subtotal for a catering invoice. The formula
will calculate the cost of each menu item first,
then add these values.

1. Select the cell that will contain the formula.

In our example, we'll select cell C5.

Excel will not always tell you if your

formula contains an error, so it's up to you to
check all of your formulas. To learn how to do
this, you can read the Double-Check Your
Formulas lesson from our Excel
Formulas tutorial.

Lesson 9: Functions
A function is a predefined formula that
2. Enter your formula. In our example, we'll performs calculations using specific values in a
type =B3*C3+B4*C4. This formula will follow particular order. Excel includes many common
the order of operations, first performing the functions that can be used to quickly find
multiplication: 2.79*35 = 97.65 and 2.29*20 = the sum, average, count, maximum value,
45.80. It then will add these values to calculate and minimum value for a range of cells. In
the total: 97.65+45.80. 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.

The parts of a function

In order to work correctly, a function
must be written a specific way, which is called
 19

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.

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.
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 4. Press Enter on your keyboard. The function
argument. will be calculated, and the result will appear in
 MIN: This function determines the lowest the cell. In our example, the sum of D3:D12
cell value included in the argument. is $765.29.

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, MAX, and MIN. 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.
 The AutoSum command can also be
accessed from the Formulas tab on
the Ribbon.

2. In the Editing group on the Home tab, click

the arrow next to the AutoSum command.  You can also use the Alt+= keyboard
Next, choose the desired function from the shortcut instead of the AutoSum
drop-down menu. In our example, we'll command. To use this shortcut, hold down
select Sum. the Alt key and then press the equals sign.

To enter a function manually:

 20

If you already know the function name,

you can easily type it yourself. In the example
below (a tally of cookie sales), we'll use
the AVERAGE function to calculate the average 3. Enter the cell range for the
number of units sold by each troop. argument inside parentheses. In our example,
we'll type (C3:C9). This formula will add the
1. Select the cell that will contain the function. values of cells C3:C9, then divide that value by
In our example, we'll select cell C10. the total number of values in the range.

2. Type the equals sign (=), then enter the

desired function name. You can also select the
desired function from the list
of suggested functions that appears below the 4. Press Enter on your keyboard. The function
cell as you type. In our example, we'll will be calculated, and the result will appear in
type =AVERAGE. the cell. In our example, the average number
of units sold by each troop is 849.

Prepared by: Checked & Verified by: Approved by:

MARY ROSE L. GLORIA P. BALIMBIN, DR. ESTELITO A.

DERRADA LPT LACAMBRA
Instructor Dept. Head/Coordinator Academic Provost
 21

Tagaran, Cauayan City, Isabela

Telefax: (078) 652-1038
Email: icat_97@[Link]

I. NAME OF PROGRAM Bachelor of Science in Criminology

(BSCrim)
II. COURSE DESCRIPTIVE Software Application (MS Office with COURSE CODE
TITLE / CODE Internet)
Com
III. PRE-REQUISITE / CO- None COURSE CREDIT
REQUISITE UNIT

3
IV. COURSE DESCRIPTION This course introduces the Microsoft
Office. Students will learn about the
features and familiarize Microsoft Word,
Excel and Presentation.

V. MODULE TITLE / Working with Data MODULE NUMBER

NUMBER
10

VI. INCLUSIVE WEEK Week 10

VII. COURSE OUTCOMES Effectively use software applications for documents used in
onboard ships. Evaluate computer networks and troubleshoot
computer.

A. Learning Outcome: At the end of the modules, the students should be able to:

1. sort and filter data;

VIII. MODULE RESOURCES

Free Excel 2016 Tutorial at GCFGlobal. (2020). [Link].

[Link]
 22

Introduction:

Excel workbooks are designed to store a lot of information. Whether you're

working with 20 cells or 20,000, Excel has several features to help you organize
your data and find what you need. You can see some of the most useful features
below. And be sure to review the other lessons in this tutorial to get step-by-step
instructions for each of these features.

MODULE 10
Working with Data

Lesson 10: Sorting Data

As you add more content to a
worksheet, organizing this information
becomes especially important. You can
quickly reorganize a worksheet by sorting your
data. For example, you can organize a list of
contact information by last name. Content can
be sorted alphabetically, numerically, and in
several other ways.

Types of sorting
When sorting data, it's important to
first decide if you want the sort to apply to
the entire worksheet or just a cell range.
 Sort sheet organizes all of the data in your
worksheet by one column. Related
information across each row is kept To sort a sheet:
together when the sort is applied. In the In our example, we'll sort a T-shirt order form
example below, the Contact Name column alphabetically by Last Name (column C).
(column A) has been sorted to display the 1. Select a cell in the column you want to sort.
names in alphabetical order. In our example, we'll select cell C2.

2. Select the Data tab on the Ribbon, then click

the A-Z command to sort A to Z, or the Z-A
command to sort Z to A. In our example, we'll
sort A to Z.
 Sort range sorts the data in a range of
cells, which can be helpful when working
with a sheet that contains several tables.
Sorting a range will not affect other
content in the worksheet.

3. The worksheet will be sorted by the selected

column. In our example, the worksheet is now
sorted by last name.
 23

To sort a range:
In our example, we'll select a separate
table in our T-shirt order form to sort the 6. The cell range will be sorted by the selected
number of shirts that were ordered on column. In our example, the Orders column
different dates. will be sorted from highest to lowest. Notice
1. Select the cell range you want to sort. In our that the other content in the worksheet was
example, we'll select cell range G2:H6. not affected by the sort.

2. Select the Data tab on the Ribbon, then  If your data isn't sorting properly, double-
click the Sort command. check your cell values to make sure they
are entered into the worksheet correctly.
Even a small typo could cause problems
when sorting a large worksheet. In the
example below, we forgot to include a
hyphen in cell A18, causing our sort to be
slightly inaccurate.

3. The Sort dialog box will appear. Choose

the column you want to sort. In our example,
we want to sort the data by the number of T-
shirt orders, so we'll select Orders. Sorting levels
If you need more control over how your
data is sorted, you can add multiple levels to
any sort. This allows you to sort your data
by more than one column.

To add a level:
In our example below, we'll sort the worksheet
by T-Shirt Size (Column D), then by Homeroom
# (column A).

1. Select a cell in the column you want to sort.

In our example, we'll select cell A2.
4. Decide the sorting order (either ascending
or descending). In our example, we'll
use Largest to Smallest.

5. Once you're satisfied with your selection,

click OK.
 24

2. Click the Data tab, then select

the Sort command.

6. The worksheet will be sorted according to

the selected order. In our example, the orders
are sorted by T-shirt size. Within each group of
T-shirt sizes, students are sorted by homeroom
number.

3. The Sort dialog box will appear. Select the

first column you want to sort. In this example,
we will sort by T-Shirt Size (column D) with the
custom list we previously created for the Order
field.

4. Click Add Level to add another column to

sort.

 If you need to change the order of a

multilevel sort, it's easy to control which
column is sorted first. Simply select the
desired column, then click the Move
Up or Move Down arrow to adjust its
priority.

5. Select the next column you want to sort,

then click OK. In our example, we'll sort
by Homeroom # (column A).

Lesson 11: Filtering Data

 25

If your worksheet contains a lot of 5. The Filter menu will appear.

content, it can be difficult to find information
quickly. Filters can be used to narrow 6. Uncheck the box next to Select All to quickly
down the data in your worksheet, allowing you deselect all data.
to view only the information you need.
In our example, we'll apply a filter to an
equipment log worksheet to display only the
laptops and projectors that are available for
checkout.
1. In order for filtering to work correctly, your
worksheet should include a header row, which
is used to identify the name of each column. In
our example, our worksheet is organized into
different columns identified by the header cells
in row 1: ID#, Type, Equipment Detail, and so
on.

7. Check the boxes next to the data you want

to filter, then click OK. In this example, we will
check Laptop and Projector to view only these
types of equipment.

2. Select the Data tab, then click

the Filter command.

3. A drop-down arrow will appear in the header

cell for each column.

4. Click the drop-down arrow for the column

you want to filter. In our example, we will filter
column B to view only certain types of
equipment.
 26

8. The data will be filtered, temporarily hiding

any content that doesn't match the criteria. In
our example, only laptops and projectors are
visible.

 Filtering options can also be accessed

from the Sort & Filter command on
the Home tab.

Tagaran, Cauayan City, Isabela

Telefax: (078) 652-1038
Email: icat_97@[Link]

I. NAME OF PROGRAM Bachelor of Science in Criminology

(BSCrim)
II. COURSE DESCRIPTIVE Software Application (MS Office with COURSE CODE
TITLE / CODE Internet)
Com
III. PRE-REQUISITE / CO- None COURSE CREDIT
REQUISITE UNIT

3
IV. COURSE DESCRIPTION This course introduces the Microsoft
Office. Students will learn about the
Prepared by: Checked & Verified by: Approved by:
features and familiarize Microsoft Word,
Excel and Presentation.
MARY ROSE L. GLORIA P. BALIMBIN, DR. ESTELITO A.
V. MODULE TITLE
DERRADA / Working withLPT
Data MODULE NUMBER
LACAMBRA
Instructor Dept. Head/Coordinator Academic Provost
 27

NUMBER 11

VI. INCLUSIVE WEEK Week 11

VII. COURSE OUTCOMES Effectively use software applications for documents used in
onboard ships. Evaluate computer networks and troubleshoot
computer.

A. Learning Outcome: At the end of the modules, the students should be able to:

1. create table and charts;

VIII. MODULE RESOURCES

Free Excel 2016 Tutorial at GCFGlobal. (2020). [Link].

[Link]

MODULE 11
Working with Data

Lesson 12: Tables 2. Locate the Table Styles group, then click
Once you've entered information into the More drop-down arrow to see all available
your worksheet, you may want to format your table styles.
data as a table. Just like regular formatting,
tables can improve the look and feel of your
workbook, and they'll also help organize your
content and make your data easier to use.
Excel includes several tools and predefined
table styles, allowing you to create tables
quickly and easily.
3. Select the desired table style.
To change the table style:
1. Select any cell in your table, then click
the Design tab.
 28

1. Select the desired cells for the conditional

formatting rule.

4. The table style will be applied. 2. From the Home tab, click the Conditional
Formatting command. A drop-down menu will
appear.
3. Hover the mouse over the
desired conditional formatting type, then
select the desired rule from the menu that
appears. In our example, we want to highlight
cells that are greater than $4000.

Conditional formatting
Let's say you have a worksheet with
thousands of rows of data. It would be
extremely difficult to see patterns and trends
just from examining the raw information.
Similar to charts and sparklines, conditional
formatting provides a way to visualize data
and make worksheets easier to understand.

Conditional formatting allows you to

automatically apply formatting—such
as colors, icons, and data bars—to one or more
cells based on the cell value. To do this, you'll
need to create a conditional formatting rule.
For example, a conditional formatting rule
might be: If the value is less than $2000, color 4. A dialog box will appear. Enter the desired
the cell red. By applying this rule, you'd be value(s) into the blank field. In our example,
able to quickly see which cells contain values we'll enter 4000 as our value.
less than $2000.
5. Select a formatting style from the drop-
down menu. In our example, we'll
choose Green Fill with Dark Green Text, then
click OK.

6. The conditional formatting will be applied to

To create a conditional formatting rule: the selected cells. In our example, it's easy to
In our example, we have a worksheet see which salespeople reached the $4000
containing sales data, and we'd like to see sales goal for each month.
which salespeople are meeting their monthly
sales goals. The sales goal is $4000 per
month, so we'll create a conditional formatting
rule for any cells containing a value higher
than 4000.
 29

Lesson 13: Charts

It can be difficult to interpret Excel
workbooks that contain a lot of
data. Charts allow you to illustrate your
workbook data graphically, which makes it
easy to visualize comparisons and trends.
In addition to chart types, you'll need to
understand how to read a chart. Charts
contain several elements, or parts, that can
help you interpret data.

 You can apply multiple conditional

formatting rules to a cell range or
worksheet, allowing you to visualize
different trends and patterns in your data.

1. Chart Title - The title should clearly

describe what the chart is illustrating.
2. Vertical Axis - The vertical axis (also
known as the y axis) is the vertical part of the
Conditional formatting presets chart.
Excel has several predefined styles— Here, the vertical axis measures the value of
or presets—you can use to quickly apply the columns. In this example, the measured
conditional formatting to your data. They are value is each genre's total sales.
grouped into three categories: 3. Horizontal Axis- The horizontal axis (also
known as the x axis) is the horizontal part of
 Data Bars are horizontal bars added to the chart.
each cell, much like a bar graph. Here, the horizontal axis identifies
the categories in the chart. In this example,
each genre is placed in its own group.
4. Data Series - The data series consists of
the related data points in a chart. In this
example, as we can see in the legend, the
yellow columns represent net sales in
February.
 Color Scales change the color of each 5. Legend - The legend identifies which data
cell based on its value. Each color scale series each color on the chart represents. In
uses a two- or three-color gradient. For this example, the legend identifies the
example, in the Green-Yellow-Red color different months in the chart.
scale, the highest values are green,
the average values are yellow, and To insert a chart:
the lowest values are red. 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.

 Icon Sets add a specific icon to each cell

based on its value.

2. From the Insert tab, click the

desired Chart command. In our example, we'll
select Column.
 30

3. Choose the desired chart type from the

drop-down menu.

 To edit a chart element, like a chart title,

simply double-click the placeholder and
begin typing.

4. The Selected chart will be inserted into the

worksheet.

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

 If you're not sure which type of chart to

use, the Recommended Charts command
will suggest several charts based on the
source data.

 Excel also includes several chart styles,

which allow you to quickly modify the look
Chart and layout style and feel of your chart. To change the chart
After inserting a chart, there are style, select the desired style from
several things you may want to change about the Chart styles group. You can also click
the way your data is displayed. It's easy to edit the drop-down arrow on the right to see
a chart's layout and style from the Design tab. more styles.
 Excel allows you to add chart elements—
including 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.

 You can also use the chart formatting

shortcut buttons to quickly add chart
elements, change the chart style,
and filter chart data.
 31

To switch row and column data:

Sometimes you may want to change 2. The Change Chart Type dialog box will
the way charts group your data. For example, appear. Select a new chart type and layout,
in the chart below Book Sales data is then click OK. In our example, we'll choose
grouped by genre, with columns for each a Line chart.
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.

1. Select the chart you want to modify.

2. From the Design tab, select the Switch
Row/Column command.

3. The selected chart type will appear. In our

example, the line chart makes it easier to see
trends in sales data over time.
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 working
well in 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.

Prepared by: Checked & Verified by: Approved by:

MARY ROSE L. GLORIA P. BALIMBIN, DR. ESTELITO A.

DERRADA LPT LACAMBRA
Instructor Dept. Head/Coordinator Academic Provost