On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Skills, Software
- Credit: ATAOP 1.5, CPE 2.0
-
In this webinar, Excel expert David H. Ringstrom will guide you on extracting PDFs with Power Query, exploring Power Query properties, enhancing external data security, creating Excel tables, adding total rows, filtering tables with slicers, utilizing functions such as IF, nested IF, VLOOKUP, IFNA, and MATCH, as well as INDEX/MATCH functions, XLOOKUP for horizontal lookup, automating tasks with macros, creating macro icons, applying conditional formatting for greater than scenarios, utilizing data bars, managing scenarios effectively, and summarizing Scenario Manager features.
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.
Who should attend:
This webinar is ideally suited for operations managers, process engineers, and anyone involved in the optimization of operational workflows who seeks to enhance their proficiency in Excel for improved data analysis and decision-making.
Your Benefits For Attending:
- Enlivening staid lists of numbers with the Data Bars conditional formatting.
- Demonstrating how XLOOKUP can look up and down columns or across rows.
- Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
- Managing the external data security warning that may appear when you link external data into Excel spreadsheets.
- Viewing the pros, cons, and limitations of nesting IF functions.Incorporating decisions into calculations with Excel's IF function.
- Using the MATCH function to find the position of an item in a list.
- Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
- Using a custom number format to hide the underlying cell values when applying the Data Bars conditional formatting to one or more cells.
- Recording a macro to automate using Center Across Selection.
- Configuring Power Query queries to update automatically in the most efficient manner possible.
- Streamlining the filtering of lists using the Slicer feature with tables.
Learning objectives:
- Identify how to improve the integrity of SUM-based formulas in Excel.
- State which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.
- State what SUMIF returns if a match cannot be found.
Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Specialized Knowledge and Applications (2 Hours) (2 hours)
Program Prerequisites: Prior experience with Microsoft Excel is recommended.
Advance Preparation: None
- Introduction
- Please Ask Questions Today 00:01:55
- Excel Versions 00:04:00
- Extracting PDFs with Power Query - Steps 1 -8 00:04:45
- Extracting PDFs with Power Query - Steps 9 -16 00:11:39
- Extracting PDFs with Power Query - Steps 18 - 25 00:15:38
- Slicers with Tables 00:23:07
- Power Query Properties 00:34:13
- External Data Security Warning 00:38:24
- VLOOKUP Function 00:40:24
- MATCH Function 00:55:07
- INDEX/MATCH Functions 00:59:11
- XLOOKUP Function (Excel 2021+) 01:04:18
- Merge Cells 01:07:27
- Center Across Selection 01:10:01
- Automating Center Across Selection 01:13:31
- Manually Running Our New Macro 01:20:20
- Creating an Icon for Our Macro 01:21:14
- Conditional Formatting - Greater Than 01:26:09
- Eliminate Conditional Formatting 01:29:44
- Data Bars 01:35:26
- Hide Numbers Behind Data Bars 01:37:09
- IF Function 01:37:55
- Scenario Manager Feature - Steps 1 - 3 01:40:42
- Scenario Manager Feature - Steps 9 - 13 01:41:40
- Scenario Manager Feature - Steps 14 - 16 01:41:53
- Scenario Manager Summary 01:42:11
- Thank you for attending! 01:43:48
- Presentation Closing 01:44:02
- Cell 01:08:04, 01:25:34
- ChatGPT 00:46:36
- Column 00:12:14, 00:14:54, 00:16:14, 00:27:40, 00:31:24, 00:48:37, 01:01:16, 01:07:09
- Conditional Formatting 01:26:07
- Data Bars 01:35:26
- Dialog Box 00:07:38, 00:15:49, 00:34:47, 01:15:04, 01:42:00
- Filter 00:11:59, 00:23:13, 01:29:40
- Formula 00:17:39
- IF Function 01:38:06
- INDEX Function 00:55:19, 01:01:12
- Macro 01:14:49, 01:20:26
- Macro Recorder 01:13:51
- MATCH Function 00:55:09, 01:01:08
- Merge Cell 01:07:44
- Microsoft 365 00:04:10
- PDF 00:05:12, 00:07:43, 00:09:44, 00:11:48, 00:14:18, 00:16:11
- Personal Macro Workbook 01:15:11
- Power Query 00:04:51, 00:05:34, 00:06:30, 00:16:22, 00:21:19, 00:34:22
- Power Query Editor 00:08:28, 00:14:42
- Query 00:34:37
- Ribbon 00:09:00, 00:16:21
- Row 00:20:38, 01:03:18
- Scenario Manager 001:40:46
- Slicer 00:23:09, 00:28:57, 01:31:07
- Spreadsheet 00:06:06
- Table 00:08:00, 00:25:19, 00:48:24
- Table Array 00:48:13
- Task Pane 00:18:17
- Text Files 00:06:01, 00:06:37, 00:09:12
- Total Row 00:20:55, 00:27:34
- VLOOKUP 00:40:17, 00:53:53, 00:59:59, 01:05:09
- Workbook 00:06:00, 00:09:32, 00:11:45, 00:38:27, 01:15:27
- XLOOKUP 01:04:14, 01:07:03
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.
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.
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.
Data Bars: Data bars are a type of conditional formatting in Excel that use colored bars to visually represent the value of cells in a range. The length of the bar indicates the value of the cell, with longer bars representing higher values and shorter bars representing lower values.
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.
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.
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.
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.
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.
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.
Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.
Merge Cell: Merge cell is a function in database software that allows multiple adjacent cells to be combined into a single larger cell. This is done by selecting all cells to be merged and choosing the "Merge Cells" command. A close-up of a spreadsheet on a computer screen.
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.
PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.
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.
Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
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.
Scenario Manager: The Scenario Manager feature allows you to create scenarios that store up to 32 inputs. You can then swap out sets of inputs on a worksheet by applying a scenario or creating reports that compare the output of scenarios. If you have more than 32 inputs that you wish to save, you can create and then apply two or more scenarios sequentially.
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.
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.
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 Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.
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.
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.
