SQL For Excel Users

Author: David H. Ringstrom

Working with databases, such as Microsoft Access and Microsoft SQL Server, as well as raw data in text files, can be intimidating to Excel users. In this live presentation, Excel expert David Ringstrom, CPA, shows you how to access the data you need and input it into Excel. Once your data is in Excel, you then can work with it a variety of ways, including using worksheet functions to summarize data extracted from databases and text files.

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.

Publication Date: August 2020

Designed For
Excel users interested in learning data analysis tricks used to query text files and databases from within Excel.

Topics Covered

• Reviewing three different ways to refresh queries you've embedded within Excel spreadsheets
• 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
• Removing automatic links between tables within a query in Microsoft Query
• Establishing links manually between tables in Microsoft Query to relate data from multiple sources together
• Adding tables to existing queries within Microsoft Query
• Importing tables from Microsoft Access into Excel, even if you don't have Microsoft Access installed on your computer
• 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
• 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
• Setting queries from other sources to refresh automatically

Learning Objectives

• Identify data analysis tricks that are used to query text files and databases from within Excel
• Describe benefits the Table feature offers
• Recognize true statements about Slicers
• Identify how Microsoft Query considers worksheet tabs within an Excel workbook
• Recognize which SQL command enables you to stack multiple sets of data together into a single list
• Describe which command to use in Power Query to relate two or more tables together
• Recognize which Microsoft Query is available on which Excel ribbon tab or menu
• Identify which menu in Power Query the Remove Rows command appears on

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
A basic understanding of Excel.

Advance Preparation
None