Financial Reporting Tips and Tricks

In this presentation author and Excel expert David Ringstrom, CPA, helps you expand your Excel toolbox by comparing the INDEX/MATCH functions to the XLOOKUP worksheet function in Excel 2021 and Excel for Microsoft 365. You'll also see how to transform ugly reports exported from accounting software and other platforms into analysis ready formats by using Power Query. David will also show you how to use Excel's Solver feature to identify the combination of amounts from a list, such as invoices or deposits, that add up to an amount of your choice.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Utilizing the Timeline feature to filter PivotTables based on date ranges
• Enlivening staid lists of numbers with the Data Bars conditional formatting
• Improving the integrity of Excel PivotTables with the Table feature
• Streamlining the filtering of lists using the Slicer feature with tables
• Transforming reports that are dozens or more columns wide into filterable rows by way of the Unpivot command in Power Query
• Adding rows to a blank PivotTable to create instant reports
• Managing the external data security warning that may appear when you link external data into Excel spreadsheets
• Saving your spreadsheets as templates so users always start with a fresh copy
• Illustrating numbers within a list by using the Icon Sets conditional formatting
• Filling blanks and removing unwanted rows from a general ledger report with Power Query
• Filtering PivotTables to show fewer columns and/or rows of data
• Filtering data within PivotTables by way of the Slicer feature

Learning Objectives

• State how to quickly add a template, such as an Amortization Table, to an existing workbook
• Recall which Conditional Formatting feature fills cells with color sized commensurate with the underlying cell values
• State the arguments used within VLOOKUP

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None