FELEC (ELECTIVE 2)
=’Sales Data’!D10 – wants a summary worksheet to display the total from cell D10
=’Monthly Sales’!B2 – retrieves the value from B2
! – separates the worksheet name from cell reference
= - symbol that must every formula begin
=data*data – used to calculate the total price of an item
=SUM(data:data) – correctly adds the value
Cross-sheet Reference – allows formula to retrieves information from another worksheet
HOW TO APPLY CELL FORMATTING
- Open Browser > select cells > save
Formula – user-created expression used to calculate values
Insert tab – commonly used to create charts
Data tab – contains tool for sorting and filtering
Alignment – formatting option used to place a heading in the middle of a cell
Alphabet – characters used to identify columns
Tab – key combination that moves the active cell (one cell to left)
Border – tool that adds lines around the selected cells
Fill Color – tool used to give column headings colored background
Line Chart – used to show trends overtime
Sum – function used to add numbers together
Average – function that calculates the mean
Function – predefined formula that performs calculations
Introduction to Microsoft Excel
Microsoft Excel is a spreadsheet application developed by Microsoft that allows users to organize, calculate, analyze, and present data using rows and columns. It is commonly used in schools, offices, businesses, and research environments for handling numerical and textual information.
Understanding the Excel Interface
When Microsoft Excel opens, several interface elements appear. Learning their functions helps users work efficiently.
Main Parts of the Excel Interface
1. Title Bar
● Located at the top of the screen.
● Displays the workbook name and application title.
● Includes window controls (minimize, maximize, close).
Function: Helps identify the current workbook.
2. Quick Access Toolbar
● Contains frequently used commands.
● Examples:
o Save
o Undo
o Redo
Function: Provides easy access to commonly used tools.
3. Ribbon
The Ribbon contains tabs and groups of commands.
Common tabs:
Home Tab
● Clipboard
● Font formatting
● Alignment
● Number formatting
● Styles
Insert Tab
● Tables
● Charts
● Pictures
● Shapes
Page Layout Tab
● Themes
● Margins
● Orientation
Formulas Tab
● Functions
● Formula tools
Data Tab
● Sorting
● Filtering
Review Tab
● Spell check
● Comments
View Tab
● Zoom
● Workbook views
Function: Organizes commands for easier navigation.
4. Name Box
Displays the address of the active cell.
Example:
If cell B4 is selected, the Name Box shows B4.
Function: Identifies selected cells.
5. Formula Bar
Displays and edits data or formulas entered into cells.
Example:
=SUM(A1:A5)
Function: Allows users to edit content and formulas.
6. Worksheet Area
The main workspace consisting of rows and columns.
● Rows = Horizontal
● Columns = Vertical
A cell is formed where rows and columns intersect.
Example:
Cell C3
7. Sheet Tabs
Located at the bottom.
Examples:
● Sheet1
● Sheet2
● Sheet3
Function: Switch between worksheets.
Toolbar Navigation Tips
Click tabs to display commands.
Hover over icons to view tool descriptions.
Use keyboard shortcuts:
Ctrl + S = Save
Ctrl + C = Copy
Ctrl + V = Paste
Ctrl + Z = Undo
Topic 2: Create and Save a Spreadsheet with Correct Structure
Steps in Creating a Spreadsheet
Step 1: Open Microsoft Excel
● Click Start Menu
● Search "Microsoft Excel"
● Open the application
Step 2: Select Blank Workbook
Click:
Blank Workbook
A new spreadsheet opens.
Step 3: Enter Data Properly
Example:
Student Name | Subject | Score |
John | Math | 90 |
Ana | Science | 88 |
Guidelines:
● Put headings at the first row.
● Keep labels clear.
● Place similar data in one column.
Step 4: Arrange Data Correctly
Maintain a structured format:
✓ Column A = Names
✓ Column B = Subjects
✓ Column C = Scores
Avoid mixing unrelated information.
Step 5: Save Your Workbook
Steps:
Click File
Select Save As
Choose location
Enter filename
Click Save
Shortcut:
Ctrl + S
File Formats in Excel
Common file types:
.xlsx
● Standard Excel workbook
.xls
● Older Excel format
.csv
● Comma-separated values
● Portable document format
Topic 3: Understand Spreadsheet Environment and Cell Formatting
What is Cell Formatting?
Cell formatting changes the appearance of data without changing its value.
Formatting improves readability and presentation.
Types of Cell Formatting
1. Font Formatting
Includes:
● Font style
● Font size
● Bold
● Italic
● Underline
● Font color
Example:
Sales Report
2. Alignment Formatting
Controls placement of content.
Options:
● Left
● Center
● Right
● Top
● Middle
● Bottom
3. Number Formatting
Changes display style of numbers.
Examples:
Value | Format |
0.25 | 25% |
500 | ₱500.00 |
3/21/2026 | Date |
4. Borders
Adds lines around cells.
Purpose:
● Organizes information
● Improves readability
5. Fill Color
Adds background color.
Uses:
● Highlight important data
● Categorize entries
Steps to Apply Formatting
Select cells.
Go to Home tab.
Choose formatting tools.
Apply desired changes.
Importance of Formatting
Formatting helps:
● Organize information
● Improve appearance
● Emphasize important data
● Increase readability
Introduction
Data entry is one of the most important skills in Microsoft Excel. Proper data entry ensures accuracy, consistency, and organization of information.
Examples of data entered in Excel:
● Names of students
● Grades
● Attendance records
● Inventory lists
● Sales data
● Contact information
Understanding the Excel Worksheet Structure
An Excel worksheet is made up of:
● Rows – Horizontal lines identified by numbers (1,2,3...)
● Columns – Vertical lines identified by letters (A,B,C...)
● Cells – The intersection of rows and columns
Example:
● A1 = Column A and Row 1
● C5 = Column C and Row 5
This cell reference helps identify where data is stored.
Steps in Entering Data Properly
Step 1: Open Microsoft Excel
Click the Start button.
Search for Microsoft Excel.
Open a Blank Workbook.
Step 2: Select a Cell
Click a cell where you want to enter information.
Example:
Click A1
Step 3: Type Data
You may enter:
Text
● Student Name
● Address
● Subject
Numbers
● Grades
● Scores
● Quantities
Dates
● January 15, 2026
● 05/24/2026
Step 4: Press Enter
After typing:
● Press Enter to move downward
● Press Tab to move to the next column
Step 5: Edit Existing Data
To change information:
Method 1:
● Double-click the cell
Method 2:
● Select the cell and press F2
Method 3:
● Edit using the Formula Bar
Step 6: Delete Data
Select the cell
Press Delete key
Note:
Delete removes contents but does not remove the cell itself.
Step 7: Use AutoFill
AutoFill quickly copies patterns.
Example:
Type:
Monday
Tuesday
Highlight both cells.
Drag the Fill Handle downward.
Excel automatically continues:
Wednesday
Thursday
Friday
Tips for Proper Data Entry
✓ Check spelling before entering data
✓ Use consistent formatting
✓ Avoid unnecessary spaces
✓ Verify numbers before saving
✓ Save work regularly
Topic 2: Navigate Through Worksheets and Use Different Cell Types
Navigation in Excel
Navigation means moving around worksheets efficiently.
Methods of Navigation
Mouse Navigation
● Click cells directly
● Use scroll bars
● Select worksheet tabs
Keyboard Navigation
Key | Function |
Arrow Keys | Move one cell |
Enter | Move down |
Tab | Move right |
Shift+Tab | Move left |
Page Up | Move up |
Page Down | Move down |
Ctrl + Home | Go to beginning |
Ctrl + End | Go to last used cell |
Worksheet Navigation
Multiple worksheets can exist in one workbook.
Steps:
Locate sheet tabs at the bottom
Click another sheet
Rename sheets if necessary
Examples:
Sheet1 → Attendance
Sheet2 → Grades
Sheet3 → Expenses
Different Cell Types in Excel
1. Active Cell
The currently selected cell.
Characteristics:
● Highlighted border
● Ready for data entry
2. Referenced Cell
Used in formulas.
Example:
=A1+B1
A1 and B1 are referenced cells.
3. Merged Cell
Combines multiple cells into one.
Example:
A1:C1 merged as title
4. Relative Cell Reference
Changes automatically when copied.
Example:
=A2+B2
5. Absolute Cell Reference
Remains fixed.
Example:
=$A$2
Topic 3: Use Formatting Tools for Organization and Readability
What is Formatting?
Formatting changes the appearance of spreadsheet content.
Formatting helps:
● Improve readability
● Organize information
● Highlight important data
● Make worksheets professional
Common Formatting Tools
Font Formatting
Located under Home Tab.
Options:
● Font style
● Font size
● Bold
● Italic
● Underline
● Font color
Example:
Student Grades
Make title:
Bold
Size 16
Cell Alignment
Controls positioning.
Options:
● Left
● Center
● Right
● Top
● Middle
● Bottom
Cell Borders
Borders separate information visually.
Steps:
Highlight cells
Click Borders
Choose style
Fill Color
Adds background colors.
Examples:
Green = Passed
Red = Failed
Number Formatting
Excel recognizes different data types.
Examples:
Data | Format |
1000 | Number |
₱500 | Currency |
50% | Percentage |
May 24, 2026 | Date |
Cell Styles
Predefined formatting combinations.
Examples:
● Heading
● Title
● Total
● Warning
Wrap Text
Allows long text to fit inside a cell.
Steps:
Select cell
Click Wrap Text
Practical Activity
Create the following table:
Student Name | Subject | Score |
Ana Cruz | Math | 92 |
John Reyes | Science | 88 |
Maria Santos | English | 95 |
Apply:
✓ Bold headers
✓ Center alignment
✓ Borders
✓ Fill color for headers
WEEK 9 – USING FORMULAS AND FUNCTIONS
I. LEARNING OBJECTIVES
By the end of this lesson, students will be able to:
Apply mathematical and logical functions in Excel.
Demonstrate correct use of formula writing in sheets.
Solve spreadsheet problems using appropriate functions.
Introduction
Microsoft Excel allows users to calculate, analyze, and organize data efficiently. Two important tools used in Excel are formulas and functions.
● Formula – a user-created expression used to calculate values. Formulas always begin with an equal sign (=).
● Function – a predefined formula in Excel that performs calculations using specific values called arguments.
Example:
Formula:
=A1+B1
Function:
=SUM(A1:B1)
Functions help reduce errors and make calculations faster and more accurate.
Topic 1: Apply Basic Formulas Including SUM and AVERAGE Correctly
A. Understanding Formula Structure
All formulas in Excel begin with:
=
General format:
=FunctionName(Cell Range)
Examples:
=SUM(A1:A5)
=AVERAGE(B1:B10)
B. Using the SUM Function
The SUM function adds numbers together.
Syntax:
=SUM(number1, number2, …)
Example Spreadsheet:
Student | Score |
Ana | 85 |
Ben | 90 |
Carl | 88 |
Dana | 92 |
To get the total score:
Step 1: Select an empty cell.
Step 2: Type:
=SUM(B2:B5)
Step 3: Press Enter
Result:
355
Why use SUM?
● Faster than typing addition manually
● Reduces errors
● Works for large datasets
C. Using the AVERAGE Function
The AVERAGE function calculates the mean value of numbers.
Syntax:
=AVERAGE(range)
Steps:
Click an empty cell.
Type:
=AVERAGE(B2:B5)
Press Enter.
Result:
88.75
Applications:
● Grade computation
● Sales analysis
● Attendance averages
● Performance monitoring
Topic 2: Explore Logical Functions Such as IF and COUNTIF
Logical functions allow Excel to make decisions based on conditions.
A. IF Function
The IF function checks whether a condition is true or false.
Syntax:
=IF(logical_test,value_if_true,value_if_false)
Example:
Score | Result |
90 | ? |
Formula:
=IF(A2>=75,"Passed","Failed")
Explanation:
● If score is 75 or higher → Passed
● If below 75 → Failed
Output:
Passed
Step-by-Step Activity
Create a table with student scores.
Enter scores.
Click a blank cell.
Type:
=IF(B2>=75,"Passed","Failed")
Press Enter.
Drag the fill handle downward.
Excel automatically checks all records.
B. COUNTIF Function
COUNTIF counts cells meeting a condition.
Syntax:
=COUNTIF(range,criteria)
Example:
Result |
Passed |
Failed |
Passed |
Passed |
Formula:
=COUNTIF(A2:A5,"Passed")
Result:
3
Meaning:
Three students passed.
Applications:
● Counting attendance
● Counting grades
● Inventory monitoring
● Survey analysis
Topic 3: Practice Calculating Data within Spreadsheets Efficiently
Efficient spreadsheet work saves time and improves productivity.
Tips for Efficient Calculations
1. Use Cell References
Avoid:
=85+90+88
Use:
=A1+A2+A3
Benefits:
● Easy updates
● Less typing
● Fewer errors
2. Use AutoFill
Steps:
Click the formula cell.
Position cursor on lower-right corner.
Drag downward.
Excel copies formulas automatically.
3. Use Relative References
Example:
If:
=A2+B2
is copied downward, Excel changes automatically:
=A3+B3
This reduces repetitive work.
Practice Activity
Create the following:
Item | Quantity | Price | Total |
Notebook | 3 | 20 | ? |
Pen | 5 | 10 | ? |
Use:
=B2*C2
Then compute totals and use:
=SUM(D2:D3)
Topic: Creating Charts in Excel
1. Creating Bar Charts
A bar chart is used to compare values between different categories.
Example:
Employee | Sales |
Ana | ₱20,000 |
Ben | ₱30,000 |
Carlo | ₱25,000 |
Dana | ₱35,000 |
Use: A bar chart can show which employee has the highest and lowest sales.
2. Creating Pie Charts
A pie chart is used to show how different categories make up a whole.
Example:
Product | Sales |
Shoes | 40 |
Bags | 30 |
Shirts | 20 |
Accessories | 10 |
Use: A pie chart can show the percentage share of each product's sales.
3. Creating Line Charts
A line chart is used to show changes or trends over a period of time.
Example:
Month | Sales |
January | ₱15,000 |
February | ₱18,000 |
March | ₱22,000 |
April | ₱25,000 |
Use: A line chart can show whether sales are increasing or decreasing each month.
4. Choosing the Appropriate Chart Type
The chart type should match the purpose of the data.
Examples:
● Bar Chart – Compare sales of employees.
● Pie Chart – Show the percentage of products sold.
● Line Chart – Show monthly sales trends.
5. Adding Chart Titles and Data Labels
A chart title tells the reader what the chart is about. Data labels display the actual values or percentages.
Example:
Chart Title: Employee Sales Performance
Data Labels: ₱20,000, ₱30,000, ₱25,000, ₱35,000
6. Formatting Charts
Formatting improves the appearance and readability of a chart.
Examples:
● Change the chart style.
● Add or remove a legend.
● Adjust the chart title.
● Add data labels.
● Resize the chart.
7. Integrating Charts with Data Reports
Charts can be added to Excel reports to present information in a clear and visual way.
Example:
A sales report may contain a table of employee sales together with a bar chart showing their sales performance.
8. Interpreting Data Using Charts
Charts help users understand and analyze data quickly.
Example:
Based on the employee sales chart, Dana has the highest sales at ₱35,000, while Ana has the lowest sales at ₱20,000.
Key Reminder:
Bar Chart = Compare
Pie Chart = Show Parts of a Whole
Line Chart = Show Trends
WEEK 11 – MULTI-SHEET WORKBOOKS AND LINKING
I. LEARNING OBJECTIVES
By the end of this lesson, students will be able to:
Create and manage Excel workbooks with multiple sheets.
Use cross-sheet formulas to consolidate spreadsheet data.
Demonstrate data referencing across different sheets.
Topic 1: Linking Worksheets Using References and Formulas Properly
Cross-sheet references allow users to use data from one worksheet in another worksheet.
Understanding Worksheet References
The basic format for a worksheet reference is:
SheetName!CellReference
Example:
=Sales!B2
Meaning:
● Retrieve the value from Cell B2 of the worksheet named Sales.
If the worksheet name contains spaces:
='Monthly Sales'!B2
Excel requires apostrophes around sheet names with spaces.
Step-by-Step: Creating Worksheet References
Scenario: You have three sheets:
● January
● February
● Summary
You want the Summary sheet to display the January total sales.
Steps:
Step 1: Open the workbook.
Step 2: Create three sheets.
Rename Sheet1 to January
Rename Sheet2 to February
Rename Sheet3 to Summary
Step 3: Enter sample data:
In January:
Product | Sales |
Pen | 2000 |
Pencil | 1500 |
Step 4: Go to Summary sheet.
Step 5: Select a cell.
Step 6: Type:
=January!B2
Step 7: Press Enter.
Excel automatically retrieves the value from January sheet.
Topic 2: Managing Data Across Multiple Excel Sheets
Managing multiple worksheets improves organization and navigation.
Common Worksheet Management Tasks
A. Insert a New Worksheet
Steps:
Click the plus (+) icon beside sheet tabs.
A new worksheet appears.
B. Rename Worksheets
Steps:
Double-click sheet tab.
Type a new name.
Press Enter.
Example:
Sheet1 → Sales Report
C. Move Worksheets
Steps:
Click and hold the worksheet tab.
Drag to a new position.
D. Copy Worksheets
Steps:
Right-click worksheet tab.
Select Move or Copy.
Check Create a Copy.
Click OK.
E. Change Sheet Colors
Steps:
Right-click worksheet tab.
Select Tab Color.
Choose a color.
Benefits of Proper Sheet Management
● Better organization
● Easier navigation
● Faster reporting
● Reduced errors
● Improved productivity
Topic 3: Using Formulas Across Sheets for Accurate Computation
Cross-sheet formulas help perform calculations using information from different worksheets.
Examples include:
● Summing monthly expenses
● Consolidating grades
● Sales analysis
● Inventory tracking
Step-by-Step: Using SUM Across Worksheets
Suppose:
January sheet:
B2 = 5000
February sheet:
B2 = 7000
You want total sales in Summary sheet.
Enter:
=January!B2+February!B2
Result:
12000
Using SUM Function Across Sheets
Formula:
=SUM(January!B2,February!B2)
This adds values from multiple sheets.
Using Range References Across Consecutive Sheets
Formula:
=SUM(January:March!B2)
Explanation:
Adds B2 values from all sheets between January and March.
This is called a 3-D reference.
Practical Activity
Create a workbook containing:
● January
● February
● March
● Summary
Input sales data in each sheet.
In Summary:
Link values from each worksheet.
Compute total sales.
Create a final summary table.
Important Reminders
✓ Use meaningful worksheet names.
✓ Double-check sheet references.
✓ Avoid deleting worksheets used in formulas.
✓ Verify formula syntax.
✓ Save work regularly.