In this presentation, author and Excel expert David H. Ringstrom, CPA, will empower you with techniques that make for efficient spreadsheet design and data analysis. Learn how to streamline your Excel worksheets by placing titles in a single row, thereby eliminating clutter and improving readability. Discover the power of referencing source data cells directly and creating settings tables for easy configuration and review. Enhance your calculations by assigning names to key input cells, writing smarter SUM formulas, and utilizing the SUBTOTAL function to build resilience into your calculations. You'll also master conditional formatting for unlocked cells and learn how to remove it when necessary.

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:

• Toggling the locked status of a worksheet cell on or off by way of a custom shortcut.

• Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.

• Mastering the IFERROR function to display alternate values in lieu of a # sign error.

• Building resilience into spreadsheets by avoiding daisy-chained formulas.

• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP.

• Enabling selected users to access protected areas of a worksheet by way of the Allow Users to Edit Ranges feature.

• Unlocking data entry cells before protecting worksheets

• Utilizing the New Window and Arrange Windows commands to view two different worksheets simultaneously.

• Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.

• Using Conditional Formatting to identify unlocked cells into which data can be entered.

• Protecting workbooks to prevent users from renaming, hiding, unhiding, or otherwise affecting worksheets.

• Learning how VLOOKUP stops looking after it finds an initial match within a list.

Learning Objectives:

• Identify how to improve the integrity of SUM-based formulas in Excel.

• State which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.

• State what SUMIF returns if a match cannot be found.

Level: Basic

NASBA Field of Study: Specialized Knowledge (2 hours)

Program Prerequisites: None

Advance Preparation: None