Excel Agility: Interactive Spreadsheets
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit:   CPE 2.0
If you’d like to learn a variety of techniques that will enable you to create interactive, accurate, and user-friendly Excel spreadsheets, this comprehensive course is just what you need. Excel expert David Ringstrom, CPA, demonstrates how to use Excel’s form controls and features to control the data input of other users, simplify data entry, preserve key formulas, minimize spreadsheet maintenance, 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.
- Contrasting Form Controls in Excel versus ActiveX Controls.
- Discovering the array of form controls available within Excel.
- Crafting data validation lists that are contingent on selections from a preceding list.
- Streamlining data entry in Excel with the CheckBox form control.
- Creating an in-cell list by way of Excel’s Data Validation feature.
- Using the ComboBox form control to create a searchable drop-down list.
- Creating an input rule that requires names to be entered in a last-name, first-name format.
- Removing data validation from a worksheet when needed.
- Avoiding the need to merge cells—use the Text Box feature for paragraphs of text instead.
- Viewing properties that can be set for specific form controls; for example, whether the object should be printed or not
- Allowing users to make multiple choices by way of the ListBox form control.
- Recall what the linked cell for a checkbox displays when a user clicks the checkbox on.
- State which Go To Special option allows you to select all form controls on a worksheet at once.
- Name Excel features that will minimize spreadsheet maintenance.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Experience with Excel Spreadsheets
Excel Versions 00:01:24
Data Validation/Text Box Features 00:02:16
Form Control Feature 00:04:12
Data Validation - Whole Number 00:05:11
Correcting Data Validation Settings 00:10:58
Data Validation - Decimal 00:13:00
Data Validation - Date 00:16:13
Data Validation - Time 00:25:24
Data Validation - Drop Down List (Cell-based) 00:29:16
Using Table Feature for Self-Expanding Lists 00:35:34, 00:36:53, 00:40:43
Slicers with Tables (Excel 2013+) 00:43:14
Undoing the Table Feature 00:46:37
Data Validation - Drop Down List )Manual) 00:47:49
Data Validation - Dependent LIsts 00:52:03,
Data Validation - Dependent LIsts (cont.) 00:53:37
Data Validation - Last Name, First Name+ 00:56:07
Data Validation - Text Length 00:58:30
Unlocking Data Validation Cells 00:59:20
Protecting Data Validation Settings 01:00:49
Identify Data Validation Overrides 01:02:07
Removing Data Validation 01:06:27
Text Box 01:07:49
Enabling the Developer Menu 01:11:37
Form Control - Combo Box 01:13:27
INDEX Converts Cell Link to Words 01:17:35
Format Control - Properties Tab 01:18:33
Form Control - List Box 01:19:11
List Box - Selection Type 01:21:21
Data Validation Lists vs. Form Control Lists 01:22:16
Form Control - Check Box 01:23:04
Form Control - Option Box 01:25:31
Form Control - Group Box 01:28:32
ActiveX Controls 01:34:54
ActiveX Control - Spin Button 01:35:23
ActiveX Control - List Box 01:37:47
ActiveX Control - Check Box 01:38:12
ActiveX Control - Combo Box 01:38:48
ActiveX Control - Scroll Bar 01:39:45
Form Controls and Protected Worksheets 01:40:02
Deleting Form Controls 01:40:53
Selection Pane 01:41:51
Presentation Closing 01:43:32
- 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
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.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
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.
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.
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.
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.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
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.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
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.
SUBSTITUTE Function: The Excel SUBSTITUTE function replaces text in a given string by matching. SUBSTITUTE is case-sensitive and does not support wildcards.
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.
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.
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.
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.
Developer menu: Turn on the Developer tab in Excel if you want to create a macro, export and import XML files or insert controls.
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.
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.
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.
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.
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.
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.
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.
Spin Button: A spin button can be used to increment a number in a cell.
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.