On Demand Webinar
Webinar Details $219
- 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
-
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
- Introduction
- Excel Versions 00:02:09
- Please Ask Questions Today! 00:06:00
- Separating Text with Flash Fill 00:06:42
- Combining Text With Flash Fill 00:22:09
- Creating Excel Tables 00:23:00
- Recommended PivotTables 00:30:45
- Recommended Charts 00:48:54
- Analyze Data Feature (Microsoft 365) 00:52:43
- Navigation Task Pane (Microsoft 365) 00:55:55
- From Picture Command (Microsoft 365) 01:00:03
- From Web Feature - Steps 1-6 01:13:17
- Data Types - Stocks (Microsoft 365) 01:16:41
- SORT Function (Excel 2021+) 01:19:06
- FILTER Function (Excel 2021+) 01:21:18
- Editing Dynamic Array Functions 01:25:08
- Microsoft Copilot (Free License) 01:25:32
- Microsoft Copilot Pro - Steps 1 - 9 01:28:10
- Microsoft Copilot Pro - Steps 9 - 11 01:32:03
- Microsoft Copilot Pro - Step 12 - 16 01:33:18
- Deconstructing Formulas with ChatGPT 01:33:20
- Writing Formulas with ChatGPT 01:37:52
- Writing Excel Macros with ChatGPT 01:38:55
- Excel Labs Add-In - Steps 1 - 7 01:39:36
- Excel Labs Add-In - Steps 8 -10 01:40:57
- LABS.GENERATIVEAI Function (MSFT 365) 01:41:18
- Thank You For Attending! 01:43:39
- Presentation Closing 01:43:40
- Analyze Data 00:52:44, 00:53:06, 01:07:57
- API Key 01:41:10
- Artificial Intelligence (AI) 00:00:08, 00:01:23, 00:29:17, 00:55:58, 00:57:08, 01:33:16
- AutoFill 00:14:29, 00:37:35
- Cell 00:09:04, 00:31:34, 00:43:34, 00:53:00, 01:20:08, 01:25:21
- Cell Reference 01:24:32
- Chart 00: 49:18, 00:51:18, 00:58:55
- ChatGPT 01:33:24
- Column 00:07:18, 00:08:19, 00:19:10, 00:24:59, 00:58:46, 01:24:06, 01:24:41
- Dialog Box 00:31:52, 00:57:56
- Dynamic Array Function 01:19:08, 01:25:09
- Fill Handle 00:14:38, 00:37:29
- Filter 00:26:10, 00:55:08
- FILTER Function 01:21:18
- Flash Fill 00:06:46, 00:08:29, 00:11:27, 00:16:26, 00:21:23, 00:39:04, 00:44:16
- Format 00:07:30
- Formula 00:22;53, 00:26:44, 01:19:17, 01:24:28, 01:33:32
- Formula Bar 01:34:02
- IF Function 01: 01:33:46
- LABS.GENERATIVEAI Function 01:41:03
- Macro 01:38:59
- Microsoft 365 00:02:54, 00:31:59, 00:52:53, 01:01:19, 01:08:47, 01:18:56
- Microsoft Copilot 01:25:38
- Navigation Task Pane 00:56:03, 00:58:09
- Pivot Table 00:30:53, 00:31:25, 00:32:17, 00:35:35
- Power Query 01:16:10
- Power Query Editor 01:16:27
- Recommended Charts Feature 00:48:57, 00:53:12
- Recommended PivotTables 00:31:10, 00:31:49, 00:32:28, 00:53:13
- Row 00:08:04, 00:10:36, 00:24:15, 01:32:10
- SORT 01:19:30
- Table 00:25:08, 00:48:47, 01:24:44
- Table Feature 00:23:25, 00:29:19, 00:31:04
- Task Pane 00:31:55, 00:33:39, 00:53:09
- Text to Columns Wizard 00:40:49
- Total Row 00:28:45, 00:34:53
- Undo Command 00:08:55
- TEXTJOIN Function 01:41:26
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.
Accounting Receivable (AR): Accounts receivable, abbreviated as AR or A/R, are legally enforceable claims for payment held by a business for goods supplied or services rendered that customers have ordered but not paid for.
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.
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
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.
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.
Fill Handle: The little notch in the bottom right-hand corner of the selected cell or block of cells. You can drag the fill handle to copy the contents to other cells, double-click to copy contents down a column, or right-drag to reveal a hidden context menu.
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.
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.
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.
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.
Navigation Task Pane: The Navigation pane in Excel is an easy way to understand a workbook's layout, see what elements exist within the workbook, and navigate directly to those elements. Find and access elements such as tables, charts, PivotTables, and images within your workbook. Once you've opened the Navigation pane, it displays on the right side of the Excel window.The Navigation pane also makes it easier for those with visual impairments to access all parts of the workbook. It can improve how tools such as screen readers interpret your workbook.
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.
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.
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.
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.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
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.
