SQL for Excel Users
Author: David H. Ringstrom
| CPE Credit: |
2 hours for CPAs |
If you work in a database like SQL Server or Access, this course covers exporting data to Excel and manipulating it with several essential worksheet functions that allow you to summarize, query, create self-updating links and more.
Publication Date: August 2018
Designed For
Excel users interested in learning data analysis tricks to query text files and databases from within Excel.
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 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
- Recall 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.
Level
Intermediate
Instructional Method
Self-Study
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
Experience Working with Databases
Advance Preparation
None
