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.
- Understand how Excel calculates; creating custom formats; and date arithmetic.
- Learn how to use Excel functions such as AND, OR; CHOOSE, INDEX, MATCH and INDIRECT.
- Learn the key skills you need to access external data such as importing from ACCESS, importing from other file types, using Microsoft Query
- Discover 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.
- Discover array formulas to simplify complex calculations, including single arrays, multi-cell arrays, comparative arrays, conditional formatting with arrays and more.
- Discover the power of Excel PowerPivot, Power View, Data Models, DAX calculations and Power Map that is available in Excel 2013 and 2016.
- CPD credit: Recommended for one day of credit
- Instructional delivery method: Group-live demo and discussion – we encourage you to bring your laptop equipped with Excel 2007 or newer. The program focuses on Excel for Windows and is not intended for Excel for macOS
- Prerequisites: Intermediate level of Excel knowledge
- Program level: Advanced
- Advance preparation: None
- Who should attend: Experienced Excel users who want their knowledge of Excel to move to the next level
Note: This course will be taught in Excel 2016. Reference will be made to the differences between Excel 2016 and Excel 2013, 2010 and 2007.