Register Online - Add to Cart
Product ID: 407843EAU
 
Credit & Course Provided by:

Create and Manage an Excel® Database

OnDemand Webinar (89 minutes)

Learn how to create, analyze, and manage data.In this topic Excel® Expert David H. Ringstrom, CPA shows you how to leverage one of the most powerful aspects of Microsoft® Excel®: using your spreadsheet as a database. You'll see how organizing your data into discrete lists streamlines spreadsheet management and empowers you to take advantage of powerful features such as Power Query for linking and managing data, and PivotTables for creating self-updating reports. He will take you even farther by illustrating dynamic array formulas in Microsoft 365® to create reports that update themselves in real time. Once you start using Excel® as a database, you won't see spreadsheets the same way again. David demonstrates every technique at least twice: first, on a PowerPoint® slide with numbered steps, and second, in the subscription-based Microsoft 365® version of Excel®. David draws your attention to any differences in the older versions of Excel® (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel® workbook that includes most of the examples he uses during the program. Microsoft 365® is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel® have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel® 2019, Excel® 2016, and so on.

Authors

David H. Ringstrom, CPA, Accounting Advisors, Inc.

Agenda

Using Excel®'s Table Feature to Specifically Demarcate Tables of Data Within a Workbook

Simplifying Database Management With Table Names

Linking Data Between Tables With Excel®'s VLOOKUP, XLOOKUP, and SUMIF Functions

Linking Data Between Spreadsheet Tables With Power Query

Creating Self-Updating Reports From Spreadsheet Databases by Using Dynamic Array Functions in Microsoft 365®

Summarizing Data From Spreadsheet-Based Databases With PivotTables in All Versions of Excel®

Linking External Files Into Spreadsheet-Based Database Tables

Using Excel®'s Data Validation Feature to Limit Inputs Within a Database Table

Using Excel®'s Table Feature to Automatically Manage Formulas Within a Database Table

Managing the Security Prompts That Can Appear When You Link External Data Into an Excel® Spreadsheet

Seeing the Chaos That Can Ensue When You Spread Data Across Multiple Worksheets Instead of Encapsulating It Into Lists