4 avg. rating (80% score) - 2 votes
Following is the order of precedence in Excel formulas:
An easy way to remember this is by the acronym PEMDAS - which is the first alphabet of each operator.
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.
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:
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.
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.
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.
There are various ways you can tackle the errors 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.
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.
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).
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])
If you've time, I recommend going this VLOOKUP function guide I created with 10 practical examples.
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))
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.
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.
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 ()
Excel Formulas Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Advanced Excel Charts Tutorial||Advanced Excel Functions Tutorial|
|Excel Dashboards Tutorial||Excel Data Analysis Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Formulas Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Excel Data Analysis Interview Questions||Excel pivot tables Interview Questions|
|Excel Power View Interview Questions|
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.