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

"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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

5. MAX

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

Formula:

=MAX(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

6. MIN

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

Formula:

=MIN(Starting_Cell_Address:Ending_Cell_Address)

Example for Demonstration:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
Example-2 for Demonstration:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image

14. TRIM

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

Formula:

=TRIM(Text_Or_Cell_Address)

Example for Demonstration:
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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:
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image

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

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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.

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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

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

No alt text provided for this image
No alt text provided for this image

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.

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

Some more ways of display the data using PIVOT Table

No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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 !!!

Khushbu Bhargava

AM-HR with Babycare Product Company

5 个月

Very nice easy explanation?

要查看或添加评论,请登录

Aniket Chakraborty的更多文章

社区洞察

其他会员也浏览了