Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

Table of Contents

Top 14 Basic Excel Functions

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).

Sum Function

 

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. 
 

Count Function

 

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). 

Counta Function
 

Microsoft Excel Masterclass
 

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:

Max function

b) MIN Function 

Here's how MIN Function works in Excel:

MIN Function

 

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. 

Round Function  
 

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. 

Trunc Function  

 

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).

Median Function 

 

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’. 

IF Function

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. 

/If function

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. 

Len Function.

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

Len Function  

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

Len Function
  
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:

Now Function

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

Now Function

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: 

Trim Function

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:
 

PRODUCT Function


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.
 

COLUMN and ROW Function


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.
 

Returns the Value
 

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! 

Frequently Asked Questions

How can beginners use Excel Functions to filter and sort data for better organisation? faq-arrow

Beginners in Excel can use Functions like FILTER and SORT to enhance data organisation. FILTER helps extract specific data based on criteria, while SORT arranges data in ascending or descending order. Simply select the data range and apply these Functions to streamline and organise information effectively.

Are there any time-saving shortcuts or tips associated with Basic Excel Functions? faq-arrow

Yes, there are shortcuts for Basic Excel Functions. Pressing Ctrl + Arrow Keys navigates to the edge of data ranges. Ctrl + Shift + "+" inserts cells, and Ctrl + Space or Shift + Space selects entire rows or columns. Additionally, Autofill (dragging the fill handle) copies formulas and patterns efficiently, saving time in repetitive tasks.

What are the other resources and offers provided by The Knowledge Academy? faq-arrow

The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide. 

Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
 

What is Knowledge Pass, and how does it work? faq-arrow

The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.

What are the related Project Management courses and blogs provided by The Knowledge Academy? faq-arrow

The Knowledge Academy offers Microsoft Excel Courses including Microsoft Excell, PowerPoint, Project Training. These courses cater to different skill levels, providing comprehensive insights into Minitab vs Excel

Our Office Application Blogs covers a range of topics related to Project Management Businesses, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Microsoft Excel skills, The Knowledge Academy's diverse courses and informative blogs have you covered.
 

Get A Quote

WHO WILL BE FUNDING THE COURSE?

cross
UP TO 40% OFF

NEW YEAR Big Sale

red-starWHO WILL BE FUNDING THE COURSE?

close

close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

close

close

Press esc to close

close close

Back to course information

Thank you for your enquiry!

One of our training experts will be in touch shortly to go overy your training requirements.

close close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.