Accessing Data from Databases and Text Files

Level: Intermediate

Available Durations:

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

Description:

In this outstanding presentation, Excel expert David Ringstrom, CPA, shows you how to access the data you need from databases, such as Microsoft SQL and Microsoft Access, as well as raw data from text files. Once your data has been extracted and input into Excel, David explains how to work with that data in a variety of ways, including using worksheet functions to summarize data, querying text files and databases from within Excel, creating self-updating links to databases and other data sources, and more.

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

  • Discovering how Microsoft Query allows you to create self-updating links to databases, spreadsheets, text files, and other data sources.
  • Understanding what SELECT, FROM, WHERE, ORDER BY, and TOP mean within SQL statements.
  • Using the SUMIF function to summarize data based on a single criterion.
  • Using the COUNTIF function to determine the number of times an item appears on a list.
  • Implementing the SUMIFS function to sum values based on multiple criteria.
  • Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.

Learning Objectives/Why You Should Attend:

  • List data analysis tricks used to query text files and databases from within Excel.
  • Apply worksheet functions to summarize data extracted from databases and text files.
  • Apply the Table feature so that supporting formulas update automatically when database query results change.

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.