Instructor for this course
more

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: November 15, 2019

11 Reviews (29 ratings)Reviews

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.
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
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.
4
Anonymous Author
Class is very easy to follow. Instructor informs by talking though screen shots and then immediately by example.
5
Member's Profile
Great overview of a very detailed subject. Very hard to do without the examples he showed.
5
Anonymous Author
This course was very helpful in understanding how Excel relates to queries.
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 .
Course Syllabus
Introduction and Overview
  12:01Introduction to Excel Efficiency: Database Techniques
  6:05Table Feature
  5:30Slicers with Tables (Excel 2013 and later)
  10:27Using Microsoft Query with Access
  9:48Add Tables
  7:13Previewing the Data
  7:39Three Ways to Manually Refresh Queries
  9:20Microsoft Query as Workbook Link Alternative
  8:37Microsoft Query as Workbook Link Alternative Continued..
  7:44Combine Multiple Worksheets
  9:31Combine Multiple Worksheets
  10:34Microsoft Query with Text Files and Conclusion
CONTINUOUS PLAY
  1:44:29Excel Efficiency: Database Techniques
Supporting Materials
  PDFSlides: Excel Efficiency: Database Techniques
  PDFExcel Efficiency: Database Techniques Glossary/Index
  XLSXWorkbook: Excel Efficiency: Database Techniques
  CSVFruit - Filtering
  CSVFruit - Sales
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM