Integrating data from databases, such as Access or SQL Server, and raw data in text files, can feel like a bridge too far for Excel users. In this practical webinar, Excel expert, David Ringstrom, CPA, shows you how to connect the data you need into Excel and then work with it on your terms. You will learn helpful tools and techniques, such as 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.
You will see each technique demonstrated 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. You will also receive an Excel workbook that includes most of the examples used 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.
You will learn how to:
- Import tables from Microsoft Access into Excel, even if you don’t have Microsoft Access installed on your computer.
- Preview the results of a query in Microsoft Query before you send the data to Excel.
- Link data from text files to Excel spreadsheets by way of Microsoft Query.
- Eliminate the risk of workbook links by using Microsoft Query to get data from one workbook into another.
- Use Microsoft Query to extract data from Access databases.
- Add tables to existing queries within Microsoft Query.
- Use the Table feature to improve the integrity of Excel spreadsheets.
- Streamline filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
Upon course completion, you will be able to:
- List data analysis tricks used to query text files and databases from within Excel.
- Apply worksheet functions to summarize data extracted from databases and text files.
- Define how to use the SUMIF function and the SUMIFS function.
Who Should Attend: Excel users interested in learning data analysis tricks to query text files and databases from within Excel.
Prerequisites: None. Previous Experience with Databases is recommended.
Advance Preparation: None
Program Level: Intermediate
Field of Study: Computer & Software Applications (2 hours)
Format: Live Webcast
Credit Type: Group Internet Based for the Live Program. [If you attend the program on-demand (after the live date), you will receive Self-Study credit. Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.]