Live Replay WebinarBasic level

Excel Formula's Accountants & Financial Professionals Need to Know

★★★★★ 5.0 · 4 attendee ratings

Excel is ubiquitous in the accounting and financial professionals' workplace, but some of the best functions and features for accounting and financial professionals are unknown or underutilized. Leverage Tables, shortcuts, and best practices to increase efficiency. Master the functions such as VLOOKUP, INDEX & MATCH, Dynamic Array Formulas. As well as Formulas and uses such as Depreciation, Amortization, Interest, Payment, and more. In this course, we will even take you deeper into statistical analysis with formulas for everything from median, most, and standard deviation and regression analysis.

CPE Credits4

Field of Study Specialized Knowledge

Instructor Stephen Yoss

Watch

1–5 of 8

3:55 IFERROR Suppresses Error Messages for Polished Reporting

3:27 Why Text-to-Columns Fails (Power Query Doesn't)Stephen Yoss shows why Text to Columns is outdated for data cleaning. Learn how Power Query splits delimited data once and refreshes automatically when your dataset changes—no manual updates needed. Perfect for accountants managing customer lists and financial data that grows over time.

3:54 SUMIFS & AVERAGEIFS: Conditional Arithmetic on Steroids

2:24 How Much of Your Loan Payment Goes to InterestMaster the PPMT and IPMT formulas to break down loan payments into principal and interest components. See exactly how much of each payment goes toward interest versus principal—and watch how the ratio shifts dramatically over time. Perfect for accountants and financial professionals who need to analyze loan amortization schedules.

3:55 Replace Nested IF with IFS for Cleaner Formulas

3:56 Data Validation: Let Users Pick From Dropdowns, Not Type WrongLearn how to prevent data entry errors by restricting cells to specific values using Excel's data validation feature. Stephen Yoss walks through creating dropdown lists—from simple hardcoded options like countries to dynamic lists pulled from tables. Perfect for accountants and financial professionals who need cleaner, more reliable spreadsheets. Watch to see how formulas and lookup tables can automate your validation rules.

3:58 Logic Formulas: The Foundation of Dynamic Spreadsheets

3:55 SUBTOTAL vs SUM: The Critical Difference Accountants MissLearn the critical difference between SUBTOTAL and SUM functions that could be hiding mistakes in your financial reports. Stephen Yoss demonstrates how SUBTOTAL automatically excludes filtered and hidden rows—while SUM counts everything. See real examples using table formulas that make your spreadsheets more auditable and error-proof. Watch to master the function every financial professional should know.

What's included

Every registration comes with the course materials — yours to keep.

Course presentation PDF
Demonstration & sample files ZIP
Additional course files PDF

Course details

Recommended CPE credit: 4
Field of study: Specialized Knowledge
Program level: Basic
Delivery method: Group Internet Based
Prerequisites: None
Advance preparation: None
Course number: 24c01008

CPE Today (Devmatics, LLC) is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors (Sponsor ID 167619). State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website, nasbaregistry.org. For information about our refund, complaint, and program cancellation policies, see Company Policies or contact [email protected].

💬 Ask this course

Not sure it covers what you need? Ask a real question — answered from the actual course content, so you can decide before you enroll.

Answers come from this course's own material and can be imperfect — the full details are in the course itself.

Every way to take this course

On its own

Live Replay Webinar Start it whenever you like — full live CPE credit · How Live Replay works · 4 credits

$200 Add to Cart

As part of a multi-day event

These seminars include this course on their agenda. One registration covers the event — you attend this course on its scheduled day, plus everything else that day offers.

Nov 19

2026 Federal Tax Update & Technology Conference Seminar · Nov 17–20, 2026 · Online · this course runs Nov 19, 2026 · event offers up to 32 credits

$300 View event

Dec 14

2026 Federal Tax Update & Technology Conference: December Broadcast Seminar · Dec 10–15, 2026 · Online · this course runs Dec 14, 2026 · event offers up to 32 credits

$300 View event

Jan 22

2026 Federal Tax Update & Technology Conference: January Broadcast Seminar · Jan 20–25, 2027 · Online · this course runs Jan 22, 2027 · event offers up to 32 credits

$300 View event

Your instructor

Stephen Yoss

Speaker & Author · CPE Today · ★ 4.7 instructor rating

Stephen M. Yoss, Jr., CPA, MS, is a certified public accountant, partner and CEO of Devmatics, LLC, a continuing education instructor for financial professionals, and a licensed pyrotechnician. While his interests and skills are varied, they all share a common thread—his love for and skill in finding technology-based solutions. In 2017, Stephen and Alex White formed Devmatics, LLC, a company focused on helping...

Full profile →

Attendee feedback

5.0 4 attendee ratings

Instructor 4.8

Learning objectives 4.5

Course materials 4.5

Time allocation 3.8

Stated prerequisites 4.5

Common questions

Will this course teach me how to use VLOOKUP, or is it only about newer formulas like XLOOKUP?

We cover both. You'll learn VLOOKUP so you understand why it's limited—it only works if your data is sorted correctly, can only look to the right of the first column, and is slow with large datasets. Then we show you XLOOKUP, which solves all those problems. XLOOKUP is faster, more flexible, and lets you specify exactly which column to search and which to return, so it's the last lookup formula you'll ever need.

Do I need to already know Excel, or will this course work if I'm just getting started?

This course is designed for financial professionals who use Excel but want to work more effectively. There are no prerequisites listed, and the instructor teaches at a Basic program level, so you don't need advanced experience. That said, you should be comfortable opening a workbook and entering data into cells—the course assumes that baseline.

Can I actually use these formulas in real client work, or are they just academic examples?

Every formula demonstrated is one the instructor uses daily in his own practice. The examples show real accounting scenarios: calculating depreciation on asset lists, analyzing budget versus actual variances using conditional logic, cleaning messy bank feeds with text functions, and pulling live financial data using Power Query. You'll see exactly how to structure a workbook your client will actually use.

I work with very large datasets—will Excel handle what I need, or should I use a database instead?

Excel handles large data better than most people expect, especially with Power Query and dynamic arrays. The instructor demonstrates working with thousands of rows and shows how to set up queries that refresh automatically when your source data changes. That said, if you need to write to data, enforce complex constraints, or support many simultaneous users, a database is the right tool—but for reporting and analysis on top of existing data, Excel will serve you well.

Will I get hands-on practice with real spreadsheets, or is this just slides and theory?

The course is almost entirely live demonstration in Excel. You download sample files at the start, and the instructor walks through formulas, builds tables, creates lookups, and sets up Power Query connections while you watch and can follow along. About 90% of the session happens inside actual workbooks, not PowerPoint slides—you see exactly where to click and what to type.