Seminar
Live Replay WebinarBasic level
Excel Accounting Tools: Most Useful Formulas and Pivot Tables
★★★★★
5.0 · 1 attendee ratings
Financial professionals can transform their daily workflow with this comprehensive training designed specifically for accountants and business analysts. Participants will discover how to leverage advanced Excel formulas, including VLOOKUP, XLOOKUP, dynamic arrays, and logical functions that streamline complex calculations and automate routine tasks. This practical approach focuses on real-world applications, showing professionals how to reduce hours of manual work into efficient, automated processes that minimize errors and maximize productivity. Beyond powerful formulas, participants will gain hands-on experience with pivot tables, the cornerstone of modern business reporting. The course demonstrates how to create dynamic reports, implement advanced calculations, and design intuitive data visualizations that enhance decision-making capabilities. Professionals will walk away with immediately applicable skills that strengthen their analytical capabilities, improve reporting efficiency, and add significant value to their organization. This course bridges the gap between basic Excel knowledge and the advanced techniques needed in today’s data-driven business environment, making it an essential investment for accounting professionals seeking to enhance their technical capabilities and career growth.
Ways to save
Save $11.12 with a credit pass
At the 40-Credit Pass rate ($22.22 a credit with the bonus), this 4-credit course comes to about $88.88: $11.12 less than buying it on its own.
Or start smaller: the 10-Credit Pass ($275.00) pays for it and leaves you 6 credits for more.
Year-end bonus: up to 5 free credits through Dec 31.
Certain conditions apply: not valid for the Federal Tax Update. Pass terms
Watch
3:07
Create Custom Groups in Pivot Tables Without Original DataLearn how to create custom groupings in Excel pivot tables to consolidate data exactly how you need it. Whether you're organizing expenses by category, time period, or department, custom groups let you simplify complex datasets and surface insights faster. Watch how to group multiple fields with just a few clicks.
1:20
Fuzzy Lookup Saved 20,000+ Records Without a KeyWhen you're integrating two systems with no shared identifier, fuzzy lookup becomes your secret weapon. Watch how Stephen handled a real acquisition scenario—matching 7,000 product SKUs and 20,000 customers across different naming conventions with 95% accuracy, cutting days of manual work down to hours. Learn this essential Excel technique for your toughest data reconciliation challenges.
3:59
Why I Still Use OLD Excel Formulas (Not the Modern Ones)Learn three practical methods to split and extract data in Excel beyond formulas. Stephen Yoss demonstrates Flash Fill's AI pattern recognition, Text to Columns for delimiter-based splitting, and Power Query for advanced data transformation. See which approach works best for your data structure.
4:01
Structured References: Name-Based Formulas Instead of Cell PositionsLearn how structured references let you build formulas based on column names instead of positions. Stephen Yoss explains why this matters when working with large datasets—your formulas automatically update if data moves or changes, plus tables auto-calculate across entire columns. Watch to see how this saves time on formula maintenance.
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
- 25c01231
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.
Reading the course…
Answers come from this course's own material and can be imperfect — the full details are in the course itself.
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
1 attendee ratings
Attendee reviews
★★★★★
“I found this session more useful because of the lookups and pivot tables.”
Common questions
Will I be able to use XLOOKUP if my company is still on older versions of Excel?
XLOOKUP has been available since 2019, so it works in most modern Excel versions, particularly Office 365 subscriptions on current channels. However, if your organization uses older standalone versions of Excel (pre-2019) or Excel Online, you'll need to rely on VLOOKUP or INDEX/MATCH instead. The instructor recommends checking your Excel version under Account settings to confirm whether XLOOKUP is available to you.
Can I actually use these formulas and pivot tables with the messy data I have right now?
Pivot tables require data in strict tabular format—rows as records, columns as descriptors, with a header row. If your data has empty rows, fragmented columns, or mixed text and numbers in value columns, you'll need to clean it first using tools like Power Query or by manually organizing it into a table. The instructor emphasizes that creating a table first actually helps surface data quality issues before you build your pivot table, so you can fix problems early.
Do I need to learn VLOOKUP and INDEX/MATCH, or can I just use XLOOKUP?
You should focus on XLOOKUP—it's the modern standard that handles everything the older functions can do, plus additional features like built-in error handling and flexible search directions. The instructor uses XLOOKUP daily and rarely reaches for the legacy formulas anymore. The only reason to learn VLOOKUP or INDEX/MATCH is if you must support clients still on Excel versions from before 2019, which is increasingly uncommon.
How much of this course is hands-on demonstration versus just theory?
The course is heavily demonstration-based. The instructor walks through concrete examples with real files for tables, text formulas, lookups, dynamic arrays, and pivot tables—showing exactly where to click, what to type, and what output you'll see. There are sample files provided throughout that you can download and follow along with, so you're seeing working formulas and pivot tables in action rather than just hearing descriptions.
Can I create pivot tables from data that's spread across multiple worksheets?
Not directly from the pivot table wizard alone. However, you can use a data model to unify multiple tables from different worksheets into one object, then create a pivot table from that model. The instructor demonstrates this with examples connecting customer, order, product, and order line tables. This requires a bit more setup initially but allows you to create powerful cross-worksheet reporting without manually combining data first.
Related reading from our instructors
You might also like
Seminar
Seminar
Next week
Seminar
Seminar