Excel - Consistent Frozen Solutions has sent a JOB OFFER!!!! THANK YOU, LORD!!

Auto Filter and Advanced Filter

Filters allow you to display only the data that meets certain conditions without deleting any records.

Auto Filter

Best for simple filtering.

Advanced Filter

Purpose:
Used when filtering with multiple criteria, extracting unique records, or copying filtered results elsewhere. Advanced Filter is useful when Auto Filter isn't flexible enough.

Sorting Data

Purpose

Arrange data in a meaningful order.

Finance Uses

• Highest outstanding balances first

• Oldest invoices first

• Largest payments first

• Customers alphabetically

Pivot Tables

Purpose

Summarize thousands of rows into an easy-to-read report.

Finance Uses

• Monthly sales

• Expenses by department

• Total collections by collector

• Payments by supplier

• Aging summaries

One of the most valuable Excel tools in accounting.

XLOOKUP vs VLOOKUP

Purpose

Find information from another table.

You want the balance beside the customer.

VLOOKUP

Searches only from left to right.

XLOOKUP

Newer and more flexible.

Advantages

✅ Searches left or right

✅ Doesn't require column numbers

✅ Easier to read

✅ Can return custom messages if not found

SUMIFS

Purpose

Adds values that meet one or more conditions.

Finance Uses

• Total collections

• Total unpaid invoices

• Total supplier payments

• Monthly expenses

COUNTIFS

Purpose

Counts records matching multiple conditions.

Finance Uses

• Number of overdue accounts

• Number of invoices this month

• Number of paid customers

IF Statements

Purpose

Makes decisions automatically.

Finance Uses

• Paid vs Unpaid

• High Risk vs Low Risk

• Approve vs Reject

• Within Budget vs Over Budget

Conditional Formatting

Purpose

Automatically changes cell formatting based on rules.

Highlight:

• Overdue invoices in red

• Negative numbers

• Duplicate invoice numbers

• Highest balances

Finance Uses

Makes important information stand out immediately, helping users spot overdue accounts, unusual transactions, or potential errors.

Remove Duplicates

Purpose

Removes repeated records.

Finance Uses

• Duplicate invoices

• Duplicate vendors

• Duplicate customer IDs

• Duplicate payments

Very useful for cleaning data before analysis.

Text Filters

Purpose

Filter based on words.

Finance Uses

Find all customers whose names contain "ABC" or all invoice numbers starting with "2026-".

Number Filters

Purpose

Filter numeric values.

Finance Uses

• Customers with balances above ₱100,000

• Expenses over budget

• High-value transactions

• Largest overdue accounts

Which Excel functions are you most comfortable using

• Mention tools like Auto Filter, Pivot Tables, XLOOKUP/VLOOKUP, SUMIFS, COUNTIFS, IF statements, Conditional Formatting, and sorting.

When would you use XLOOKUP instead of VLOOKUP

• Explain that XLOOKUP is more flexible because it can search in either direction, doesn't require a column index number, and lets you specify a custom message if no match is found.

How can Pivot Tables help in accounting

• They summarize large datasets quickly, making it easy to analyze sales, expenses, collections, or payments by customer, supplier, or time period.

How does Conditional Formatting help your work

• It visually highlights important information, such as overdue invoices, duplicate records, or unusually high balances, allowing you to identify issues faster.

Key takeaway for finance and accounting roles (8)

• Organize data using sorting and filters.

• Analyze large datasets with Pivot Tables.

• Retrieve related information using XLOOKUP or VLOOKUP.

• Calculate totals and counts with SUMIFS and COUNTIFS.

• Automate decisions with IF statements.

• Identify trends or errors with Conditional Formatting.

• Clean datasets by removing duplicates.

• Quickly locate specific records using text and number filters.