Microsoft Excel Formulas and Functions

Microsoft Excel is widely considered as one of the best tools preferred and commonly used in every industry to organise, simplify, structure, and manage data across organisations. This application is probably used by every professional out there to generate reports and business insights. Therefore, it is crucial that you know the Basic Excel Formulas and learn how to utilise them for your projects.   

There are numerous keyboard shortcuts available in Excel that can be used to increase the organisation's productivity. A decent knowledge of Microsoft Excel can land you in jobs mostly related to the financial sector or accounting. This blog has discussed some of the most important Basic Excel Formulas to help you navigate this application. Read further to know more!

Table of Contents

1) What are Formulas in Excel? 

2) What are Functions in Excel?  

3) List of Basic Microsoft Excel Formulas

4) List of Basic Microsoft Excel Functions

5) Conclusion

What are Formulas in Excel?

Formulas in Excel can be considered as an equation. As equations state that two items are equal, Excel formulas start with ‘=’ all the time. The correlation between two or more variables is presented by a formula. 

A range of cells operated with an expression to derive a specific calculated value can also be a Formula. Using Formulas in Microsoft Excel, you can calculate addition, subtraction, multiplication, and division. Apart from these basic calculations, you can calculate percentages and averages, manipulate date and time, calculate text lengths, compatibility, and a lot more high-end actions. Calculations can be executed using this amazing tool.  
 

 

 

What are Functions in Excel?

From a general point of view, Functions in Excel are a type of Formula. The only difference is that they are pre-set to aid in carrying out mathematical, logical, and statistical operations. Basically, one or more functions can be contained within a Formula. 

Unlike Formulas that you must write yourself, Functions pre-exist in Excel. There are several Functions available in Excel that can make your calculation task easy. In addition to simple calculations like SUM or AVERAGE, functions also perform a variety of complicated calculations. 

Furthermore, you must realise that Functions are utilised within formulas but that formulas cannot be used in functions. A Function is always written using a certain syntax. For the Function to compute appropriately, the syntax must be written correctly. In a function, syntax always begins with a "=" sign.   

Build your career as a Data Analyst and learn the usage of Microsoft Excel for Data Analysis purposes with Data Analysis Training using MS Excel training. 

List of Basic Microsoft Excel Formulas

A Formula helps in the calculation of values in a selected range of cells or just a cell. Formulas in Excel can contain functions, references, operators and constants (a value that isn’t calculated).

1) SUM 

The SUM function is used in the formula to aggregate values from the selected rows or columns. It performs the mathematical operation of addition.

=SUM(num1, [num2], …)

The following is an example of using the SUM Function in Microsoft Excel:

2) MAX & MIN

The MIN and MAX functions help you find the minimum and maximum value from the certain selected cell range with numbers. 

=MIN(num1, [num2], …) 

=MAX(num1, [num2], …) 

3) AVERAGE 

The AVERAGE Function is used to derive simple Averages for the data. It follows the syntax:

=AVG(num1, [num2], …)

The following image is an example of using the AVERAGE Function to calculate the average of a selected set of values or numerical:



The above table shows that by entering the Function “=AVERAGE(C2:C4)”, it is possible to figure out the average of a specified set of values (C2 to C4 in this case).

4) COUNT 

The COUNT Function is used to Count the numeric values given in a certain cell range. It follows the syntax:

=COUNT(value1, [value2], …)

The following is an image that shows how the COUNT Function works with a string of text values and numerical values:



This image showcases the use of the COUNT Function to obtain the number of values that are numerical in nature. The result is obtained upon entering the Function “=COUNT(C1:C4)”, because all the values from C1 to C4 were the selected values.

5) COUNTA 

The COUNTA Function counts all the cells in the given range regardless of the type of cells. It follows the syntax: 

=COUNTA(value1, [value2], …)

The following image showcases the use of the COUNTA Function:



This image shows us how to use the COUNTA Function to obtain the result of the total number of values regardless of their type. Here, the Function “=COUNT(C1:C4)” has been used to count all the values from cell C1 to cell C4.

6) TRIM 

The TRIM Function removes all the empty spaces in the cell selected. It can operate in only one cell at a time; it ensures that your functions do not return with errors due to the extra spaces in the data. It follows the syntax:

The following image shows us the correct usage of the TRIM function:



Here, the TRIM Function has been used on the cell with the value ‘Total Sales’.  The Function “=TRIM(C1)” has provided us with the text in cell C1 without any extra spaces.

7) IF 

The IF function can help you sort the data according to the given logic. 

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

The following is an image that showcases one of the uses of the IF Function:


 

This image represents data regarding the number of vegetables currently in stock at a store.  The condition used here results in taking no orders or ordering for more.  The Function “=IF(B2<10,”Order”,” No action”)” has conditioned the table to show “No action” if the vegetable number is more than 10 and to show “Order” if the number is below 10.

8) POWER

The POWER Function in Excel is a mathematical function.  It produces the result of a number raised to a specific power.  It works with the base and the exponent.  The following is an example of how the Power Function works:

 

The image above contains values for the base number and power.  Using the Function “=POWER(A2,B2)”, the values A2 and B2 were selected as the base number and power, respectively, to obtain the desired result.  Upon dragging down the cell with the Function, the condition is also applied to the remaining cells.

9) CEILING

The objective of the CEILING Function in Excel is to round up to the nearest multiple of a given number.  It operates with instructions on the number that is to be rounded and the multiple to use when rounding.  It follows the syntax:

=CEILING(number, significance)

The following image shows an example of using the CEILING Function in Microsoft Excel:



The image displayed above shows how using the Function “=CEILING(A2,B2)” has helped provide our desired result. The values provided in cells A2 and B2 have been used to formulate the rounding up of the number in A2

10) FLOOR

 Contrary to the CEILING Function, the FLOOR Function in Excel is used to round down to the nearest multiple of the provided number.  It requires the number to be rounded and the multiple to refer to the rounding while operating.  It follows the syntax:

=FLOOR(number, significance)

The following image is an example of using the FLOOR Function in Microsoft Excel:



This image shows the steps required to round down a given number to the nearest multiple. According to the values provided in the above cells, the Function used to obtain the desired result is “=FLOOR(A2,B2)”.

11) LEN

The LEN Function in Excel helps in returning the length of a specified text string as the number of characters. It also helps count characters in numbers, spaces, and even special characters. The syntax it follows is:

=LEN(text)

The following image is an example of using the LEN Function in Microsoft Excel:



The image above shows us the correct way to use the LEN Function. By using the Function “=LEN(A2)”, you can figure out the length of the text or values provided in the cell ‘A2’. The text length of the following cells is also displayed above.

12) UPPER, LOWER, PROPER

The UPPER Function helps in the conversion of text strings to uppercase.  Similarly, the LOWER Function helps in the conversion of a selected text string to lowercase.  The PROPER Function converts text from a selected text string to the proper case.  In the proper case, the first letter of each word is capitalised, and the rest is in lowercase.

The syntax for UPPER Function: =UPPER(text)

The syntax for LOWER Function: =LOWER(text)

The syntax for PROPER Function: =PROPER(text)

The following image is an example of using the UPPER Function:
 

The image shows some random text that was conditioned to produce results in uppercase. Using the Function “=UPPER(A2)”, you can obtain results which are fully in uppercase. The same condition is applied to all the text values.

The following image is an example of using the LOWER Function:

The image above shows an example of converting certain words to lowercase. The Function “=LOWER(A2)” has been used to convert the text values in A2 to lowercase. The same condition has been applied to the rest of the values.

The following image is an example of using the PROPER Function:



The above image shows some random words being given the PROPER Function. It converts the given data into the appropriate result. The Function “=PROPER(A2)” was used to obtain result on text in cell A2.

Microsoft Excel Functions

Functions in Microsoft Excel are nothing but predetermined formulas that help in the calculation of selected values.  Functions must always be written in their correct syntax, or else they won’t work.  Another feature is that they can contain one or more arguments.  Following is the list of advanced Excel formulas that every financial analyst should know. 

Following is the list of advanced Excel formulas that every financial analyst should know.  

1) INDEX MATCH

The INDEX and MATCH Function, as the name suggests, is a combination of the INDEX and the MATCH functions.  The INDEX Function is a flexible FORMULA that provides the value of a specified location within a range.  

Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

The following image is an example of using the INDEX and MATCH Function:

In the above image, we have used data based on the names of salespeople in a company and listed their respective sales for each month.  The objective here is to find the sales number for the month of February for the salesperson ‘Jackson’.  As INDEX is used to retrieve a value within a range, we provide the values of B2:D7 with row 4 and column 2.

The MATCH Function is used to find the position we require. Nesting the MATCH Function within the INDEX, we use the final Formula “=INDEX(B2:D7,MATCH("Jackson",A2:A7,0),2)”.

2) CHOOSE

CHOOSE is a lookup and reference Function of Excel that provides a value from the given data range when a position is specified by the user.  It can be used for multiple sets of values or just a single set.

Formula: =CHOOSE(choice, option1, option2, option3)

The following image is an example of using the CHOOSE Function:

 

The image above is one that shows columns containing data about names, colours, and flowers.  The objective is to choose the third value from all of it.  The third value is within the range of “D2:D7”.  Therefore, the output of the Formula will be identical to the values in the range “D2:D7”.  The Formula used in this situation is:

 =CHOOSE(3,A2:A7,B2:B7,C2:C7)

3) IF combined with AND / OR

The IF AND/OR Formula is a result of the combination of two logical functions. The use of the AND Function characterises the presence of multiple conditions. This Formula can be used for any set of values.

Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7)

The following is an image that showcases the usage of the IF AND Formula with an example:


 

Here, data regarding fruits, the number of fruits in stock, and whether they are packed or not is provided.  The IF AND Function is applied using a combination of less than equal to (<=) and the equal to (=) text Function.  The Formula used in this situation is:

=IF(AND(B2<=5,C2="No"),"Check","")

As the “Output” column shows, the results on each cell follow the same condition.  There are two observations made from the results in the last column: 

a) The IF Function presents the result of “Check” only if the arguments entered in the AND Function are “true.”

b) The IF Function presents a blank string if either of the arguments in the AND Function is “false” or both the arguments are “false”.

4) OFFSET combined with SUM or AVERAGE

The OFFSET Function in Microsoft Excel returns the value of a cell or a range of adjacent cells from the reference point.  The reference point is the starting cell that has been provided to a Function as an argument.  

Formula: =SUM(B4:OFFSET(B4,0,E2-1))

The following image is an example of using the OFFSET Function with the SUM Function:


 

The image displayed above holds the data on five stock names and their maximum and minimum returns. The objective is to use the SUM Function on the maximum returns of the stock. Here, we use the OFFSET combined with SUM Function in the Formula:

=SUM(OFFSET(B2,0,0,6,1))

In this Formula, B2 is the reference point. The mention of two zeroes is to signify that the OFFSET Function won’t move up, down, right, or left. The height (number of rows) of the considered range is 6. This means that the OFFSET Function sums six rows beginning from cell B2. This includes the range B2:B7. The width (number of columns) argument is 1. This implies that the OFFSET Function will sum the values of column B.

5) PMT and IPMT 

The PMT Function is one of the financial functions used in Microsoft Excel.  It is an advanced Excel formula.  It is used to calculate monthly payment amounts against loan amounts. 

Formula: =PMT(interest rate, # of periods, present value) 

The Function is based on three compulsory parameters and two optional ones ([Fv], [Type]).  The compulsory parameters are: 

a) Rate: Interest rate for loan amount 

b) Nper: The total number of instalments for the loan amount is known as Nper 

c) Pv: The total loan amount or present value is known as Pv. 

The following image displays an example of the usage of PMT:

The above image uses examples of a loan amount of 25,000, an interest rate of 10% annually, and a period of 5 years.  The total number of payments is 60 (5*12=60).  Here, B2/12 is taken to obtain the monthly rate because the 10% rate is annual.  The Function used is: 

=PMT(B2/12,B3,B4) 

The result is shown as a negative amount because, ideally, the amount is credited from your bank.  If you add the negative sign before using the Function, then you can obtain a positive result.  The image below is an example of using the Function with a negative sign:

As you can see, the image above produces a positive result. The Function used for this is:

=-PMT(B2/12,B3,B4)

The IPMT Function is also a financial Function. It is used to find the value of interest of a certain payment. The syntax for IPMT is as follows:

=IPMT(rate,per,nper,pv,[fv],[type])

The following image is an example of using IPMT in Microsoft Excel:

In the image above, a loan of 150000 is taken for 10 years with an interest rate of 5%. The objective is to find the Interest using the IPMT Function. Here the Function used is:

=-IPMT(B1,B5,B2,B3)

6) XNPV and XIRR

The XNPV Formula makes use of certain dates that are linked to each cash flow which are being discounted in the given series. This is a better option over the regular NPV Function because the regular one always assumes the time periods to be the same. 

Formula: =XNPV(discount rate, cash flows, dates) 

The following image is an example of the XNPV Function that is used in Microsoft Excel to calculate the Net Present Value of a series of cash flows based on certain dates:

In this image, the discount rate is 10%, start date is June 30, 2018, and the time period between the start date and the initial cash flow is only six months. The Function for the XNPV is:

=XNPV(C2,C8:M8,C6:M6)

The XIRR Function in MS Excel is used to calculate the Internal Rate of Return (IRR) for a series of cash flows that are not likely to be periodic. It operates by assigning specific dates to each cash flow. The syntax is as follows:

=XIRR(values, dates,[guess])

The following image is an example of using XIRR in Excel:


 

The values considered in this example are based on five different cashflows and their corresponding dates. The result of 213.10% is obtained by making use of the XIRR Function:

=XIRR(B2:B6, C2:C6

7) LEN and TRIM

The LEN Function is used to count the character number in a string of letters, number, special characters, etc. The LEN Function is used alongside TRIM Function to exclude the leading and trailing spaces.

Formulas: =LEN(text) and =TRIM(text) 

The following image is an example of using the LEN and TRIM Function:



In this example, the LEN and TRIM Function has been used to count the characters of a given set of text strings. The Function is first applied to the cell A14 to give the result 25. The Function used here is:

=LEN(TRIM(A14))

8) SUMIF and COUNTIF

The SUMIF Function provides the sum of cells that are based on a single condition.  Criteria or conditions can be added to text, numbers, and even dates. The COUNTIF Function in Excel is used to count the number of cells that fulfil a condition. It can also be used on text, numbers, and dates.

a) SUMIF

The SUMIF Function is a widely used Function in Excel. Although it is largely useful, only one criterion can be applied on the range of cells. Multiple conditions can be applied using the COUNTIFS Function. The syntax is as follows:

=SUMIF(range, criteria, [sum_range])

The following image is an example of using the SUMIF Function:

 

This example makes use of data on a list of fruits and their amount. The objective is to find the sum of a selected number of cells, holding the amount for Avocado. The result £1,550 is obtained upon using the Function:

=SUMIF(A2:A11,D2,B2:B11)

b) COUNTIF

The COUNTIF Function is also a widely used Function in Microsoft Excel. Identical to SUMIF, COUNTIF Function can only apply a single condition as well. At the need of applying multiple criteria, COUNTIFS Function can be used. The syntax for COUNTIF is:

=COUNTIF(range,criteria)

The following image is an example of using COUNTIF Function:

This image showcases the use of COUNTIF Function to count the number of times the word “Avocado” has been mentioned in the selected array of cells (A2:A11). The Function used to obtain the result of 5 is as follows:

=COUNTIF(A2:A11, "Avocado")

9) CONCATENATE

The CONCATENATE Function in Microsoft Excel is used to join values together and return them as text. Up to 30 values can be joined together using this Function. The syntax followed for the CONCATENATE Function is:

 =CONCATENATE(text1, text2, [text3], ...)


 

In this example, the CONCATENATE Function has been used to conjoin three parts of a name to return the result of a full name. The words “Jose”, “M”, and “David” are the ones that have been joint using the Function:

=CONCATENATE(A2," ",B2," ",C2)

10) CELL, LEFT, MID and RIGHT functions 

The CELL Function can return a variety of information about the contents of a cell (such as its, column, row, location, name, and more). The syntax followed is:

  =CELL(info_type, [reference])The LEFT Function can help you to return text from the beginning of a cell (left to right). It can return the value of two or more characters. The LEFT Function uses two arguments – the selected text and the number of characetrs to be extracted. The syntax is:

=LEFT(text, [num_chars])

The MID Function returns text from any start-point of the cell (left to right)   It provides values from the middle of a text string. It makes use of three arguments – the selected text string, the character number to start from, and the number of characters to extract. It follows the syntax:

=MID(text, start_num, num_chars)

The RIGHT Function returns text from the end of the cell (right to left) or the right side of the cell. Like the LEFT Function, the RIGHT Function also follows two arguments – the selected text and the numbers of characters to be extracted. 

By combining these advanced Excel functions, you can create some advanced and complex formulas to use. Here is an example of using these functions in Excel:



The above image shows the implementation of the CELL, LEFT, RIGHT, and MID functions for a text string “We are the Best”.

11) XLOOKUP 

XLOOKUP is an advanced function that is available only in Excel 2021 and Excel for Microsoft 365. The earlier versions of Excel will not be able to support this Microsoft Excel function. XLOOKUP is the latest and refined version of VLOOKUP. It addresses most of the drawback's VLOOKUP has. The syntax is as follows:

=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

The following image is an example of using the XLOOKUP Function in Excel:


 

The above-mentioned example shows the use of multiple values to tally the result using XLOOKUP Function. Fruits, its quantity and amount are the factors being considered as data here. The Function used is as follows:

=XLOOKUP(G1, A2:A11, C2:C11)

12) VLOOKUP 

To return a value in the same row from a specified column, the VLOOKUP Function does a vertical look up in the leftmost column. Let us translate this into everyday terms.

A serial number field in the home supply budget uniquely identifies each item in the budget. The VLOOKUP method can be used if you have the item serial number and want to get the item description. The syntax used is:

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

The above image uses an example based on order numbers, orders, their quantity and amount. The results obtained have used the Function in three different ways:

'=VLOOKUP(B2, B2:D2, 2, 0)

'=VLOOKUP(B2, B2:D11, 2, 0)

'=VLOOKUP(B2, B2:D11, 2, FALSE)

Learn Microsoft Excel more effectively with Microsoft Excel Masterclass. Sign up now! 

Conclusion 

This blog has discussed everything you need to know about formulas and functions in Excel. We have also noted the 504 Functions available on MS Excel are for your perusal. You need to master these functions and formulas if you are looking to advance your career as a Data Analyst or a master's in finance modelling. Let us know in the comments below if you have any doubts related to this blog.  

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