On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Taxation and Accounting, Business Administration, Business Skills
- Credit: CPE 2.0, ATATX 1.5, ATAAA 1.5, ATAOP 1.5
-
In this presentation, author and Excel expert David H. Ringstrom will bring numerous features and functions to your attention that fly under the radar for many Excel users. The table feature offers tremendous automation and data integrity improvements in any version of Excel, as do dynamic array worksheet functions in Excel 2021 and Microsoft 365. You'll see how to create self-updating stock quotes in Excel, use a better alternative to merged cells, unlock hidden shortcuts, and much more.
David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Topics typically covered:
- Adding Full Screen View to the Quick Access Toolbar to view more rows in large spreadsheets.
- Managing the external data security warning that may appear when you link external data into Excel spreadsheets.
- Filtering a cleaned-up accounts receivable aging report to display only overdue amounts.
- Streamlining Custom Views by adding a drop-down list to Excel's Quick Access Toolbar.
- Displaying subsets of data dynamically by way of the new FILTER worksheet function.
- Creating custom views that will enable you to display all worksheets in a workbook at once as well as hide/display selected worksheets.
- Streamlining the filtering of lists using the Slicer feature with tables.
- Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.
- Assemble a new collection of data by specifying the rows or columns to extract from another data set with the CHOOSEROWS and CHOOSECOLS functions.
- Changing the data type for columns of data within the results grid.
- Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.
- Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
Learning objectives:
- Recall the menu in Excel where the Table feature resides.
- Specify which command the Remove Rows command appears on within the Power Query Editor.
- Identify the menu where the Custom Views feature resides.
Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Specialized Knowledge and Applications (2 hours)Program
Prerequisites: Prior experience with Microsoft Excel is recommended.
Advance Preparation: None
- Introduction
- Please Ask Questions Today 00:01:48
- Excel Versions 00:03:46
- Creating Excel Tables 00:04:52
- Adding a Total Row to an Excel Table 00:13:00
- Filtering Excel Tables with Slicers 00:17:30
- Convert Excel Table to Normal Range 00:30:36
- Clean A/R Aging with Power Query 00:33:26
- Clean A/R Aging with Power Query - Steps 1 - 8 00:36:25
- Clean A/R Aging with Power Query - Steps 9 - 18 00:39:34
- Clean A/R Aging with Power Query - Steps 19 - 27 00:47:17
- Clean A/R Aging with Power Query - Steps 28 - 36 00:48:43
- Clean A/R Aging with Power Query - Steps 37 - 42 00:54:33
- Filter A/R Aging for Overdue Amounts 00:59:34
- External Data Security Warning 01:08:20
- FILTER Function (Excel 2021+) 01:10:37
- CHOOSEROWS/CHOOSECOLS (Microsoft 365) 01:17:46
- SORT Function (Excel 2021+) 01:21:56
- Group Worksheets 01:24:48
- All Worksheets Custom View 01:28:05
- Creating a Custom View to Hide Worksheets 01:31:25
- Utilizing a Custom View 01:33:01
- Streamlining Custom Views 01:33:51
- Analyze Data Feature (Microsoft 365) 01:37:24
- Data Types - Stocks (Microsoft 365) 01:39:12
- Restoring Full Screen View 01:41:05
- Thank you for attending! 01:43:41
- Presentation Closing 01:43:41
- Analyze Data 01:37:30
- Cell 00:36:11, 00:45:13
- Cell Reference 00:29:41, 01:19:31
- CHOOSECOLS 01:18:14
- CHOOSEROWS 01:
- Clipboard 00:20:42
- Column 00:40:24, 00:45:10, 00:50:07, 00:55:32, 01:15:40, 01:22:34
- COUNTIF 00:59:42
- Custom Views 01:24:55, 01:28:06, 01:30:06, 01:33:42
- Dialog Box 00:45:23, 01:28:40
- Dynamic Array Function 01:10:42, 01:29:51
- Fill Down 00:50:12
- Filter 00:09:57, 00:13:39, 00:36:02, 00:54:37, 01:04:40, 01:17:57
- FILTER Function 01:11:27
- Format 00:30:38
- Formula 00:34:17, 01:04:58, 01:20:12
- Freeze Panrs 00:22:26
- Microsoft 365 00:03:12, 00:03:42, 00:19:57, 00:39:48, 01:10:38, 01:37:26
- Number Formatting 01:03:57
- Power Query 00:33:31, 00:35:45, 01:35:36
- Power Query Editor 0:34:02, 00:40:53, 00:44:36, 00:52:26
- Quick Access Toolbar 01:33:57, 01:41:08
- Row 00:40:09, 00:47:05
- Slicers 00:17:33, 00:18:00, 00:18:27, 00:24:57, 01:04:11
- SORT 01:22:00
- Table 00:05:11, 00:05:18, 00:05:26, 00:08:46, 00:10:13, 00:23:36, 00:31:02, 00:59:35, 01:37:36
- Table Feature 00:22:23
- Total Row 00:29:34, 00:36:14
- Workbook 00:34:51, 01:33:02, 01:34:14
- Worksheet 00:19:55, 00:31:20, 01:28:12, 01:33:04, 01:41:52
Analyze Data: Analyze Data in Excel empowers you to understand your data through natural language queries that allow you to ask questions about your data without having to write complicated formulas. In addition, Analyze Data provides high-level visual summaries, trends, and patterns.
CHOOSECOLS: Returns the specified columns from an array. The CHOOSECOLS function syntax has the following arguments:array The array containing the columns to be returned in the new array. Required.col_num1 The first column to be returned. Required.col_num2 Additional columns to be returned. Optional.
CHOOSEROWS: Returns the specified rows from an array. The CHOOSEROWS function syntax has the following arguments:array The array containing the columns to be returned in the new array. Required.row_num1 The first row number to be returned. Required.[row_num2] Additional row numbers to be returned. Optional.
COUNTIF: Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition. For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Clipboard: A clipboard is a temporary storage area for data that the user wants to copy from one place to another. In a word processor application, for example, the user might want to cut text from one part of a document and paste it in another part of the document or somewhere else.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.
FILTER Function: The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. array is the range or array containing the values you want filtered. include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or width as the array.
Fill Down: Fill Down is a rather unique transform in how it operates. By selecting Fill Down on a particular column, a value will replace all Null values below it until another non-null appears. When another non-null value is present, that value will then fill down to all Null values.
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.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
Freeze Panes: This command on the View tab of Excel’s ribbon interface, or the Window menu in Excel 2003 and earlier, allows you to ensure that one or more rows and/or columns always remain on-screen as you scroll down through a worksheet.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SORT: Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.
Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
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.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
