On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Administration, Business Skills, Taxation and Accounting, Finance
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
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.
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:
- 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.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience Working with Databases
Advance Preparation:
None
Introduction
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 used to store large amounts of data in an organized and efficient manner. It allows you to create tables, forms, queries, and reports to manage your data.
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.
