1. Aurora Training Advantage
  2. Training
  3. Webinar

Using AI in and with Microsoft Excel

Live Webinar

Webinar Details $219

  • Webinar Date: July 23, 2024
  • Webinar Time: 12:00pm - 1:40pm EDT   live
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
  • Credit:   ATATX 1.5, ATAAA 1.5, ATAOP 1.5, CPE 2.0

All Access Membership

Although artificial intelligence seems like it is suddenly permeating every aspect of our daily lives, AI has been part of Excel for over a decade now. In this presentation author and Excel expert David Ringstrom, CPA will walk you through various AI-powered features in Excel, including Flash Fill, Analyze Data, Power Query's Column From Example, extracting data from pictures and PDF documents, and more. Of course, no discussion of AI would be complete without an exploration of ChatGPT, so David will show you how to write and deconstruct formulas as well as automate repetitive tasks with only a minimal understanding of Excel's Visual Basic for Applications programming language.

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:

  • Transforming data in Power Query by way of the Column From Example feature.
  • Creating PivotTables and PivotCharts based upon your data by asking natural language questions with the Analyze Data feature in Excel for Microsoft 365.
  • Linking charts to dynamic array results to create self-updating charts without using programming code.
  • Using the Recommended Charts feature to create suitable charts from data with a couple of mouse clicks.
  • Installing the free Excel Labs Add-In which enables API access to ChatGPT.
  • Extracting data from pictures by way of the From Picture command.
  • Separating first/last names into two columns without using formulas or retyping.
  • Jump-starting PivotTables by way of the Recommended PivotTables feature.
  • Understanding the nuance of editing dynamic array-based formulas in Excel 2021 and Microsoft 365.
  • Using ChatGPT to create formulas in Excel based upon a written description of the desired calculation.
  • Deconstructing existing formulas by asking ChatGPT to explain the formula in detail.
  • Keep or remove rows from the start or end of an array with the TAKE and DROP functions.

Learning objectives:

  • State the functionality that the Flash Fill feature offers.
  • Define the purpose of the Forecast feature in Excel 2016 and later.
  • Recognize the color that dynamic array functions are displayed in within Excel's formula bar in all cells except the cell where the formulas was entered.

Skill Level: 

Beginner/Intermediate

  1. Introduction
  2. Please Ask Questions Today 00:01:59
  3. Excel Versions 00:04:01:
  4. Separating Text with Flash Fill 00:04:50
  5. Combining Text With Flash Fill 00:09:26
  6. Column from Example in Power Query 00:15:39
  7. Excel Labs Add-In 00:21:10
  8. Recommended PivotTables 00:22:12
  9. Recommended Charts 00:25:45
  10. Analyze Data Feature (Microsoft 365) 00:28:08
  11. From Picture Command (Microsoft 365) 00:42:35
  12. From Web Feature - Steps 1-6 00:50:23
  13. From Web Feature - Step 7 00:57:13
  14. Data Types - Stocks (Microsoft 365) 01:00:39
  15. Data Types - Stocks (Microsoft 365) 01:04:10
  16. Forecast Feature Steps 1-6 01:05:26
  17. Forecast Feature Steps 7-8 01:07:46
  18. Writing Formulas with ChatGPT 01:07:49
  19. Deconstructing Formulas with ChatGPT 01:13:14
  20. Excel Labs Add-In 01:15:45
  21. CHAR and UNICHAR Functions 01:19:17
  22. TEXTJOIN Function (Excel 2019+) 01:22:43
  23. LABS.GENERATIVEAI Function (MSFT 365) 01:25:02
  24. SORT Function (Excel 2021+) 01:35:50
  25. FILTER Function (Excel 2021+) 01:37:50
  26. Editing Dynamic Array Functions 01:39:03
  27. VSTACK/HSTACK Functions (Microsoft 365) 01:40:33
  28. TAKE/DROP Functions (Microsoft 365) 01:41:39
  29. Dynamic Array Integration with Charts 01:41:57
  30. Thank You For Attending! 01:43:11
  31. Presentation Closing 01:43:18
  • Add-In 01:16:04
  • Analyze Data 00:28:16, 00:30:19, 01:33:19
  • API Key 00:21:10, 01:18:36, 01:25:56, 01:28:29
  • Artificial Intelligence (AI) 00:01:25, 00:11:20, 00:16:22, 00:25:33
  • AutoFill 00:09:11
  • Cell 00:11:40, 00:14:40, 00:22:44, 00:45:24, 01:20:12, 01:26:23, 01:36:08
  • CHAR Function 01:19:28
  • Charts 00:25:50, 00:26:59
  • ChatGPT 01:07:53, 01:11:22, 01:15:59, 01:25:07
  • Columns 00:05:34, 00:08:11, 00:14:33, 00:37:48, 01:37:02
  • Dialog Box 00:22:58, 00:26:03
  • DROP Function 01:41:44
  • Dynamic Array Function 01:35:55, 01:39:15
  • FILTER Function 01:37:51
  • Flash Fill 00:04:16, 00:04:57, 00:06:34, 00:07:05, 00:09:37, 00:14:20, 00:25:37, 00:39:16, 01:22:37
  • Forecast Feature 01:05:32
  • Format 00:13:25
  • Formula 00:13:47, 01:10:52, 01:13:21, 01:35:14
  • HSTACK Function 01:40:36
  • IF Function 01:13:02
  • Keyboard Short 00:08:47
  • LABS.GENERATIVEAI Function 01:25:58, 01:28:22
  • Microsoft 365 00:01:04, 00:23:57, 00:43:41
  • PivotChart 00:30:00, 00:33:32
  • Pivot Table 00:22:19, 00:30:00, 00:33:33
  • Power Query 00:15:48, 00:16:19, 00:20:59, 00:39:11, 00:54:16
  • Power Query Editor  00:16:51,  00:18:37 
  • Recommended Charts Feature  00:22:12, 00:28:45
  • Recommended PivotTables 00:25:45, 00:28:45
  • Row 00:06:16
  • Slicer 00:32:14
  • SORT 01:36:23, 01:39:31
  • SUMIFS Function 01:10:06, 01:11:47
  • TAKE Function 01:41:44
  • Task Pane 00:22:55. 00:24:02
  • TEXT Function 00:13:17
  • TEXTJOIN Function 01:19:57,  01:22:43, 01:25:39
  • Undo Command 00:08:16
  • UNICHAR Function 01:19:29
  • VSTACK Function 01:40:36
  • Worksheet 00:13:18, 00:27:42, 01:06:56, 01:24:44

API Key: An application programming interface (API) key is a code used to identify and authenticate an application or user. API keys are available through platforms, such as a white-labeled internal marketplace. They also act as a unique identifier and provide a secret token for authentication purposes.

Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.

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.

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

AutoFill: Use the Auto Fill feature to fill cells with data that follows a pattern or are based on data in other cells.

CHAR Function: The CHAR function in Excel is a command that users can type into the function box to generate specific characters using a number code. CHAR uses the American Standard Code For Information Interchange, or ASCII, which is a collection of numbers between one and 255 that specify characters for computers.

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.

ChatGPT: ChatGPT, which stands for Chat Generative Pre-trained Transformer, is a large language model-based chatbot developed by OpenAI and launched on November 30, 2022, notable for enabling users to refine and steer a conversation towards a desired length, format, style, level of detail, and language used.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

DROP Function: The DROP function in Excel removes the specified number of rows and/or columns from the start or end of an array.

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.

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.

Forecast Feature: A feature in Excel 2016 that enables you to extrapolate trends for a data set into the future.

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.

HSTACK Function: HSTACK returns the array formed by appending each of the array arguments in a column-wise fashion. The resulting array will be the following dimensions: Rows The maximum of the row count from each of the array arguments. Columns The combined count of all the columns from each of the array arguments.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

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.

LABS.GENERATIVEAI Function: A custom function that allows you to send prompts from the Excel grid to a generative AI model and then return the results from the model directly back to your 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.

Pivot Chart: Pivot charts are an adjunct to Excel’s pivot table feature, which allows you to summarize data by dragging and dropping data with your mouse. Pivot charts are much more interactive than the traditional Excel charts.

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.

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.

Recommended Charts Feature: A feature in Excel 2013 and later that enables beginners to get a jump start on creating charts, while also allowing experienced users to view data to be charted in a variety of formats.

Recommended PivotTables : Recommended PivotTables is an artificial intelligence feature in Excel 2013 and later that enables users to jump-start creating a pivot table. Click any cell within a list of data and then choose Recommended PivotTables from the Insert menu. Excel will typically suggest several report formats. Choose a report and then click OK to create the pivot table, which can then be modified in the usual fashion if needed.

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.

SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS 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

TAKE Function: Returns a specified number of contiguous rows or columns from the start or end of an array.

TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.

TEXTJOIN Function : The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter. The TEXTJOIN function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.

Task Pane: Task panes are interface surfaces that typically appear on the right side of the window within Word, PowerPoint, Excel, and Outlook. Task panes give users access to interface controls that run code to modify documents or emails, or display data from a data source.

UNICHAR Function: The UNICHAR function is categorized under Excel Text functions. UNICHAR gives the user a Unicode character of a specified number, where Unicode is a decimal number from 0 through 65535. The function was introduced in MS Excel 2013.

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.

VSTACK Function: VSTACK returns the array formed by appending each of the array arguments in a row-wise fashion. The resulting array will be the following dimensions: Rows: the combined count of all the rows from each of the array arguments. Columns: The maximum of the column count from each of the array arguments.

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

Guest Speaker

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “E [...]

    View Full Profile