The HR Diary

The HR Diary, the HR blog to get updates on the latest Global HR trends,
Global Employment/Tax laws/practices, HR books & Training recommendations.

Excel for HR

Excel for HR



EXCEL is one of the most important office software for Compensation (HR) professionals. Work efficiency can be greatly improved by mastering EXCEL skills, because it can be utilized for compensation & benefits analysis, salary/bonus computations, setting formulas for sales commission schemes, etc. Hopefully the EXCEL tips here will be of help to Compensation (HR) professionals.

Excel tips for Compensation professionals:
Function Purpose Excel formula
Age Returns the age of the employee. The formula "today()" will give you the current date everytime you open up your file. =year(today())-year(cell with the employee date of birth)
Average Returns the average of the arguments. =average(Data region)
Count if Counts the number of cells within a range that meet a given criteria. =countif(Data region, criteria)
If Returns one value if a condition you specify evaluates to true and another value if it evaluates to false. =IF(logical test, value if ture, value if false)
Max Returns the largest value in a set of values. =max(Data region)
Median Returns the median of the given numbers. =median(Data region)
Min Returns the smallest number in a set of values. =min(Data region)
Percentile Returns the X percentile of values in a range. E.g. you can decide to find out the salary of employees above the 75th percentile. =percentile(Data region,0.75)
Power Returns the result of a number raised to a power. E.g. If current salary is 1,000 and will be increased by 6% every year in the next 3 years. What is the salary after 3 years? =1,000* power(1.06,3)