On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Administration, Business Skills, Taxation and Accounting
- Credit: ATATX 2.0, CPE 2.0
-
It's here! An Excel webinar dedicated to Excel for Mac users. This enlightening webcast presented by David Ringstrom, CPA, will empower you to quickly accomplish tasks that might currently be taking you several minutes or even hours to complete. David explains a variety of Excel’s timesaving features and shares helpful tips, such as how to filter data faster, streamline repetitive tasks, identify and remove duplicates, and transform unwieldy reports with Power Query.
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 who will benefit from using Excel for Mac more effectively and efficiently.
Topics covered:
• Creating a self-updating list of worksheets in any workbook with Power Query.
• Creating instant spreadsheets such as amortization tables, timecards, expense reports, and more from templates.
• Discovering how to use the Flash Fill feature to separate first/last names into two columns without formulas or retyping.
• Eliminating duplicates from a list with just a few mouse clicks.
• Eliminating the need to manually resize charts when data is added—automate this with tables instead.
• Exploring the nuance of the 1904 date system and how it can cause dates to shift by 4 years.
• Exploring the risks and benefits of participating in the free Microsoft Office Insider program.
• Filtering a cleaned-up accounts receivable aging report to display only overdue amounts.
• Gaining control of long lists of data by filtering instead of sorting.
• Identifying duplicates in a list using Conditional Formatting.
• Inserting totals into lists with a few mouse clicks by way of Excel’s Subtotal feature.
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
Learning objectives:
• Recognize how to avoid retyping text by using Excel features and worksheet functions.
• Recall how to easily compile large lists of data by filtering vs. sorting.
• Identify how to create self-updating charts by way of Excel's Table feature.
Level: Basic
Format: Group Internet Based
Instructional Method: Live Webcast
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: None
Advance Preparation: None
- 1904 Date System 01:36:39
- Column 00:52:52, 01:28:45
- Flash Fill 00:05:20
- Function Builder 00:57:25
- Keyboard Shortcut 01:11:25
- Microsoft 365 00:03:35, 01:03:45
- Power Query 01:16:49
- Remove Duplicates 00:26:12
- Row 00:42:39
- Slicers 00:22:03
- Subtotal Feature 00:39:01
- Table 00:17:29
- Table Feature 00:17:29
- UNIQUE 00:29:38
- VLOOKUP 00:58:40
- Workbook 01:04:09
- XLOOKUP 01:01:01
1904 Date System: When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, 1904. For example, if you enter July 5, 2011, Excel converts the date to the serial number 39267. This is the default date system in earlier versions of Excel for Mac.
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.
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.
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.
Function Builder: To open the formula builder, first click into any empty cell where you'd like to place a formula. Then, click the formula button (fx) immediately left of the formula bar. Excel will open the Formula Builder sidebar on the right side of your screen.
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.
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.
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.
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.
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.
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.
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.
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.
