Analytical Skills

1.What is the use of VLOOKUP and how do we use it?

The function VLOOKUP in Excel is used to look up information in a table and extract the corresponding data.

Syntax: VLOOKUP (value, table, col_index, [range_lookup])

value – Indicates the data that you are looking for in the first column of a table

table – refers to the set of data (table) from which you have to retrieve the above value

col_index – Refers to the column in the table from where you are to retrieve the value

range_lookup – FALSE = exact match [optional] TRUE = approximate match (default)

Shown below is an example of the VLOOKUP function.

2. How is VLOOKUP different from the LOOKUP function?

VLOOKUP	LOOKUP
VLOOKUP lets the user look for a value in the left-most column of a table. It then returns the value in a left-to-right way.	Meanwhile, the LOOKUP function enables the user to look for data in a row/column. It returns the value in another row/column.
It is not very easy to use as compared to the LOOKUP function.	It is easier and can also be used to replace the VLOOKUP function.

3. How does the IF() function in Excel work?

In Excel, the IF() function performs a logical test. It returns a value if the test evaluates to true and another value if the test result is false. It returns the value depending on whether the condition is valid for the entire selected range.

Let’s look at the below example:

As seen above, the IF function returns “Record is Valid” if age is greater than 20, and the salary should be greater than $40000. Else, it will return “Record is Invalid”. Here the final answer will be “Record is Valid” as the entire selected range qualifies both the conditions.

4. How do you perform a horizontal lookup in Excel?

To perform a horizontal lookup, you will have to make use of the HLOOKUP function.

SYNTAX:

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

here,

lookup_value gives the value to be looked out for
table_index is the range from where the data is to be taken
row_index_num specifies the row from which you want to fetch the value
range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)

EXAMPLE:

5. What are the different types of errors you can encounter in Excel?

When working with Excel, you can encounter the following six types of errors:

#N/A Error: This is called the ‘Value Not Available’ error. You will see this when you use a lookup formula and it can’t find the value (hence Not Available).
#DIV/0! Error: You’re likely to see this error when a number is divided by 0. This is called the division error.
#VALUE! Error: The value error occurs when you use an incorrect data type in a formula.
#REF! ERROR: This is called the reference error and you will see this when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row/column or worksheet that was referred in the formula).
#NAMEERROR: This error is likely to a result of a misspelled function.
#NUM ERROR: Number error can occur if you try and calculate a very large value in Excel. For example, =194^643 will return a number error.

6. What are the known limitations of the VLOOKUP function?

The VLOOKUP function is mighty useful, but it also has a few limitations:

It cannot be used when the lookup value is on the right. For VLOOKUP to work, the lookup value should always be in the left-most column. Now this limitation can be overcome by using it with other formulas, it tends to make formulas complex.
VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place.
When used on large data sets, it can make your workbook slow.

7. What is the difference between Vlookup and Index Match function?

Vlookup	Index Match
Vlookup can only be used for looking up values from Left to Right	Index Match can lookup the values from Left to Right as well as Right to Left
Vlookup only can lookup through vertical lines i.e, columns and not through rows	Index Match can lookup values through rows as well as columns
Vlookup has limit for lookup_value size. It should not be exceeding 255 characters	Index Match does not possess any limit of size or length for matching values.
Vlookup is easy to understand as well as remember in comparison with Index match	Index Match is hard to understand as well as remember due to its complex nature that combines two functions/ formula together

8. What is Relative cell referencing in Excel?

Relative cell referencing is used when dealing with formulas in Excel. If you write a sum formula to add the values of a set of cells (e.g A4 to A8) together, it will look like this: =SUM(A4:A8). If you use relative cell references, then when you copy this formula to a different section of the spreadsheet, the cells will change relative to where the formula has been pasted. For example, if you copy the formula across one column, the formula will become =SUM(B4:B8).

9. What is Absolute cell referencing ?

Absolute cell referencing is the exact opposite of relative cell referencing. By marking the row number and column letter with a $ symbol, you can make a cell reference fixed (or “absolute”). This means that when you copy and paste it to another cell or use AutoFill, the cell references will not change. The formula =SUM(A4:A8) will stay as =SUM(A4:A8) no matter where you put it.

10. What is the difference between COUNT , COUNTA, COUNTBLANK AND COUNTIF in Excel?

COUNT: This function counts how many cells within a specified range contain numerical data. It will ignore (not count) any cells that are blank or contain text or symbols only.
COUNTA: This function counts how many cells within a specified range contain data of any type. It will count all cells that are not blank.
COUNTBLANK: This function will count the number of blank cells within the designated range.
COUNTIF: This function will count only the cells whose value meets a certain condition specified by the user.

11. How would you highlight cells with duplicate values in it?

You can do this easily using conditional formatting. Here are the steps:

Select the data in which you want to highlight duplicate cells.
Go to the Home tab and click on Conditional Formatting option.
Go to Highlight Cell Rules and click on ‘Duplicate Values’ option.

12. What is a Pivot Table?

A pivot table is a tool in data summation that is common in many business software. It is utilized to collect the summary of a specific data set in a compressed technique. It is a very useful tool in consolidating a large quantity of data that is contained in Microsoft Excel. They let the user make a faster organization and drawing of conclusions from data being collected. Pivot table consists of columns, rows, pages, and data fields. These can be moved around and it assists in expanding, isolating, summarizing, and grouping the specific data. And all of these can be accomplished in real time.

13. Advantages and Disadvantages of Pivot Table?

Advatages of Pivot Table are: