10 Productive Excel Features for Accountants

1. Introduction
2. Topics At A Glance 00:05:06
3. Presenting with Microsoft 365 for Windows 00:06:08
4. Section 1: Filtering Faster and More Precisely 00:07:44
5. Filtering Data with the AutoFilter Shortcut 00:08:20
6. Adding a Clear Filter Button to the Quick Access Toolbar 00:14:23
7. Getting More Value from Excel Tables 00:16:07
8. Using Totals and Filters in Tables 00:18:03
9. Filtering Excel Tables with Slicers 00:25:50
10. Removing Table Formatting or Tables 00:32:25
11. Section 3: Turning Data into Summaries with PivotTables 00:34:56
12. Initiating a PivotTable 00:36:28
13. Auditing PivotTables 00:42:59
14. Section 4: Modern Formulas for Pattern Discovery 00:46:38
15. Comparing XLOOKUP to Older Lookup Tools 00:49:06
16. Returning Multiple Columns with XLOOKUP 00:58:46
17. Performing Multi-Criteria Lookups 01:04:15
18. Using the SUMIF Function 01:11:05
19. Combining UNIQUE and SUMIF with Spill & Trim 01:19:12
20. Section 5: Streamlining Tasks with the Quick Access Toolbar 01:26:38
21. Highlighting Duplicates with Conditional Formatting 01:28:24
22. Removing Conditional Formatting 01:33:29
23. Section 6: Safeguarding Work and Avoiding Costly Mistakes 01:35:15
24. Adjusting AutoRecover Settings for Desktop Workbooks 01:35:44
25. Accessing AutoRecover Backups 01:38:51
26. Avoiding the File Close Trap 01:41:00
27. What We Covered 01:42:45
28. Now It’s Your Turn—But I’m Here If You Need Me! 01:43:31
29. Presentation Closing 01:43:45

• 

  David H. Ringstrom, CPA

  David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

  View Full Profile

• #N/A Error 00:56:03
• Analyze 00:43:17
• Artificial Intelligence (AI) 00:01:08
• AutoFilter 00:07:58, 00:09:39
• AutoRecover 01:36:26
• Cell 00:13:52, 00:18:13, 00:26:14, 00:36:39, 00:42:52, 00:48:50, 01:29:04
• Cell Reference 00:54:40
• Column 00:10:04, 00:19:53, 00:55:35, 00:59:17, 01:04:54
• Column Headings 00:18:20
• Conditional Formatting 01:26:50, 01:33:19
• Dialog Box 00:09:29, 00:19:34, 00:37:22, 01:30:54
• Direct Reference 00:49:15
• Dynamic Array Function 00:48:39, 01:25:11
• Field 01:27:11, 00:34:02, 00:40:50
• Filter 00:07:48, 00:21:06, 00:27:30
• Formula 00:24:39, 00:48:16, 00:59:38, 01:13:56, 01:20:24
• INDEX Function 00:46:50, 00:55:03
• Keyboard Shortcuts 00:13:47, 00:20:19
• LOOKUP 00:46:43
• MATCH Function 00:46:51, 00:54:39
• Microsoft 365 00:05:11
• Pivot Table 00:33:39, 00:37:17
• Quick Access Toolbar 00:08:10, 00:14:36
• Ribbon 00:20:12
• Row 00:18:57, 00:25:09, 01:12:19
• Slicer Feature 00:16:39, 00:26:00, 00:32:03
• Spreadsheet 01:13:53, 01:35:27
• SUM 01:03:06
• SUMIF 00:47:57, 01:11:08, 01:14:21
• Table 00:16:10, 00:19:20, 00:26:10, 00:32:32, 00:36:56, 00:45:12
• Table Array 00:51:04
• Table Array 01:01:39
• Table Feature 00:16:22
• Total Row 00:20:16
• UNIQUE 01:19:19, 01:30:38
• VLOOKUP 0046:47, 00:49:16, 00:54:49
• Workbook 01:37:07
• Worksheet 00:48:01
• XLOOKUP 00:46:53, 00:49:22, 00:55:14, 00:58:57, 01:04:31

#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.

Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.

Artificial Intelligence (AI): Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.

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.

Autofilter: The basic Excel filter (also known as the Excel Autofilter) allows you to view specific rows in an Excel spreadsheet while hiding the other rows. When the Excel autofilter is added to the header row of a spreadsheet, a drop-down menu appears in each cell of the header row.

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

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.

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.

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.

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.

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.

Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.

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.

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.

Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.

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

Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.

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.

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.

UNIQUE: =UNIQUE - The Excel UNIQUE function returns a list of unique values in a list or range.

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.

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.

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.