On-Demand Webinar

Interactive Spreadsheets in Excel

  • Recorded August 7, 2019
  • 100 Minutes
  • Available now
4.4 / 5 5 attendee reviews
CE credits
  • 1.50 ATAAA (Aurora Administrative)
  • 1.50 ATATX (Aurora Accounting)
  • 1.50 ATAOP (Aurora Operations)

What You'll Learn

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. 
View Full Description

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the 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:

•Allowing users to make multiple choices by way of the ListBox form control.

•Contrasting data validation–based lists versus form control–based lists.

•Using the ComboBox form control to create a searchable drop-down list.

•Viewing properties that can be set for specific form controls; for example, whether the object should be printed or not.

•Creating an in-cell list by way of Excel’s Data Validation feature.

•Minimizing ongoing spreadsheet maintenance with Excel’s Table feature.

•Streamlining data entry in Excel with the CheckBox form control.

•Using Excel’s Go to Special feature to easily select form controls you wish to remove en masse.

•Contrasting form controls in Excel versus ActiveX controls.

•Limiting the number of characters a user can enter in a worksheet cell.

•Specifying a range of whole numbers a user can enter in a worksheet cell.

•Unlocking all data validation cells at once within a worksheet as opposed to tracking cells individually.

Learning Objectives:

•Describe how Excel’s Table feature can be used 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.

Meet Your Speaker

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

View Full Bio of David H. Ringstrom, CPA

What Attendees Are Saying

  • “A lot of information to take in within the hour and forty minutes.Although very well presented, some brain overload...”

    DORIS C. , AP MANAGER
See the survey ratings

5 attendees rated this webinar 4.4 out of 5 on average.

4.4 Overall Rating

Webinar Presentation

How many of the objectives of the event were met?
4.6
How useful was the information presented at this event?
4.4
Overall, how satisfied were you with this event?
4.4

Speaker Performance

Overall, how satisfied were you with this presenter?
4.4
How closely did the presenter follow the schedule?
4.2

CE Credits

These are the continuing education credits available to you for this webinar.

  • 1.50 ATAAA Aurora Administrative Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.
  • 1.50 ATATX Aurora Accounting Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.
  • 1.50 ATAOP Aurora Operations Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

Frequently Asked Questions

What are Excel form controls and how do they make spreadsheets more interactive?
Excel form controls are built-in interactive elements that allow spreadsheet creators to control how other users input and interact with data in a workbook. Rather than leaving users to type directly into cells—with all the errors and inconsistencies that entails—form controls provide structured, guided input mechanisms. The ComboBox creates a searchable drop-down list allowing users to select from predefined values, dramatically reducing data entry errors. The ListBox displays multiple options simultaneously and can allow multiple selections at once. The CheckBox enables binary yes/no inputs linked to cell values, ideal for tracking attributes or options. The Scroll Bar and Spin Button allow users to adjust numerical values within defined ranges. Unlike freeform typing, form controls enforce consistency—users can only select valid options, not enter arbitrary text that breaks formulas or downstream reports. For spreadsheets shared with multiple users or non-Excel experts, form controls are transformative tools that shift a technical workbook into a user-friendly, error-resistant business tool accessible to any skill level, requiring no training in Excel data entry conventions.
What is Excel's Data Validation feature and when should you use it?
Excel's Data Validation feature restricts what users can enter into designated cells, preventing data entry errors before they occur rather than discovering them after reports have been generated. Data Validation can enforce whole number ranges, decimal ranges, date ranges, text length limits, and custom formula-based conditions. The drop-down list option creates an in-cell selection menu of acceptable values that users pick from, similar to a form control list but requiring no advanced configuration. Data Validation also allows customizable error messages that appear when invalid input is attempted, guiding users toward acceptable entries. Circle Invalid Data mode visually highlights cells that already contain entries violating the validation rules—useful for auditing existing data quality. For shared workbooks used across teams or departments, strategic Data Validation implementation dramatically reduces the time HR, finance, and operations professionals spend cleaning up incorrectly entered data before analysis or reporting can proceed. Data Validation combined with Excel's Table feature creates a scalable, self-maintaining data entry architecture ideal for operational tracking spreadsheets.
How does Excel's Table feature help minimize spreadsheet maintenance?
Excel's Table feature—applied via Insert > Table—transforms a standard data range into a dynamic, auto-expanding structured data object that dramatically reduces ongoing spreadsheet maintenance. When a new row is added to a Table, all formatting, formulas, and data validation rules automatically extend to the new row—eliminating the need to manually copy configurations each time the dataset grows. Table column headers automatically lock as you scroll, improving navigation in long datasets. Structured references in Table formulas—using column names rather than cell addresses—update automatically when columns are added, removed, or renamed, making formulas far more readable and self-maintaining. Tables integrate seamlessly with PivotTables and charts: as source data grows, a PivotTable based on a Table automatically includes new rows when refreshed, without requiring manual data range expansion. For spreadsheets used to track ongoing operational data—employee rosters, expense logs, procurement records—the Table feature provides a foundational architecture that makes spreadsheets scalable and reduces the technical maintenance burden that often leads to formula errors and data quality issues over time.
What is the difference between Excel form controls and ActiveX controls?
Excel offers two categories of interactive controls: form controls and ActiveX controls, and choosing between them significantly impacts spreadsheet compatibility and ease of use. Form controls are simpler, more compatible, and easier to configure without programming knowledge. They work consistently across Windows and Mac versions of Excel and in protected worksheets, and are linked to cell values through straightforward cell reference properties. ActiveX controls are more powerful and customizable—they can be controlled through VBA programming, respond to events like mouse clicks, and offer more sophisticated appearance options. However, ActiveX controls do not work on Mac versions of Excel, can trigger security warnings in environments with macro restrictions, and require VBA knowledge to configure effectively. For most business spreadsheet users creating interactive forms for colleagues or clients, form controls are the preferred choice: they are easier to implement, more cross-platform compatible, and sufficient for the vast majority of interactive spreadsheet use cases—including searchable drop-downs, multi-select lists, and checkbox inputs—without requiring any programming knowledge or creating compatibility concerns.
How do you create a searchable drop-down list in Excel?
Creating a searchable drop-down list in Excel—where users can type to filter the list rather than scrolling through all options—requires the ComboBox form control rather than Data Validation's standard list. To create one: enable the Developer tab (File > Options > Customize Ribbon), click Insert under the Controls group, and select the ComboBox from Form Controls. Draw the ComboBox on the worksheet, right-click to access Format Control, and set the Input Range property to the cell range containing your valid values. Set the Cell Link property to a cell where the selected value will be stored for use in formulas elsewhere in the spreadsheet. The ComboBox allows users to type the beginning of a value and have the list filter to matching options, enabling efficient selection from long lists without scrolling through hundreds of entries. Pairing the ComboBox's Input Range with an Excel Table ensures the valid values list automatically expands when new items are added—no manual updates required. For any spreadsheet with large reference lists, the searchable ComboBox delivers dramatically better user experience than static Data Validation drop-downs.