December 11, 2020 @ 12:00 pm – 1:30 pm


Session Summary

The Region 1 chapters are excited to offer two tracks this year–intermediate and advanced. Jeff Lenning has provided Excel training to our chapter members for the past five years with rave reviews. Don’t miss the opportunity to get nine hours of Excel training for financial professionals for just $150 (HFMA member rate)! For those who attended our Excel webinar series these past four years, you know this is an incredible value! The training will be offered in six stand-alone webinars 90 minutes each over lunch time beginning in December and running through January. Each participant will receive a set of Excel templates, as well as a PDF summary handout for future reference. If you would like to work along, please be advised that Excel for Windows (2016 or later) is recommended. If you have a different version of excel, you are welcome to attend! But if you want to work along, note that not all features presented are available in all Excel versions, including Excel for Mac and earlier windows versions. The webinars are $25 each for members and $50 each for non-members. Please refer to the detailed described of each webinar below.

Please note that these webinars are not recommended for beginner users. The material offered is for intermediate to advance level users. Webinars will begin at noon and end at 1:30 pm EST and will not be recorded for future use. Separate registration for each webinar is required so that you can sign up for one or all.

What’s New In Excel 365 (Intermediate) – If you are on the Microsoft 365 subscription, you may have access to new Excel features. In this webinar, I’ll cover several new capabilities, including Dynamic arrays, XLOOKUP, LET, FILTER, UNIQUE, SORT, Data types (stock, geography) and STOCKHISTORY. At the time of this webinar, these items are not available in the perpetual licenses of Excel, including Excel 2019, 2016, 2013 and so on. Whether or not you have a 365 subscription, you are welcome to attend if you would like to learn more about these new capabilities!

Links and descriptions to future webinars are noted below:

1/6/2021 – Excel Series 2 – Which Lookup Function Should I Use
1/8/2021 – Excel Series 3 – Power Query Basics
1/15/2021 – Excel Series 4 – PivotTables 101
1/20/2021 – Excel Series 5 – Power Pivot Basics
1/22/2021 – Excel Series 6 – Better Together: Power Query and Power Pivot

Which Lookup Function Should I Use (Intermediate) – While the iconic VLOOKUP function is probably the most popular lookup function, there are times when other options may be a better fit. Depending on the type of workbook, INDEX/MATCH or SUMIFS may offer some benefits. And, depending on the version of Excel you are using, XLOOKUP may be a wonderful option. Join us to learn the pros and cons of the key lookup functions and when to use each.

Power Query Basics (Advanced) – Do you ever import data into Excel, and then spend a bunch of time manually preparing it for use? Perhaps you delete extra columns or rows. Maybe you need to split a single column into multiple columns, or, combine multiple columns into a single column. The good news is that many of these transformations can be fully automated with Excel! Power Query is an incredibly powerful tool that, at a high level, helps us retrieve data from a variety of sources and prepare it for use. In this session, we’ll use Power Query to help with several common tasks, including flattening data, splitting and combining columns, formatting data, unpivoting summarized data, and combining several csv exports. If you frequently export/import data from one system to another, you will want to understand how to use Power Query to streamline your work. Learning objectives: (1) recall the steps to create a new query; (2) identify commands that transform data; and (3) state the process to return the transformed data to Excel.

PivotTables 101 (Intermediate) – In this session, we’ll explore the fundamentals of creating PivotTable reports. We’ll examine the report layout areas and how to update source data. We’ll also examine report layout options so that you can obtain just the right formatting and structure for your report. We’ll talk about how to group your report into monthly columns and how to filter PivotTables as well. By the end of the session, you’ll understand how to create basic PivotTable reports.

Power Pivot Basics (Advanced) – Do you love PivotTables? Of course, we all do! Traditional PivotTables are incredible … but, they do have limitations. If you have ever encountered one of these limitations, you’ll be excited to learn that by using Power Pivot, you’ll be able to bypass many of the restrictions that exist with traditional PivotTables. Power Pivot provides a new data source for PivotTables, and essentially combines the flexibility of formula-based reports with the PivotTable engine. Microsoft provides Power Pivot free for most editions of Excel for Windows 2010 and forward (currently, it is unavailable in Excel for Mac). In this session, we’ll explore Power Pivot and the data model, build PivotTables from multiple tables, create calculated columns, and write measures. If you frequently build PivotTables and would like to do more with them, you’ll want to understand how Power Pivot can enhance your reports and workflow. Learning objectives: (1) recall the steps to access the Power Pivot window; (2) identify the process to create table relationships; and (3) state the procedure for building named sets.

Better Together (Power Query and Power Pivot) (Advanced) – Excel users have prepared reports the same way for decades … formulas and PivotTables. Typically, we export data from an accounting system, copy and paste it into an Excel workbook, clean and prepare it for use, and then use PivotTables and/or functions such as VLOOKUP and SUMIFS. But, that workflow is manual and slow. In this session, we’ll use Power Query and Power Pivot to improve our workflow. Specifically, we’ll use Power Query to get and shape external data. We’ll send the query results into the data model. We’ll use Power Pivot to organize tables and create measures and then use them as the source for PivotTable reports. If you frequently build formula-based or PivotTable reports, join this session to learn how Power Query and Power Pivot can help. Learning objectives: (1) identify which tool in Excel helps to import and transform data; (2) state the process for relating tables in Power Pivot; and (3) recall a benefit of using Power Query and Power Pivot when building PivotTables.

Your attendance may qualify for 1.8 hours of Continuing Professional Education (CPE) credits. We have computed the education credits based on an equivalent of 50 minutes for each education credit hour. You are responsible for maintaining adequate records and determining if the content of this program meets the professional licensing requirements of your state.