Excel Agility: Preventing Errors in Your Spreadsheets
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Software, Finance, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
You’ll learn from Excel expert David Ringstrom, CPA, a variety of techniques and tricks you can use to create spreadsheets that are free of errors. In this valuable course, David demonstrates how to implement Excel features and functions, such as hide and protect features, the Conditional Formatting feature, the VLOOKUP function, and the SUBTOTAL function. He also demonstrates how to preserve key formulas, perform dual lookups, audit the spreadsheets created by others, 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 who wish to learn how to create error-free Excel spreadsheets.
Your Benefits of Attending:
- Building resilience into spreadsheets by avoiding daisy-chained formulas.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Enabling selected users to access protected areas of a worksheet by way of the Allow Users to Edit Ranges feature.
- Improving the integrity of many Excel features by placing column headings within a single row instead of spanning two or more rows.
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.
- Limiting access to sensitive workbooks by way of password protection.
- Mastering the IFERROR function to display alternate values in lieu of a # sign error.
- Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
- Preserving key formulas using hide and protect features.
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.
- Protecting workbooks to prevent users from renaming, hiding, unhiding, or otherwise affecting worksheets.
Learning Objectives:
- Identify the mouse action that enables you to add an existing menu command in Excel to the Quick Access Toolbar.
- Define the argument within Excel’s SUBTOTAL function that sums columns or rows.
- Recall the purpose of Excel’s IFERROR worksheet function.
Level:
Intermediate
Format:
On-Demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Previous Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:00:57
Place Titles in a Single Row 00:02:41, 00:07:00
Refer Directly to the Source 00:08:57
Creating a Settings Table 00:15:55
Assigning Range Names to Key Inputs 00:22:27
Custom Views - Multipurpose Worksheets 00:28:40, 00:33:41
Streamlining Custom Views 00:337:23
Viewing Two Worksheets at Once 00:42:25, 00:47:06
Smarter SUM Formulas 00:48:46
SUBTOTAL Function 00:52:40
Introduction to VLOOKUP 00:57:12
VLOOKUP with MATCH 01:00:56
Introduction to IFERROR 01:05:01
SUMIF Function 01:08:23
SUM/OFFSET Functions 01:11:08
Self-Updating Headings 01:14:31
Unlock Input Cells 01:16:27
Creating a Lock Cell Shortcut 01:19:23
Unlocking Cells/Conditional Formatting 01:21:14
Worksheet Protection 01:24:55
Allow Users to Edit Ranges 01:25:34
Protect Workbook 01:28:23
Password Protect the Workbook 01:29:13
Using Excel Templates 01:30:05
Create a Template 01:32:51
Automatic Backup of Key Excel Workbooks 01:35:14
Other Ways to Create Fall-Back Positions 01:38:04
Closing 01:39:30
- AutoSum Function 00:50:46
- Conditional Formatting 01:21:24
- Custom Views Function 00:29:17
- IFERROR Function 01:05:06
- MATCH Function 01:01:23
- Named Range 00:19:05
- Name Manager 00:19:21, 00:24:08
- OFFSET Function 01:11:12
- Quick Access Toolbar 00:37:30
- SUBTOTAL Function 00:52:40
- SUM Function 00:56:23
- Table Array 00:58:00
- Templates 01:30:05
- Wrap Text 00:08:16
AutoSum: The AutoSum feature appears on both the Home menu and the Formulas menu as a Greek sigma symbol. When you click AutoSum, or press Alt-= Excel adds a sum function to the current cell or cells that you've selected.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Constants: A constant is a value that doesn't change (or rarely changes).
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
OFFSET: A worksheet function that allows users to create dynamically sized ranges for use within other worksheet functions, such as SUM or SUMIF.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
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.
TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.
Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
Templates: Documents designed to serve as a starting point so that information does not have to be recreated. Templates also make it much harder for users to inadverently save over a master copy when replicating a document or spreadsheet.
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.
Wrap Text: Wrap Text is a feature that wraps the text within a cell. Wrap Text can be turned off by highlighting the cell and clicking the Wrap Text button again.
