Excel Agility: Interactive Spreadsheets
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
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:
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
Introduction
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
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.
