Excel Agility: SQL and Database Techniques
Please see below for additional instructions and information regarding this program.
The process of culling data from text files or from databases, such as Access or SQL Server, can be intimidating to Excel users. In this comprehensive course,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.
Who Would Be Interested in This Course:
Excel users interested in learning data analysis tricks to query text files and databases from within Excel.
- 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.
- Identify how Microsoft Query allows you to link data from text files to Excel spreadsheets.
- Compare the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
- Recognize how to duplicate columns, rows, or cells within an Excel worksheet.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Experience Working with Databases
Excel Versions 00:00:50
Import Table from Access 00:02:13
Table Feature 00:12:02
Slicers with Tables (2013+) 00:18:06
Using Microsoft Query with Access 00:23:36
Query Wizard 00:30:21
Add Tables 00:34:03
Two ways to Unjoin Tables 00:37:09
Relink the Tables (Join) 00:39:49
Previewing the Data 00:43:51
Control Sort Order 00:45:49
Adding Criteria to Query 00:47:11
Returning Data to Microsoft Excel 00:49:45
Three Ways to Refresh Microsoft Queries 00:51:04
Setting Queries to Refresh Automatically 00:54:37
Microsoft Query as Workbook Link Alternative 00:58:42
Microsoft Query as Workbook Link Alternative (cont.) 01:05:22
Microsoft Query as Workbook Link Alternative (cont.) 01:08:03
Microsoft Query as Workbook Link Alternative (cont.) 01:11:35
Combine Multiple Worksheets 01:16:40
Combine Multiple Worksheets (cont.) 01:17:53
Combine Multiple Worksheets (cont.) 01:20:28
Combine Multiple Worksheets (cont.) 01:24:24
Microsoft Query: New Data Source 01:30:30
Microsoft Query with Text Files 01:33:55
Microsoft Query with Text Files (cont) 01:35:23
Add Formulas to Query Results 01:36:52
Data Security Prompts 01:40:25
Presentation Closing 01:43:58
- Design Menu 00:14:50
- External Data Connections 00:02:13
- Join 00:35:24
- Microsoft Access 00:03:18, 00:23:49
- Microsoft Query 00:24:20, 00:59:30
- Pivot Table 01:11:50
- Query 00:08:46
- Query Wizard 00:30:21
- Refresh 00:51:42
- Slicer Feature 00:18:35
- Table Feature 00:12:10
- Text Files 01:30:34
- Workbook Links 00:58:45
Design: The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.
External Data Connections : Connections to data outside of the Excel workbook, such as query that pulls from a database or other spreadsheet, or a connection to a text file.
Join: Join is to combine two tables by matching the values in corresponding columns. In result, you will get a merged table which consists of the first table, plus the matched rows copied from the second table.
Microsoft Access: Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.
Microsoft Query : A feature on Excel's Data menu that enables you to connect Excel spreadsheets to external data sources such as accounting software, databases, text files, Excel workbooks, and more.
Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
Query Wizard: You can use Microsoft Query to retrieve data from external sources. By using Microsoft Query to retrieve data from your corporate databases and files, you don't have to retype the data that you want to analyze in Excel. You can also refresh your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.
Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. To learn more about David, you can view his LinkedIn profile and follow him on ... View Full Profile
Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or firstname.lastname@example.org.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.