Maximize Excel: Database Techniques


CPE Credit: 2 hours

Databases, such as Access or SQL Server as well as raw data in text files, can be intimidating to Excel users. In this valuable 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, 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 used in the course. 




Topics Covered

• Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another.
• Adding new data sources to Microsoft 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 Microsoft Query to extract data from Access databases.
• Streamlining filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
• Establishing links manually between tables in Microsoft Query to relate data from multiple sources together.
• Importing tables from Microsoft Access into Excel, even if you don’t have Microsoft Access installed on your computer.
• Adding tables to existing queries within Microsoft Query.
• Linking data from text files to Excel spreadsheets by way of Microsoft Query.
• Previewing the results of a query in Microsoft Query before you send the data to Excel.

 
Learning Objectives:

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

Course Number: 
DR1908
NASBA Field of Study: 
Computer Software and Applications
Level: 
Intermediate
Presenter:
David Ringstrom
Prerequisites: 
Experience Working with Databases
Advanced Preparation: 
None