Republic of the Philippines

NUEVA VIZCAYA STATE UNIVERSITY

Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

College: INDUSTRIAL TECHNOLOGY

Campus: Bambang Campus

DEGREE BS Industrial COURSE

TGY 2 COMT2
PROGRAM Technology NO.
Computer COURSE
SPECIALIZATION Advance Computer Application
Technology TITLE
TIME WK IM
YEAR LEVEL Second 5 hours 4
FRAME NO. NO.

I. UNIT TITLE/CHAPTER TITLE

SPREADSHEET (MS EXCEL)

II. LESSON TITLE

1. Formulas and Functions

1.1 Introduction to Formulas
1.2 Creating More Complex Formulas
1.3 Relative and Absolute Cell References
1.4 Functions
2. Working with Data
2.1 Freezing Panes and View Options
2.2 Sorting Data
2.3 Filtering Data
2.4 Groups and Subtotals
2.5 Charts
2.6 Conditional Formatting
3. Introduction to Pivot Tables

III. LESSON OVERVIEW

This module covers formulas and functions from simple to complex, relative and
absolute reference, working with data, charts, conditional formatting, and PivotTables.

IV. DESIRED LEARNING OUTCOMES

At the end of the lesson, the students should be able to:

1. Create and work with formulas and functions used to perform

calculations.
2. Create more complex formulas and functions.
3. Use formula cell referencing.
4. Create absolute and mixed references.
5. Create arguments to calculate values and cell references.
6. Sort data in a list in a worksheet.
7. Filter data in a table.
8. Organize data in groups.
9. Create effective charts.
10. Use a range of elements and features to enhance charts.
11. Inspect and protect Excel workbook.
12. Use PivotTables for data analysis.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 1 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

V. LESSON CONTENT

1. Formulas and Functions

1.1 Introduction to Formulas

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.
Mathematical operators

Excel uses standard operators for formulas, such as a plus

sign for addition (+), a minus sign for subtraction (-),
an asterisk for multiplication (*), a forward slash for division
(/), and a caret (^) for exponents.

All formulas in Excel must begin with an equals sign (=). This is
because the cell contains, or is equal to, the formula and the
value it calculates.

Understanding cell references

In creating simple formulas in Excel using numbers (for example, =2+2 or =5*5), most of
the time you will use cell addresses. 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:

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 2 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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:

To create a formula:

In our example below, we'll use a simple 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.

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: cell D10 in our
example. A blue border will appear around the
referenced cell.

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 our
example. A red border will appear around the
referenced cell.

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.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 3 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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. In the example below, we've
modified the value of cell D10 from $1,200
to $1,800. The formula in D12 will
automatically recalculate and display the
new value in cell D12.

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 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. In our example below, we'll create a 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.
2. Type the equals sign (=).
3. Select the cell you want to
reference first in the formula:
cell B4 in our example. The cell
address will appear in the
formula.
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.
6. Press Enter on your keyboard. The
formula will be calculated, and the value will be displayed in the cell.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 4 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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. In our example, we'll select
cell D12.
2. Click the formula bar to edit the formula. You
can also double-click the cell to view and
edit the formula directly within the cell.

3. A border will appear around any referenced

cells. In our example, we'll change the first
part of the formula to reference
cell D10 instead of cell D9.

4. When you're finished, 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.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 5 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Press the Esc key on your keyboard or click the Cancel command in the formula bar to
avoid accidentally making changes to your formula.

To show all of the formulas in a spreadsheet, you can hold the Ctrl key and
press ` (grave accent). The grave accent key is usually located in the top-left corner of
the keyboard. You can press Ctrl+` again to switch back to the normal view.

ACTIVITY 6

1.2 Creating More Complex Formulas

More complex formulas can contain several mathematical operators, such as 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:

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.

Creating complex formulas

Example we'll we want to

calculate the cost of sales
tax for a catering invoice. To
do this, we'll write our formula
as =(D3+D4+D5)*0.075 in
cell D6. This formula will add
the prices of our items, then
multiply that value by the
7.5% tax rate (which is
written as 0.075) to calculate the answer.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 6 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Excel follows the order of

operations and first adds the values
inside the
parentheses: (45.80+68.70+159.60)
= 274.10. It then multiplies that
value by the tax rate: 274.10*0.075.
The result will show that the sales
tax is $20.56.

It's especially important to follow the order of operations when creating a formula.
Otherwise, Excel won't 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.

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.
2. Enter your formula. In our example, we'll type =B3*C3+B4*C4. This formula will
follow the order of operations, first performing the multiplication: 2.79*35 =
97.65 and 2.29*20 = 45.80. It then will add these values to calculate the
total: 97.65+45.80.

3. Double-check your formula for accuracy, then press Enter on your keyboard. The
formula will calculate and display the result. In our example, the result shows that
the subtotal for the order is $143.45.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 7 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Parentheses can be added to any equation to make it easier to read.

Excel will not always tell you if your formula contains an error, so it's up to you to
check all of your formulas.

ACTIVITY 7
1.3 Relative and Absolute Cell References

There are two types of cell references: relative and absolute. Relative and absolute
references behave differently when copied and filled to other cells. Relative
references change when a formula is copied to another cell. Absolute references, on the
other hand, remain constant no matter where they are copied.

Relative references

By default, all cell references are relative references. When copied across multiple
cells, they change based on the relative position of rows and columns. For example, if
you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2.
Relative references are especially convenient whenever you need to repeat the same
calculation across multiple rows or columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each
item's price by the quantity. Instead of creating a new formula for each row, we can
create a single formula in cell D4 and then copy it to the other rows. We'll use relative
references so the formula calculates the total for each item correctly.

1. Select the cell that will

contain the formula. In our
example, we'll select cell D4.
2. Enter the formula to
calculate the desired value. In
our example, we'll
type =B4*C4.
3. Press Enter on your
keyboard. The formula will be
calculated, and the result will be displayed in the cell.

4. Locate the fill handle in the

bottom-right corner of the
desired cell. In our example,
we'll locate the fill handle for
cell D4.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 8 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

5. Click and drag the fill handle over

the cells you want to fill. In our
example, we'll select cells D5:D13.

6. Release the mouse. The formula

will be copied to the selected cells
with relative references, displaying
the result in each cell.

You can double-click

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

Absolute references
There may be times when you do not want a cell reference to change when filling cells.
Unlike relative references, absolute references do not change when copied or filled.
You can use an absolute reference to keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign

($) before the column and row. If it precedes the column or row (but not both), it's known
as a mixed reference.
$A$2 - The column and the row do not change when copied
A$2 - The row does not change when copied
SA2 - The column does not change when copied

You will use the relative (A2) and absolute ($A$2) formats in most formulas. Relative
references are used less frequently.

When writing a formula in Microsoft Excel, you can press the F4 key on your keyboard to
switch between relative, absolute, and mixed cell references, as shown in the video
below. This is an easy way to quickly insert an absolute reference.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 9 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To create and copy a formula using absolute references:

In the example below, we're going to use cell E2 (which contains the tax rate at 7.5%) to
calculate the sales tax for each item in column D. To make sure the reference to the tax
rate stays constant—even when the formula is copied and filled to other cells—we'll
need to make cell $E$2 an absolute reference.

1. Select the cell that will contain

the formula. In our example, we'll
select cell D4.

2. Enter the formula to calculate

the desired value. In our
example, we'll type
=(B4*C4)*$E$2, making $E$2 an
absolute reference.
3. Press Enter on your keyboard.
The formula will calculate, and
the result will display in the cell.

4. Locate the fill handle in the

bottom-right corner of the
desired cell. In our example,
we'll locate the fill handle for
cell D4.

5. Click and drag the fill handle over

the cells you want to fill
(cells D5:D13 in our example).

6. Release the mouse. The formula

will be copied to the selected
cells with an absolute reference,
and the values will be calculated
in each cell.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 10 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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

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

Using cell references with multiple worksheets

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if
you want to reference a specific value from one worksheet to another. To do this, you'll
simply need to begin the cell reference with the worksheet name followed by
an exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its
cell reference would be Sheet1!A1.

Note that if a worksheet name contains a space, you'll need to include single quotation
marks (' ') around the name. For example, if you wanted to reference cell A1 on a
worksheet named July Budget, its cell reference would be 'July Budget'!A1.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 11 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To reference cells across worksheets:

In our succeeding example, we'll refer to a cell with a calculated value between two
worksheets. This will allow us to use the exact same value on two different worksheets
without rewriting the formula or copying data.

1. Locate the cell you want to reference, and

note its worksheet. In our example, we
want to reference cell E14 on the Menu
Order worksheet.

2. Navigate to the desired worksheet.

In our example, we'll select
the Catering Invoice worksheet.

3. Locate and select

the cell where you want
the value to appear. In
our example, we'll select
cell C4.

4. Type the equals sign (=), the sheet name followed by an exclamation point (!),
and the cell address. In our example, we'll type ='Menu Order'!E14.

5. Press Enter on your keyboard. The value of the referenced cell will appear. Now, if
the value of cell E14 changes on the Menu Order worksheet, it will be updated
automatically on the Catering Invoice worksheet.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 12 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

If you rename your worksheet at a later point, the cell reference will be updated
automatically to reflect the new worksheet name.

If you enter a worksheet

name incorrectly,
the #REF! error will appear
in the cell. In our example
below, we've mistyped the
name of the worksheet.
To edit, ignore, or
investigate the error, click
the Error button beside
the cell and choose an option from the menu.

ACTIVITY 8

1.4 Functions

A function is a predefined formula that performs calculations using specific values in a

particular order. Excel includes many common functions that can be used to quickly find
the sum, average, count, maximum value, and minimum value for a range of cells. In
order to use functions correctly, you'll need to understand the different parts of a
function and how to create arguments to calculate values and cell references.

The parts of a function

In order to work correctly, a function must be

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

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed
within parentheses. You can include one argument or multiple arguments, depending
on the syntax required for the function.

For example, the function =AVERAGE(B1:B9) would

calculate the average of the values in the cell range
B1:B9. This function contains only one argument.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 13 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Multiple arguments must be separated by a comma. For example, the

function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three
arguments.

Creating a function

There are a variety of functions available in Excel. Here are some of the most common
functions you'll use:

 SUM: This function adds all of the values of the cells in the argument.
 AVERAGE: This function determines the average of the values included in the
argument. It calculates the sum of the cells and then divides that value by the
number of cells in the argument.
 COUNT: This function counts the number of cells with numerical data in the
argument. This function is useful for quickly counting items in a cell range.
 MAX: This function determines the highest cell value included in the
argument.
 MIN: This function determines the lowest cell value included in the argument.

To create a function using the AutoSum command:

The AutoSum command allows you to automatically insert the most common functions
into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In the example
below, we'll use the SUM function to calculate the total cost for a list of recently
ordered items.

1. Select the cell that

will contain the
function. In our
example, we'll select
cell D13.

2. In the Editing group on the Home tab, click the arrow next to
the AutoSum command. Next, choose the desired function from the drop-down
menu. In our example, we'll select Sum.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 14 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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.

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

The AutoSum command can also be accessed from the Formulas tab on the Ribbon.

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

To enter a function manually:

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 number of units sold by each troop.

1. Select the cell that will contain the function. In our example, we'll select cell C10.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 15 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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

the desired function name. You can
also select the desired function from
the list of suggested functions that
appears below the cell as you type. In
our example, we'll type =AVERAGE.

3. Enter the cell range for the

argument inside parentheses. In our
example, we'll type (C3:C9). This
formula will add the values of cells
C3:C9, then divide that value by the
total number of values in the range.

4. Press Enter on your keyboard. The

function will be calculated, and
the result will appear in the cell. In our
example, the average number of units
sold by each troop is 849.

The Function Library

While there are hundreds of functions in Excel, the ones you'll use the most will depend
on the type of data your workbooks contain. You can even use the Function
Library on the Formulas tab to browse functions by category, such
as Financial, Logical, Text, and Date & Time.

To access the Function Library, select the Formulas tab on the Ribbon. Look for
the Function Library group.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 16 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To insert a function from the Function Library:

In the example, we'll use the COUNTA function to count the total number of items in
the Items column. Unlike COUNT, COUNTA can be used to tally cells that contain data
of any kind, not just numerical data.

1. Select the cell that will contain the function. In our example, we'll select cell B17.
2. Click the Formulas tab on the Ribbon to
access the Function Library.
3. From the Function Library group, select
the desired function category. In our
example, we'll choose More Functions,
then hover the mouse over Statistical.
4. Select the desired function from the
drop-down menu. In our example, we'll
select the COUNTA function, which will
count the number of cells in
the Items column that are not empty.
5. The Function Arguments dialog box will
appear. Select the Value1 field, then
enter or select the desired cells. In our
example, we'll enter the cell
range A3:A12. You may continue to add
arguments in the Value2 field, but in this
case we only want to count the number of
cells in the cell range A3:A12.
6. When you're satisfied, click OK.

7. The function will be calculated, and the result will

appear in the cell. In our example, the result
shows that a total of 10 items were ordered.

The Insert Function command

While the Function Library is a great place to browse for functions, sometimes you may
prefer to search for one instead. You can do so using the Insert Function command.
The Insert Function command can be a powerful way to find a function quickly.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 17 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To use the Insert Function command:

In the example below, we want to find a function that will calculate the number of
business days it took to receive items after they were ordered. We'll use the dates in
columns E and F to calculate the delivery time in column G.

1. Select the cell that will contain

the function. In our example,
we'll select cell G3.
2. Click the Formulas tab on
the Ribbon, then click
the Insert
Function command.

3. The Insert Function dialog box will appear.

4. Type a few keywords describing the

calculation you want the function to
perform, then click Go. In our example,
we'll type count days, but you can also
search by selecting a category from the
drop-down list.

5. Review the results to find the desired

function, then click OK. In our example,
we'll choose NETWORKDAYS, which will count the number of business days
between the ordered date and received date.

6. The Function Arguments dialog box will appear. From here, you'll be able to enter
or select the cells that will make up the arguments in the function. In our example,
we'll enter E3 in the Start_date field and F3 in the End_date field.
7. When you're satisfied, click OK.
8. The function will be calculated, and the result will appear in the cell. In our
example, the result shows that it took four business days to receive the order.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 18 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Like formulas, functions can be copied to

adjacent cells. Simply select the cell that
contains the function, then click and drag
the fill handle over the cells you want to
fill. The function will be copied, and
values for those cells will be calculated
relative to their rows or columns.

ACTIVITY 9

The IF Function

The IF function checks whether a condition is met, and returns one value if true and
another value if false.
1a. For example, take a look at the IF function in cell B2 below.

Explanation: if the price is greater than 500, the IF function returns High, else it returns
Low.

1b. The following IF function produces the exact same result.

Note: you can use the following comparison operators: = (equal to), > (greater than), <
(less than), >= (greater than or equal to), <= (less than or equal to) and <> (not
equal to).
2. Always enclose text in double quotation marks.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 19 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

3a. The formula below calculates the progress between two points in time.

3b. You can use the IF function to display an empty string if the end value hasn't been
entered yet (see row 5).

Explanation: if the end value is not empty (<> means not equal to), the IF function
calculates the progress between the start and end value, else it displays
an empty string (two double quotes with nothing in between).

And/Or Criteria

Use the IF function in combination with the AND function and the OR function.

1. Take a look at the IF function in cell D2.

Explanation: the AND function returns TRUE if the first score is greater than or equal to
60 and the second score is greater than or equal to 90, else it returns
FALSE. If TRUE, the IF function returns Pass, if FALSE, the IF function
returns Fail.

2. Take a look at the IF function in cell D2 below.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 20 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Explanation: the OR function returns TRUE if at least one score is greater than or equal
to 60, else it returns FALSE. If TRUE, the IF function returns Pass, if
FALSE, the IF function returns Fail.
3. Take a look at the IF function in cell D2 below.

Explanation: the AND function above has two arguments separated by a comma (Table,
Green or Blue). The AND function returns TRUE if Product equals "Table"
and Color equals "Green" or "Blue". If TRUE, the IF function reduces the
price by 50%, if FALSE, the IF function reduces the price by 10%.
Nested IF

The IF function in Excel can be nested, when you have multiple conditions to meet. The
FALSE value is being replaced by another IF function to make a further test.

1. Take a look at the nested IF formula in cell C2 below.

Explanation: if the score equals 1, the nested IF formula returns Bad, if the score equals
2, the nested IF formula returns Good, if the score equals 3, the nested IF
formula returns Excellent, else it returns Not Valid. If you have Excel 2016
or later, simply use the IFS function.

2. Take a look at the nested IF formula in cell C2 below.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 21 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Explanation: if the score is less than 60, the nested IF formula returns F, if the score is
greater than or equal to 60 and less than 70, the formula returns D, if the
score is greater than or equal to 70 and less than 80, the formula returns C,
if the score is greater than or equal to 80 and less than 90, the formula
returns B, else it returns A.
More about IF

1. Use IF and AND to test if a value is between two numbers.

Explanation: the AND function returns TRUE if the person is older than 12 and younger
than 20, else it returns FALSE. If TRUE, the IF function returns Yes, if
FALSE, the IF function returns No.
2. You can combine IF with AVERAGE, SUM and other Excel functions.

Explanation: the AND function returns TRUE if the input value is greater than 100 and
the average of the values in the named range Data1 is greater than 100,
else it returns FALSE. If TRUE, the IF function returns the sum of Data2, if
FALSE, the IF function returns 0.

Confused? You can always use the Evaluate Formula tool to step through your IF
formula. This Excel tool helps you understand a formula.
3. Select cell G3 above.
4. On the Formulas tab, in the Formula Auditing
group, click Evaluate Formula.
5. Click Evaluate multiple times.

ACTIVITY 10 ASSIGNMENT #2

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 22 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

VLOOKUP function
What exactly is VLOOKUP?

Basically, VLOOKUP lets you search for specific information in your spreadsheet. For
example, if you have a list of products with prices, you could search for the price of a
specific item.

Once you learn how to use VLOOKUP, you'll be able to use it with larger, more complex
spreadsheets, and that's when it will
become truly useful.

We're going to use VLOOKUP to find

the price of the Photo frame. You
can probably already see that the
price is $9.99, but that's because this
is a simple example.

We'll add our formula to cell F2, but you can add it to any blank cell. As with any formula,
you'll start with an equals sign (=). Then type the formula name. Our arguments will need
to be in parentheses, so type an open parenthesis. So far, it should look like this:

=VLOOKUP(

Adding the arguments

Now, we'll add our arguments. The arguments will tell VLOOKUP what to search for and
where to search.

The first argument is the name of the item you're searching for, which in this case
is Photo frame. Because the argument is text, we'll need to put it in double quotes:

=VLOOKUP("Photo frame"

The second argument is the cell range that contains the data. In this example, our
data is in A2:B16. As with any function, you'll need to use a comma to separate each
argument:

=VLOOKUP("Photo frame", A2:B16

It's important to know that VLOOKUP will always search the first column in this range.
In this example, it will search column A for "Photo frame". The value that it returns (in
this case, the price) will always need to be to the right of that column.

The third argument is the column index number. It's simpler than it sounds: The first
column in the range is 1, the second column is 2, etc. In this case, we are trying to find
the price of the item, and the prices are contained in the second column. This means
our third argument will be 2:

=VLOOKUP("Photo frame", A2:B16, 2

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 23 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

The fourth argument tells VLOOKUP whether to look for approximate matches, and it
can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches.
Generally, this is only useful if the first column has numerical values that have been
sorted. Because we're only looking for exact matches, the fourth argument should
be FALSE. This is our last argument, so go ahead and close the parentheses:

=VLOOKUP("Photo frame", A2:B16, 2, FALSE)

That's it! When you press Enter, it should give you the answer, which is 9.99.

How it works

Let's take a look at how this formula works. It

first searches vertically down the first column
(VLOOKUP is short for vertical lookup).
When it finds "Photo frame", it moves to the
second column to find the price.

As we mentioned earlier, the price needs to be to the right of the item name. VLOOKUP
cannot look to the left of the column that it's searching.

If we want to find the price of a different item, we can just change the first argument:

=VLOOKUP("T-shirt", A2:B16, 2, FALSE)

or:

=VLOOKUP("Gift basket", A2:B16, 2, FALSE)

It would be very tedious to edit your VLOOKUP formula whenever you want to find the
price of a different item. In the next example, we'll show how to avoid this by using a cell
reference.

Another example

In the previous example, we typed the item name directly into the VLOOKUP formula.
But in the real world, you'll usually use a cell reference instead. In this example, we'll
type the item name in cell E2, and our VLOOKUP formula can then use a cell reference
to find information about that product. Then, we can simply type a new item name into
E2 to find any product we want.

We've also added a third column that has the category for each item. This will give us
the option of finding the price or category. Here's what the spreadsheet looks like so far:

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 24 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Our formula will be similar to the previous example, but we'll need to change the first
three arguments. Let's start by changing the first argument to a cell reference (make
sure to remove the quotation marks):

=VLOOKUP(E2, A2:B16, 2, FALSE)

To find the category, we'll need to change the second and third arguments. First, we'll
change the range to A2:C16 so it includes the third column. Next, we'll change the
column index number to 3 because our categories are in the third column:

=VLOOKUP(E2, A2:C16, 3, FALSE)

When you press Enter, you'll see that the Gift basket is in the Gifts category.

If we want to find the category of a different

item, we can simply change the item name
in cell E2:

ACTIVITY 11 ASSIGNMENT #3 EXCEL QUIZ # 2

2. Working with Data

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
check out the other lessons in this tutorial to get step-by-step instructions for each of
these features.

2.1 Freezing Panes and View Options

Whenever you're working with a lot of data, it can be difficult to compare information in
your workbook. Fortunately, Excel includes several tools that make it easier to view

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 25 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

content from different parts of your workbook at the same time, including the ability
to freeze panes and split your worksheet.

To freeze rows:

You may want to see certain rows or columns all the time in your worksheet,
especially header cells. By freezing rows or columns in place, you'll be able to scroll
through your content while continuing to view the frozen cells.

1. Select the row below the row(s) you want to freeze. In our example, we want to
freeze rows 1 and 2, so we'll select row 3.
2. On the View tab, select the Freeze Panes command, then choose Freeze
Panes from the drop-down menu.

3. The rows will be frozen in place, as indicated by the gray line. You can scroll
down the worksheet while continuing to view the frozen rows at the top. In our
example, we've scrolled down to row 18.

To freeze columns:

1. Select the column to the right of the column(s) you want to freeze. In our example,
we want to freeze column A, so we'll select column B.

2. On the View tab, select the Freeze Panes command, then choose Freeze
Panes from the drop-down menu.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 26 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

3. The column will be frozen in place, as indicated by the gray line. You can scroll
across the worksheet while continuing to view the frozen column on the left. In our
example, we've scrolled across to column E.

If you only need to freeze the top

row (row 1) or first column (column A)
in the worksheet, you can simply
select Freeze Top Row or Freeze First
Column from the drop-down menu.

To unfreeze panes:

If you want to select a different view

option, you may first need to reset the
spreadsheet by unfreezing panes.
To unfreeze rows or columns, click
the Freeze Panes command, then
select Unfreeze Panes from the drop-
down menu.

Other view options

If your workbook contains a lot of content, it can sometimes be difficult to compare

different sections. Excel includes additional options to make your workbooks easier to
view and compare. For example, you can choose to open a new window for your
workbook or split a worksheet into separate panes.

To open a new window for the current workbook:

Excel allows you to open multiple windows for a single workbook at the same time. In
our example, we'll use this feature to compare two different worksheets from the same
workbook.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 27 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

1. Click the View tab on the Ribbon, then select

the New Window command.

2. A new window for the workbook will appear.

3. You can now compare different

worksheets from the same workbook
across windows. In our example, we'll
select the 2013 Sales Detailed
View worksheet to
compare 2012 and 2013 sales.

If you have several windows open at the same time,

you can use the Arrange All command to rearrange
them quickly.

To split a worksheet:

Sometimes you may want to compare different sections of the same workbook without
creating a new window. The Split command allows you to divide the worksheet into
multiple panes that scroll separately.

1. Select the cell where you want to split the

worksheet. In our example, we'll select
cell D6.
2. Click the View tab on the Ribbon, then
select the Split command.
3. The workbook will be split into

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 28 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

different panes. You can scroll

through each pane separately using
the scroll bars, allowing you to
compare different sections of the
workbook.

4. After creating a split, you can click

and drag the vertical and horizontal dividers to change the size of each section.

To remove the split, click the Split command again.

ACTIVITY 12

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

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
together when the sort is applied. In the
example below, the Contact Name column
(column A) has been sorted to display the
names in alphabetical order.

 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 on the worksheet.

To sort a sheet:

In our example, we'll sort a T-shirt order form alphabetically by Last Name (column C).

1. Select a cell in the column you want to sort by. In our example, we'll select cell C2.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 29 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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.

3. The worksheet will be sorted by the selected column. In our example, the worksheet
is now sorted by last name.

To sort a range:

In our example, we'll select a separate table in our T-shirt order form to sort the number
of shirts that were ordered on different
dates.

1. Select the cell range you want to sort. In

our example, we'll select cell
range G2:H6.

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

the Sort command.

3. The Sort dialog box will

appear. Choose
the column you want to sort
by. In our example, we want
to sort the data by the
number of T-shirt orders, so
we'll select Orders.
4. Decide the sorting
order (either ascending or

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 30 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

descending). In our example, we'll use Largest to Smallest.

5. Once you're satisfied with your selection, click OK.
6. The cell range will be sorted by the selected
column. In our example, the Orders column will be
sorted from highest to lowest. Notice that the
other content in the worksheet was not affected by
the sort.

Custom sorting

Sometimes you may find that the default sorting options can't sort data in the order you
need. Fortunately, Excel allows you to create a custom list to define your own sorting
order.

To create a custom sort:

In our example below, we want to sort the worksheet by T-Shirt Size (column D). A
regular sort would organize the sizes alphabetically, which would be incorrect. Instead,
we'll create a custom list to sort from smallest to largest.

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

example, we'll select cell D2.
2. Select the Data tab, then click the Sort command.

3. The Sort dialog box will appear. Select the column you
want to sort by, then choose Custom List... from
the Order field. In our example, we will choose to sort by T-
Shirt Size.

4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom
Lists: box.
5. Type the items in the desired custom order in
the List entries: box. In our example, we want
to sort our data by T-shirt size
from smallest to largest, so we'll
type Small, Medium, Large, and X-Large,
pressing Enter on the keyboard after each item.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 31 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

6. Click Add to save the new sort order. The new list will be added to the Custom
lists: box. Make sure the new list is selected, then click OK.

7. The Custom Lists dialog box will

close. Click OK in the Sort dialog box
to perform the custom sort.

8. The worksheet will be sorted by the custom

order. In our example, the worksheet is now
organized by T-shirt size from smallest to
largest.

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), and then
by Homeroom Number (column A).

1. Select a cell in the column you want to sort by. In our example, we'll select cell A2.
2. Click the Data tab, then select the Sort command.
3. The Sort dialog box will appear. Select the first column you want to sort by. 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
by.

5. Select the next column you want to sort by, then click OK. In our example, we'll sort
by Homeroom # (column A).

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 32 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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.

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.

ACTIVITY 13

4.2 Filtering Data

If your worksheet contains a lot of content, it can be difficult to find information

quickly. Filters can be used to narrow down the data in your worksheet, allowing you to
view only the information you need.

To filter data:

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.
2. Select the Data tab, then click the Filter command.

3. A drop-down arrow will appear in the header cell for each column.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 33 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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.

5. The Filter menu will appear.

6. Uncheck the box next to Select All to quickly
deselect all data.

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.

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.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 34 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Filtering options can also be accessed from the Sort & Filter command on
the Home tab.

To apply multiple filters:

Filters are cumulative, which means you can apply multiple filters to help narrow down
your results.

1. Click the drop-down arrow for

the column you want to filter. In
this example, we will add a filter
to column D to view information
by date. The Filter menu will
appear.

2. Check or uncheck the boxes depending on the data

you want to filter, then click OK. In our example, we'll
uncheck everything except for August.

3. The new filter will be applied.

In our example, the
worksheet is now filtered to
show only laptops and
projectors that were checked
out in August.

To clear a filter:

After applying a filter, you may want to remove—or clear—it from your worksheet so
you'll be able to filter content
in different ways.

1. Click the drop-down

arrow for the filter you
want to clear. In our
example, we'll clear the
filter in column D.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 35 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

2. The Filter menu will appear.

3. Choose Clear Filter From [COLUMN NAME] from the
Filter menu. In our example, we'll select Clear Filter
From "Checked Out".

4. The filter will be cleared from the column. The

previously hidden data will be displayed.

To remove all filters from

your worksheet, click
the Filter command on
the Data tab.

ACTIVITY 14

4.3 Groups and Subtotals

Worksheets with a lot of content can sometimes feel overwhelming and even become
difficult to read. Fortunately, Excel can organize data into groups, allowing you to
easily show and hide different sections of your worksheet. You can also summarize
different groups using the Subtotal command and create an outline for your worksheet.

To group rows or columns:

1. Select the rows or columns you want to group. In this example, we'll select
columns B, C, and D.
2. Select the Data tab on the Ribbon, then click
the Group command.
3. The selected rows or columns will be grouped. In our example,
columns B, C, and D are grouped.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 36 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To ungroup data, select the grouped rows or columns, then click

the Ungroup command.

To hide and show groups:

1. To hide a group, click the minus sign, also

known as the Hide Detail button.

2. The group will be hidden. To show a hidden group, click the plus sign, also known
as the Show Detail button.

Creating subtotals

The Subtotal command allows you to automatically create groups and use common
functions like SUM, COUNT, and AVERAGE to help summarize your data.

Your data must be correctly sorted before using the Subtotal command.

To create a subtotal:

In our example, we'll use the Subtotal command

with a T-shirt order form to determine how many
T-shirts were ordered in each size (Small,
Medium, Large, and X-Large). This will create
an outline for our worksheet with a group for
each T-shirt size and then count the total
number of shirts in each group.

1. First, sort your worksheet by the data you

want to subtotal. In this example, we'll create
a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size
from smallest to largest.
2. Select the Data tab, then click the Subtotal command.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 37 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

3. The Subtotal dialog box will appear. Click the drop-

down arrow for the At each change in: field to select
the column you want to subtotal. In our example, we'll
select T-Shirt Size.
4. Click the drop-down arrow for the Use function: field
to select the function you want to use. In our
example, we'll select COUNT to count the number of
shirts ordered in each size.

5. In the Add subtotal to: field, select

the column where you want the calculated
subtotal to appear. In our example, we'll select T-
Shirt Size. When you're satisfied with your selections,
click OK.

6. The worksheet will be outlined into groups, and

the subtotal will be listed below each group. In our
example, the data is now grouped by T-shirt size, and
the number of shirts ordered in that size appears
below each group.

To view groups by level:

When you create subtotals, your worksheet it is divided into different levels. You can
switch between these levels to quickly control how much information is displayed in the
worksheet by clicking the Level buttons to the left of the worksheet. In our example, we'll
switch between all three levels in our outline. While this example contains only three
levels, Excel can accommodate up to eight.

1. Click the lowest level to display the least detail. In our example, we'll select level 1,
which contains only the grand count, or total number of T-shirts ordered.

2. Click the next level to expand the detail. In our example, we'll select level 2, which
contains each subtotal row but hides all other data from the worksheet.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 38 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

3. Click the highest level to view and expand

all of your worksheet data. In our example,
we'll select level 3.

You can also use the Show and Hide Detail buttons to show and hide the groups within
the outline.

To remove subtotals:

Sometimes you may not want to keep subtotals in your worksheet, especially if you want
to reorganize data in different ways. If you no longer want to use subtotaling, you'll
need remove it from your worksheet.

1. Select the Data tab, then click the Subtotal command.

2. The Subtotal dialog box will appear. Click Remove All.
3. All worksheet data will be ungrouped, and the subtotals will be removed.

To remove all groups without deleting the subtotals, click the Ungroup command drop-
down arrow, then choose Clear Outline.

ACTIVITY 15

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

Understanding charts

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

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

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 39 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

To insert a chart:

1. Select the cells you want to chart, including the column titles and row labels.
These cells will be the source data for the chart. In our example, we'll select cells
A1:F6.
2. From the Insert tab, click the desired Chart command. In our example, we'll
select Column.

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

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

Chart and layout style

After inserting a chart, there are several things you may want to change about the way
your data is displayed. It's easy to edit a
chart's layout and style from the Design tab.

 Excel allows you to add chart elements—such as chart

titles, legends, and data labels—to make your chart
easier to read. To add a chart element, click the Add
Chart Element command on the Design tab, then
choose the desired element from the drop-down menu.
 To edit a chart element, like a chart title, simply double-
click the placeholder and begin typing.

If you don't want to add chart elements individually, you can use one
of Excel's predefined layouts. Simply click the Quick

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 40 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Layout command, then choose the desired layout from the

drop-down menu.

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

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

Other chart options

There are many other ways to customize and organize your charts. For example, Excel
allows you to rearrange a chart's data, change the chart type, and even move the chart
to a different location in a workbook.

To switch row and column data:

Sometimes you may want to change the way charts group your data. For example, in
the chart below Book Sales data is grouped by genre, with columns for each month.
However, we could switch the rows and columns so the chart will group the data by
month, with columns for each genre. In both cases, the chart contains the same data—
it's just organized differently.

1. Select the chart you want to modify.

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

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 41 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

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

To change the chart type:

If you find that your data isn't well suited to a certain chart, it's easy to switch to a new
chart type. In our example, we'll change our chart from a column chart to a line chart.

1. From the Design tab, click

the Change Chart Type command.

2. The Change Chart Type dialog box will

appear. Select a new chart type and layout,
then click OK. In our example, we'll choose
a Line chart.

3. The selected chart type will appear. In our

example, the line chart makes it easier to see
t
r
e
n
ds in sales data over time.

To move a chart:

Whenever you insert a new chart, it will appear as an object on the same worksheet that
contains its source data. Alternatively, you can move the chart to a new worksheet to
help keep your data organized.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 42 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

1. Select the chart you want to

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

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

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the
new data. To fix this, you can adjust the data range. Simply click the chart, and it will
highlight the data range in your spreadsheet. You can then click and drag the handle in
the lower-right corner to change the data range.

ACTIVITY 16 ASSIGNMENT #4

4.5 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 another way to visualize data
and make

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.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 43 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

For example, a conditional

formatting rule might be: If
the value is less than
$2000, color the cell red.
By applying this rule, you'd
be able to quickly see
which cells contain values
less than $2000.

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd like to see 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.

1. Select the desired cells for the conditional

formatting rule.
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.

4. A dialog box will appear. Enter the desired value(s) into the blank field. In our
example, we'll enter 4000 as our value.
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 the selected cells. In our
example, it's easy to see which
salespeople reached the $4000
sales goal for each month.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 44 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

You can apply multiple conditional formatting

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

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to quickly apply
conditional formatting to your data.
They are grouped into three
categories:

 Data Bars are horizontal bars

added to each cell, much like a bar
graph.

 Color Scales change the color of

each cell based on its value. Each
color scale uses a two- or three-
color gradient. For example, in
the Green-Yellow-Red color
scale, the highest values are
green, the average values
are yellow, and
the lowest values are red.
 Icon Sets add a specific
icon to each cell based on
its value.

To use preset conditional formatting:

1. Select the desired cells for the conditional

formatting rule.
2. Click the Conditional Formatting command. A
drop-down menu will appear.
3. Hover the mouse over the desired preset, then
choose a preset style from the menu that
appears.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 45 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

4. The conditional formatting will be applied to the selected cells.

Removing conditional formatting

1. Click the Conditional Formatting command.
A drop-down menu will appear.
2. Hover the mouse over Clear Rules, and
choose which rules you want to clear. In our
example, we'll select Clear Rules from
Entire Sheet to remove all conditional
formatting from the worksheet.

3. The conditional formatting will be removed.

Click Manage Rules to edit or delete individual rules. This is especially useful if you've
applied multiple rules to a worksheet.

ACTIVITY 17 EXCEL QUIZ #3

3. Pivot Tables

When you have a lot of data, it can sometimes be difficult to analyze all of the
information in your worksheet. PivotTables can help make your worksheets more
manageable by summarizing your data and allowing you to manipulate it in different
ways.
“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 46 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Using PivotTables to answer questions

Consider the example below. Let's say we wanted to answer the question What is the
amount sold by each salesperson? Answering it could be time consuming and
difficult; each salesperson appears on multiple rows, and we would need to total all of
their different orders individually. We could use the Subtotal command to help find the
total for each salesperson, but we would still
have a lot of data to work with.

Fortunately, a PivotTable can instantly

calculate and summarize the data in a way
that will make it much easier to read. When
we're done, the PivotTable will look something
like the figure on the right:

Once you've created a PivotTable, you

can use it to answer different questions
by rearranging—or pivoting—the data.
For example, let's say we wanted to
answer What is the total amount sold
in each month? We could modify our
PivotTable to look like this:

To create a PivotTable:

1. Select the table or cells (including column headers) you want to include in your
PivotTable.
2. From the Insert tab, click
the PivotTable command.
3. The Create PivotTable dialog box
will appear. Choose your settings,
then click OK. In our example,
we'll use Table1 as our source
data and place the PivotTable on
a new worksheet.

4. A blank PivotTable and Field List will appear on a new worksheet.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 47 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

5. Once you create a PivotTable, you'll need to decide

which fields to add. Each field is simply a column
header from the source data. In the PivotTable Field
List, check the box for each field you want to add. In
our example, we want to know the total amount sold
by each salesperson, so we'll check
the Salesperson and Order Amount fields.

6. The selected fields will be added to one of the four

areas below. In our example, the Salesperson field has
been added to the Rows area, while Order
Amount has been added to Values. Alternatively, you
can drag and drop fields directly into the desired area.

7. The PivotTable will calculate and summarize the

selected fields. In our example, the PivotTable shows
the amount sold by each salesperson.

Just like with normal spreadsheets, you can sort the

data in a PivotTable using the Sort & Filter command
on the Home tab. You can also apply any type
of number formatting you want. For example, you
may want to change the number format to Currency.
However, be aware that some types of formatting may

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 48 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

disappear when you modify the PivotTable.

If you change any of the data in your source worksheet, the PivotTable will not update
automatically. To manually update it, select the PivotTable and then go to Analyze
Refresh.

Pivoting data
One of the best things about PivotTables is that they can quickly pivot—or reorganize—
your data, allowing you to examine your worksheet in several ways. Pivoting data can
help you answer different questions and even experiment with your data to discover
new trends and patterns.

To add columns:

So far, our PivotTable has only shown one column of data

at a time. In order to show multiple columns, you'll need to
add a field to the Columns area.

1. Drag a field from the Field List into the Columns area.
In our example, we'll use the Month field.

2. The PivotTable will

include multiple columns.
In our example, there is
now a column for each
person's monthly sales,
in addition to the grand
total.

To change a row or column:

Changing a row or column can give you a completely different perspective on your data.
All you have to do is remove the field in question, then replace it with another.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 49 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

1. Drag the field you want to remove out of

its current area. You can also uncheck the
appropriate box in the Field List. In this example,
we've removed
the Month and Salesperson fields.

2. Drag a new field into the desired area. In our

example, we'll place the Region field
under Rows.

3. The PivotTable will adjust—or pivot—to show the new data.

In our example, it now shows the amount sold by each
region.

ACTIVITY 18

Filters

Sometimes you may want focus on a certain section of your data. Filters can be used
to narrow down the data in your PivotTable, so you can view only the information you
need.

To add a filter:

In the example below, we'll filter out certain salespeople to determine how their
individual sales are impacting each region.
“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 50 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

1. Drag a field from the Field List to the Filters area. In this
example, we'll use the Salesperson field.

2. The filter will appear above the PivotTable. Click the drop-
down arrow, then check the box next to Select Multiple
Items.

3. Uncheck the box next to any item you don't want to

include in the PivotTable. In our example, we'll
uncheck the boxes for a few salespeople, then
click OK.

4. The PivotTable will adjust to reflect the

changes.

Slicers

Slicers make filtering data in PivotTables even easier. Slicers are basically
just filters but are easier and faster to use, allowing you to instantly pivot your data. If
you frequently filter your PivotTables, you may want to consider using slicers instead of
filters.

To add a slicer:

1. Select any cell in the PivotTable.

2. From the Analyze tab, click the Insert Slicer command.
3. A dialog box will appear. Check the box next to the
desired field. In our example, we'll select Salesperson, then
click OK.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 51 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

4. The slicer will appear next to the

PivotTable. Each selected item
will be highlighted in blue. In the
example below, the slicer contains
all eight salespeople, but
only five of them are currently
selected.

5. Just like filters, only selected items are

used in the PivotTable. When
you select or deselect an item, the
PivotTable will instantly reflect the change.
Try selecting different items to see how
they affect the PivotTable. Press and hold
the Ctrl key on your keyboard to
select multiple items at once.

You can also click the Filter icon in the top-

right corner of the slicer to select all items at once.

PivotCharts

PivotCharts are like regular charts, except they display data from a PivotTable. Just like
regular charts, you'll be able to select a chart type, layout, and style that will best
represent the data.

To create a PivotChart:

In the example below, our PivotTable is showing a portion of each region's sales
figures. We'll use a PivotChart so we can see the information more clearly.

1. Select any cell in your PivotTable.

2. From the Insert tab, click the PivotChart command.

3. The Insert Chart dialog box will appear. Select

the desired chart type and layout, then
click OK.

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 52 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

4. The PivotChart will appear.

Try using filters or slicers to narrow down the data in your PivotChart. To view different
subsets of information, change the columns or rows in your PivotTable.

ACTIVITY 19 EXCEL QUIZ #4

VI. LEARNING ACTIVITIES

The purpose of this activity is to give you practice on all lessons covered in this module.
Filename Format: ActNumber_Lastname (ex. Act2_Fernandez)

RUBRICS FOR LEARNING ACTIVITIES

Categories Excellent Fair Poor
Less than 60% of the
60% to 75% of the
Required Elements All the requirements requirements were
requirements were
(10 points) were met (10) met, activity needs to
met (7)
be re-done (3)
Completed the
Completion of Task Completed the Did not complete the
activity and was
and Timeliness activity using all due activity
turned in by due
(5 points) date extensions (3) (1)
date (5)
Total (15 points)

VII. EVALUATION

VIII. ASSIGNMENT
Excel Assignment #1
 What are the different ways in formatting numbers?
Excel Assignment #2
 Write an IF statement so that if the number in Cell A2 is less than 100 the
formula displays the text “Within budget”, otherwise the formula displays
the text “Over budget”.
Excel Assignment #3
 What is the difference of Formula and Function?

IX. REFERENCES
Baycon Group, Inc. (n.d.). Microsoft Excel 2016 Tutorial - Free and Online. Retrieved
September 10, 2020, from [Link]
2016-tutorial-free-and-online/

[Link] (n.d.). IF function in Excel. Retrieved September 10, 2020, from

[Link]

GCFGlobal (n.d.). Free Excel 2016 Tutorial. Retrieved September 12, 2020, from
[Link]

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 53 of 54

 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.:TGY 2 COMT 2-2NDSEM-2024-2025

Guru99.(n.d.).Microsoft Excel Training Tutorial: Learn in 7 Days. Retrieved September 8,

2020, from [Link]

Rivers, D. (1 December 2015). Learning Excel 2016. Retrieved September 13, 2020,
from [Link]

[Link]. (n.d.). MS Excel 2016: Basics (Tutorial Complete). Retrieved

September 18, 2020, from
[Link]

“In accordance with Section 185. Fair Use of Copyrighted Work of Republic Act 8293, the copyrighted works included in this material
may be reproduced for educational purposes only and not for commercial distribution.”

NVSU-FR-ICD-05-00 (081220) Page 54 of 54