This course will provide course participants with advanced knowledge of Microsoft Excel. Participants will work with advanced text editing and cleaning functions. Exercises will include functions related to: VLOOKUP, HLOOKUP, CHOOSE, MATCH, INDEX, OFFSET. The course will cover advanced PivotTables and PivotCharts. Participants will test out scenarios through the use of goal-seek and data tables. Several Excel add-ins will be introduced: Data Analysis for regression analysis, and Solver for optimization. Participants will learn to work with, and build their own, array functions. The course will also consist of a brief introduction to VBA, covering such topics as recording macros and using macros to create keyboard shortcuts. VBA is particularly useful to automate situations where a series of actions must be repeated for different files or at different times. This course is hands-on and participants are expected to have computers and follow along with the instruction. Many exercises will be provided to help reinforce concepts.
Course Objectives
By the end of the course, the participants will be able to:
- Use advanced functions to analyze data and test out different scenarios
- Goal-Seek, Data Tables for sensitivity analysis
- Add customized controls to your spreadsheets
- VLOOKUP, HLOOKUP, CHOOSE, INDEX, MATCH, OFFSET
- Advanced text editing and cleaning
- Advanced use of PivotTables and PivotCharts
- Data Analysis add-in for regression analysis and forecasting
- Solver add-in for optimization
- Array functions
- Brief introduction to VBA to record and edit macros
Suggested Prerequisites:Understanding of Microsoft Excel at an Intermediate Level
Program Level: Advanced
Advance Preparation: None
Computers and Financial Calculators: Laptops are required during the training
Recommended CPE Credits: 7