Excel Agility: Interactive Spreadsheets

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

In this comprehensive webcast, Excel expert David Ringstrom, CPA, teaches multiple techniques for creating spreadsheets that are interactive, accurate, and user friendly. He shows you how to use a variety of Excel’s form controls to regulate the data input of other users, simplify data entry, create searchable drop-down lists, and more. David also explains such beneficial Excel features as the Table feature, the Data Validation feature, the Text Box feature, and the Go To Special feature. 

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 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.

Microsoft 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 2021, Excel 2019, and so on.


Who should attend:

Practitioners who want to learn how to create user-friendly, interactive spreadsheets. 


Topics Covered:

    • Allowing users to make multiple choices by way of the ListBox form control.
    • Avoiding the need to merge cells—use the Text Box feature for paragraphs of text instead.
    • Contrasting data validation–based lists versus form control–based lists.
    • Crafting data validation lists that are contingent on selections from a preceding list.
    • Creating an in-cell list by way of Excel’s Data Validation feature.
    • Creating an input rule that requires names to be entered in a last-name, first-name format.
    • Discovering the array of form controls available within Excel.
    • Enabling the hidden Developer menu in Excel to access additional functionality.
    • Identifying data validation overrides by way of the Circle Invalid Data command.
    • Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
    • Limiting the number of characters a user can enter in a worksheet cell.
    • Limiting users to a single choice by way of Excel’s OptionButton form control.

    Learning Objectives:

    • Apply Excel’s Table feature to minimize spreadsheet maintenance.
    • Recall how to restrict users from entering dates within a given range.
    • Identify the form control that allows users to make multiple choices.

    Level:

    Intermediate

    Format:

    On-Demand Webcast

    Instructional Method:

    Self-Study

    NASBA Field of Study:

    Computer Software & Applications (2 hours)

    Program Prerequisites:

    Experience with Excel Spreadsheets

    Advance Preparation:

    None


    1. Introduction

    2. Excel Versions 00:01:24

    3. Data Validation/Text Box Features 00:02:16

    4. Form Control Feature 00:04:12

    5. Data Validation - Whole Number 00:05:11

    6. Correcting Data Validation Settings 00:10:58

    7. Data Validation - Decimal 00:13:00

    8. Data Validation - Date 00:16:13

    9. Data Validation - Time 00:25:24

    10. Data Validation - Drop Down List (Cell-based) 00:29:16

    11. Using Table Feature for Self-Expanding Lists 00:35:34, 00:36:53, 00:40:43

    12. Slicers with Tables (Excel 2013+) 00:43:14

    13. Undoing the Table Feature 00:46:37

    14. Data Validation - Drop Down List )Manual) 00:47:49

    15. Data Validation - Dependent LIsts 00:52:03, 

    16. Data Validation - Dependent LIsts (cont.) 00:53:37

    17. Data Validation - Last Name, First Name+ 00:56:07

    18. Data Validation - Text Length 00:58:30

    19. Unlocking Data Validation Cells 00:59:20

    20. Protecting Data Validation Settings 01:00:49

    21. Identify Data Validation Overrides 01:02:07

    22. Removing Data Validation 01:06:27

    23. Text Box 01:07:49

    24. Enabling the Developer Menu 01:11:37

    25. Form Control - Combo Box 01:13:27

    26. INDEX Converts Cell Link to Words 01:17:35

    27. Format Control - Properties Tab 01:18:33

    28. Form Control - List Box 01:19:11

    29. List Box - Selection Type 01:21:21

    30. Data Validation Lists vs. Form Control Lists 01:22:16

    31. Form Control - Check Box 01:23:04

    32. Form Control - Option Box 01:25:31

    33. Form Control - Group Box 01:28:32

    34. ActiveX Controls  01:34:54

    35. ActiveX Control - Spin Button 01:35:23

    36. ActiveX Control - List Box 01:37:47

    37. ActiveX Control - Check Box 01:38:12

    38. ActiveX Control - Combo Box 01:38:48

    39. ActiveX Control - Scroll Bar 01:39:45

    40. Form Controls and Protected Worksheets 01:40:02

    41. Deleting Form Controls 01:40:53

    42. Selection Pane  01:41:51

    43. Presentation Closing 01:43:32

    • David H. Ringstrom, CPA

      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 [...]

      View Full Profile

    ATAAA Credit

    Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

    ATATX Credit

    Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

    ATAOP Credit

    Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

    CPE Credit

    Continuing Professional Education

    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.

    • CheckBox 00:04:42, 01:23:07, 01:38:12
    • Combo Box 00:04:27, 01:13:31, 01:38:48
    • Data Validation 00:02:25, 00:05:13, 01:06:40
    • Developer Menu 00:04:21, 01:12:00
    • Drop Down List 00:29:22, 00:47:49
    • EOMONTH 00:16:26
    • FIND Function 00:56:27
    • Form Control 00:04:15,  01:18:35
    • INDIRECT Function 00:53:46
    • ListBox 00:04:38, 01:19:27, 01:37:48
    • Name Box 00:37:24
    • Name Manager 00:38:08
    • NOW Function 00:21:58
    • Option Button 00:04:44, 01:25:38
    • Scroll Bar 00:05:09,  01:39:45
    • Slicer Feature 00:43:21
    • Spin Box 00:05:04
    • SUBSTITUTE Function 00:52:12
    • Table Feature 00:35:36
    • Text Box Feature 00:03:18, 01:07:49
    • TODAY Function 00:17:56
    • INDEX Function 01:17:37
    • Form control
    • Group Box 01:28:32
    • Macros 01:34:00
    • Macro Recorder 01:34:08
    • Spin Button 01:35:31

    CheckBox: In Excel, a checkbox is an interactive tool that can be used to select or deselect an option. You can use a checkbox in Excel to create interactive checklists, dynamic charts, and dashboards.

    Combo Box: A form of control that is both a text field and a drop-down list. The user can either type information in the combo box directly or make a choice from the list.

    Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.

    Developer menu: Turn on the Developer tab in Excel if you want to create a macro, export and import XML files or insert controls.

    Drop Down List: A drop-down list is an excellent way to give the user an option to select from a pre-defined list. It can be used while getting a user to fill a form, or while creating interactive Excel dashboards. Drop-down lists are quite common on websites/apps and are very intuitive for the user.

    EOMONTH: The Microsoft Excel EOMONTH function calculates the last day of the month after adding a specified number of months to a date. The result is returned as a serial date. The EOMONTH function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel.

    FIND Function: The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive. The FIND 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.

    Form Control : Objects that you can place on a worksheet to add interactivity. Form controls include checkboxes, option buttons, sliders, command buttons, and much more.

    Group Box: Group boxes and frame controls are rectangular objects with optional labels. Use a group box or a frame control to visually organize related items on a form. For example, in a customer order application, group the name, address, and account number of a customer. Or in an order form, group a list of available items.

    INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.

    Indirect Function: The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

    ListBox: A ListBox is a control that you can add to a worksheet or user form to display a list of items. ListBoxes can be set to allow the user to make a single choice or multiple choices.

    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.

    NOW Function: The Microsoft Excel NOW function returns the current system date and time.The NOW function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel. As a worksheet function, the NOW function can be entered as part of a formula in a cell of a worksheet. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor. When used as a worksheet function, the NOW function will refresh the date/time value whenever the worksheet recalculates.

    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.

    Option Button: A radio button or option button is a graphical control element that allows the user to choose only one of a predefined set of mutually exclusive options. The singular property of a radio button makes it distinct from a checkbox, which allows more than one (or no) item to be selected and for the unselected state to be restored.

    SUBSTITUTE Function: The Excel SUBSTITUTE function replaces text in a given string by matching. SUBSTITUTE is case-sensitive and does not support wildcards.

    Scroll Bar: A vertical or horizontal bar commonly located on the far right or bottom of a window that allows you to move the window viewing area up, down, left, or right. Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box. You can move through a page (a preset interval) of values by clicking the region between the scroll box and either scroll arrow.

    Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.

    Spin Box: Makes it easier to increase or decrease a value, such as a number increment, time, or date. To increase the value, click the up arrow; to decrease the value, click the down arrow. A user can also type a text value directly in the associated cell or text box. Use a spin button, for example, to make it easier to enter a month, day, year number, or to increase a volume level.

    Spin Button: A spin button can be used to increment a number in a cell.

    TODAY Function: The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.

    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.

    Text Box Feature : Available on the Insert menu of Excel 2007 and later, or the Drawing toolbar of Excel 2003 and earlier, the Text Box feature is the easiest way to place a paragraph or more of text in a spreadsheet.

    Customer Satisfaction Guarantee
    Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

    Frequently Asked Questions

    Data Validation is an Excel feature that lets you assign data entry rules to one or more cells, restricting what users can input to a worksheet. You access it through Data > Data Validation, where you can set rules such as allowing only whole numbers within a specified range, decimal values, dates within a window, times, or text of a defined length. When a user enters invalid data, Excel displays a custom error message you configure. Data Validation is essential for building spreadsheets that are shared with colleagues who may not know the intended data format—it prevents typos, out-of-range entries, and formatting errors before they occur. You can also use it to create dropdown lists that restrict users to a predefined set of choices. For finance and operations teams building input forms, budget templates, or data collection sheets, Data Validation is the primary tool for maintaining spreadsheet accuracy. Aurora Training Advantage's Excel Agility: Interactive Spreadsheets webinar covers all Data Validation types with step-by-step demonstrations.
    Creating a dropdown list in Excel involves using the Data Validation feature with the List option. Select the cell or range where you want the dropdown, go to Data > Data Validation, choose List as the Allow type, and specify the source—either a comma-separated list of values or a reference to a range of cells containing your options. When users click the cell, a dropdown arrow appears and they can only select from the approved choices. For lists that grow over time, linking the source to an Excel Table ensures the dropdown automatically includes new entries as they are added. You can also create dependent dropdown lists—where the choices in a second dropdown change based on what was selected in a first—using the INDIRECT function as the source reference. Dropdown lists dramatically reduce data entry errors in shared workbooks and are a foundational technique for building professional, user-friendly Excel input forms. David Ringstrom, CPA, demonstrates both cell-based and manual dropdown approaches in this Aurora Training Advantage on-demand webinar.
    Excel Form Controls are interactive objects you place directly on a worksheet to allow user input without requiring cell editing. Available through the Developer menu, they include Combo Boxes (text field plus dropdown), ListBoxes (scrollable multi-select lists), CheckBoxes (for yes/no or true/false choices), Option Buttons (for mutually exclusive single selections), Group Boxes (to visually organize related controls), Spin Buttons (to increment values up or down), and Scroll Bars. Each control links to a specific cell, storing its current value there, which can then drive formulas, charts, or other calculations. For example, a Combo Box linked to a cell can control which dataset a chart displays. Form controls are ideal for building interactive dashboards and input forms that non-Excel-savvy users can operate without accidentally breaking underlying formulas. Excel expert David Ringstrom, CPA, walks through all major form control types in the Aurora Training Advantage Excel Agility: Interactive Spreadsheets on-demand webinar.
    Dependent dropdown lists are cascading dropdowns where the options in a second list change based on what the user selects in a first. For example, selecting a Region in one cell might filter the dropdown in the next cell to show only cities within that region. The key to building this in Excel is using named ranges—each set of sub-options is stored in a named range that matches the values in the first dropdown exactly. The second dropdown's Data Validation source uses the INDIRECT function to reference the named range dynamically: =INDIRECT(A1) points to the named range whose name matches whatever is in cell A1. When a user changes the first selection, the second dropdown instantly updates to show only relevant options. This technique is essential for multi-level selection forms such as product categories, account hierarchies, or geographic filters. It requires careful naming conventions but creates a highly professional, error-reducing user experience in shared Excel workbooks.
    Both Form Controls and ActiveX Controls are available through Excel's Developer menu, but they serve different purposes and have different capabilities. Form Controls are simpler, more stable objects that work directly on a worksheet and are compatible across all Excel platforms including Mac. They link to cells and are ideal for interactive dashboards where values are driven into formulas. ActiveX Controls—including the ActiveX ListBox, CheckBox, ComboBox, Spin Button, and Scroll Bar—are more powerful and customizable through VBA code, supporting event-driven programming. For example, an ActiveX ComboBox can trigger a macro automatically when the user makes a selection. However, ActiveX Controls are Windows-only, can behave inconsistently across Excel versions, and require VBA knowledge to fully utilize. For most interactive spreadsheet needs, Form Controls are the preferred choice due to their simplicity and cross-platform compatibility. ActiveX Controls are best reserved for advanced VBA-driven applications. This distinction is covered in Aurora Training Advantage's Excel Agility: Interactive Spreadsheets webinar with David Ringstrom, CPA.