Excel is a useful tool for everyone, from making a simple list to doing complicated math, there is nothing you can’t do on this handy application. Among all the options that this tool has, Excel functions are the most useful ones because they let you create your form or analytical report more interactive and accurate. Here is the list of the most common Excel functions.
If you want to know how many cells contain data in your spreadsheet, you can use the COUNT function. However, you should know that the simple COUNT function only checks the cells for numeric data, so if the cells contain text or formula, they’ll not be counted.
To count all the cells in a range containing any type of data (text, numbers, formula, etc.), you can use the COUNTA function, which counts all the cells except the blank ones.
In case you are looking to count the blank cells, the blank cells, you can use the COUNTBLANK function.
There is another counting function which helps you count the cells that have a specific condition. Using the COUNTIF function, you can set a condition and count specific cells having that condition. For example, you want to know how many cells contain a particular text such as “Good”, or how many cells have numbers that are higher than “7”. So, you have two parts in this type of function, one specifies the range and the other specifies the condition. The formula will be as follow:
The result of all types of count functions will be a number, which will be displayed on the cell that contains the formula.
Please note that the COUNT function is different from the SUM function, which will be explained next.
One of the most common and frequently used functions in Excel is the SUM function, which helps you easily calculate the numeric data in cells. The simplest way to calculate numbers in a row or a column is by clicking on AUTOSUM on the Home tab. However, it’s also possible to calculate numbers that are in different rows using the SUM formula and “+” sign, check out the following formula:
In this method, you only need to select the cells that you want to calculate.
Excel AND Function
AND function is a logical function that checks multiple statements. If all conditions are TRUE, it will return “TRUE”, but even if one condition is false, the result will be “False.” Check the following statement:
AND (A2>10, A2<20)
It means it returns TRUE if A2 is 15, but it will return FALSE if it is 21.
AND function can be used in combination with other functions such as OR, IF. Since it can check multiple conditions, it’s mostly like the IFS function and they can be used interchangeably. So, if your version of Excel is old and you don’t have an IFS function, you can use the AND function as the logical test inside the IF function to avoid extra nested Ifs.
This is one of the extremely useful and also simple tools which helps you find the average value between a range of numbers. The function has several functions in its heart because generally finding the average value needs various processes. Usually, the numerals are calculated first and will be divided into the number of cells. But, with the Average function, none of the steps are needed because it does all the steps in a blink of an eye.
Like all other Excel functions, the AVERAGE function starts with an “=” sign in the cell or the address bar. Then you can add the formula by writing the word AVERAGE and opening parenthesis. The AVERAGE function needs two or a range of values, which can be selected in the spreadsheet. Check the following example:
The AVERAGE function also has different types which are AVERAGEIF, AVERAGEA, and AVERAGEIFS.
This function has a more complicated usage. It receives a number and returns a character. Generally, each character has a numerical code between 0-255. Therefore, the CHAR function turns ASCII code into its characters. This function is used for characters that are hard to enter into a formula.
The opposite side of the CHAR function is the CODE function, which receives the character and returns its CODE, such as the following example:
One of the reasons that make Excel so wonderful is because you can check cells for different conditions. Up until 2016, you could only use the IF function, which checked only two conditions that had a TRUE or FALSE result. For example, it was only possible to check if the data in the cell is greater than 10.
In the latest versions of Excel, from its 2016 version onwards, Excel introduced a new conditional function called the IFS function. Now it’s possible to do more complicated conditions using the IFS function. For example, to check if the data is greater than 10 and at the same time smaller than 20. Therefore, the result will only return data in this range. In fact, the IFS function can have more than two conditions, it covers up to 127 statements.
The MATCH function can help you find a certain value in a range of cells and return its position. This function has three sections; the first is the value you want to find and its match, which is usually a text. Second, you need to mention in which cells you want to find that match, which can be done by selecting the range of cells. The last part is the match type, which means you want to find the exact match or the closest match. It can be 1,0 or -1. The most frequently used option is 0, which is used to look for the exact match. Let’s see the result in the following example:
The result is 3, which means the exact match for “dress” is found on the third cell between B4 to B10.
One way to search for a value in a column and return a value from a different column in the same row is using the VLOOKUP function. Consider you have a table of your products,their prices and number of items sold, you want to find the price of one of the products. VLOOKUP helps you write a formula that can do this search for you. VLOOKUP stands for Vertical Lookup, which means, it does the search in vertical form (or in the column you mention in the formula). Let’s see the formula below and the result it returns.
In this simple example, we want to know the price of a product, so we write it in a separate cell. Then, we write the formula using VLOOKUP in another cell. VLOOKUP contains different parts, first, we need to mention the cell we want to read its value, in this example, it’s C13. Next, the range that we want to read and find the mentioned value should be selected, in this example, it’s the whole table from B4 to D10.
In the next part of the formula, we need to make it clear which column should be read. In our example, it’s the third column of the range that has been selected. If we want to find the Amount, we need to input 2 instead of 3. Then we have TRUE and FALSE, which are used when we want to read the Exact Match or Approximate Match of the value,as you can see above the result is $50.
Rounding numbers is a common function in Excel, especially when we do calculations that might have some decimal digits. There are different types of rounding a number in Excel, which is under the ROUND group. The simple ROUND function can round the mentioned number or the value in a cell to the specific number of decimal places. For example, if the number has two decimal places, we can round it to one, which means the number should have one decimal place so it will be rounded to the nearest number. See the following example:
In this example, we have rounded the number in D5 to 0. It means, we don’t want the result to have any decimal number so it will be rounded to the nearest number, which is 51. The positive numbers will round up the numbers and the negative numbers will do the opposite. There are other rounding options in Excel, the most common ones are ROUNDUP, ROUNDDOWN, MROUND.
This function is used to gather the values of different cells together and return them to a different cell. CONCATENATE function is very simple yet very effective because it can combine several columns or fields. Assume you have a table of your customers’ addresses, which have been separated into different cells. You can combine the information of each cell to make a complete address. Let’s check that in the following example:
You only need to add a SPACE between each cell so the values won’t stick together and become more clear to read.
Excel has a handful of functions that are used in different situations. Some are more commonly used, while others may have more advanced uses. It would be extremely helpful if you learn to use the more basic and frequently used Excel functions so that you can easily improve your overall business productivity.
Our experts will be glad to help you, If this article didn't answer your questions.
We believe this content can enhance our services. Yet, it's awaiting comprehensive review. Your suggestions for improvement are invaluable. Kindly report any issue or suggestion using the "Report an issue" button below. We value your input.
Contact us today at – and speak with our specialist.
BSUPERIOR SYSTEM LTD. provides custom software and technology solutions to small and medium sized businesses. We have been helping businesses to operate more efficiently, reduce money and time spend on redundant repetitive daily tasks.