In this valuable presentation, Excel expert David Ringstrom, CPA, shows you how to extract data from databases, such as Access or SQL Server, as well as text files and then transfer that data into Excel. Once your data is in Excel, you then can work with it 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 Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 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.  

Topics 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 Structured Query Language (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.
  • Streamlining filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
  • Using Microsoft Query to extract data from Access databases.
  • Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another.
  • Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
  • Removing automatic links between tables within a query in Microsoft Query.
  • Applying sort criteria to queries you’ve established in Microsoft Query.
  • Linking data from text files to Excel spreadsheets by way of Microsoft Query.
  • Adding new data sources to Microsoft Excel so you can extract data from text files. 
Learning Objectives
  • Identify how to sum values based on a single criterion and on multiple criteria.
  • Discover how to use Microsoft Query to get data from one workbook into another.
  • Recognize how to duplicate columns, rows, or cells within an Excel worksheet.
Last updated/reviewed: August 7, 2023
22 Reviews (84 ratings)

Reviews

2
Anonymous Author
Too much emphasisis placed on the buttons to click rather than the concepts. Take a look at the exam questions as evidence of this misplaced emphasis. Suggest changing to focus on what sorts of problems need to be solved rather than the button clicking which, frankly, is unimportant relative to "what problems do accountants face" and "how to solve these problems using excel functionality"

4
Anonymous Author
I really like this presenter and his other classes. This particular class has a few things i learned, some of this is quite outdated now related to Microsoft query unless you have outdated Excel.

4
Anonymous Author
Very informative overview of the different ways to access multiple sources of data in Excel. Very easy to follow and doesn't require SQL or extensive database knowledge in order to understand.

4
Anonymous Author
Microsoft query was the most useful. I have a lot of text files that I can link to & now pivot without having to create it every time. 2 hours was a little too long for these topics.

5
Anonymous Author
David make very good use of step by step explanations. Sometimes it can be too fast and one may need to go back and review sections, but it is valuable informaton

5
Member's Profile
Very good examples of how to bring data together from larger spreadsheets to get data summarized quickly and how slicer and pivot tables can be put to good use.

5
Anonymous Author
I thought this course was very interesting and helpful. I thought the instructor explained the topic very well. I would recommend this course to others.

5
Anonymous Author
Great course. Stated learning objectives were met. Program materials were relevant and contributed to the achievement of the learning objectives.

5
Member's Profile
This course is an amazing introduction to queries and using excel for database management. I will be referencing this for some time to come.

5
Member's Profile
Lots of good information. I deal a lot with Access and this will make it easier to pull updated information into excel files to use.

5
Member's Profile
This course was excellent. I learned a great deal. I appreciate courses such as this because it will help me grow within my career.

4
Anonymous Author
Class is very easy to follow. Instructor informs by talking though screen shots and then immediately by example.

4
Anonymous Author
Very informational and a lot of examples. The only issue is, the instructors mic kept fading in and out.

4
Anonymous Author
The slides are always very helpful as a reference tool with this instructor. Much better than most!

5
Member's Profile
Great overview of a very detailed subject. Very hard to do without the examples he showed.

4
Member's Profile
excellent in depth course on using the MS query tool to get data from external sources.

5
Anonymous Author
This course was very helpful in understanding how Excel relates to queries.

5
Anonymous Author
People who have used sql or are new to it would benefit from this course

5
Anonymous Author
Good intro to queries using Microsoft query. Good tips for starters.

4
Member's Profile
Great course, learned a lot about queries. The videos really helped.

5
Member's Profile
It was a very helpful introduction to Excel Database techniques.

4
Member's Profile
This tutorial could have been much shorter and to the point.

Prerequisites
Course Complexity: Advanced

Program Prerequisites: Experience Working with Databases

Advance Preparation: None

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
Course Syllabus
Introduction and Overview
  Introduction to Excel Efficiency: Database Techniques12:01
  Table Feature6:05
  Slicers with Tables (Excel 2013 and later)5:30
  Using Microsoft Query with Access10:27
  Add Tables9:48
  Previewing the Data7:13
  Three Ways to Manually Refresh Queries7:39
  Microsoft Query as Workbook Link Alternative9:20
  Microsoft Query as Workbook Link Alternative Continued..8:37
  Combine Multiple Worksheets7:44
  Combine Multiple Worksheets9:31
  Microsoft Query with Text Files and Conclusion10:34
CONTINUOUS PLAY
  Excel Efficiency: Database Techniques1:44:29
Supporting Materials
  Slides: Excel Efficiency: Database TechniquesPDF
  Excel Efficiency: Database Techniques Glossary/IndexPDF
  Workbook: Excel Efficiency: Database TechniquesXLSX
  Fruit - FilteringCSV
  Fruit - SalesCSV
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam