Database Techniques

Level: Intermediate

Available Durations:

  • 50 minutes
  • 60 minutes
  • 75 minutes
  • 90 minutes
  • 100 minutes
  • 120 minutes

Description:

The process of culling data from text files or from databases, such as Access or SQL Server, can intimidate Excel users. In this comprehensive presentation, Excel expert David Ringstrom, CPA, shows you how to get the data you need into Excel where you then can work with it a variety of ways, including using worksheet functions to summarize data, querying text files and databases from within Excel, and creating self-updating links to databases and other data sources.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Office 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 webcast.

Office 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.

Topics/Areas Typically Covered:

  • Using Microsoft Query to extract data from Access databases.
  • Using the Query Wizard in Microsoft Query to step through pulling data from other sources.
  • Setting queries from other sources to refresh automatically.
  • Recognizing the limitations of undoing actions within Microsoft Query.
  • Adding new data sources to Excel so you can extract data from text files, accounting software, and other data sources.
  • Reviewing three different ways to refresh queries you’ve embedded within Excel spreadsheets.
  • Using the SUMIF function to summarize data based on a single criterion.
  • Applying sort criteria to queries you’ve established in Microsoft Query.
  • Adding tables to existing queries within Microsoft Query.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Previewing the results of a query in Microsoft Query before you send the data to Excel.
  • Linking data from text files to Excel spreadsheets by way of Microsoft Query.

Learning Objectives/Why You Should Attend:

  • List data analysis tricks you can use to query text files and databases from within Excel.
  • Identify worksheet functions used to summarize data extracted from databases and text files.
  • Define how to use the SUMIF function and the SUMIFS function.

Target Industries:

  • Accounting and Finance
  • Business
  • Excel Users
  • Consulting
  • IT
  • Auditing
  • Human Resources
  • Marketing
  • Sales
  • Government
  • Tax

Target Job Title:

  • Accountants
  • CPAs
  • CFOs
  • Controllers
  • Income Tax Preparers
  • Enrolled Agents
  • Financial Consultants
  • IT Professionals
  • Auditors
  • Human Resource Personnel
  • Bookkeepers
  • Excel Users
  • Marketers
  • Government Personnel

About the Instructor:

David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.
Pricing and Format Options:

Pricing and Format Options:

Click here to learn about the presentation format and view pricing information.

Click here to view other topics.