Create Powerful Excel Dashboards to Streamline Compensation Work in This Excel Training Course
Are you a compensation professional who routinely uses Excel to analyze large amounts of data and dream of being able to work more efficiently? If so, this Excel training class designed for HR professionals is for you. In it you will learn advanced Excel techniques developed by Dianne Auld, an in-demand total rewards expert and faculty member at WorldatWork.
Over the two-day course you will learn:
- How to create powerful interactive Excel dashboards that consolidate large amounts of data into executive summary views to facilitate business decision making.
- Advanced Excel functions, including complex LOOKUP and IF formulas, data validation and protection, array formulas and macros to do pay and benefits calculations and pay and commissions calculations.
- How to build Excel dashboards using real-world scenarios.
Participants must be proficient with writing VLOOKUP, IF and other formulas and be able to create charts. Exercises are included throughout each section of the course for hands-on practice. Specific topics covered in this Excel course include:
- Lookup Formulas Part 1 – Learn to use the following formulas for pay and benefits calculations:
- VLOOKUP
- HLOOKUP
- IFERROR with VLOOKUP
- Using named ranges with VLOOKUP
- MATCH
- Looking up a merit matrix using VLOOKUP and MATCH
- Lookup Formulas Part 2 – Learn to use the following formulas to do pay and commission calculations:
- INDIRECT with VLOOKUP
- INDEX
- OFFSET
- CHOOSE
- Inserting form controls – scroll bar and combo box
- If Formulas – Learn to use the following formulas to do pay and benefits calculations:
- IF
- IF(AND); IF(OR)
- Nested IF
- COUNTIF, SUMIF, AVERAGEIF
- COUNTIFS, SUMIFS, AVERAGEIFS
- Array Formulas
- Understand the purpose and rules of array formulas
- Write multi-cell array formulas and single-cell array formulas
- Use array formulas for measures of location
- Use an array formula with INDIRECT
- Data Validation and Protection
- Set up a form or document with data validation and protection
- Validate using a list and other criteria
- Hide sheets
- Unprotect cells and hiding formulas
- Protect the sheet and the workbook
- Macros
- Record macros
- Run macros
- Macro security settings
- Introduction to Dashboards
- Create a dashboard framework
- Create a market comparison dashboard
Upon completing this course, you will have a new set of tools to create powerful Excel dashboards that analyze large amounts of HR data and facilitate business decision making.
