On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
When you take this course created by Excel expert David Ringstrom, CPA, you’ll learn how to create and navigate workbook links in Excel. Workbook links, which are quite beneficial, allow you to connect one Excel spreadsheet to other spreadsheets, Word documents, databases, and even web pages. David shows you how to dramatically improve the integrity of linked workbooks, copy links across rows or down columns, repair broken links, 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 he uses during the webcast.
Who Would Be Interested in This Course:
Practitioners seeking to learn about Excel workbook links and improve the integrity of their linked workbooks.
Topics Covered:
- Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Creating flexible workbook links that can be copied down columns or across rows.
- Controlling whether linked workbooks update automatically or not as well as suppress unwanted prompts.
- Summarizing data from Access databases with pivot tables, even if you don’t have Microsoft Access installed.
- Learning how to repair broken workbook links.
- Discovering a free tool you can use to locate even the most buried workbook links.
- Integrating data on web pages into your workbooks and creating automatic links.
Learning Objectives:
- Recall how to determine if a workbook contains links.
- Recall the Excel function that allows you to sum values related to multiple instances of criteria you specify.
- Describe how to use Microsoft Query to move data from one workbook into another.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:00:51
Indications of Workbook Links 00:01:47
Arranging Two Workbooks Onscreen 00:05:35
Creating a Workbook Link 00:09:17
Deconstructing a Workbook Link 00:14:59
Flexible Workbook Links 00:16:04
Linked Workbook Security Settings 00:18:03
Link Start-Up Prompt 00:22:19
Find Links in a Workbook 00:24:55
Breaking Workbook Links 00:28:37
Another Place that Links Sometimes Hide 00:31:10
Workbook Link Removal Tool 00:36:16
Using the Link Finder Tool 00:39:27
Correcting Broken Workbook Links 00:40:40
Finicky Functions 00:46:04
Correcting Broken Workbook Links (cont.) 00:48:11
SUMIF Function 00:49:33
SUMIF/Linked Workbooks 00:54:03
Introduction to SUMPRODUCT 00:59:13
SUMPRODUCT/Linked Workbooks 01:02:18
SUMPRODUCT Alternative to SUMIF 01:05:50
Microsoft Query as Workbook Link Alternative 01:08:42
Microsoft Query as Workbook Link Alternative (cont.) 01:15:23
Microsoft Query as Workbook Link Alternative (cont.) 01:18:03
Microsoft Query as Workbook Link Alternative (cont.) 01:21:37
Linking Word Documents to Excel Spreadsheets 01:26:44
Formatting Linked Excel Data in Word 01:29:34
Summarize Data with Pivot Tables 01:31:05
Summarize Data with Pivot Tables (cont.) 01:33:33
Link Data from Web Pages 01:34:34
Link Data from Web Pages (cont.) 01:35:40
Presentation Closing 01:39:30
- Add-In 00:39:34
- Arrange All Command 00:06:34
- HTML 01:27:29
- Microsoft Query 01:09:28
- Named Range 00:32:03
- Name manager 00:31:19
- Pivot Table 01:33:29, 01:34:07
- SUMIF 00:46:26, 00:49:33, 01:05:58
- SUMIFS 00:46:27, 01:05:58
- SUMPRODUCT 00:59:17
- VLOOKUP 00:46:31
- Workbook Links 00:01:47
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
Arrange All Command: The Arrange All command provides you with a number of options for arranging multiple workbooks on screen simultaneously, depending on your particular needs. You can access the Arrange All command by selecting VIEW?Arrange All.
HTML: Hypertext Markup Language is a document format commonly used for Web pages, but you can save Office documents in this format as well.
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.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
Named Range : A descriptive name — such as Jan_Sales or June_Precip — that is attached to a specific cell or range of cells in a worksheet or workbook. Named ranges make it easier to use and identify data when creating charts, and in formulas.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
Workbook Links: A technique by which one or more cells in one workbook can reference one or more other workbooks. Such links pose data-integrity risks and should be used sparingly.
