Must know functions before you attend Excel Interview questions

Tired of spending hours on tedious data entry and struggling with complex formulas? Say goodbye to Excel frustration. Transform yourself into a data manipulating wizard with our top 10 must-know formulas! Start by skyrocketing your productivity with these must-know formulas. For you to know more, here we are breaking down the top 10 Excel formulas that you need to know to streamline your work and boost your productivity. From Average Function to VLOOKUP and Drop down lists, we'll explain each formula in simple, easy-to-understand terms. Don't let Excel hold you back any longer - learn these formulas. Don't miss out on this opportunity to take your Excel skills to the next level! 

Topics covered in this

1. Average Function
2. Autosum
3. Sum If
4. Count
5. Counta 
6. Count If Function
7. Concantenate
8. If logical function
9. Vlookup
10. Drop down lists

1. Average Function

The AVERAGE function in Excel is a built-in function that calculates the average of a range of cells. The basic syntax of the AVERAGE function is "=AVERAGE(range)", where "range" is the range of cells that you want to calculate the average for.

For example, if you have a range of numbers in cells A1 to A10, you can use the formula "=AVERAGE(A1:A10)" in an empty cell to calculate the average of the numbers in that range.

2. Autosum

The AutoSum function in Excel is a quick and easy way to sum a range of cells. When you select a range of cells and then click the AutoSum button on the Home tab, in the Editing group, Excel will automatically detect the range of cells that you want to sum and will create a formula to add them up. 

For example, if you have a range of numbers in cells A1 to A10, you can select the cell below A10, then click on the AutoSum button, and Excel will automatically create the formula "=SUM(A1:A10)" in the selected cell, which will give you the sum of the numbers in that range

3. Sum If

The SUMIF function in Excel is a built-in function that allows you to sum a range of cells based on a certain criteria. The basic syntax of the SUMIF function is "=SUMIF(range, criteria, [sum_range])", where "range" is the range of cells that you want to evaluate, "criteria" is the condition that the cells in the range must meet, and "sum_range" is the range of cells that you want to sum.

For example, if you have a range of numbers in cells A1 to A10, and a range of corresponding text in cells B1 to B10, you could use the formula "=SUMIF(B1:B10, "Apples", A1:A10)" to sum up the numbers in cells A1 to A10 only for the rows where the text in cells B1 to B10 is "Apples".

4. Count 

The COUNT function in Excel is a built-in function that counts the number of cells in a range that contain numbers. The basic syntax of the COUNT function is "=COUNT(range)", where "range" is the range of cells that you want to count. 

For example, if you have a range of numbers in cells A1 to A10, you can use the formula "=COUNT(A1:A10)" in an empty cell to count the number of cells in the range that contain numbers.

5. Counta 

COUNTA function is similar to COUNT function but it counts all the cells that contain any type of data, including text, numbers, and errors. The syntax for the COUNT function is COUNT(range) where range is the set of cells you want to count. This function will include cells with text, numbers, and errors. 

For example, if you have a range of cells from A1 to A5 and you want to count the number of cells that contain any type of data, you would use the formula =COUNT(A1:A5) or COUNT(A1:A5). This would return the number of cells in the range A1:A5 that contain any type of data. Note that the COUNT function will only count numbers and the COUNTA function will count all types of values.

6. Count If Function

The COUNTIF function in Excel is a worksheet function used to count the number of cells within a range that meet a specific criterion. The function takes two arguments: the range to count and the criteria to evaluate. The syntax of the function is as follows: COUNTIF(range, criteria) 

For example, if you wanted to count the number of cells in the range A1:A10 that contain the value "apple," you would use the following formula: =COUNTIF(A1:A10, "apple") The function can also be used to count cells that meet more complex criteria, such as those that are greater than a certain value or those that contain a certain string of text.

7. Concatenate

The CONCATENATE function in Excel is used to join two or more text strings together. The syntax for the CONCATENATE function is as follows: CONCATENATE(text1, text2, ...). Each text argument can be a text string, a cell reference containing a text string, or a formula that returns a text string. 

For example, if you have a cell A1 containing "Hello" and a cell B1 containing "World", you can use the formula =CONCATENATE(A1, " ", B1) to join them together and produce the text string "Hello World" in a new cell. Additionally, you can use the "&" operator instead of the CONCATENATE function. Like =A1 & " " & B1 will also give you the same result.

8. If logical function

The IF function in Excel is a logical function that allows you to perform a certain action if a certain condition is met, and a different action if the condition is not met. The syntax for the IF function is as follows: IF(condition, value_if_true, value_if_false). 

For example, if you have a cell A1 with a value of 100 and you want to check if the value is greater than 50, you can use the formula =IF(A1>50, "Greater than 50", "Less than or equal to 50"). This formula will return "Greater than 50" if the value in cell A1 is greater than 50, and "Less than or equal to 50" if the value is less than or equal to 50.

9. Vlookup

The VLOOKUP function in Excel is a lookup and reference function that allows you to search for a specific value in a table and return a corresponding value from a different column in the same row. The syntax for the VLOOKUP function is as follows: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

lookup_value: The value you want to look up. table_array: The range of cells that contains the data you want to search through. col_index_num: The column number in the table_array from which the matching value should be returned. 

[range_lookup]: A logical value that specifies whether you want an exact or an approximate match. (default is TRUE which means approximate match) 

For example, if you have a table of data in cells A1:C5, with a list of names in column A and corresponding ages in column B, and you want to look up the age of a specific person, you can use the formula =VLOOKUP ("John”, A1:B5,2,FALSE) to search for the name "John" in column A of the table, and return the corresponding age from column B. 

It's important to note that the table_array must be sorted in ascending order based on the first column, otherwise the function may return inaccurate results.

10. Drop down lists 

A drop-down list in Excel is a feature that allows you to create a list of items that can be selected from a drop-down menu in a cell. This is useful for creating data validation, making it easy to enter data in a consistent format, and reducing the risk of data entry errors. There are a few different ways to create a drop-down list in Excel. 

Data Validation: You can use the Data Validation feature to create a drop-down list in a cell. To do this, you need to select the cell or range of cells where you want to create the list, then go to the Data tab, and click on Data Validation. In the Data Validation dialog box, you can set the Allow option to "List" and then enter the list of items in the Source field, separated by commas. 

Named Range: You can create a named range for a list of items and then use that named range as the source for a drop-down list. To do this, you need to select the cells that contain the list of items, then go to the Formulas tab, and click on Define Name. In the New Name dialog box, you can give the named range a name and then press OK. 

Using a Table: You can also create a drop-down list using a table. To do this, you need to select the cells that contain the list of items and press "CTRL + T" to convert the data into a table. Then, you can select the column of the table, right click, and choose "Table Column Properties" then you can check the checkbox for "Allow in-cell dropdown" 

Once you have created the drop-down list, you can select the cell where you want to use the list, and a drop-down arrow will appear. Clicking on the arrow will open the list, and you can select the desired item from the list.

Conclusion

By mastering these top 10 must-know Excel formulas you can now take your productivity to new heights. These formulas given here are essential tools for any data manipulating wizard. Once you learn these formulas, you will be amazed at how much more effective and efficient you will be. Make the most of this opportunity to improve your Excel skills! You will be amazed at how much easier your life will be if you begin mastering these formulas today. Start your path to becoming a data manipulating wizard now! Join the Excel course at keySkillset.

Here, you have the free trial access to the keySkillet courses.

‍