Excel basic functions you should definitely know

The tutorial provides a list of Excel basic formulas and functions with examples.

Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. You can also compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees. All this is done by entering formulas in cells.

This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel.

The basics of Excel formulas:

Before providing the basic Excel formulas list, let's define the key terms. So, what do we call an Excel formula and Excel function?

Formula is an expression that calculates values in a cell or in a range of cells.

For example, =A2+A2+A3+A4 is a formula that adds up the values in cells A2 through A4.

Function is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters.

For example, instead of specifying each value to be summed like in the above formula, you can use the SUM function to add up a range of cells: =SUM(A2:A4)

You can find all available Excel functions in the Function Library on the Formulas tab:


There exist 300+ formulas and functions in Excel, and the number is growing by version to version. Of course, it's next to impossible to memorize all of them, and you actually don't need to. The Function Wizard will help you find the function best suited for a particular task, while the Excel Formula Intelligence will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell:



 Top Excel basic functions you should definitely know:

Below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel beginner to an Excel professional.

1.SUM

The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:

SUM(number1, [number2], …)

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:

=SUM(B1:B6) - adds up values in cells B1 to B6.

=SUM(B1, B6) - adds up values in cells B1 and B6.

If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by 5:

=SUM(B2:B6)/5

 



Tip: To see a Formula, double click on the cell.

2.SUMIF

To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria (A2:A6), in the 2nd argument - the criteria itself (D2), and in the last argument - the cells to sum (B2:B6):

=SUMIF(A2:A6, D2, B2:B6)

OR

=SUMIF(A2:A6,”Orange” , B2:B6) – Put direct condition in between “ ”. 

In your Excel worksheets, the formulas may look something similar to this:



3.AVERAGE

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average, or arithmetic mean, of numbers. Its syntax is similar to SUM's:

AVERAGE(number1, [number2], …)

The Excel AVERAGE function performs these calculations behind the scenes. So, instead of dividing sum by count, you can simply put this formula in a cell:

=AVERAGE(B2:B7)

4.AVERAGEIF

To average cells based on condition, use the following AVERAGEIF formula, where A2:A7 is the criteria range, D3 is he criteria, and B2:B7 are the cells to average:

=AVERAGEIF(A2:A7, D3, B2:B7)


5.MAX & MIN

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. For our sample data set, the formulas will be as simple as:

=MAX(B2:B7)

=MIN(B2:B7)


6.COUNT & COUNTA

If you are curious to know how many cells in a given range contain numeric values (numbers or dates), don't waste your time counting them by hand. The Excel COUNT function will bring you the count in a heartbeat:

COUNT(value1, [value2], …)

While the COUNT function deals only with those cells that contain numbers, the COUNTA function counts all cells that are not blank, whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

COUNTA (value1, [value2], …)

For example, to find out how many cells in column B contain numbers, use this formula:

=COUNT(B:B)

To count all non-empty cells in column B, go with this one:

=COUNTA(B:B)

In both formulas, you use the so-called "whole column reference" (B:B) that refers to all the cells within column B.

The following screenshot shows the difference: while COUNT processes only numbers, COUNTA outputs the total number of non-blank cells in column B, including the the text value in the column header.


7.IF

Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:

IF(logical_test, [value_if_true], [value_if_false])

For example, the following IF statement checks if the order is completed (i.e. there is a value in column B) or not. To test if a cell is not blank, you use the "not equal to" operator ( <>) in combination with an empty string (""). As the result, if cell B2 is not empty, the formula returns "Yes", otherwise "No":

=IF(B2<>"", "Yes", "No")


8. TRIM

If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells (You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets just until something goes wrong!).

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:

TRIM(text)

For example, to trim extra spaces in column A, enter the following formula in cell A2, and then copy it down the column:

=TRIM(A2)

It will eliminate all extra spaces in cells but a single space character between words:


9. LEN

Whenever you want to know the number of characters in a certain cell, LEN is the function to use:

LEN(text)

Wish to find out how many characters are in cell A2? Just type the below formula into another cell:

=LEN(A2)

Please keep in mind that the Excel LEN function counts absolutely all characters including spaces:


10. AND & OR

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

AND returns TRUE if all conditions are met, FALSE otherwise.

OR returns TRUE if any condition is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger formulas.

For example, to check the test results in columns B and C and return "Pass" if both are greater than 40, "Fail" otherwise, use the following IF formula with an embedded AND statement:

=IF(AND(B4>40, B4>40), "Pass", "Fail")

If it's sufficient to have just one test score greater than 40 (either test 1 or test 2), embed the OR statement:

=IF(OR(B4>40, B4>40), "Pass", "Fail")




11. CONCATENATE

In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function:

CONCATENATE(text1, [text2], …)

For example, to combine the values from cells A4 and B4, just enter the following formula in a different cell:

=CONCATENATE(A4, B4)

To separate the combined values with a space, type the space character (" ") in the arguments list:

=CONCATENATE(A4, " ", B4)


12. TODAY & NOW

To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:

=TODAY() to insert the today's date in a cell.

=NOW() to insert the current date and time in a cell.

The beauty of these functions is that they don't require any arguments at all, you type the formulas exactly as written above.


13. VLOOKUP

The VLOOKUP function below looks up the value 55 (first argument) in the leftmost column of the red table (second argument). The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

=VLOOKUP(G3,A3:D8,4,FALSE)


14. INDEX and MATCH

Use INDEX and MATCH in Excel to perform advanced lookups (if you're new to Excel, you can skip this formula). For example, use INDEX and MATCH to perform a two-column lookup.

=INDEX(D3:D8,MATCH(G2&G3,B3:B8&C3:C8,0))



15. SUMPRODUCT

To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's powerful SUMPRODUCT function.

=SUMPRODUCT(B2:B5,C2:C5)


Explanation: the SUMPRODUCT function performs this calculation: (1 * 20000) + (3 * 3000) + (5 * 9000) + (2 * 12000) = 98000. The SUMPRODUCT function is an extremely versatile function and can produce the same result as many built-in functions in Excel and even array formulas!