Advanced Data Magic With Excel
Full Day Course
Ready to take your Excel skills to the next level? This seminar starts by clarifying some key Excel misunderstandings that affect typical Excel workbooks and shows you how advanced Excel functions will improve your calculating skills. Need to bring in data into Excel from an outside source? The seminar will cover this in depth. You will learn how to use Excel’s data analysis tools, including advanced lookups. Then the course dives into array formulas, a powerful tool that very few Excel users understand. Finally, you will be exposed to PowerPivot, a tool first introduced in Excel 2010 that enables you to work with large amounts of data from multiple data sources.
Learning Objectives
Upon completing this course, you should be able to:
- Understand how Excel calculates; creating custom formats; and date arithmetic.
- Use Excel functions such as AND, OR; CHOOSE, INDEX, MATCH and INDIRECT.
- Understand how to access external data including importing from ACCESS and other file types, using Microsoft Query
- Use data analysis tools such as Goal Seek, Data Tables, Scenario Manager and Solver.
- Understand the power of lookups, VLOOKUPS with IFERROR, VLOOKUPS with wild cards, combining VLOOKUPS and doing a VLOOKUP across multiple sheets.
- Use array formulas to simplify complex calculations, including single arrays, multi-cell arrays, comparative arrays, conditional formatting with arrays and more.
- Use the power of Excel PowerPivot, Power View, Data Models, DAX calculations and Power Map that is available in Excel 2013 Pro Plus and 2016 Pro Plus.
Course Information
- CPE credit: Recommended for 7 hours
- Instructional delivery method: This course will be taught in Excel for Office 365 with reference to the differences to Excel 2010 and subsequent. The program focuses on Excel for Windows and we encourage you to bring your windows laptop equipped with Excel 2010 or newer to follow along with the instructor.
- Prerequisites: Experienced Excel user
- Program level: Advanced
- Advance preparation: None
- Who should participate: Business professionals who work with Excel 2010 or newer and want to extend their knowledge with advanced features and functions. The focus of this session is on Excel for Windows; however, many of the Excel features discussed are also available in Excel for Mac.