advance excel

Introduction

Advance excel skills include the ability to produce graphs and table,use spreadsheet efficiently and perform the calculation and automation to the process large volumes of data . whit the rise of big data and data analytics .advance excel skills are a real asset both during the job hunt and everyday tasks .

what is formula of advance excel ?

1. lookup

It is one of the most used formulae in Excel mainly due to the simplicity of this formula and its application in looking up a certain value from other tables, which has one standard variable across these tables.

For example, if we have two tables detailing a company’s employee salary and name, with “Employee ID” being a primary column, and we want to get the salary from Table B in Table A.

We can use VLOOKUP as below.

It will result in the table below when we apply this advanced Excel formula in other cells of the “Employee Salary” column.

Drag the formula to the rest of the cells.

2. Index

It is used to get the value in a given table specifying the number of row and columns e.g. to get employee’s name of the 5th observation .below is the data.

We can use the advanced Excel formula below:

3.match

It returns the row and column number when a specific string or number is given the range

In the given example ,we are trying find the ” Rajesh ved ” in the ” employee name “in column .

The formula would be as given below:

The MATCH function would return 5 as the value.

The 3^rd argument is used for the exact MATCH. We can also use +1 and -1 based on our requirements.

4.IF AND formula

There are many instance when one needs to create flags based on some constraint. we all are familiar whit the basic of IF .we use this advance excel IF function to create a new field on some existing field constraint . but what if we need to consider multiple columns

E.g., in the below case, we want to flag all the employees whose salary is greater than 50,000. But “Employee ID” is greater than 3.

FORMULA (IF) =if (logical text,value if true ,value if false )

FORMULA (AND) =logical expression 1,logical expression 2, ….)

We would use the IF AND formula in such cases. Please find below the screenshot for the same.

It would return the result as 0.

We can have many conditions or constraints to create a flag based on multiple columns using AND.

4. SUM IF

Filter the required data based on certain or condition and finds the sum of the returned numeric value.

E.g. we can find the total salaries of the employee whose employee IDs under specific criterion .

FORMULA (sum if) =sum if(range,criterion,[start number])

Let us enter the SUMIFS formula :

The formula returns the results as 322000.

We can also count the number of employees in the organization having an employee ID greater than 3 using COUNTIF instead of SUMIF.