Excel Essentials

My Favorite Excel Formulas

My go-to Excel formulas are XLOOKUP for flexible, error-resistant lookups, SUMIFS / COUNTIFS for dynamic financial analysis, and INDEX-MATCH for complex models. I also rely on IF / IFS, TEXT, LEFT/RIGHT/MID, ROUND, and EOMONTH to build clean, audit-ready reports.

Uses

XLOOKUP – Used to pull exact or approximate values across tables (e.g., account names, employee rates, vendor details) with built-in error handling, replacing VLOOKUP/HLOOKUP and reducing model fragility.

SUMIFS / COUNTIFS – Used to aggregate financial or operational data based on multiple criteria, such as summing payroll costs by department, month, or employee type, or counting transactions that meet defined conditions.

INDEX-MATCH – Used for advanced, two-way, or non-adjacent lookups in financial models where flexibility and performance matter, especially in large datasets or when column order may change.

IF / IFS – Used to apply business logic and decision rules, such as classifying transactions, flagging variances, determining bonus eligibility, or handling conditional calculations in forecasts.

TEXT – Used to format numbers and dates consistently for reporting, such as converting dates to month-year labels or standardizing account codes for exports and dashboards.

LEFT / RIGHT / MID – Used to extract specific characters from strings, such as parsing GL account segments, employee IDs, or invoice numbers for reconciliation and analysis.

ROUND – Used to control numerical precision in financial models, ensuring clean reporting, accurate payroll calculations, and avoidance of rounding discrepancies.

EOMONTH – Used to calculate period-end dates for accruals, payroll cutoffs, aging schedules, and month-end close timelines, ensuring consistent time-based reporting.

Payroll Examples

XLOOKUP (Pay Rates & Benefits)
Used to pull hourly rates, salary amounts, or benefit deductions from a master employee table into the payroll register, ensuring consistent and error-free calculations across pay cycles.

SUMIFS (Payroll by Department)
Used to total gross wages, employer taxes, or benefits by department, location, or cost center for payroll accruals and management reporting.

IF / IFS (Overtime & Bonus Rules)
Used to calculate overtime pay, shift differentials, or bonus eligibility based on hours worked, role classification, or performance thresholds.

ROUND (Pay Accuracy)
Used to control cents-level rounding in gross-to-net calculations to prevent paycheck discrepancies and reconciliation issues.

TEXT (Payroll Period Formatting)
Used to standardize pay-period labels (e.g., “Jan-2026”) for payroll summaries, journal entries, and uploads to accounting systems.

📌 Accounts Payable / Accounts Receivable (AP / AR)

XLOOKUP (Vendor & Customer Details)
Used to pull vendor payment terms, customer credit limits, or GL accounts into AP/AR schedules to ensure accurate postings.

SUMIFS (Aging Analysis)
Used to calculate outstanding AP or AR balances by aging bucket (Current, 30, 60, 90+ days) to support cash planning and collections.

COUNTIFS (Invoice Tracking)
Used to count unpaid invoices, overdue bills, or disputed items for workload tracking and KPI reporting.

LEFT / RIGHT / MID (Invoice Parsing)
Used to extract invoice numbers, vendor codes, or customer IDs from reference fields during imports and reconciliations.

IF (Payment Status Flags)
Used to flag invoices as “Paid,” “Due Soon,” or “Overdue” based on due dates and payment status.

📌 Month-End Close & Reporting

EOMONTH (Accrual Cutoffs)
Used to automatically calculate month-end dates for payroll accruals, prepaid expenses, depreciation, and revenue recognition.

SUMIFS (Monthly Financials)
Used to aggregate expenses and revenue by account, department, or project for P&L and variance analysis.

INDEX-MATCH (Reconciliation Support)
Used to match GL balances against sub-ledgers, bank statements, or inventory reports during close.

IF (Variance Analysis)
Used to flag material variances between actuals and budget or prior periods, helping focus review efforts.

TEXT (Financial Presentation)
Used to format dates and labels consistently across financial statements, dashboards, and board reports.

FAQs

1. Why is XLOOKUP preferred over VLOOKUP or HLOOKUP?
XLOOKUP is more flexible and reliable because it works in any direction, does not require fixed column positions, and includes built-in error handling. This reduces the risk of broken formulas in dynamic financial models.

2. When should SUMIFS be used instead of SUM?
SUMIFS is used when totals must be calculated based on multiple conditions, such as summing expenses by department, month, and account type. It enables precise financial analysis and cleaner reporting.

3. How does COUNTIFS help in financial reporting?
COUNTIFS is useful for tracking volumes, such as the number of transactions, employees, or invoices that meet specific criteria. It supports trend analysis, exception tracking, and operational metrics.

4. Why is INDEX-MATCH still valuable if XLOOKUP exists?
INDEX-MATCH remains valuable for complex or legacy models, two-way lookups, and large datasets where performance and backward compatibility with older Excel versions are important.

5. How are IF and IFS formulas used in accounting workflows?
These formulas apply business logic, such as categorizing transactions, flagging variances, determining eligibility for bonuses, or assigning risk levels based on thresholds.

6. What is the purpose of the TEXT function in reports?
TEXT is used to standardize how numbers and dates appear, ensuring consistency across reports, dashboards, and exports without changing the underlying values.

7. When are LEFT, RIGHT, and MID most commonly used?
These functions extract specific parts of text strings, such as GL segments, employee IDs, or invoice numbers, making them essential for data cleanup and reconciliation tasks.

8. Why is ROUND important in financial models?
ROUND controls decimal precision, preventing small rounding differences that can cause reconciliation issues, payroll discrepancies, or reporting inconsistencies.

9. How does EOMONTH support month-end close processes?
EOMONTH ensures consistent calculation of period-end dates, supporting accruals, aging schedules, payroll cutoffs, and standardized financial timelines.

10. How do these formulas work together in real-world finance tasks?
Combined, these formulas enable accurate data extraction, validation, aggregation, formatting, and analysis—forming the foundation of reliable financial reporting and decision-making.