10 Productive Excel Features for Accountants
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Accounting, Business Skills, Software
- Credit: CPE 2.0, ATATX 1.5
-
In this presentation Excel expert David H. Ringstrom, CPA cherry-picks 10 of the best features in Excel that can make accountants more productive. He'll also share 3 common time-wasters that can frustrate accountants and heavy users of Excel. You'll learn about two key worksheet functions, the benefits of the Table feature, pivot tables for report writing, and managing information overload by filtering. Tame unwieldy workbooks by instantly unhiding all hidden worksheets, and then deploying Excel's Custom Views feature to enable you to hide/unhide multiple worksheets at once. You'll see how to build in some insurance against Excel crashes, and quickly identify duplicates within a list. David will also discuss three common timewasters/frustrations in Excel.
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:
Practitioners seeking to use Excel more effectively.
Topics typically covered:
- Adding rows to a blank pivot table to create instant reports.
- Choosing between custom views within a workbook to hide/display multiple worksheets at once.
- Creating a pivot table to transform lists of data into on-screen reports.
- Creating custom views that will enable you to display all worksheets in a workbook at once as well as hide/display selected worksheets.
- Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
- Discovering new worksheet functions available in Excel 2016 and later.
- Displaying alternate results with XLOOKUP by populating the If_Not_Found argument instead of using IFERROR or IFNA.
- Distinguishing the differences among pivot table-related menus in Excel 2013 and later versus older versions of Excel.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.
- Gaining control of long lists of data by filtering instead of sorting.
- Identifying duplicates in a list using Conditional Formatting.
Learning objectives:
- Define the arguments used with the VLOOKUP function.
- Recall the benefits of Excel's Table feature.
- State which Conditional Formatting menu contains the Duplicate Values option.
- Introduction
- Please Ask Questions Today! 00:01:09
- Excel Versions 00:03:42
- VLOOKUP Introduction 00:04:32
- VLOOKUP - Approximate Match 00:12:03
- VLOOKUP Deleting Columns Causes #REF! 00:16:36
- VLOOKUP Text vs. Numbers Causes #N/A 00:21:14
- VLOOKUP with IFNA (Excel 2013+) 00:28:36
- XLOOKUP Introduction (Excel 2021+) 00:38:31
- XLOOKUP If_Not_Found Argument 00:42:03
- XLOOKUP Can Return Multiple Columns 00:52:31
- SUMIF Function 01:03:32
- Table Feature 01:06:24
- Slicers with Tables (Excel 2013+) 01:12:30
- Remove Table Feature from Worksheet 01:16:21
- Filtering Data 01:18:23
- Ideal Data for Pivot Tables 01:18:44
- Initiating a Pivot Table 01:20:23
- Adding Fields to a Pivot Table 01:20:40
- Pivot Table Interfaces 01:23:21
- Unhide All Worksheets in a Workbook 01:30:01
- All Worksheets Custom View 01:31:15
- Quarters Only Custom View 01:32:14
- Tables Conflict With Custom Views 01:32:41
- AutoRecover Settings 01:33:10
- Conditional Formatting – Duplicates 01:36:51
- Remove Conditional Formatting 01:38:27
- File Close Trap (Excel 2013+) 01:38:47
- Managing Quick Analysis Icon 01:39:45
- Worksheet Function Evolution 01:40:36
- Thank you for attending! 01:42:17
- Presentation Closing 01:42:37
- #N/A Error 00:21:40, 00:28:46, 00:52:02
- #REF! Error 00:16:41, 00:28:59, 00:41:45
- AutoRecover 01:33:10
- Cell 00:54:11, 01:06:49, 01:12:42, 01:18:50
- Column 00:06:33, 00:08:53, 00:18:11, 00:41:38, 00:43:26, 00:52:59, 01:21:20, 01:32:20
- Column Headings01:07:12
- Conditional Formatting 01:36:57
- Custom Views 01:31:19, 01:32:50
- Direct References 00:05:35
- Dynamic Array Function 00:52:44, 00:54:48
- Field 01:12:48
- Filter 01:07:11, 01:14:20, 01:18:26, 01:20:45
- Formula 00:05:56, 00:11:11, 00:45:42, 00:53:08
- FORMULATEXT 00:59:39
- IFERROR Function 00:28:37, 00:43:23, 01:03:57
- IFNA Function 00:29:06, 00:42:04, 01:03:59
- LOOKUP 00:04:36, 00:05:57, 00:11:53, 01:03:42
- Macro 01:30:18
- MATCH Function 00:33:07
- Microsoft 365 00:03:56, 00:38:33, 00:55:00, 01:30:09
- Pivot Table 01:12:36, 01:18:44, 01:23:35
- Power Query 01:23:45
- Row 00:07:14, 00:09:02, 01:32:20
- Slicer 01:12:31
- Spreadsheet 00:05:21, 00:41:34
- SUM 00:56:22, 01:03:33
- SUMIF 00:47:27
- Table 01:12:33
- Table Array 00:06:14, 00:07:09, 00:14:15
- Table Feature 01:06:28, 01:32:37
- Total Row 01:06:59
- TRANSPOSE 01:03:00
- VLOOKUP 00:04:41, 00:06:05, 00:06:40, 00:11:05, 00:14:10, 00:33:52, 00:52:16, 01:05:37
- XLOOKUP 00:04:52, 00:17:21, 00:39:41, 00:38:40, 00:52:34, 01:05:37
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#REF! Error: Excel displays this error when a formula contains an invalid cell reference. For instance, Excel’s VLOOKUP function may return #REF! if the col_index_num argument is incorrect. Other formulas may return #REF! if a user deletes one or more columns and Excel can’t adjust the cell references properly.
AutoRecover: The Auto-Recover feature saves copies of all open Excel files at a user-definable fixed interval. The files can be recovered if Excel closes unexpectedly, for example, during a power failure.
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.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Direct References: Direct cell referencing is a method of passing the value of one cell as an argument in a linkage function of another cell. By directly referencing an Excel cell number, you can streamline the link creation process and avoid manually building or modifying each link.
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.
FORMULATEXT: The Excel FORMULATEXT function returns a formula as a text string from given reference. You can use FORMULATEXT to extract a formula as text from a cell. If you use FORMULATEXT on a cell that doesn't contain a formula, you'll get an #N/A error.
Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.
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.
Formula: A formula is an expression which calculates the value of a cell.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
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.
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.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
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.
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.
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: 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
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
TRANSPOSE: The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.
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 Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.
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.
XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.
