What is Function in MS Excel - A Beginner's Guide

Sienna Roberts 14 December 2022

Want to take your Microsoft Excel skills to next level? Read this blog to learn how to use the Functions in Excel, its components, importance and more.

Home

Resources

Office Applications

What is Function in MS Excel - A Beginner's Guide

Training Outcomes Within Your Budget!

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

Share this Resource

Table of Contents

Related Courses

What is Function in MS Excel

Microsoft Excel for various purposes. Excel can be used to store, arrange, compute and analyse data. It is also used to represent data in graphs. Excel provides you with several formulas and functions to perform complex calculations. These Excel Formulas and Functions are pre-defined formulas that assist in mathematical, statistical, and logical operations.

Read this blog to understand Functions and how they can be used to perform various kinds of calculations.

Table of Contents

1) What is Function in MS Excel?

2) How to Insert a Function in Excel

3) What are the most basic Excel Functions?

4) List of Numeric Functions

5) List of String Functions

6) Date and Time Functions

7) What is VLOOKUP Function?

8) Components of a Function in Excel

9) The Importance of Functions

10) Conclusion

What is Function in MS Excel?

In Excel, you can type various formulas to perform your required calculations. But you must know that Excel provides easily accessible pre-set formulas, and you don’t have to type them manually every time you need a formula. These are called Function in Excel. Function serves as the building block while working with data in Excel.

Functions can be used to compute values in a range of cells. Before working with Functions, you must understand the various components of a Function.

How to insert a function in Excel

The easiest way to execute a Function in Excel is by typing the Function's name with an equal sign (=) at its beginning. Let's first understand with an example how we can insert Functions in an Excel spreadsheet.

If you want to ‘add’ the values of the cells, use SUM. You can do this by typing = and then typing SUM. Further, use brackets to enter the cell value or refer to the cell directly. Close the bracket and press enter. For example, =SUM (A1:A5).

There are certain things to keep in mind while applying formulas in a table:

Using double quotes: while it is essential to enclose texts in the Excel formulas in quotation marks; however, one should never use them with numbers unless you want Excel to treat numbers as text.

Using formats: the input of the Excel formula should be done without any formatting. For example, use a decimal or a Dollar sign. If you need to remove table formatting or Change Date Format from the input, make sure to do so before using the Excel formula. If you need to Remove Table Formatting or change date format from the input, make sure to do so before using the Excel formula.

Matching opening and closing parentheses: if you create a formula using more than one Function, use more than one set of parentheses. Also, pair the parentheses properly to get the desired result, i.e., close and open the parentheses carefully.

Copying the same formula: you need to type the same formula for every cell; copy the formula to adjacent cells. Drag the fill handle and position the mouse pointer to the fill handle. Finally, copy the formula for the whole column. Remember to check the cell references.

What are the most basic Excel Functions?

The most common use of functions is to find the sum, average, maximum and minimum values of a range of cells. Let's see what the other basic Excel Formulas used most often are:

FUNCTION	DESCRIPTION	USAGE
SUM	Adds all the values in a range of cells	=SUM(B4:B12)
MIN	Finds minimum value in a range of cells	=MIN(B6:B16)
MAX	Finds maximum value in a range of cells	=MAX(B2:B18)
AVERAGE	Calculates average value in a range of cells	=AVERAGE(B14:B18)
COUNT	Counts number of cells in a range of cells	=COUNT(B3:B12)
LEN	Returns the number of characters in a string text	=LEN(B10)
SUMIF	Adds all values in a specified criteria in a range of cells	=SUMIF(B2:B18,”>=100″,C3:C12)
AVERAGEIF	Calculates the average value in a specified criteria in a range of cells	=SUMIF(B2:B12,”>=100″,C2:C6)
DAYS	Returns the number of days between two different dates	=DAYS(B4,D4)
NOW	Returns the current date and time	=NOW()

Sign up now to learn Microsoft Excel more effectively with Microsoft Excel Course!

List of Numeric Functions

Here we will discuss some Functions that deal with numeric values:

Function	Description	Usage
ROUND	Rounds off a value in decimals to particular number of points	=ROUND(3.14455,2)
POWER	Result will be the number raised to a power	=POWER(4,2), here the number is 4 and the power is 2
MOD	Result will be the remainder when you divide two numbers	=MOD(12,4)
RAND	The result will be a random number between 0 and 1	=RAND()
ISNUMBER	The result will be ‘true’ if the supplied value is numeric and ‘false’ if it is not	=ISNUMBER(D4)
ROMAN	The result will convert a number to roman numerals	=ROMAN(348)
MEDIAN	The result will be the middle number from a given set of numbers	=MEDIAN(4,2,6,4,7,3,6)
PI	The result will be the value of a math function - PI(π)	=PI()

List of String Functions

Here we will discuss the Functions that are used to manipulate text data. These Functions are called string Functions and are described below:

Function	Description	Usage	Explanation
LEFT	The result will be specified characters from the start, I.e., from the left side of a string	=LEFT(“STAR24”,4)	It will give the 4 characters from left, I.e., STAR
RIGHT	The result will be specified characters from the end, I.e., from the right side of a string	=RIGHT(“STAR24”,3)	It will give the 3 characters from right, I.e., R24
MID	The result will be specified characters from the middle of a string, I.e., from a specified start position and length	=MID(“STAR24”,2,3)	It will give the 4 characters from MIDDLE, I.e., TA R2
ISTEXT	The result is ‘true’ if the given parameter is text	=ISTEXT(value)
FIND	The result will be the starting position of a text string within another text string. Also, this Function is case-sensitive.	=FIND("AR","STAR24",1)	It will give you the position of the character AR calculating from the start that is 1^st position
REPLACE	The result will replace a part of the string with another specified string	=FIND("STAR24",2,2,vv)	It will replace from the second character, I.e., TA and with vv

Are you interested in building upon your basic Excel Skills? Then join Microsoft Excel VBA and Macro Training now!

Date and Time Functions

The following Functions manipulate date values in the specified cell range.

Function	Description	Usage
DATE	The result will give you the number that represents date in the excel code	=DATE (2021,4,9) Here, it will give you the date as (date, month, day) 09/04/2021
DAYS	The result will give you the number of days between two different dates	=DAYS(B7,F7)
MONTH	The result will give you the month from a date value	=MONTH(“09/04/2021”)
MINUTE	The result will give you the minutes from time value	=MINUTE(“02:46”)
YEAR	The result will give you the year from a date value	=YEAR(“09/04/2021”)

What is VLOOKUP Function?

VLOOKUP Function is used for vertical look-up in the left-most column and gives a value in the same row from the column specified. To understand this better, let's look at the following table. It has a serial number and items assigned to it in the adjacent column. Here, VLOOKUP can be used to find the item description of the specified serial number.

VLOOKUP

Here, we used the Function =VLOOKUP(C16,B2:C7,2,FALSE), where C16 specifies that value that should be looked up, B2:C7 species the cell range, 2 defines the column number and row value that the VLOOKUP method will return and FALSE tells that we need the specified lookup value.

Components of a Function in Excel

It’s now time to understand how to work with Functions. Understanding Function includes understanding the different components of Functions and how to create arguments.

A Function is always written in a particular order, called syntax. The syntax starts with an equal (=) sign for any Function, then the Functions name like SUM or MAX, and the required arguments. Therefore, the two components of a Function are the Function name and arguments.

Components of a Function in Excel

Arguments refer to both individual cells and the range of cells. This must be closed with parentheses or brackets to be called an argument. A Function can have one or more arguments depending on the required syntax for the calculations. Here is a list of the types of arguments:

1) Required: this argument is compulsory for the users to specify, otherwise a Function will not be able to calculate results.

2) Option: an optional argument is skippable. In simple terms skipping these will not hamper the calculations.

3) No arguments: these include Functions such as NOW where users need not specify any argument.

For example, =SUM(B2:B6), here this Function contains only one argument that will give the sum of values from A2 cell to A6 cell.

Tables

You will need multiple arguments if you need to find sum values for different rows and columns at once. A comma will separate these arguments. Let's say you need to add values in five arguments then = SUM(B2:B6, C2:C6, D2:D6, E2:E6, F2:F6).

The Importance of Functions

Functions in Excel are responsible for handling extensive amounts of data. All computations, processing and analyses take place using the Functions. Merely typing a Function can calculate hundreds of cells.

For example, if you want to add 59 cells, using the SUM formula would make it easier to calculate and add the cell values of all 59 cells in one go. So, you see that Functions are essential to Excel as they increase user productivity.

Conclusion

In this blog, we have discussed everything you will need to know about Functions in Excel, which are essential for many Jobs with Excel Skills. You can excel at any job if you learn how to manipulate data using the above Functions.

Are you interested to learn and create advanced formulas and macros in MS Excel? Sign up for Microsoft Excel C ourse Now!