On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
You’ll discover how Excel’s Table feature can transform the way you use Excel and increase your effectiveness when you take this course. Excel expert David Ringstrom, CPA, provides a top-to-bottom overview of the Table feature. He discusses numerous opportunities to utilize this valuable feature, vastly improve the integrity of spreadsheets, and reduce spreadsheet maintenance.
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 used in the course.
Who Would Be Interested in This Course:
Practitioners seeking to learn about the Table feature and how it can help them work more effectively in Excel.
Your Benefits of Attending:
- Minimizing a data integrity risk within pivot tables by way of Excel’s Table feature.
- Managing cumbersome lists of data using the Table feature.
- Filtering multiple lists on a single worksheet using the Table feature.
- Identifying the feature conflict that arises when you utilize tables within Excel workbooks.
- Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
- Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
- Determining whether formulas within tables use cell references or field names.
- Eliminating the need to manually resize charts when data is added—automate this with tables instead.
- Creating an in-cell list by way of Excel’s Data Validation feature, and then automating the addition of new items with a table.
- Understanding how the Table feature automates formula management within lists.
- Avoiding the need to write repetitive formulas using Excel’s Data Table feature.
Learning Objectives:
- Recall how the Table feature can improve the integrity of Excel spreadsheets and reduce the time required to maintain them.
- Identify the nuances of the Table feature, thereby avoiding frustration.
- Identify the feature that makes charts expand automatically as you add additional data to the source range.
Level:
Basic
Format:
On-demand webcast
Instructional Method:
Self-study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
None
Advance Preparation:
None
Table of Contents
Introduction
Excel Versions 00:01:02
Table Feature 00:02:03
Data Table - Two Variables 00:11:08
Undoing the Table Feature 00:15:54
Managing Table Styles 00:18:27
Table Feature Total Row 00:22:24
Table Feature Filtering Nuance 00:27:20
Slicers with Tables (Excel 2013+) 00:31:22
Filtering Data with Custom Views 00:37:13
Filtering Data with Custom Views (cont.) 00:39:31
Tables Conflict with Custom Views 00:41:40
Locate Tables within a Workbook 00:43:21
Table Navigation Shortcuts 00:46:36
Paste Table as Raw List 00:49:43
Formula Management within a Table 00:52:38
Table Formulas: Column Names vs. Cell References 00:56:52
Introduction to VLOOKUP 0059:15
Using the Table Feature with VLOOKUP 001:05:32
Introduction to SUMIF 01:08:40
Simplify Formula Writing with Tables 01:11:44
In-Cell Drop-Down List 01:13:57
In-Cell Drop-Down List (cont.) 01:18:04
In-Cell Drop-Down List (cont.) 01:19:42
Self-Expanding Charts 01:21:36
Pivot Tables with Table Feature 01:28:02
Table Conflicts: Copying Grouped Sheets 01:30:06
SUM/OFFSET 01:32:23
COUNTA Function 01:35:25
Creating a Dynamic Range 01:37:03
Conclusion 01:40:23
Index
- Charts 01:21:39
- COUNTA Function 01:35:29
- Custom Views 00:37:13
- Data Table 00:11:12
- Data Validation 00:06:33
- Dynamic Range 01:32:44, 01:37:03
- Filter 00:27:41
- Group Worksheets 01:30:24
- Name Manager 00:44:19
- OFFSET 01:32:29
- Slicer Feature 00:31:32
- SUM 01:32:59
- SUMIF 01:08:44
- Table Feature 00:02:03
- Total Row 00:22:20, 00:22:34
- VLOOKUP 00:59:15
- What-If Analysis 00:12:43
COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.
Charts: A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Data Table: Data tables are defined as a range of cells that are used for testing and analyzing outcomes on a large scale. It is a way to see how altering the values in a formula affect the results. Data tables can store the results of multiple scenarios in your spreadsheet, and saves you time in calculating multiple formulas.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
Dynamic Range: Dynamic ranges are also known as expanding ranges - they automatically expand and contract to accommodate new or deleted data.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Group Worksheets : To group two or more worksheets, hold down the Shift key to select a range of sheets or the Ctrl key to select individual sheets. When sheets are grouped, any changes are made simultaneously on any other worksheets presently included in the group.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
OFFSET: A worksheet function that allows users to create dynamically sized ranges for use within other worksheet functions, such as SUM or SUMIF.
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.
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.
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.
What-If Analysis: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.
