Excel for Administrative Assistants

1. Introduction
2. Topics At A Glance 00:01:56
3. Questions Are Welcome Any Time 00:03:10
4. Presenting with Microsoft 365 for Windows 00:05:18
5. Section 1: Cleaning And Preparing Data 00:07:16
6. Transforming and Separating Text with Flash Fill 00:08:57
7. Combining Text With Flash Fill 00:20:32
8. Removing Duplicates in Excel 00:21:33
9. Highlighting Duplicates with Conditional Formatting 00:27:58
10. Removing Conditional Formatting 00:36:41
11. Section 2: Organizing And Filtering Lists 00:38:09
12. Filter Feature 00:40:19
13. Using Totals and Filters in Tables 00:48:00
14. Filtering Excel Tables with Slicers 00:53:53
15. Managing or Removing Table Formatting 01:05:39
16. Section 3: Summarizing And Analyzing Data With Pivot Tables 01:08:24
17. Creating a PivotTable from an Excel Table 01:09:27
18. Exploring PivotTable Interfaces 01:12:52
19. Building a Basic PivotTable 01:13:34
20. Adding Columns to PivotTables 01:15:52
21. Drilling Down into PivotTables 01:18:36
22. Using Excel’s Analyze Data Feature (Microsoft 365) 01:23:47
23. Section 4: Formulas And Visuals 01:23:38
24. Using the SUMIF Function in Excel 01:30:47
25. Creating Self-Expanding Charts 01:36:36
26. Inserting Recommended Charts 01:38:17
27. Section 5: Everyday Productivity Boosts 01:39:37
28. Copying/Pasting Data from the Internet 01:39:38
29. Using the Navigation Pane (Microsoft 365) 01:40:22
30. What We Covered 01:42:14
31. Speaker Closing 01:42:59
32. Presentation Closing 01:43:22

• 

  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

• Analyze 01:08:44, 01:23:53
• Cell 00:02:16, 00:09:54, 00:28:30, 00:32:46, 00:37:30, 00:40:58, 00:48:07, 00:55:04, 01:38:24
• Chart 01:30:40, 01:36:47
• Column 00:09:11, 00:11:11, 00:22:14, 00:28:14, 00:41:19, 00:54:44, 00:59:56, 01:09:07, 01:24:02, 01:34:07
• Column Headings 00:48:49
• Conditional Formatting 00:08:42, 00:28:18, 00:30:44, 00:36:51
• COUNT 01:02:54
• Dialog Box 00:13:17, 00:26:11, 00:48:16, 00:52:56, 01:06:59, 01:10:36
• Drill Down 01:18:49, 01:19:09
• Field 00:59:11, 01:08:42, 01:12:57, 01:16:12
• Filter 00:02:25, 00:32:53, 00:38:21, 00:40:05, 00:47:06, 00:51:16, 01:18:46
• Flash Fill 00:07:27, 00:08:59, 00:14:59, 01:42:20
• Format 00:02:19, 0:08:48, 00:13:36, 00:23:11, 01:05:53
• Formula 00:02:58, 00:39:14, 01:09:36, 01:34:24
• Formula Bar 01:02:17
• Microsoft 365 00:05:25, 01:23:57
• Pivot Table 00:02:28, 00:49:29, 01:07:20, 01:08:32, 01:10:16, 01:13:35, 01:19:43
• Quick Access Toolbar 01:27:01
• Remove Duplicates 00:08:28, 00:21:47, 00:23:47
• Ribbon 00:28:36, 01:13:12
• Row 00:11:12, 00:22:27, 00:28:15, 00:41:37, 00:49:01, 01:09:57
• Slicers 00:39:24, 00:53:57, 00:55:18, 01:42:29
• Spreadsheet 00:28:02
• SUBTOTAL 01:02:20
• SUM 01:02:29
• SUMIF 01:29:47, 01:30:54
• Tab Key 00:16:32
• Table 00:39:09, 00:48:01, 00:52:52, 01:06:40, 01:10:06, 01:30:27
• Table Feature 00:38:44
• Total Row 00:39:10, 01:00:09, 01:09:46
• Undo Command 00:17:29, 01:06:50
• Worksheet 00:51:11, 00:54:33, 01:32:46
• Wrap Text 00:49:02

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

COUNT: Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

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.

Chart: In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that can bring more understanding to the data than just looking at the numbers. 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.

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.

Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.

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.

Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.

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.

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

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.

Remove Duplicates: This feature first appeared in Excel 2007. This Data tab command allows you to reduce a list of items to a list of unique constituents. This action required the Advanced Filter command in Excel 2003 and earlier.

Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.

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.

SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.

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.

Tab Key: The tab key Tab ↹ on a keyboard is used to advance the cursor to the next tab stop

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.

Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Wrap Text: Wrap Text is a feature that wraps the text within a cell. Wrap Text can be turned off by highlighting the cell and clicking the Wrap Text button again.