"18 Must-Know" Advanced Excel Functions for HR Professionals - A Complete Tutorial

In this article, I will be discussing some very important and useful “Advanced Excel” Functions for HR professionals. 

I will demonstrate the working of each of these functions with examples.  

1. COUNTA

“COUNTA” will return the number of rows/columns (Cells), i.e. for a particular cell range. COUNTA does not count a CELL if it is EMPTY. 

Formula: 

=COUNTA(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:

2. COUNT

“COUNT” will return the number of rows/columns (Cells), i.e. for a particular cell range. COUNT only calculates NUMBERS. 

Formula: 

=COUNT(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:

3. COUNTIF

“COUNTIF” will return the number of rows/columns (Cells), i.e. whose values meets/matches a particular condition. 

Formula: 

=COUNTIF(Starting_Cell_Address:Ending_Cell_Address,”CONDITION")

Example for Demonstration:

4. RANK

“RANK” will return the rank of a numeric value when compared to a list of other numeric values. RANK can rank the values from “Smallest to Largest” or “Largest to Smallest”, i.e. depending on the value of the “ORDER” argument.

Formula:

=RANK(Number,Array,Order)

[Note: ORDER can be either “0” or “1”. Order value “0” will rank the values from “Largest to Smallest” and order value “1” will rank the numbers from “Smallest to Largest”]

Example for Demonstration:

5. MAX

“MAX” will return the maximum value for a cell range. 

Formula:

=MAX(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:

6. MIN

“MIN” will return the minimum value for a cell range.

Formula:

=MIN(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:

7. NESTED IF-ELSE

The "IF" Function is a Logical Function in Excel.

Formula:

=IF(Condition,Value_If_Condition_True,Else_Value)

Now, when multiple "IF" Functions are nested in a Single Excel Formula, it is known as NESTED-IF/NESTED IF-ELSE

Formula (Upto 3 Conditions as a Example):

=IF(Condition1,Value_If_Condition1_True,IF(Condition2,Value_If_Condition2_True,IF(Condition3,Value_If_Condition3_True,Else_Value)))

Example-1 for Demonstration:

Example-2 for Demonstration:

8. "IF" combined with "AND", "OR", "NOT"

"AND", "OR" and "NOT" Logical Functions can be combined with "IF" in an Excel Formula.

Formula (IF-AND):

=IF(AND(Some_Condition_Is_True,Some_Other_Condition_Is_True)Value_If_True,Value_If_False)

Formula (IF-OR):

=IF(OR(Some_Condition_Is_True,Some_Other_Condition_Is_True)Value_If_True,Value_If_False)

Formula (IF-NOT):

=IF(NOT(Some_Condition_Is_True),Value_If_True,Value_If_False)

Examples for Demonstration:

9. SUM with OFFSET Function

SUM is used with OFFSET when the limit/range for Summation keeps on changing.

Suppose, you want to add the Gross Monthly Salary of an employee for a particular time period. [Here, the Time Period is a number which might get changed frequently]

Example, sometimes you want to add the Gross Monthly Salary for 3 Months. While, other times you might need to add them for 6 months.

Formula:

=SUM(Starting_Cell_Address:OFFSET(Starting_Cell_Address,Rows,Columns))

Note: 1st Row and 1st Column Value is taken as value "0"

Examples for Demonstration:

10. SUMIFS

SUMIFS returns the sum of the values of cells that meets multiple criteria.

Formula:

=SUMIFS(Range_To_Sum,Range1,Criteria1,Range2,Criteria2)

Examples for Demonstration:

11. INDEX-MATCH

Apart from VLOOKUP, INDEX and MATCH are the most widely used functions for performing lookups.

INDEX and MATCH functions can be Nested.

INDEX returns the value for a given location in a list or table

Formula:

=INDEX(Cell_Range,Row_Number)

=INDEX(Cell_Range,Row_Number,Column_Number)

MATCH returns the numeric position of an item in a list

Formula:

=MATCH(Item_Value,Cell_Range,0)

Nested INDEX-MATCH

Formula:

=INDEX(Cell_Range,MATCH(Item_Value,Cell_Range,0))

=INDEX(Cell_Range,MATCH(Item_Value,Cell_Range,0),Column_No)

Examples for Demonstration:

12. CHOOSE

The CHOOSE function returns a Value from a list using a given position or index

Formula:

=CHOOSE(Item_Value,Cell_1,Cell_2,Cell_3)

Example for Demonstration:

13. PROPER

This Function capitalises the 1st Letter in each word of a given phrase/statement

Formula:

=PROPER(TEXT_Or_Cell_Address)

Example for Demonstration:

14. TRIM

This Function is used to remove the unnecessary spaces in a string.

Formula:

=TRIM(Text_Or_Cell_Address)

Example for Demonstration:

15. LEN, LEFT, MID, RIGHT

LEN function returns the length of a given text string/number as the number of characters.

[Note: LEN will also count spaces]

Formula:

=LEN(Text_String_Or_Number_Cell_Address)

LEFT function returns a substring from a string/number and starts from the leftmost character.

Formula:

=LEFT(Text_String_Or_Number_Cell_Address,No_Of_Characters_Value)

RIGHT function returns a substring from a string/number and starts from the rightmost character.

Formula:

=RIGHT(Text_String_Or_Number_Cell_Address,No_Of_Characters_Value)

MID function returns a substring from the middle of the string/number. (It is based on the Starting Point and the No. of Characters specified by you)

Formula:

=MID(Text_String_Or_Number_Cell_Address,Starting_Point_Value,No_Of_Characters)

Example for Demonstration:

16. CONCATENATE

This Function concatenates (joins) multiple words/strings to form a longer/new string.

Formula:

=CONCATENATE(String1_Cell_Address,String2_Cell_Address,String3_Cell_Address)

Example for Demonstration:

17. VLOOKUP

This Function will look-up and retrieve data from a specific column in a table.

Lookup values must appear in the 1st column of the table, with lookup columns to the right.

Formula:

=VLOOKUP(Value,Table,Col_Index,Range_lookup)

Value: The Value to look for in the 1st column of the table

Table: The table from which to retrieve a value

Col_Index: The column in the table from which to retrieve a value

Range_Lookup: Can either be TRUE ("Appropriate Match", Default) or FALSE (Exact Match)

Example for Demonstration:

18. PIVOT TABLES

Pivot Table is a report which provides an interactive view of your data.

With the help of pivot table, you can look at the same data from different perspectives.

You can group data into categories, break down data into years and months, filter data to include or exclude categories, and even build charts.

A Step By Step Demonstration

A Sample Table

Now, let's create a PIVOT Table for the above at location "H2"

Thus, I have dragged "INCENTIVES EARNED (INR)" Field Name into the "Values" Area and "EMPLOYEE NAME" Field Name into the "Rows"Area. So, my PIVOT Table got Created at H2 Location in the existing Excel Sheet.

Now, if I change any Data in the Original Table, the data in the PIVOT Table will also change automatically.

Thus, now "EMP2" has earned the highest incentives.

Also, in the PIVOT Table, you can add more than 1 Field to the "Values" Area

There are also different ways to display value. One options is to show the value as a "Percentage(%) of Grand Total"

Now, I'll drag "INCENTIVES EARNED (INR)" Field again to the "Values" Area.

Some more ways of display the data using PIVOT Table

PIVOT Tables can also plot your data in various 2-Dimensional arrangements, i.e. by interchanging the Row Field and Column Field

Thus, these were the "18 Must-Know" Advanced Excel Functions for HR Professionals.

Now, I am sure that you will be able to use these functions in your day-to-day HR Tasks.

All The Best !!!