Top 14 Basic Excel Functions – A Complete Guide

You would have surely used Microsoft Excel to make your tasks easier, whether a student or a professional. But this may seem challenging for those who don’t know how to use Basic Excel Functions. Excel is an excellent data visualisation tool used to derive meaningful business insights and is considered an essential Excel skill for business professionals.  

You can use Excel for simple calculations, like adding values and performing even the most complex calculations. Basic Excel Functions act as building blocks to perform these calculations in just a few clicks. In research conducted by Statista, more than 86% of respondents admitted to using Excel tools to achieve their everyday tasks at their jobs. This indicates the importance of learning Excel Formulas and Functions if you want to Excel at your profession.

Therefore, we have brought this blog to help you learn the 14 most popular Excel functions with examples. Read this blog to learn how to use the Basic Excel Functions. Let's delve in to learn more!

Table of Contents

1) Basic Excel Functions and Formulas

      a) SUM Function  

      b) Average Function  

      c) COUNT Function  

      d) COUNTA Function  

      e) MAX and MIN Function  

      f) ROUND Function 

     g) TRUNK Function  

     h) MEDIAN Function  

     i) IF Function  

      j) LEN Function  

2) Conclusion|
 

Basic Excel Functions and Formulas

In Microsoft Excel, Formulas and Functions are integral components for performing calculations and data analysis. Formulas are user-defined expressions that execute calculations by combining mathematical operations, cell references, and Functions. For example, a Formula like "=A1+B1" adds the values in cells A1 and B1. 

On the other hand, Functions are pre-built operations designed to simplify complex tasks, offering efficiency in spreadsheet calculations. An illustration of this is the SUM Function, which adds up values in a specified range, such as "=SUM(A1:A5)," summing the values in cells A1 through A5.

In essence, Formulas and Functions empower users to automate computations, manipulate data, and streamline the analytical processes within Excel. Let's look at the top 14 Basic Excel Functions:

1) SUM Function  

The SUM Function in Excel adds values in one or more cells or all the values in a cell range. The syntax for SUM Function starts with an equal (=) sign, then the functions name SUM and you have to include cell reference or range in each argument.  

In the example give below, we are adding the quantity of all items in the cell range B2 to B11, where B is the column, and 2 and 11 is the row number in the B column. Select the cell where you need the answer and type the formula =SUM(B2:B11).

2) Average Function 

As the Function's name indicates, AVERAGE Functions is used to find the average values in two or more cells or a cell range. The syntax is similar to the SUM Function, starting with the equal sign (=) sign and then AVERAGE and cell reference.   

For example – In the same table, to find the average of all quantity values between B3 to B7, select all the values between the 3rd and 7th row in the B column to find the average. 

3) COUNT Function 

Let's say in an extensive data set; you need to find how many cells contain numeric values, then you can use the COUNT Function. This function will make this significantly easier for you than manually counting all the cells. This Function also starts with = sign, function name – COUNT and then the cell reference in arguments. In the image below we will find how many cells between A1 to B11 contain numeric values. 
 

4) COUNTA Function   

Like the COUNT Function, this function will counts how many cells contain any values. These values can be numeric or alphabetical.   

For example, in the table given below we are finding how many cells between A1 and B11 contain values using =COUNTA(A1:B11). 

5) MAX and MIN Function   

MAX Function can be used to find the maximum value in a range of cells, and the same goes with MIN Function. The syntax for MAX and MIN Functions starts with = sign, function name, and cell reference.  

For example, you can find the maximum and minimum value between B4 to B8 using =MAX(B4:B8) and =MIN(B4:B8) 

a) MAX Function  

Here's how MAX Function works in Excel:

b) MIN Function 

Here's how MIN Function works in Excel:

6) ROUND Function 

The ROUND Function can be used to round off a decimal number in your data in Excel. The syntax starts with = sign, function name and then the number you want to round off.  For example, using the following formula to round off 5.76 to one decimal place. 

7) TRUNC Function  

As the name suggests, TRUNC Function is used to truncate numbers. You can use this Function to remove decimals from the number instead of rounding it. The syntax starts with = sign, function name and then enter the digits in the brackets. In the following image truncated 12.5. So the result will be the number 12, eliminating the digits after decimals. Use the syntax =TRUNC(12.5) in your sheet. 

8) MEDIAN Function   

If you want to know the middle value between the maximum value and minimum value in the data set, use the MEDIAN Function. The syntax order is similar, starting with an equal (=) sign, Function name, and cell reference. This Function will give you the middle value in the cell range.   

To find the middlemost value between the maximum and minimum value in our data by use =MEDIAN(D1:D10).

Get expertise in data visualisation, register for our Microsoft SQL Server Training! 

9) IF Function  

IF Function is used to perform a logical test in Excel. The IF Function checks if a condition is met and gives one value if the result is true or false.  

For example – in the following image, we are checking if the number in the D2 cell is greater or smaller than 42. If the number in D2 is greater, we will get the result as ‘N’, and if it is not, we will get the result as ‘M’. 

For example - Here we are considering the number 48, which is greater than 42, we will use =IF(D2>42,"N","M") and get the result as ‘N’. 

Now, we will consider the number 39, smaller than 42. We should get the result as ‘M’, which is the value for false. This fails the logical test. 

10) LEN Function  

This Function counts the length of characters in a cell or cell range. Remember that even the spaces between the letters are counted as characters.  

For example – First, let's see how LEN Function works in a single cell. 

Now, let's see a range of cells. Here the syntax will remain the same we will only give the complete cell range details.

You can use the complete cell range or just the fill handle to get the characters of data below the one already found.

  
Build your career as a Data Analyst and learn to prepare tables, charts, sheets and reports with our Data Analysis Training using MS Excel!

NOW Function  

If you have wanted to write the current time or date in your spreadsheet, the easiest way is to use the NOW Function. Just put the syntax =NOW(), and you will have the exact date and time. If you want to move a few days ahead and check the date, add that many numbers to the syntax. For example, for today’s date and time use the following syntax:

If you have to see the date seven days later, just type =NOW()+7 

12) TRIM Function  

If you have copied something and pasted it into an Excel cell, you would have noticed it contains extra spaces. If you want to eliminate these spaces between text, you can use the TRIM Function. The syntax for TRIM Function is =TRIM(cell reference)  

Here's how you can eliminate the extra space between Kate and Andrew: 

13) PRODUCT Function

The PRODUCT Function helps us in case we need to multiply the values in two or more cells. Just put the syntax as PRODUCT(value1, value2,...) in order to use the function. For example – if you are looking to calculate Andrew’s yearly income based on his hourly wage, use this formula:
 

14) COLUMN and ROW Function

The COLUMN Function returns the column number of a given cell reference. For example, the formula =COLUMN(A1) return the value 1 since Column A is the first column in the spreadsheet.
 

Similarly, the ROW Function returns the row number of a given cell range. For example, the formula =ROW(A1) returns the value 1 since Row A is the first row in the spreadsheet.
 

Conclusion 

The 14 Basic Excel Functions discussed in this blog will help you succeed in any profession. Learning Excel is always a good use of time, as you only get more proficient at completing your tasks faster. If you’re interested in a job role that deals with data and calculation, you must learn to use these Excel functions. Understanding the difference between Function and Formula in Excel can greatly enhance your ability to analyse and manipulate data effectively.

Want to learn how to create advanced formulas and macros in MS Excel? Sign up for Microsoft Excel Expert Training!