EXCEL WEBINAR SERIES (1/6/21)
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.
Future Webinar Description, Dates and Links
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. Links to the registration are noted below:
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
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.