Excel for Human Resource and Payroll Personnel
Excel for Human Resource and Payroll Personnel
This course is for anyone who works in the Human Resources and Payroll departments. The focus of the course is all the typical issues faced in these departments. The areas covered would also benefit anyone working with people data e.g. Learning and Development/ Training departments.
- Time Req – 20 hrs
- Skill Score >4
- FAQ
- All Online Courses
- Live Course Options
- Ask a Question
Request info on Online Courses (T&C’s, discounts etc)
Modules
Ultimate Excel Tool
Lessons
Tips and Tricks
Lessons
Filtering and Sorting
Lessons
- Data sorting-Revision, colour and icon sorting
- Easily changing column sort order e.g. firstname column then surname column Free Preview
- Custom sort orders e.g. specific department order
- Sorting Examples- sales staff by city, cell colour, department
- Data Filtering
- Delete rows from a messy payroll report
- Remove duplicate rows
- Use Advanced Filter for repeating filters
- Filter Examples- vendor report cleanup, delete duplicate sales people
The Specials- GOTO and Paste
Lessons
- GOTO Special- find cells with comments, values, and more
- Find errors, conditional formatting and data validation
- Copy only the visible staff members details
- Fill in the blanks with the cell above (without having to copy paste)
- GoTo Examples- fill in department and employee names
- Paste Special- Values, Formulas with/without formatting
- Paste Special- change negative numbers to positives Free Preview
- Switch rows and columns
- Skip blanks when pasting to avoid overwriting information
- Re Use the Format Painter
Tools for text
Lessons
Pivot Tables
Lessons
- Required data format for Pivot tables
- Create a Pivot Table- Step by Step
- Inserting Values into a Pivot Table
- Enhancing the values e.g. percentage of another column
- Show the details of a number in the Pivot Table
- Pivot Examples: SUM, COUNT, AVERAGE, Percentage of
- Laying out the Pivot Report in completely separate columns
- Working with the rows in a Pivot Table- dropdown
- Working with the rows in a Pivot Table- field settings
- Normalize Pivot Reports to use elsewhere as a copy paste
- Pivot Examples: rows in a pivot table
- Working with the Columns in a Pivot Table
- Pivot Examples: values, rows and columns
- Working with the Filter in a Pivot Table
- Automatically duplicate your Pivot Tables with different selections
- Slicers in a Pivot Table
- Control all Pivot Tables and Charts at once
- Timelines in a Pivot Table
- Grouping Dates into days, months, quarters and years in a Pivot Table
- Grouping Dates into weeks in a Pivot Table
- Grouping Text in a Pivot Table
- Grouping Numbers in a Pivot Table
- Problems with grouping in Pivot Tables
- Pivot Charts in Excel
- Pivot Examples: All features learnt so far
- Pivot Examples: Business Intelligence
- EXERCISE: PIVOT TABLES IN HR (LEAVE REGISTER)
Conditional Formatting
Lessons
- Change cell colour based on value
- Change color based on benchmark e.g. performance reviews
- Highlight above/below average cells- staff performance comparisons
- Create a comparative bar chart in cells e.g. leave days taken
- Heat maps e.g. are there too many people on leave at the same time
- Add risk robots, milestone status, and more to HR dashboard
- Control the heat maps, icons and data bars in conditional formatting
- Conditional Format Examples
Function and Formula Basics
Lessons
- Tip- Opening Frequent files
- Function Wizard- the Excel cheat tool
- Track where a formula comes from
- Formula Auditing Examples
- Referring to a fixed cell e.g. commision rate
- Using the F4 shortcut for $ signs
- $A$1 Examples- comm payouts and more
- The basic “IF” function
- IF Examples- staff ratings, account printouts
Text Functions
Lessons
- Fitting a sentence inside a fixed width cell
- Typewriter style data entry
- Quick Drop Down List for spelling of names
- Printing Tips- gridlines, cell comments and more
- Get Excel to read out payroll amounts for crosscheck (and more)
- Combine first name and surname columns
- Split names into separate first name and surname columns
- Make ‘numbers’ usable
- Change text to lower, UPPER or Proper Case
- Remove double and other unnecessary spaces
- Force excel to treat number as text
- Split text based on character
- Replace text using formula
- Text Examples- change case, ID number check, generate full names
Key Lookup Functions
Lessons
- VLOOKUP explained in simple terms
- Build your first VLOOKUP
- The many uses of VLOOKUP in the HR environment Free Preview
- VLOOKUP Examples- join address list, reconcile lists, create monthly reports
- Stop #NA errors affecting all your formulas
- Vlookup approximate match
- VLOOKUP Examples- categorise KPI, age bands, tax calcs
- Vlookup partial match i.e. find Smith in John Smith
- VLOOKUP Examples- match names to email address
- Sum, count or average matching cells
- SUMIFS et al Examples- sales by area, recon lists, duplicate bank accounts
- Sum, count or average cells e.g. bigger than
- SUMIFS et al Examples- count profitable months, stop duplicate entry
- Sum, count or average with many conditions
- SUMIFS et al Examples- multiple conditions, duplicate bank account numbers
- Sum, count or average with partial matches
- SUMIFS et al Examples- count designations, match name to email address
Date functions and issues
Lessons
- How Excel Handles Dates and Time
- Safest way to capture a date in Excel
- Pull the day, month or year out of the date
- Create a VALID Excel date with a day, month and year number
- Calculate the proper end of month
- What day of the week was a date on
- When is the next workday for payroll payments
- How many working days of leave taken
- What week number is a date
- How many months/years service
- Make Excel recognise a date
- Date Examples- leave taken, date correction
Protecting spreadsheet information and formulas
Lessons
- How to protect an Excel Spreadsheet from opening
- Allow Spreadsheet to be viewed but not changed (read only)
- Protect only certain cells/ formula from everything except input changes
- Lock cells but allow colour formatting or comments
- Lock Cells but allow column/ row size change
- Lock Cells but allow insert or delete of columns/ rows
- Protect cells but allow filter/ pivot table/ sort
- Hide formulas in Excel
- Hide and protect a sheet in a spreadsheet
- Allow only certain values into a cell
- Protect the source data of a pivot table
- Protect cells with different passwords to allow levels of authority
- Trick: Allow user to only see their information in Excel
- Trick: Protect rows and columns without protecting sheet
- Some Useful Tools when protecting spreadsheets
- Protection Examples
