Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
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
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!
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.
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
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.
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.
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.
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.
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.
Upcoming Office Applications Resources Batches & Dates
Mon 4th Mar 2024
Mon 18th Mar 2024
Mon 8th Apr 2024
Mon 22nd Apr 2024
Mon 13th May 2024
Mon 3rd Jun 2024
Mon 17th Jun 2024
Mon 1st Jul 2024
Mon 15th Jul 2024
Mon 5th Aug 2024
Mon 19th Aug 2024
Mon 2nd Sep 2024
Mon 16th Sep 2024
Mon 7th Oct 2024
Mon 21st Oct 2024
Mon 4th Nov 2024
Mon 18th Nov 2024
Mon 2nd Dec 2024
Mon 9th Dec 2024
Mon 16th Dec 2024