Description

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.

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.

Areas Covered:-

• 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.

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.