St.

Scholastica’s Academy
Tabunok, Talisay City, Cebu
Academic Year 2023-2024

Embracing Differences: Uniting Benedictine Youth in Christ

Focus: Community and Discipline
Name: ______________________Section: _____________Date received: _________Date returned: ____________
Subject: Empowerment Technology - Grade 11 All Strands Teacher: Mrs. Jane Shaira B. Cueno

Third Quarter Module

Grade 11

Lesson 4 : The use Advanced Excel Tools to process and present data

LEARNING OUTCOMES:
 Construct formulas to manipulate numeric data in an Excel worksheet
 Organize or narrow down data using the Sort and Filter Features in MS Excel
 Set Up the Chart Function of Excel to represent numeric data in multiple formats

INSTRUCTIONAL MATERIALS:
 Empowerment Technology Book , reference books
 Cellphone with access to data
 Optional: Computer, Laptop, Wi-Fi Connectivity

A. INTRODUCTION
Instruction: Create a Venn diagram and try to compare the similarities and differences of
manual from computerized computation.

Manual Computation Computerized

Computation

Guide Question:
If you are dealing with tons of numbers to be computed, which do you think is the easiest way to
process data and will give a precise results and why?

B. LESSON PROPER

On this module, it will discuss the advance tool of MS Excel. Working with Excel program has already been
discussed when you were in your JHS years. Thus, you already have a prior knowledge about the Excel Program.To
refresh your thoughts about the program, please check on the following terms use in manipulating Excel file.

Definition of Terms:
Spreadsheet - is the area where you arrange , calculate and sort data.
Worksheet - refers to a single spreadsheet or page in Excel.
Workbook - is the collection of worksheet, or sometimes referred to as the Excel File.
Active Cell - is the currently selected cell in a spreadsheet and it is enclosed in a green box.

"Honesty is the first chapter in the book of wisdom." -Thomas Jefferson

 Column - are the vertical lines in Excel that are identified using letters from A-XFD.
Row- are the horizontal lines in Excel that identified using numbers from 1-1,048,576.
Name Box - it is a combination of the column letter and the row number of a selected cell found beside the function box.
Cell Name - it is identified by its column letter and row number.
Cell Range - it refers to a group of selected cells.
How to get the Cell Name in a range?I
t starts with the upper left cell to the bottom right cell.

With the given illustration, Cell Name in the range is A2:B6, separated by a colon (:).

Press F2 - if you want to edit or type a word in a cell.

Alt + Enter - if you want to move the cursor on the next line in a cell.

Adjusting Column and Row Sizes

You might be encountering the different mouse pointer in Excel, so it’s better to review some of the commonly use
pointers.

What is Excel?

Microsoft Excel is a helpful and powerful program for data analysis and documentation. It is a spreadsheet
program, which contains a number of columns and rows , where each intersection of a column and a row is a “cell.”
Each cell contains one point of data or one piece of information. By organizing the information in this way, you can make
information easier to find, and automatically draw information from changing data.1
Computing large data is a bit hassle and tiring, especially if you’re dealing with numerical data such as student’s
grade, accounts, statistical data and many more. It might consume a lot of your time and effort to do it manually. Some
teachers have been doing this routine but what if you were tasked to do the same? How will you work it out? Luckily, MS
Excel has an advance tools that you can make your work easier and effective. Go and check it out below.

Topic: Formulas and Functions

MS Excel or any spreadsheet programs allows you to create formulas that will automatically calculate results.
Without formulas, a spreadsheet is not much more than a large table for displaying text.

What is a formula?
In solving equation in Mathematics, you usually write 5+8=13 or follow the PEMDAS rule if you are solving
complex number problems. But how Excel program works with its formulas? The order of operators in Excel works the
same with what we are used to solve problems in Mathematics, but the symbol are quite different and still works in the
same way.
In solving problems in Math, we usually see the example below.
Illustration 1: (5+3)x2=16
In working with Excel Formula, it doesn’t look that way. Instead equal sign
must be placed at the leftmost part of the equation then the rest follows.
Illustration 2: =(5+3)*2
What will happened if you did not type the equal sign first? The program will
only categorize your data as text. But in an Excel program , you will rarely see
illustration 2 if you are already dealing with tons of data for you will start using the
cell name like what is shown in the illustration 3.

A formula is an equation that makes calculations based on the data in your spreadsheet. Formulas are entered
into a cell in your worksheet. They must begin with an equal sign, followed by the addresses of the cells that will be
calculated upon, with an appropriate operand placed in between. Once the formula is typed into the cell, the calculation
executes immediately. The formula appears in the formula bar. 2

Illustration 3:

1
https://itconnect.uw.edu/learn/workshops/online-tutorials/microsoft-office-2010/microsoft-excel-2010/
2
https://itconnect.uw.edu/learn/workshops/online-tutorials/microsoft-office-2010/microsoft-excel-2010/formulas-and-functions/

"Honesty is the first chapter in the book of wisdom." -Thomas Jefferson

 To know more about the standard operators ,creating and modifying formulas, open and read your Empowerment
Technology E-Book on page 60-62.

Functions
A function is a predefined formula in Excel that performs calculations for a specific value using reference cells. It
can be a more efficient way of performing mathematical operations than formulas. On pages 62, some commonly use
function commands are stated and these are.

1. =SUM(range) – used to compute the to total of all items in the specified range
2. =AVERAGE(range) – used to get the average of the items in a range
3. =MAX(range) – used to get the larges value within the range
4. =MIN(range) – used to get the smallest value within the range
5. =COUNT(range) – used to count the values found in the range.
6. COUNTIF(range) - if you want to count only numbers that meet certain criteria.

How do you use this functions? See the illustrated examples below.

SUM MIN

AVERAGE COUNT

MAX COUNTIF

Topic: Sorting and Filtering

Imagine that you have paperworks that needs to be sorted, of course you can do it manually. But what if, it will be
hundreds of papers worst in digital format, how will obtain those data without keep on scanning the papers? You know
that Excel can sort and filter it all out for you. You plough through the jumble of papers and enter the data into Excel,
ending up with something that looks like the figure below. But then, how you can sort and filter the data in just a quick
snap?

Read page 66 - 73 on your E-Books for you to know how to sort

and filter data in Excel. Make sure that you follow the given examples on
the E- books, so that you will be able to complete the given Performance
task found on next page.

Topic: Charts

To display series of numeric data in a graphical format, you can use MS Excel to make it easier for you to
understand large quantities of data and the relationship between different series of data.
How do we start making a chart in MS Excel? First you need to have the numeric data. Once you have the
numerical data,you may now present the data in a graphical format using charts. But remember in choosing a chart, one
has to know the purpose of it. Charts can be found in the Insert Tab. Once you click charts you can see a variety of
Charts available.Please read page 74-77 to know what are the different types of charts and how to insert, change, move
a chart in the MS Excel.

PERFORMANCE TASK

"Honesty is the first chapter in the book of wisdom." -Thomas Jefferson

 Using the raw data below, try to get what is asked by using MS excel or any spreadsheet program such as Google
Spreadsheet,Office 365 Excel or WPS Spreadsheet. Download the excel file in the LMS class so you don’t have to
type all the data. Look for the file name, “Lesson 4 Performance Task (Working with advance tool in Excel). (50 pts )

How to name your file upon completion and submission of the said task?

Section - Lastname - L4PT.xlsx (file should be sent in an excel format)

LESSON 4 QUIZ
I. Instruction: Name all the charts below. Place your answer on the space provided below the image. (10 pts)

II. Instruction: Read the questions carefully then write the letter of your answer on the space provided. (10pts)
______1. Which of the following is the correct formula to calculate the weighted average score in cell I3 as shown
below?

A. =AVERAGEEXPENSES(B3:H3) C.= AVE(B3:H3)

B. =AVERAGE(B3:H3) D. AVERAGE(B3-H3)

______2. What are the keyboard shortcut keys to edit formula in a cell?
A. F4 B. F10 C. Alt+F3 D. F2
______3. What is the meaning of this symbol ###### in a cell?
A. You've misspelled something. C.You've entered a number wrong.
B. The cell is not wide enough. D. All of the above.
______4. What do you call is a predefined formula that performs calculations using specific values in a
particular order?
A. Cell B. Functions C. Filter D. Library
______5. What is the correct formula to get the sum in a cells?
. A. SUM(B5&B10) C. SUM(B5:B10)
B. SUM (B5+B10) D. SUM (B5-B10)
______6. What do you call the blank boxes on a spreadsheet?
A. Square
B. Cell
C. Spots
D. Active Cell
______7. What do you call a combination of numbers and symbols used to express a calculation. Always
begins with an = sign.
A. Worksheet B. Cell Address C. Functions D. Formula
"Honesty is the first chapter in the book of wisdom." -Thomas Jefferson
 ______8. How do you make a cell the active cell?
A. Cells are always active C. Click INSERT CELL
B. Click on the cell D. Hover the mouse on the cell
______9.What do you call the type of chart that is ideal for showing trends over time?
A. Bar B. Line C. Area D. Scatter
______10. How can you automatically get the sum of large range of data?
A. Auto format C. Auto fill
B. Auto add D. Auto sum
C.
III. Instruction: Using the raw data below, try to get what is asked. Instead of putting the value, place the formula on the
space provided. (30 pts)

C. SYNTHESIS/CLOSURE/WRAP UP
Twitter Board
Summarize what you learned from the lesson in a short sentence.

"Honesty is the first chapter in the book of wisdom." -Thomas Jefferson