Question 1. What Is The Order Of Operations Used When Evaluating Formulas In Excel?
Following is the order of precedence in Excel formulas:
- Parentheses (Brackets)
- Exponentiation (^)
- Multiplication or Division – both have equal precedence and is evaluated based on whichever comes first.
- Addition or Subtraction – both have equal precedence and is evaluated based on whichever comes first.
An easy way to remember this is by the acronym PEMDAS – which is the first alphabet of each operator.
Question 2. What Is The Difference Between A Function And A Formula In Excel?
A formula is a user-defined expression that calculates a value. A function is pre-defined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it.
For example, =A1+A2 is a formula and =SUM (A1:A10) is a function.
Question 3. What According To You Are The Top 5 Functions In Excel?
While there are 450+ functions in Excel and there is no set criterion to select the top five, here are the ones I believe are worthy:
- INDEX / MATCH
I have chosen the above functions as these are not very basic and are quite useful for someone who does analysis in Excel. You can also consider the following functions – SUMPRODUCT, TEXT, SUM, AVERAGE, and LEN/LEFT/RIGHT/MID.
Again, there is no right or wrong answer to this. Just make sure you know the functions that you mention.
Question 4. What Is The Difference Between Absolute And Relative Cell References?
In Excel reference refers to a cell reference – such as A1 or range reference – such as A1:A10.
Relative References: These are cell references that change when you copy and paste the formula that has the references. To give you a simple example, if you put =A10 in cell A1, and then you copy cell A1 and paste it in cell A2, the reference would change to A11. This happens as this is a relative cell reference and it changes relative to the cell its copied from.
Absolute References: These are the references that remain the same and don’t change copy and paste the formula that has the references. For example, if you put =$A$10 in cell A1 and then copy cell A1 and paste it in cell A2, the reference would still remain $A$10. The $ sign before the column alphabet and the row number makes it absolute.
Question 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).
#NAME ERROR: 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.
Question 6. How Can You Tackle Errors When Working With Excel Formulas?
There are various ways you can tackle the errors in Excel:
- You can highlight the errors using conditional formatting. This requires using the ISERROR function in conditional formatting.
- You can use the IFERROR function in Excel to get a specific value in case the formula returns an error.
- You can use ISERROR to get TRUE in case there is an error and FALSE in case there is not.
- You can use IFNA function to tackle the #N/A error.
Question 7. Which Function Would You Use To Get The Current Date And Time In Excel?
The following functions can be used:
TODAY () – This function takes no argument and would return the current date value.
NOW () – This function takes no argument and would return the current date and time value.
Remember that that dates and times are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates.
Question 8. How Can You Combine The Text From Multiple Cells Using A Formula?
To combine text from different cells, you can use any one of the following three methods:
TEXTJOIN function – If you’re using Office 365 subscription, you will have the TEXTJOIN function available in your version. Click here to read how it works.
CONCATENATE function – If you want to combine values in cell A1 and A2, you can use the formula =CONCATENATE (A1, A2).
Ampersand (&) operator: This works just like the CONCATENATE function. To combine text strings in cell A1 and A2, use the formula =A1&A2.
Question 9. What Formula Would You Use To Find The Length Of A Text String In A Cell?
You can find the length of a string in a cell using the LEN function.
For example, if you want to know the length of the string in cell A1, you can use the formula =LEN (A1).
Question 10. What Is The Syntax Of The Vlookup Function?
VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel questions that I have seen in interviews.
Here is the VLOOKUP syntax:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
- table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
- col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
- [range_lookup] – Here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match.
If you’ve time, I recommend going this VLOOKUP function guide I created with 10 practical examples.
Question 11. How Would You Get Rid Of Leading / Trailing / Double Spaces In A Text In Excel?
To get rid of leading, trailing, and double spaces, you need to use the TRIM function.
For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula:
Note that it doesn’t remove single spaces between words.
Excel TRIM function does a good job in removing spaces in Excel; however, it fails when you have non-printing characters (such as line breaks) in your data set. To remove non-printing characters, you can use a combination of TRIM and CLEAN functions.
If you have some text in cell A1 from which you want to remove spaces, use the below formula:
=TRIM (CLEAN (A1))
Question 12. What Are The Known Limitations Of The Vlookup Function?
The VLOOKUP function is mighty useful, but it also has a few limitations:
It can not 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.
Question 13. When Would You Use The Subtotal Function?
When you’re working with tabular data, you can use the SUBTOTAL function to get variety of subtotals – such as AVERAGE, COUNT, MAX, MIN, and STDEV.
One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only.
Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too.
Question 14. What Are Volatile Functions? Can You Name A Few?
A volatile function calculates the formula again and again (whenever there is any change in the worksheet). This can slow down the workbook considerably.
A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculate. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing.
Here is a list of volatile formulas:
Highly volatile: RAND (), NOW (), TODAY ()
Almost volatile: OFFSET (), CELL (), INDIRECT (), INFO ()
VBA For Excel Interview Questions
VBA For Excel Tutorial
Microsoft Excel Interview Questions
Microsoft Excel Tutorial
Excel Data Analysis Interview Questions
Excel pivot tables Interview Questions
Advanced Excel Charts Tutorial
Excel Power View Interview Questions
VBA For Excel Interview Questions
Advanced Excel Functions Tutorial