Webinar Details $219
- Webinar Date: February 12, 2025
- Webinar Time: 12:00pm - 1:40pm EST live
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0, ATATX 1.5, ATAAA 1.5, ATAOP 1.5, ATAPU 1.5
-
-
When you attend this comprehensive presentation, you’ll learn a variety of tricks and techniques to improve the integrity of your Excel spreadsheets as well as audit the spreadsheets created by others. Excel expert David Ringstrom discusses the benefits and uses of several Excel features and functions, including the Hide and Protect feature, the Conditional Formatting feature, the VLOOKUP function, the SUMIF function, and more.
David demonstrates every technique 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. David also provides an Excel workbook that includes most of the examples he uses 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.
Topics typically covered:
- Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
- Using Conditional Formatting to identify unlocked cells into which data can be entered.
- Performing dual lookups, which allow you to look across columns and down rows to cross-reference the data you need.
- Limiting access to sensitive workbooks by way of password protection.
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.
- Preserving key formulas using hide and protect features.
- Utilizing the New Window and Arrange Windows commands to view two different worksheets simultaneously.
- Utilizing the New Window and Arrange Windows commands to view two different worksheets simultaneously.
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
- Streamlining Custom Views by adding a drop-down list to Excel's Quick Access Toolbar.
- Improving the integrity of many Excel features by placing column headings within a single row instead of spanning two or more rows.
- Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.
Learning objectives:
- Apply Data Validation to document input cells and enforce data integrity.
- State how to hide and unhide multiple worksheets with a single command.
- Apply Conditional Formatting to identify unlocked cells into which data can be entered.
- Introduction
- Please Ask Questions Today 00:02:08
- Excel Versions 00:03:59
- Place Titles in a Single Row 00:04:55
- Place Titles in a Single Row Cont’d 00:15:23
- Refer Directly to the Source 00:17:34
- Custom Views - Multipurpose Worksheets 00:27:44
- Custom Views - Multipurpose Worksheets Cont’d 00:34:44
- Streamlining Custom Views 00:44:34
- Viewing a Workbook on Two Monitors 00:58:29
- Viewing Two Worksheets On One Monitor 01:02:25
- Smarter SUM Formulas 01:05:34
- SUBTOTAL Function 01:10:06
- VLOOKUP Introduction 01:14:44
- XLOOKUP If_Not_Found argument 01:18:25
- VLOOKUP with IFNA (Excel 2013+) 01:22:32
- SUMIF Function 01:25:46
- Unlock Input Cells 01:28:46
- Creating a Lock Cell Shortcut 01:30:49
- Unlocked Cells/Conditional Formatting 01:32:15
- Remove Conditional Formatting 01:35:16
- Worksheet Protection 01:35:19
- Protect Workbook 01:35:47
- Password Protect the Workbook 01:36:44
- Using Excel Templates 01:37:46
- Automatic Backup of Key Excel Workbooks 01:38:19
- Other Ways to Create Fall-Back Positions 01:40:15
- Thank You For Attending! 01:42:21
- Presentation Closing 01:45:15
-
David H. Ringstrom, CPA
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 use [...]
CPE Credit
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 training@auroratrainingadvantage.com.
ATATX Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.ATAAA Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.ATAOP Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.ATAPU Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in purchasing.- #N/A Error 00:26:10, 01:24:01
- #REF! Error 00:20:02, 01:24:22
- Absolute Reference 00:19:32
- Cell 00:09:54, 00:20:15, 0:36:43, 01:19:48, 01:31:05
- Column 00:05:10, 00:18:28, 01:18:46
- Column Headings 00:05:30
- CONCATENATE Function 00:10:37
- Conditional Formatting 01:32:27
- Custom Views 00:29:23, 00:30:50, 00:35:09, 00:45:13
- Dialog Box 00:45:35, 01:02:51, 01:36:55
- Filter 00:06:43
- Formula 00:10:12, 00:19:47, 00:37:41, 01:22:19, 01:28:56
- FORMULATEXT 00:26:38
- IFERROR Function 01:24:08
- IFNA Function 00:26:32, 01:23:42
- Indirect Reference 00:18:53
- Macro 00:53:35
- Macro Recorder 00:53:58
- Microsoft 365 00:04:08, 00:09:32
- Mixed Reference 00:19:29
- Pivot Table 00:05:58, 00:35:20
- Power Query 00:30:21, 00:49:57
- Quick Access Toolbar 00:45:18, 01:30:56
- Relative References 00:19:40
- Ribbon 00:51:49
- Row 00:05:45
- SUBTOTAL Function 01:10:12
- SUM Function 01:05:40
- SUMIF 0125:07:
- Table Array 01:16:13
- Table Feature 00:06:08, 00:30:17, 00:35:59
- TEXTJOIN Function 00:09:36
- Total Row 00:37:07
- TRIM Function 00:12:54, 01:13:31
- VLOOKUP 01:05:58, 01:14:52, 01:22:27
- Workbook 00:28:44, 00:44:40, 00:59:05, 01:40:23
- Worksheet 00:28:20, 00:32:28, 00:59:43
- Wrap Text 00:07:03, 00:14:59
- XLOOKUP 00:04:04, 01:18:25
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#REF! Error: Excel displays this error when a formula contains an invalid cell reference. For instance, Excel’s VLOOKUP function may return #REF! if the col_index_num argument is incorrect. Other formulas may return #REF! if a user deletes one or more columns and Excel can’t adjust the cell references properly.
Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.
CONCATENATE Function : The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.
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.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format Command: When we format cells in Excel, we change the appearance of a number without changing the number itself.
Formula: A formula is an expression which calculates the value of a cell.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.
Indirect Reference: As its name suggests, Excel INDIRECT is used to indirectly reference cells, ranges, other sheets or workbooks. In other words, the INDIRECT function lets you create a dynamic cell or range reference instead of hard-coding them.
Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.
Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Mixed Reference: A mixed reference in Excel is a type of cell reference different from the other two absolute and relative. We only refer to the cell's column or row in the mixed cell reference. So, for example, in cell A1 if we want to refer to only the A column, the mixed reference would be $A1.
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.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Relative References: When you reference a worksheet cell in an Excel formula, this defaults to a relative reference, meaning the column letters and row numbers change automatically when you copy or move the formula. Conversely, unless you turn on the Relative Reference feature, macros you record will capture the absolute address of any cells that you effect during the recording process. Relative References instruct the Macro Recorder to transcribe the number of rows or columns to move to next rather than to a specific cell address.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
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.
TEXTJOIN Function : The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter. The TEXTJOIN function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
TRIM Function : The TRIM function removes extraneous spaces from a cell or string of text once space is kept between each word.
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.
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.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
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: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
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.
XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.