For management consultants who must conduct data analysis and give presentations to clients, *Microsoft* products are truly incredible resources. And without a doubt, one of the most crucial tools for management consultants is *Microsoft Excel*. In this article, we will discuss the top *Excel* functions and features for management consultants.

**Table of Contents**Expand

## Top 70 Excel Functions

In this section, we will discuss the top * 70* such Excel functions that are crucial in managing and presenting data in

*Excel*. We will divide them into categories and show illustrations one by one.

### DATE Function (8 Functions)

The management of business data can benefit greatly from **the DATE function**. Here, we will give brief descriptions of all the

*functions.*

**DATE**#### DAY Function

*The DAY function* is a mono argument function. It returns the day of a date. The day is given as an integer, ranging from

*to*

**1***.*

**31****Generic Syntax**

**DAY(serial_number)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Serial_number |
Required | The date of the day that we are trying to find |

**Example of DAY Function**

In this example, we will use **the DAY function** on the data in the **B5** cell. As a result , we will get a numerical value denoting the day of the date for that particular month. Since, **13th September** is the **13th** day of the month, the function will return **13**. So, the required formula with the **DAY** function in Cell **C5** will be:

`=DAY(B5)`

- Then, press
.**Enter**

- Consequently, we will have the numeric value of the day.
- Lower the cursor to get the value for the rest of the cells.

#### MONTH Function

This function takes only one argument. It returns the month of a date represented by a serial number. The month is given as an integer, ranging from * 1 (January)* to

*.*

**12 (December)****Generic Syntax**

**MONTH(serial_number)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Serial_number |
Required | The date of the month that we are trying to find. |

**Example of MONTH Function**

In this example, we will use * the MONTH function* on the data in the

*cell. As a result, we will get a numerical value denoting the month of the date for that particular month. Since,*

**B5***is the*

**September***day of the month, the function will return*

**9th***. So, the required formula with*

**9***the*function in cell

**MONTH***will be:*

**C5**

`=MONTH(B5)`

- Then, hit
.*Enter*

- As a result, you will get the value for the month.
- Move the cursor down to get the rest of the values.

#### YEAR Function

This function takes a single argument. It returns the year corresponding to a date. The year is returned as an integer, ranging from * 1900 *to

*.*

**9999****Generic Syntax**

**YEAR(serial_number)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Serial_number |
Required | The date of the year that we want to find. |

**Example of YEAR Function**

In this example, we will use the** YEAR** function on the data in the **B5** cell. As a result, we will get a numerical value denoting the day of the date for that particular month. Since, **13th September** is the **13th** day of the month, the function will return **13**. So, the required formula with **the YEAR function** in Cell **C5** will be:

`=YEAR(B5)`

- Then, hit
.**Enter**

- As a result, you will get the value for the year.
- Then, move the cursor down to get the rest of the values.

#### WEEKDAY Function

This function takes one required argument and one optional argument. It returns the day of the week corresponding to the date. The day is given as an integer, ranging from **1** (**Sunday**) to **7** (**Saturday**) (by default).

**Generic Syntax**

**WEEKDAY(serial_number,[return_type])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Serial_number |
Required | A sequential number that represents the date of the day we are trying to find. |

Return_type |
Optional | A number that determines the type of return value. This number actually means from which day the week count will begin. By default the week count begins from Sunday. |

**Example of WEEKDAY Function**

In this example, we will insert the date in the **B5** cell as the argument for the **WEEKDAY** function in the **C5** cell. This function will return 3 as this is Tuesday and by default, it is **3rd** day of the week. So, the required formula with **the WEEKDAY function** in cell **C5** will be:

`=WEEKDAY(B5)`

- Then, hit
.**Enter**

- Consequently, we will get the weekday value.
- Move the cursor down to the last data cell to get the rest of the values.

#### WEEKNUM Function

This function takes only one essential argument and one optional one. It returns the week number of a specific date.

**Generic Syntax**

**WEEKNUM(serial_number,[return_type])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Serial_number |
Required | A date within the week. |

Return_type |
Optional | A number that establishes the first day of the week. 1 is the default. |

**Example of WEEKNUM Function**

In this instance, the date in the B5 cell will be the sole argument for **the WEEKNUM function** in the **C5** cell. The function will return **38** as the date is in the **38th** week of the year. So, the required formula with the **WEEKNUM** function in Cell **C5** will be:

`=WEEKNUM(B5)`

- After that, hit
.**Enter**

- As a result, we will get the value for the argument.

#### NETWORKDAYS Function

This function takes two essential arguments and one optional. It returns the number of working days between two dates excluding weekends and holidays.

**Generic Syntax**

**NETWORKDAYS(start_date, end_date, [holidays])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Start_date |
Required | A day that serves as the beginning date. |

End_date |
Required | A day that serves as the ending date. |

Holidays |
Optional | An optional range of one or more dates, such as state and federal holidays and floating holidays, to be excluded from the working calendar |

**Example of NETWORDAY Function**

In this case, the date in the * B5* and

*cells will be passed as the argument for*

**C5***in the*

**the NETWORKDAY function***cell. The function will return the difference between the two dates. So, the required formula with the*

**C5****NETWORKDAY**function in cell

*will be:*

**C5**

`=NETWORKDAYS(B5,C5)`

- Press
.**Enter**

- As a result, we will get the net workdays excluding weekends.

#### DATE Function

This function takes three arguments. It returns the sequential serial number that represents a particular date.

**Generic Syntax**

** DATE(year,month,day)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

year |
Required | The argument’s value might be one to four digits.year |

month |
Required | A month of the year, from 1 to 12, represented by a positive or negative integer. |

day |
Required | A month of the year, from 1 to 31, is represented by a positive or negative integer. |

**Example of DATE Function**

In this example, the values in the * B5, C5, *and

*cells will combine to return date after being used as the arguments of the*

**D5***function.*

**DATE**So, the required formula with the* DATE* function in cell

*will be:*

**E5**

`=DATE(B5,C5,D5)`

- Press the
button.**Enter**

- As a result, we will get the desired value.

#### TODAY Function

This function returns the serial number of the current date. It has no argument.

**Generic Syntax**

**TODAY()**

### TEXT Function (11 Functions)

* TEXT functions* let you work with strings to get information or create stunning reports. In this section, we will show excerpts of

*of the most important text functions.*

**11**#### FIND Function

This function takes one required argument and one optional one. It locates * find_text* string within

*and returns the number of the starting position of the*

**within_text***from the first character of the second*

**find_text***.*

**wthin_text***is optional. It is the number in the*

**Start_num***argument at which you want to start searching.*

**within_text***function is case-sensitive.*

**FIND****Generic Syntax**

**FIND(find_text,within_text**,**start_num)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

Find_text |
Required | This is the text you’re looking for. |

Within_text |
Required | This is the text that contains the text you’re looking for. |

Start_num |
Optional | It defines the character at which the search should begin. |

**Example of FIND Function**

In this example, **the FIND function** in the * C5* cell will return

*. Since*

**3***is in*

**“a”***place in*

**3rd***. So, the required formula with the*

**“Adam”****FIND**function in cell

*will be:*

**C5**

`=FIND(“a”,B5)`

- Hit
.**Enter**

- As a result, we will get the required value.

#### SEARCH Function

This function takes two essential arguments and one optional one. It locates the * find_text* string within

*and returns the number of the starting position of the*

**within_text***from the first character of the second*

**find_text***.*

**wthin_text***is optional. It is the number in the*

**Start_num***argument at which you want to start searching. The*

**within_text***function is not case-sensitive.*

**SEARCH****Generic Syntax**

**SEARCH(find_text,within_text,start_num)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

find_text |
Required | This is the text you’re searching for. |

within_text |
Required | This is the text that contains the text you’re searching for. |

start_num |
Optional | It defines the character at which the search begins. |

**Example of SEARCH Function**

In this example, * the SEARCH function* in the

*cell will return*

**C5***. Since*

**1***is in*

**“a”***place in*

**1st***and this function is not case-sensitive. So, the required formula with the*

**“Adam”****SEARCH**function in cell

*will be:*

**C5**

`=SEARCH(“a”,B5)`

- Hit
.**Enter**

- As a result, we will get the desired result.

#### LEFT Function

This function returns the specified number of characters from a string starting from the left side.

**Generic Syntax**

**Left( string, length )**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

string |
Required | String expression that returns the leftmost characters. If Null is present in the string, Null is returned. |

length |
Required | It is an expression in numbers indicating the number of characters to return. If 0, a string with zero length (“”) is produced. The complete string is returned if the number of characters in the string is more than or equal to that number. |

**Example of LEFT Function**

**The LEFT function** in the* C5* cell will return

*. Since we will insert*

**“Ad”****2**as the length of the text that will be extracted from the left side of

*. So, the required formula with the*

**“Adam”****LEFT**function in cell

*will be:*

**C5**

`=LEFT(B5,2)`

- Then, hit
.**Enter**

- Consequently, we get the result we want.

#### RIGHT Function

This function returns the specified number of characters from a string from the right side.

**Generic Syntax**

**RIGHT( string , length )**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

string |
Required | String expression that returns the rightmost characters. If Null is present in the string, Null is returned. |

length |
Required | It is an expression in numbers indicating the number of characters to return. If 0, a string with zero length (“”) is produced. The complete string is returned if the number of characters in the string is more than or equal to that number. |

**Example of RIGHT Function**

The* RIGHT* function in the

*cell will return*

**C5****“am”.**Since we will insert

*as the length of the text that will be extracted from the right side of*

**2***. So, the required formula with*

**“Adam”***in cell*

**the RIGHT function***will be:*

**C5**

`=RIGHT(B5,2)`

- Then, hit
.**Enter**

- Consequently, we will get a portion of the text from the right side.

#### MID Function

This function returns the specified number of characters from a string as specified by the start argument.

**Generic Syntax**

**MID(text,start_num,num_chars)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text |
Required | This is the text string from which you wish to extract the desired characters. |

start_num |
Required | This is is the position where you want to extract the first character from the text. The text starts with start num 1, then continues with start num 2, etc. |

num_chars |
Required | It indicates how many characters from the text you wish MID to return. |

**Example of MID Function**

* The MID function* in the

*cell will return*

**C5***. Since we will insert*

**“Ada”***as the length of the text that will be extracted from index*

**3***of*

**1***as the start number of the text is*

**“Adam”***. So, the required formula with*

**1***the*in cell

**MID**function*will be:*

**C5**- Firstly, choose the
cell and type the following formula,**C5**

`=MID(B5,1,3)`

- Then, hit
**Enter**.

- Consequently, we will have the desired result.

#### LEN Function

This function returns the number of characters from a string.

**Generic Syntax**

**LEN(text)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text |
Required | This is the text which length you’re looking for.Spaces between the texts are considered as characters. |

**Example of LEN Function**

** The LEN function **in the

*cell will return*

**C5***as the text in the*

**4***cell has*

**B5***characters. So, the required formula in the*

**4***cell will be,*

**C5**

`=LEN(B5)`

- Finally, hit
.**Enter**

- Consequently, we will get the length of the text.

#### VALUE Function

This function converts the number saved as a text into a number.

**Generic Syntax**

**VALUE(text)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text |
Required | This is the text you want to convert in quotes, or a referenceto a cell that contains it. |

**Example of VALUE Function**

In this example, **the VALUE function** in the

*cell will turn the value in the*

**C5***cell that is saved as a text into a numeric type data. So, the required formula of the*

**B5***function in the*

**VALUE***cell will be,*

**C5**

`=VALUE(B5)`

- Press
.**Enter**

- As a result, we will get the value.

#### TEXT Function

This function converts a number into a string with a specific format.

**Generic Syntax**

**TEXT(value, format_text)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

value |
Required | This is a number that you want to transform into text. |

format_text |
Required | This is an expression that specifies the formatting you want to apply to the supplied value in text form. |

**Example of TEXT Function**

In this instance, **the TEXT function** in the

*cell will arrange the date value in the*

**C5***cell as the format shown in the second argument.*

**B5**So, the required formula for the* TEXT* function in the

*cell will be,*

**C5**

`=TEXT(B5,"dd/mm/yyyy ")`

- Then, hit
.**Enter**

- As a result, we will get the data in a date format.

#### TRIM Function

This function removes all spaces from a text except for single spaces between words.

**Generic Syntax**

**TRIM(text)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text |
Required | The text from where you want to remove the spaces. |

**Example of TRIM Function**

**The TRIM function** in the * C5* cell will trim the extra spaces between the texts in the

*cell. So the required formula in the*

**B5***cell will be,*

**C5**

`=TRIM(B5)`

- Then, hit
.**Enter**

- Consequently, we will get the desired outcome.

#### CLEAN Function

This function removes all nonprintable characters from text.

**Generic Syntax**

**CLEAN(text)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text |
Required | This is any worksheet data that you want to clean up of non-printable characters. |

**Example of CLEAN Function**

**The CLEAN function** in the * C5* cell will remove the non-printable character from the text in the

*cell. So, the required formula in the*

**B5***cell will be,*

**C5**

`=CHAR(7)& “A”`

- Then, hit
.**Enter**

- As a result, we will have a non-printable character before
.**“A”** - Next, select the
cell and write the following formula,**C5**

`=CLEAN(B5)`

- Then, press
.**Enter**

- Consequently, Excel will erase the non-printable character.

#### CONCATENATE Function

This function joins two or more strings into one string.

**Generic Syntax**

**CONCATENATE(text1, [text2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

text1 |
Required | This is the initial item to concatenate. The item could be a cell reference, a number, or a text value. |

text2, … |
Optional | There are more text elements to join. You are allowed 255 items and 8,192 characters in total. |

**Example of CONCATENATE Function**

**The CONCATENATE function** in the * D5* cell will merge the two texts from the

*and*

**B5***cells respectively. So, the required formula will be,*

**C5**

`=CONCATENATE(B5,C5)`

- Then, hit
.**Enter**

- As a result, the texts will be merged.

**Read More: **How to Use Format Function in Excel

### LOGICAL Function (8 Functions)

Making logical comparisons between a value and what you anticipate is made possible by *the Logical functions*. In this section, we will talk about some of the essential logical functions.

#### TRUE Function

This function returns * TRUE* if a logical statement is true. It does not have any argument.

**Generic Syntax**

**TRUE()**

#### FALSE Function

This function returns * FALSE* if a logical statement is false. It does not contain any argument.

**Generic Syntax**

**FALSE()**

#### AND Function

This function returns * TRUE* if all of its arguments are true. It has no argument.

**Generic Syntax**

**AND()**

#### OR Function

This function returns * TRUE* if any of its arguments are true. It does not encompass any argument.

**Generic Syntax**

**AND()**

#### NOT Function

This function reverses the logic of its arguments. It has no argument.

**Generic Syntax**

**NOT()**

#### SWITCH Function

This function evaluates a value against a list of values and returns the corresponding results. The optional default value will be returned if there is no match.

**Generic Syntax**

**SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

expression |
Required | Expression is the value that will be compared to values value1 through value126, such as a number, date, or some text. |

ValueN |
Required | We will compare ValueN against expression. |

ResultN |
Required | When the matching valueN parameter matches the expression, ResultN is the value that will be returned. |

default |
Optional | If no matches are discovered among the valueN and the expressions, the default value is what will be returned. |

**Example of SWITCH Function**

**The SWITCH function** in the * C5* cell will return

*because the value in the*

**“Executive”***is*

**B5***which is associated with*

**“Adam”***So the formula in the*

**“Executive”.***cell will be,*

**C5**- Select the
cell and write the following formula,**C5**

`=SWITCH(B5,"Harry","Manager","Adam","Executive","Unknown")`

- Then, hit the
button.**Enter**

- As a result, we will get the output.

#### IF Function

This function has **3** arguments. If argument **1** is * TRUE*, then it does as argument

*dictates. Otherwise, it does as argument*

**2***dictates.*

**3****Generic Syntax**

**IF(logical_test, value_if_true, [value_if_false])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

logical_test |
Required | The condition we would like to test. |

value_if_true |
Required | The value that should be returned if logical test returns TRUE. |

value_if_false |
Optional | The value that should be returned if logical test returns FALSE. |

#### IFERROR Function

This function returns * value_if_error* if the first argument is evaluated to an error. Otherwise, it returns the result of the first argument.

**Generic Syntax**

**IFERROR(value, value_if_error)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

value |
Required | The argument that is examined for errors. |

value_if_error |
Required | The value that should be returned if the formula results in an error. Evaluations are made for the following error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! |

**Example of IFERRRO Function**

**The IFERROR function** in the * D5* cell will return

*because the*

**” No Result”***expression will return an error. So the formula will be,*

**B5/B6**

`=IFERROR(B5/B6,"No Result")`

- Then, hit
.**Enter**

- As a result, we will have an error message.

#### Combining TRUE, FALSE, AND, and IF Functions

We can not use the * TRUE* and

*functions independently. So we will use them in combination with the*

**FALSE***and*

**AND***functions here.*

**IF****Steps:**

- Select the
cell and write down the following formula,**D5**

`=IF(AND(B5>B6,B7>B5,TRUE(),FALSE())`

- Then, hit
.**Enter**

- As a result, we will get a logical result.

#### Combining TRUE, FALSE, OR, and IF Functions

We can not use the * TRUE* and

*functions solo. So we will use them in combination with the*

**FALSE***and*

**OR***functions in this instance.*

**IF****Steps:**

- Select the
cell and write the following formula down,**D5**

`=IF(OR(B5>B6,B7>B5,TRUE(),FALSE())`

- Then, hit
.**Enter**

- As a result, we will get an output.

#### Combining TRUE, FALSE, NOT, and IF Functions

We can not use the * TRUE* and

*functions independently. So we will use them in combination with the*

**FALSE***and*

**NOT***functions in this example.*

**IF****Steps:**

- Select the
cell and write down the following formula,**D5**

`=IF(NOT(B5>B6,B7>B5,TRUE(),FALSE())`

- Then, hit
.**Enter**

- Consequently, we will get a logical answer.

### LOOKUP and Reference Functions (13 Functions)

Lookup functions are frequently used when retrieving data, expanding data tables, or mapping records to categories. Here, we will discuss some of these functions.

#### CHOOSE Function

This function selects one of up to * 254* values from the value list based on

*.*

**index_num****Generic Syntax**

**CHOOSE(index_num, value1, [value2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

index_num |
Required | Specifies which value argument is selected. Index_num must be a number between 1 and 254, or formula or reference to a cell containing a number between 1 and 254. |

value1 |
Required | This is a must-value. Based on this value the function matches the index number. |

value2, … |
Optional | After the first value, all the values are optional. Based on index num, chooses a value or an action from a list of the CHOOSE function1 to 254 value parameters. The arguments may be text, formulas, defined names, cell references, numbers, or other types of data. |

**Example of CHOOSE Function**

**The CHOOSE function** in the * C5 *cell will return

*“Manager”*because the number

*in the*

**1***cell is associated with*

**C5***“Manager.”*So, the formula in that cell will be,

`=CHOOSE(C5, "Manager", "HR" ," Executive”)`

- Then, hit
.**Enter**

- As a result, we will get our desired outcome.

#### ROW Function

This function returns the row number of the given cell reference.

**Generic Syntax**

**ROW([reference])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

reference |
Required | This is the cell or a group of cells for which the row number is desired. |

**Example of ROW Function**

** The ROW function** in the

*cell will return*

**C5***since the referenced cell*

**5***is in row*

**B5***. So, the formula will be,*

**5**

`=ROW(B5)`

- Then, hit
.**Enter**

- Consequently, we will get the desired outcome.

#### COLUMN Function

This function returns the column number of the given cell reference.

**Generic Syntax**

**COLUMN([reference])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

reference |
Required | This is the cell or a group of cells for which the column number is desired. |

**Example of COLUMN Function**

This function will return * 2* in the

*cell because the referenced cell is in the*

**C5***columns. So. the desired formula in the*

**2nd***cell will be,*

**C5**

`=COLUMN(B5)`

- Then, hit
.**Enter**

- Consequently, we will get the desired outcome.

#### ROWS Function

This function returns the number of rows in a reference of an array.

**Generic Syntax**

**ROWS(array)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array |
Required | This is an array, an array formula, or a reference to a range of cells for which you want the number of rows. |

**Example of ROWS Function**

** The ROWS function** in the

*cell will return*

**C5***because the array under it has*

**4***rows. So, the formula in the*

**4***cell will be,*

**C5**

`=ROWS(B5:B8)`

- Then, hit
.**Enter**

- Consequently, we will get the desired outcome.

#### COLUMNS Function

This function returns the number of columns in a reference of an array.

**Generic Syntax**

**COLUMNS(array)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array |
Required | This is an array, an array formula, or a reference to a range of cells for which you want the number columns. |

**Example of COLUMNS Function**

* The COLUMNS function* in the

*cell will return*

**C5***because the array under it has*

**1***column. So, the formula in the*

**1***cell will be,*

**C5**- Choose the
cell and write the following formula,**C5**

`=COLUMNS(B5:B8)`

- Then, hit
.**Enter**

- Consequently, we will get the desired outcome.

#### VLOOKUP Function

This function looks in the first column of an array and returns the value of **offset_column.**

**Generic Syntax**

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

lookup_value |
Required | You want to search up this value. The first column of the cell range that you supply in the table_array argument must contain the value you wish to look up. |

table_array |
Required | The range of cells in which the VLOOKUP will look for the lookup_value and the return value. |

col_index_num |
Required | the return value’s corresponding column number, which begins with 1 for table_array’s leftmost column. |

range_lookup |
Optional | A logical value that tells the VLOOKUP function whether you want an exact match or a close match |

**Example of VLOOKUP Function**

** The VLOOKUP function** in the

*cell will return*

**D5***. Because it is in the second column of the lookup range*

**102***and beside the lookup value*

**B5:C8***“Adam”*. So the formula in the

**cell will be,**

*D5*

`=VLOOKUP(“Adam”,B5:C8,2)`

- Then, press
.**Enter**

- As a result, we will have our
value.**VLOOKUP**

#### HLOOKUP Function

This function looks in the top row of an array and returns the value of * offset_column*.

**Generic Syntax**

**HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

lookup_value |
Required | You want to search up this value. The first row of the cell range that you supply in the table_array argument must contain the value you wish to look up. |

table_array |
Required | The range of cells in which the HLOOKUP will look for the lookup_value and the return value. |

row_index_num |
Required | the return value’s corresponding row number, which begins with 1 for table_array’s leftmost column. |

range_lookup |
Optional | A logical value that tells the HLOOKUP function whether you want an exact match or a close match |

**Example of HLOOKUP Function**

** The HLOOKUP function** in the

*cell will return*

**D5***“Jhon”*. Because it is in the second row of the lookup range

*and below the lookup value*

**B5:C8***“Adam”*. So the formula in the

*cell will be,*

**D5**

`=HLOOKUP(“Adam”,B5:C8,2)`

- Then, press
.**Enter**

- As a result, we will have our
value.**HLOOKUP**

#### LOOKUP Function

This function looks at a single row or column and finds the value from the same position in a second row or column.

**Generic Syntax**

**LOOKUP(lookup_value, lookup_vector, [result_vector])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

lookup_value |
Required | A value in the first vector that LOOKUP looks for. Lookup value is a variable that can take the form of a number, text, logical value, name, or reference that points to a value. |

lookup_vector |
Required | A set of cells with just one row or one column. Text, integers, or logical values can all be used as lookup_vector values. |

result_vector |
Optional | A set of cells with just one row or column. The size of the lookup vector and the result vector arguments must match. It must be the same dimension. |

**Example of LOOKUP Function**

* The LOOKUP function* in the

*cell will return*

**D5***. Because it matches the lookup value*

**102***“Adam”*in the lookup range

*. So, the formula will be,*

**B5:C8**

`=LOOKUP(“Adam”,B5:C8)`

- Then, press
.**Enter**

- As a result, we will have our lookup value.

#### MATCH Function

This function gets the position of an item in an array.

**Generic Syntax**

**MATCH(lookup_value, lookup_array, [match_type])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

lookup_value |
Required | The value that we want to match in argument.lookup_array |

lookup_array |
Required | The set of cells being searched. |

match_type |
Optional | The value 1, 0, or -1 . The lookup value and values in the lookup array are matched by Excel according to the match type argument. This argument’s default value is 1. |

**Example of MATCH Function**

* The MATCH function* will return

*as*

**1***is the first element in the range*

**“Adam”***So, the desired formula will be,*

**B5:B8.**

`=MATCH(“Adam”,B5:B8)`

- Then, hit
.**Enter**

- Consequently, we will have a match.

#### INDEX Function

This function returns the value of an element in a table or an array based on the * row_num* and

*.*

**column_num****Generic Syntax**

**INDEX(array, row_num, [column_num])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array |
Required | The range of cells or an array of constants where the function will operate. |

row_num |
Required | In absent of the column_num, this must be present. It chooses which row in the array to return a value from. The column_num is necessary if row_num is left out. |

column_num |
Optional | It chooses the array column from which to retrieve a value. The row_num is necessary if column_num is left out. |

**Example of INDEX Function**

* The INDEX function* will return

*in the*

**“Jhon”***cell since it is in the second row of the*

**D5***range. So, the formula will be,*

**B5:B8**

`=INDEX(B5:B8,2)`

- Then, hit
.**Enter**

- As a result, we will get the value of the indexed cell.

#### OFFSET Function

This function returns values in a cell or a range that is rows and columns away from a reference cell.

**Generic Syntax**

**OFFSET(reference, rows, cols, [height], [width])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

reference |
Required | This is the reference that you want to use to determine the offset. The OFFSET function returns the #VALUE! error value if reference does not refer to a cell or range of neighboring cells. |

rows |
Required | The number of rows that you wish the upper-left cell to point to, either up or down. |

cols |
Required | The number of columns that you wish the upper-left cell to point to, either up or down. |

height |
Optional | The desired height of the returned reference is expressed in rows. Height needs to be a positive integer. |

width |
Optional | The desired width of the returned reference is expressed in columns. The width needs to be a positive integer. |

**Example of OFFSET Function**

* The OFFSET function* in the

*cell will return*

**D5***Because it is the value of the cell that is two rows below and one column to the right of the referenced cell*

**101.***. So, the desired formula in the*

**B5***cell will be,*

**D5**

`=OFFSET(B5,2,1)`

- Press
.**Enter**

- Consequently, we will have an output.

#### INDIRECT Function

This function returns the reference specified by a text string.

**Generic Syntax**

**INDIRECT(ref_text, [a1])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

ref_text |
Required | This is an A1-style reference, an R1C1-style reference, a name designated as a reference, or a reference to a cell as a text string are all examples of references to cells that contain references. The INDIRECT function returns the #REF! error value if the cell reference provided by ref text is invalid. |

a1 |
Optional | This is a logical value describing the kind of reference that is present in the cell ref_text. |

**Example of INDIRECT Function**

* The INDIRECT function* in the

*cell will return*

**E5***because the referenced*

**103***cell has*

**D8***as its value and the value contained in the*

**C8***cell is*

**C8***. So, the required formula will be,*

**103**

`=INDIRECT(D8)`

- Then, press
.**Enter**

- As a result, we will get the value from the
cell.*C8*

#### GETPIVOTDATA Function

This function returns data stored in a * PivotTable* report.

**Generic Syntax**

**GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

data_field |
Required | This is the name of the field in a PivotTable that has the information you want to access. It is necessary to quote this. |

pivot_table |
Required | This is a reference to any PivotTable cell, range of cells, or designated range of cells. This data is needed to identify which PivotTable holds the data you’re looking for. |

field1, item1, field2, item2… |
Optional | Field names and item names that define the data you want to obtain range from 1 to 126 in pairs. Any order is possible for the pairings. You must surround field names and names for objects other than dates and numbers in quotation marks. |

**Example of GETPIVOTDATA Function**

This function will return * 450* in the

*cell because we will define the arguments accordingly. So, the required formula in the*

**E5***cell is as follows,*

**E5**

`=GETPIVOTDATA(“Revenue”,$B$4,”Month”,”January”)`

- Press
.**Enter**

- As a result, we will get an output.

### MATH Function (15 functions)

Large companies typically have specialized teams that can analyze data and perform in-depth statistical analysis for consultants. However, basic math and statistics calculations require the use of Excel functions on a daily basis for management consultants.

#### RAND Function

This function returns an evenly distributed random real number greater than or equal to * 0* and less than

*.*

**1***can return number between*

**RAND()*(b-a)+a***and*

**a***. It does not contain any argument.*

**b****Generic Syntax**

**RAND()**

**Example of RAND Function**

**The RAND function** takes no argument. Here, we will modify the formula as follows to return a value in the **D5** cell,

`=RAND()*(C5-B5)+B5`

- Then, press
.**Enter**

- As a result, we will get a random number.

#### RANDBETWEEN Function

This function generates a random integer number between the bottom number and the top number.

**Generic Syntax**

**RANDBETWEEN( bottom, top)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

bottom |
Required | The smallest integer that the RANDBETWEEN function will return. |

top |
Required | The largest integer that the RANDBETWEEN function will return. |

**Example of RANDBETWEEN Function**

* The RANDBETWEEN function* will return any random number in the

*cell between the values in the*

**D5***and*

**B5***cells respectively. So, the desired formula will be,*

**C5**- To start with, select the
cell and enter the following formula,**D5**

`=RANDBETWEEN(B5,C5)`

- Then, press
.**Enter**

- Consequently, we will get a random number in between the numbers.

#### INT Function

This function rounds a number down to the nearest integer.

**Generic Syntax**

**INT(number)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | The real number that we want to round down to an integer. |

**Example of INT Function**

* The INT function* in the

*cell will return*

**C5***since it will only return the integer portion of the number. So, the required formula will be,*

**2**

`=INT(B5)`

- After that, press
.**Enter**

- As a result, we will get the desired outcome.

#### ROUND Function

This function rounds a number to a specified number of digits.

**Generic Syntax**

**ROUND(number, num_digits)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | The number that we want to round. |

num_digits |
Required | The number of digits to which we want to round the number argument. |

**Example of ROUND Function**

* The ROUND function* in the

*cell will round the*

**C5***in the*

**2.05***cell to the nearest*

**B5***and will return*

**1***. So the required formula will be,*

**2.1**

`=ROUND(B5,1)`

- After that, press
.**Enter**

- As a result, we will get the desired outcome.

#### FLOOR Function

This function rounds the numbers down, toward zero, to the nearest multiple of significance.

**Generic Syntax**

**FLOOR(number, significance)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | The numeric value that we want to round. |

significance |
Required | The multiple to which we want to round. |

**Example of FLOOR Function**

This function will return * 2* in the

*cell because it will round down the*

**C5***in the B5 cell to*

**2.05***. So, the formula in the*

**2***cell will be,*

**C5**

`=FLOOR(B5,1)`

- After that, press
.**Enter**

- As a result, we will get the desired outcome.

#### CEILING Function

This function rounds the numbers up, away from zero, to the nearest multiple of significance.

**Generic Syntax**

**CEILING(number, significance)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | The value that we want to round. |

significance |
Required | The multiple to which we want to round. |

**Example of CEILING Function**

** The CEILING function** in the

*cell will return*

**C5***because it will round up the*

**3***in the*

**2.05***cell. So, the required formula will be,*

**B5**

`=CEILING(B5,1)`

- After that, press
.**Enter**

- As a result, we will get the desired outcome.

#### SUM Function

This function adds together all the numbers given as arguments and returns the total number.

**Generic Syntax**

**SUM(number1,[number2],…)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | This is the first number that we want to add. |

[number2],…255 |
Optional | These 256 numbers are optional and can be added at a time. |

**Example of SUM Function**

It will simply return the sum of the numbers in the * B5:B8* range. So, the formula in the

*cell will be,*

**D5**

`=SUM(B5:B8)`

- Then, hit
.**Enter**

- As a result, we will get the sum of the specified cells.

#### SUMIF Function

This function adds together cells in * range_sum* if corresponding cells in

*meet the criteria. If*

**range_evaluate***is omitted, Excel will add cells in*

**range_sum***.*

**range_evaluat****e****Generic Syntax**

**SUMIF(range, criteria, [sum_range])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

range |
Required | This is the selection of cells you want to use as criteria. Each range must include only numbers, names, arrays, or references containing numbers. Values that are blank or text are ignored. Dates in the default Excel format could be included in the selected range. |

criteria |
Required | This is the condition based on which you will add the data. |

sum_range |
Optional | If you want to add cells other than those listed in the range argument, you must add the actual cells. The cells that are given in the range parameter are added by Excel if the sum_range argument is not present. |

**Example of SUMIF Function**

* The SUMIF function* in the

*cell will return the sum of the numbers in the*

**D5***range which are greater than zero. So, the formula will be,*

**B5:B8**

`=SUMIF(B5:B8,”0>”)`

- Then, press
.**Enter**

- Consequently, we will get the conditional summation.

#### SUMIFS Function

This function adds all cells meeting all of the specified criteria.

**Generic Syntax**

**SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

sum_range |
Required | The set of cells to sum. |

criteria_range1 |
Required | This is the range that is tested using .Criteria1 |

criteria1 |
Required | It is the criteria that define which cells in will be added.Criteria_range1 |

criteria_range2, criteria2, … |
Optional | These are the optional criteria. |

**Example of SUMIFS Function**

** The SUMIFS function** in the

*cell will return the sum of numbers from the*

**D5***range which are greater than zero. So, the formula in the D5 cell will be,*

**B5:B8**

`=SUMIFS(B5:B8,”0>”)`

- Then, press
.**Enter**

- Consequently, we will get the conditional summation.

** **

#### PRODUCT Function

This function multiplies all the numbers given as arguments and returns the product.

**Generic Syntax**

**PRODUCT(number1, [number2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | This is the first number or range that you want to multiply. |

number2, … |
Optional | These are additional numbers or ranges that you want to multiply, up to a maximum of 255 arguments. |

**Example of PRODUCT Function**

** The PRODUCT function** in the

*cell will return the product of the numbers in the range*

**D5***. So, the formula will be,*

**B5:B8**

`=PRODUCT(B5:B8)`

- Then, press
.**Enter**

- Consequently, we will get the product.

#### POWER Function

This function returns the result of a number raised to a power.

**Generic Syntax**

**POWER(number, power)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | This is base number. It can be any real number. |

power |
Required | This is the exponent to which the base number is raised. |

**Example of POWER Function**

** The POWER function** in the

*cell will return 1. Because it will raise the power of*

**D5***1*to

*and the result is*

**4***1*. So, the required formula in the

*cell will be,*

**D5**

`=POWER(B5,C5)`

- Then, press
.**Enter**

- As a result, the base value will be raised to power.

#### SQRT Function

This function returns a positive square root.

**Generic Syntax**

**SQRT(number)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | This is the number for which we will find the square root. |

**Example of SQRT Function**

* The SQRT function* in the

*cell will simply return the square root of*

**C5****that is in the**

*4**cell and return*

**B5***. So, the formula for the*

**2***cell will be,*

**C5**

`=SQRT(B5)`

- Then, hit
.**Enter**

- As a result, we will get the square root of the numbers.

#### MOD Function

This function returns the remainder after a number is divided by a divisor.

**Generic Syntax**

**MOD(number, divisor)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | This is the number for which we want to find the remainder. |

divisor |
Required | This is the number by which we want to divide number. |

**Example of MOD Function**

** The MOD function** in the

*cell will simply return the remainder of the division between the*

**D5***and*

**B5***cell and it will be*

**C5***. So, the required formula will be,*

**1**

`=MOD(B5,C5)`

- Then, press
.**Enter**

- As a result, we will get the reminder value.

#### SUBTOTAL Function

This function returns a subtotal in a list or database. The * function_ number *includes

*or*

**1-11***. It specifies the function to be used for the subtotal.*

**101-111***includes manually hidden rows while*

**1-11***excludes them. Filtered-out cells are always excluded.*

**101-111****Generic Syntax**

**SUBTOTAL(function_num,ref1,[ref2],…)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

function_num |
Required | The range 1–11 or 101–111 designates the function to be applied for the subtotal. Rows that have been manually buried are included in rows 1–11 but not in rows 101–111; filtered-out cells are always excluded. |

ref1 |
Required | This is the first named range or reference for which we want the subtotal.. |

ref2,… |
Optional | This is the Named ranges or references 2 to 254 for which we want the subtotal. |

**Example of SUBTOTAL Function**

** The SUBTOTAL function** in the

*cell will return the average of the numbers in the range of cell*

**D5***. So, the required formula in the*

**B5:B8***cell will be,*

**D5**

`=SUBTOTAL(1,B5:B8)`

- Then, press
.**Enter**

- Consequently, we will get the average of the selected range.

#### SUMPRODUCT Function

This function multiplies the corresponding items in given arrays and then returns the sum of the results.

**Generic Syntax**

**SUMPRODUCT(array1, [array2], [array3], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array1 |
Required | This is the first array argument whose components we want to multiply and then add. |

[array2], [array3],… |
Optional | These are the array arguments 2 to 255 whose components we want to multiply and then add. |

**Example of SUMPRODUCT Function**

* The SUMPRODUCT function* in the

*cell will first multiply two adjacent cells( for example,*

**D5***and*

**B5***) and finally sum the products of all the cells from*

**C5***to*

**C5***. So, The required formula in the*

**C8***cell will be,*

**D5**

`=SUMPRODUCT(B5:B8,C5:C8)`

- Then, press
.**Enter**

- As a result, we will get the product and then the sum of the values simultaneously.

**Read More: **51 Mostly Used Math and Trig Functions in Excel

### STATISTICAL Function (15 functions)

Management consultants often need some statistical functions to analyze their data. In this section, we will talk about some of the statistical functions.

#### COUNT Function

This function counts the total number of cells that contain numbers.

**Generic Syntax**

**COUNT(value1, [value2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

value1 |
Required | This is the first item, cell reference, or range within which we want to count numbers. |

value2, … |
Optional | These are Up to 255 additional items, cell references, or ranges within which we want to count numbers. |

**Example of COUNT Function**

** The COUNT function** will return the number of cells in the range

*and will return*

**B5:B8***. So, the required formula will be,*

**4**

`=COUNT(B5:B8)`

- Then, press
.**Enter**

- As a result, we will get the cell count.

#### COUNTA Function

This function counts the number of non-blank cells.

**Generic Syntax**

**COUNTA(value1, [value2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

value1 |
Required | This is the first argument representing the values that you want to count. |

value2, … |
Optional | These are the additional arguments representing the values that we want to count, up to a maximum of 255 arguments. |

**Example of COUNTA Function**

** The COUNTA function** in the

*cell will return the number of nonempty cells in the range*

**D5***. So, the required formula will be,*

**B5:B8**

`=COUNTA(B5:B8)`

- Then, press
.*Enter*

- As a result, we will get the nonempty cell count.

#### COUNTIF Function

This function counts the number of cells within a range that meet the given criteria.

**Generic Syntax**

**COUNTIF(range, criteria)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

range |
Required | This is the set of cells you want to count |

criteria |
Required | This is a number, expression, cell reference, or text string that determines which cells we will count. |

**Example of COUNTIF Function**

* The COUNTIF function *in the

*cell will return the number of cells that have a value greater than zero in the range*

**D5***. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=COUNTIF(B5:B8, “>0”)`

- Then, press
.**Enter**

- As a result, we will have conditional counting.

**COUNTIFS Function**

This function counts the number of cells within a range that meet multiple criteria.

**Generic Syntax**

**COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

criteria_range1 |
Required | This is the first range in which to evaluate the related criteria. |

criteria1 |
Required | This is the criteria in the form of a number, expression, cell reference, or text that define which cells we will count. |

criteria_range2, criteria2, … |
Optional | These are the additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed. |

**Example of COUNTIFS Function**

** The COUNTIFS function** in the

**cell will return the number of cells that have a value greater than**

*D5**in the range*

**10***. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=COUNTIFS(B5:B8, “>10”)`

- Then, press
.**Enter**

- As a result, we will get the count.

#### AVERAGE Function

This function returns the average/mean of the arguments.

**Generic Syntax**

**AVERAGE(number1, [number2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | This is the first number, cell reference, or range for which we want the average. |

number2, … |
Optional | These are additional numbers, cell references or ranges for which we want the average, up to a maximum of 255. |

**Example of AVERAGE Function**

* The AVERAGE function* in the

*cell will simply return the average of the values on the range*

**D5***. So, the required formula will be,*

**B5:B8**

`=AVERAGE(B5:B8)`

- Then, press
.**Enter**

- As a result, we will get the average value.

#### AVRAGEIF Function

This function gets the average of cells in range_ average if corresponding cells in* range_evaluate *meet criteria. If

*is omitted,*

**range_averag****e***will take the average on cells in*

**Excel***.*

**range_evaluate****Generic Syntax**

**AVERAGEIF(range, criteria, [average_range])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

range |
Required | This is a cell or cells to average, with or without names, arrays, or references containing numeric values. |

criteria |
Required | This is the criteria of which cells are averaged, expressed as a number, expression, cell reference, or text. |

average_range |
Optional | This is the actual set of cells to average. If omitted, range is used. |

**Example of AVERAGEIF Function**

** The AVERAGEIF function** will return the average of the values in the range

*which are greater than zero into the*

**B5:B8***cell. So, the formula in the*

**D5***cell will be,*

**D5**

`=AVERAGEIF(B5:B8, “>0”)`

- Then, press
.**Enter**

- Consequently, we will get a conditional average.

#### AVERAGEIFS Function

This function gets the average of cells meeting all of those criteria.

**Generic Syntax**

**AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

average_range |
Required | This is a cell or cells to average, with or without names, arrays, or references containing numeric values. |

criteria_range1, criteria_range2, … |
Required | Criteria_range1 is a must, the next criteria_ranges are optional. 1 to 127 ranges in which to evaluate the assigned criteria. |

criteria1, criteria2, … |
Optional | Criteria1 is a must , the next criteria are optional |

**Example of AVERAGEIFS Function**

** The AVERAGEIFS function** in the

*cell will return the average of the values that are greater than zero from the*

**D5***range. So, the formula will be,*

**B5:B8**

`=AVERAGEIFS(B5:B8,B5:B8, “>0”)`

- Then, press
**Enter.**

- Consequently, we will have a conditional average.

#### MIN Function

This function returns the minimum value in the arguments.

**Generic Syntax**

**MIN(number1, [number2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | Number1 is a must. |

number2, … |
Optional | These subsequent numbers are optional. |

**Example of MIN Function**

** The MIN function** in the

*cell will return the minimum value in the range*

**D5***. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=MIN(B5:B8)`

- Then, press
**Enter.**

- As a result, we will have the minimum value.

#### MAX Function

This function returns the maximum value in the arguments.

**Generic Syntax**

**MAX(number1, [number2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | Number1 is a must tp get the maximum value. |

number2, … |
Optional | These subsequent numbers are optional. |

**Example of MAX Function**

* The MAX function* in the

*cell will return the maximum value in the range*

**D5***. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=MAX(B5:B8)`

- Then, press
.**Enter**

- As a result, we will have the maximum value.

#### MEDIAN Function

This function calculates the median of a sequence of numbers.

**Generic Syntax**

**MEDIAN(number1, [number2], …)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | Number1 is a must tp get the median value. |

number2, … |
Optional | These additional numbers are optional. |

**Example of MEDIAN Function**

* The MEDIAN function* in the

*cell will return the median value of the cells in the range*

**D5****So, the formula in the**

*B5:B8.**cell will be,*

**D5**

`=MEDIAN(B5:B8)`

- Then, press
.**Enter**

- As a result, we will have the median value.

#### STDEV Function

This function calculates standard deviation based on arguments.

**Generic Syntax**

**STDEV(number1,[number2],…)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | The first argument representing a population sample. |

number2, … |
Optional | Arguments in the range of 2 to 255 represent a sample of a population. |

**Example of STDEV Function**

* The STDEV function* in the

*cell will return the standard deviation value of the cells in the range*

**D5***. So, the formula in the*

**B5:B8****D5**cell will be,

`=STDEV(B5:B8)`

- Then, press
.**Enter**

- As a result, we will have the standard deviation value.

#### VAR Function

This function estimates variance based on a sample.

**Generic Syntax**

**VAR(number1,[number2],…)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number1 |
Required | The first number argument represents a sample of a population. |

number2, … |
Optional | Number arguments 2 to 255 represent a sample of a population. |

**Example of VAR Function**

**The VAR function** in the * D5* cell will return the variance value of the cells in the range

*So, the formula in the*

**B5:B8.***cell will be,*

**D5**`=VAR(B5:B8)`

- Then, press
.**Enter**

- As a result, we will have the variance value.

#### RANK Function

This function returns the rank of a number in a list of numbers. Order is omitted by default. If it is * 0 *or omitted, Excel ranks number as if ref were a list sorted in descending order. If the order is any non-zero value,

*ranks number as if ref were a list sorted in ascending order.*

**Excel****Generic Syntax**

**RANK(number,ref,[order])**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

number |
Required | This is the number whose rank we want to find. |

ref |
Required | A set of, or a reference to, a list of numbers. Nonnumeric values in ref are not accepted. |

order |
Optional | This is a number specifying how to rank the numbers. |

**Example of RANK Function**

**The RANK function** in the * D5* cell will rank the values in the cell range

*in an ascending order. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=RANK(B5,$B$5:$B$8)`

- Hit
.**Enter**

- As a result, we will get the ranks of the values.

#### LARGE Function

This function returns the k-th largest value in an array.

**Generic Syntax**

**LARGE(array, k)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array |
Required | This is the array or range of data for which we want to determine the k-th largest value. |

k |
Required | This is the position of the largest value that the function will return.
For example: the 2nd largest or the 3rd largest value. |

**Example of LARGE Function**

**The LARGE function** in the * D5* cell will return the second largest value from the values in the range

*. So, the formula in the*

**B5:B8***cell will be,*

**D5**

`=LARGE(B5:B8,2)`

- Then, press
.**Enter**

- Consequently, we will get the second-largest value.

#### SMALL Function

This function returns the** k-th** smallest value in an array.

**Generic Syntax**

**SMALL(array, k)**

**Argument Description**

Argument | Requirement | Explanation |
---|---|---|

array |
Required | This is the array or range of data for which we want to determine the k-th samllest value. |

k |
Required | This is the position of the smallest value that the function will return.
For example the 2nd smallest or the 3rd smallest value. |

**Example of SMALL Function**

**The SMALL function** in the * D5* cell will return the second smallest value from the values in the range

*So, the formula in the*

**B5:B8.***cell will be,*

**D5**

`=SMALL(B5:B8,1)`

- Then, press
.**Enter**

- Consequently, we will get the smallest value.

**Read More: **What Are Excel Function Arguments

## Top 7 Features

### Named ranges

You can make your formula simpler by using named ranges. You are not required to include a range reference. And readers can easily comprehend. Additionally, it enables you to automatically update formulas when data changes.

**What Is Named Range?**

* Excel’s “Named Range*” feature allows you to call a group of cells by name rather than by range. A whole column, row, or even a few particular cells may be the subject. Once the named range has been defined, any operation on those cells can be carried out by calling the name of the named range.

We will follow the steps below to define a **“Named Range.”**

**Steps:**

- Firstly, select the cells that we want to make a named range.
- Here, we select a range from
to*D5*.*D8* - Then, go to the
tab.*Formulas* - From the
group of commands, select the drop-down*Defined Names*.*Define Name* - From the drop-down, select the command
*Define Name**.*

- As a result, we will get a dialogue box named
*“New Name”**.* - Set a name in the
.*Name* - We can also see the selected range from the
box.*Refers to* - Then, click
.*OK*

- Finally, our selected range will be named according to our definition.
- Select the range containing the sales information in
to check our named range again.*Column D* - We will see that the range is named as we wanted.

### Conditional Formatting

The useful feature of conditional formatting lets you highlight specific cells in a particular color. You can also use it to compare values, find discrepancies, find the smallest duplicate, and display basic icons. You can learn how to use this fantastic feature by looking at the two easy examples that follow.

#### Creating Alternating Bands

Here, we will create bands with alternate colors to visualize our data more aesthetically and clearly.

**Steps:**

- Firstly, select the data range.
- Here, we will select the range
.*B4:D8* - Secondly, go to the
tab.*Home* - Then, click on
.*Conditional Formatting* - After that, from the drop-down list, select
.*New Rule*

- Consequently, the
dialogue box will appear on the screen.*New Formatting Rule* - In the prompted
dialogue box, select*New Formatting Rule*option.*Use a formula to determine which cells to format* - Then, type the following formula into
box,*Format values where this formula is true*

`=EVEN(ROW())=ROW()`

- Then, click on the
button to open*Format*dialogue box.*Format Cells*

- Then, go to the
tab in the*Fill*dialogue box.*Format Cells* - Select the color that you want to have for your data cells.
- Finally, click
.*OK*

- Consequently, you will find that your data set is colored in alternative bands.
- You can also use the following formula to format odd rows.

`=ODD(ROW())=ROW()`

#### Finding Duplicate Values

In this instance, we will find duplicate values from our dataset using conditional formatting.

**Steps:**

- To begin with, select the data range.
- In this case, we will select the range
.*B4:D10* - After that, go to the
tab in the ribbon.*Home* - Then, click on
.*Conditional Formatting* - Afterward, from the drop-down list, select
.*New Rule*

- Consequently, the
dialogue box will appear on the screen.*New Formatting Rule* - In the prompted
dialogue box, select*New Formatting Rule**Format only unique or duplicate value*

- Then, select
from the*duplicate*option.*Format all* - Finally, click on the
button to open*Format*dialogue box.*Format Cells*

- Next, open the
dialogue box and select the*Format Cells*tab.*Fill* - Select the color that you want to have for your data cells.
- Finally, click
.*OK*

- Consequently, we will find that the duplicate values are marked with the desired color.

### Pivot Tables and Charts

You can analyze datasets quickly with pivot tables. Also, you can divide sales by region, average revenue per customer, country, and other metrics, for instance. You can use pivot tables to calculate, slice, and dice the data along any dimension. This is crucial because it can help you understand how the data is organized and where further investigation is required.

#### Pivot Table

We will use the pivot table to analyze data in the following steps.

**Steps:**

- Firstly, go to the
tab.*Insert* - Then, select the
tab.*Pivot Table* - From the drop-down, select From
.*Table/Range* - Consequently, a prompt will appear.

- From the prompt, first, select your data set as
.*Table/Range* - Then, check the
box.*Existing Worksheet* - Set the location of the pivot table.
- Finally, click
.*OK*

- As a result, you will have your
.*Pivot table* - Then, check the pivot table fields to get the entire data set.

- Next, you can calculate the average, minimum value, maximum value, etc from the pivot table.
- To calculate the average revenue, first, right-click on any of the revenue data.
- Then, select the
option*Summarize Values By* - Finally, select
from the available options.*Average*

- Consequently, you will have the average of the revenue values for each employee.

#### Pivot Chart

In this example, we will explore the Pivot Chart. Follow the subsequent steps to do that.

**Steps:**

- To begin with, go to the
tab.*Insert* - Then, choose the
tab.*Pivot Chart* - From the drop-down menu, select the
feature.*Pivot Chart* - Consequently, we will have a prompt on the screen.

- In the prompt, at first, choose your data set as
.*Table/Range* - Then, mark the
oval.*Existing Worksheet* - Set the location of the pivot table.
- Finally, click
.*OK*

- As a result, we will have our pivot chart with a pivot table.

### RSQ

You can quickly check the correlation between two datasets using ** RSQ**.

**can also be plotted on a scatter plot. We will show that in the following steps.**

*RSQ***Steps:**

- Firstly, select the
cell and type the following formula,*F5*

`=RSQ(D5:D10,C5:C10)`

- Then, hit
**Enter.**

- As a result, we will have the
value for our data.*R squared*

- After that, select the data in the range
.*C5:D10* - Then, go to the
tab in the ribbon.*Insert* - From the
group, select a scatter plot for the data.*Charts* - As a result, we will have a scatter plot of the data.

- Then, right-click on any data point on the plot.
- From the options, select
.*Add Trendline* - Consequently, a menu bar will appear on the right.

- Then, from the
, select*Trendline Options*.*Linear* - Then check the
and*“Display Equation on chart”*box.*“Display R -squared value on chart”*

- As a result, you will have an
value on the chart, the same as the value from the*R-squared*.*RSQ function*

### Regression

* Regression* is most frequently used to predict outcomes and improve business operations. For instance,

**r**

*can predict based on past behavior and other data how many products consumers will buy. Additionally, as a factory manager, you can create a model to determine the connection between cookie shelf life and oven temperature. We will show how to use regression in the following steps.*

**egression****Steps:**

- Firstly, go to the
tab.*Data* - Then, select
.*Data Analysis* - As a result, the
prompt will appear.*Data Analysis*

- From the prompt, first, select
.*Regression* - Then, choose
.*OK* - The Regression dialogue box will appear on the screen.

- Firstly, select
.*Input Y Range* - Here, we will select the range (
*D5:D10**)*. - Then, choose
.*Input X Range* - In our case, the range is (
*C5:C10**)**.* - Then, select the
oval.*New Worksheet Ply* - After that, check the
,*Residuals*and*Residual Plots,*boxes.*Line Fit Plots* - Finally, click
.*OK*

- As a result,
will show a complete report of the data with regression plots.*Excel*

### Excel Solver

One of the most helpful features in * Excel* that can help you find the best solution to a problem is the

*.*

**Excel Solver***is an optimization tool that may be used to find ways to modify a model’s assumptions in order to obtain the desired result. It is a form of what-if analysis and is especially helpful when attempting to identify the “optimal” outcome in light of a number of assumptions.*

**Excel Solver**### VBA Programming

The most crucial feature of * Excel* is

*programming. Management consultants can automate the printing of more than*

**VBA****100**files by using

**VBA**to control the printer. Additionally, they can create slides using

*so that slides update as the*

**VBA***source data does. Furthermore,*

**Excel***can be used to download online sources of data for analysis. In conclusion, learning*

**VBA***is a crucial skill that can help you become more productive and efficient.*

**VBA****Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

In this article, we have talked in detail about some of the key functions and features for management consultants of *MS Excel*. These functions and features will allow them to manage and interpret data properly.

**Related Articles**

**<< Go Back to Excel Functions | Learn Excel**

nice