Creating Error-Free Spreadsheets
Access this expert-led webinar instantly, available anytime on-demand.
Included in All-Access Membership
In this presentation, author and Excel expert David H. Ringstrom, CPA, will empower you with techniques that make for efficient spreadsheet design and data analysis. Learn how to streamline your Excel worksheets by placing titles in a single row, thereby eliminating clutter and improving readability. Discover the power of referencing source data cells directly and creating settings tables for easy configuration and review. Enhance your calculations by assigning names to key input cells, writing smarter SUM formulas, and utilizing the SUBTOTAL function to build resilience into your calculations. You'll also master conditional formatting for unlocked cells and learn how to remove it when necessary.
David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Who should attend:
Professionals seeking to use Microsoft Excel more effectively.
- Summing sections quickly with the SUBTOTAL function.
- Using SUMIF to total values based on a single condition.
- Removing Conditional Formatting rules when they are no longer needed.
- Displaying alternate results with XLOOKUP by populating the If_Not_Found argument.
- Toggling cell lock status with a custom shortcut.
- Unlocking data entry cells before protecting worksheets.
- Understanding how the VLOOKUP function allows you to look up data.
- Using Conditional Formatting to identify unlocked cells for data entry.
- Most features and functions work in Excel for Mac as well, but expect differences.
- Building resilience into spreadsheets by avoiding daisy-chained formulas.
- Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.
- Enabling worksheet protection to preserve data and formulas within locked cells.
- Define the arguments for the INDEX worksheet function.
- State which section of Excel's File menu enables you to mark a document as trusted.
- Recall the area of Excel's Options dialog box that allows you to enable the Solver feature in Excel.
Level: Basic
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
- Introduction
- Topics At A Glance 00:00:47
- Presenting with Microsoft 365 for Windows 00:01:15
- Section 1: Structuring the Workbook 00:03:17
- Placing Headers in a Single Row (1/2) 00:04:18
- Placing Headers in a Single Row (2/2) 00:10:36
- Referring Directly to the Source 00:12:53
- Assigning Names to Key Input Cells 00:20:34
- Section 2: Custom Views and Multitasking 00:28:23
- Custom Views for Multipurpose Worksheets (1/2) 00:30:50
- Custom Views for Multipurpose Worksheets (2/2) 00:35:58
- Viewing a Workbook on Two Monitors 00:39:26
- Viewing Two Worksheets on One Monitor 00:43:28
- Section 3: Enhancing Formulas and Functions 00:45:49
- Creating Smarter SUM Formulas 00:57:57
- Preventing Double-Counting with SUBTOTAL 01:03:53
- Using the SUMIF Function 01:03:56
- Section 4: Mastering Lookup Functions 01:12:49
- Using the VLOOKUP Function 01:13:56
- Using the XLOOKUP If_Not_Found Argument 01:19:32
- Section 5: Controlling Error and Access 01:24:51
- Handling Errors with IFERROR 01:28:01
- Unlocking Input Cells 01:32:12
- Creating a Lock Cell Shortcut 01:36:10
- Conditionally Formatting Unlocked Cells 01:38:18
- Removing Conditional Formatting 01:43:43
- Section 6: Protecting Worksheets 01:43:44
- Protecting a Worksheet 01:43:57
- What We Covered 01:46:02
- Thank You for Attending! 01:47:04
-
David H. Ringstrom, CPA
David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]
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 [email protected].
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.
- #N/A Error 01:20:31, 01:26:11
- Absolute Reference 00:14:48, 00:20:14, 00:28:05
- Arrange All Command 00:43:32
- AVERAGE 01:13:09
- Cell 00:01:10, 00:03:38, 00:10:51, 00:20:43, 00:31:30, 00:40:29, 00:48:04, 00:59:40, 01:24:57, 01:32:22, 01:37:08
- Cell Reference 00:20:53
- Column 00:03:52, 00:04:35, 00:12:31, 00:20:00, 00:29:37, 00:36:16, 01:06:02, 01:17:28, 01:32:59
- CONCATENATE Function 00:06:38
- Conditional Formatting 01:25:42, 01:38:14, 01:43:42
- Constants 01:33:14
- Custom Views 00:28:28, 00:38:33
- Dialog Box 00:31:49, 00:43:55, 01:33:06
- Format 01:36:18
- Formula 00:01:07, 00:10:38, 00:13:00, 00:20:56, 00:27:49, 00:34:39, 00:45:52, 00:59:27, 01:17:50, 01:28:32, 01:32:35
- Formula Bar 00:11:21
- Go To Special 01:33:05
- IFERROR Function 01:20:41
- IFNA Function 01:19:06, 01:28:01
- Indirect Reference 00:13:36
- ISERROR 01:28:49
- Keyboard Shortcut 00:49:26, 01:36:14
- LET Function 01:31:49
- Microsoft 365 00:01:17, 01:13:51
- Name Box 00:21:19
- Pivot Table 00:05:20
- Power Query 00:32:58
- Quick Access Toolbar 01:36:41
- Restore Down Button 00:40:45
- Row 00:03:52, 00:04:56, 00:13:25, 00:29:37, 00:47:16
- Select All button 00:31:07
- Spreadsheet 00:00:14, 00:04:15, 00:13:54, 00:46:52, 00:59:02, 01:13:22, 01:24:29, 01:38:24
- SUBTOTAL 00:46:19, 00:58:55
- SUM 00:45:58, 00:58:08
- SUMIF Function 00:49:24, 01:03:56, 01:19:54
- Table Array 01:15:19
- Table Feature 00:32:33
- TEXTJOIN Function 00:06:37
- Total Row 00:55:37
- TRIM Function 00:10:16
- VLOOKUP 01:06:36, 01:13:11, 01:19:12
- Workbook 00:00:54, 00:36:54, 00:44:00
- Worksheet 00:21:23, 00:28:56, 00:36:54, 01:38:38
- Wrap Text 00:11:01
- XLOOKUP 01:06:36, 01:13:25, 01:19:36
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
AVERAGE : Returns the average (arithmetic mean) of the arguments.
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.
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.
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.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
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.
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.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.
Go To Special Command: Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet.
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.
ISERROR: The ISERROR function checks whether a value is an error and returns TRUE or FALSE. The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message or run a different calculation when found.
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.
LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
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.
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.
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.
Restore Down: The restore down button, located in the top-right corner of a window between the minimize and close buttons, reduces a maximized window to a smaller, adjustable size. It is represented by two overlapping boxes, indicating a transition from full-screen to a windowed view.
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.
Select All : A clickable box located at the intersection of the row numbers and column letters in the upper-left corner of a worksheet. Clicking the Select All button highlights the entire worksheet, making it easy to apply formatting, unhide rows and columns, or perform other actions across all cells.
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
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.
