This tutorial introduces how to use Excel formulas and functions. Formulas are expressions entered by a user for the purpose of calculating cell values. Functions, on the other hand, are predefined formulas built into Excel that assist in data analysis.
Microsoft Excel has over 450 inbuilt functions, many introduced at different versions of Excel. For example, the VAR.S function was introduced in Excel 2010 while the CONCAT function was introduced in Office 2019. Between 2010 and 2019, over 100 new functions were introduced for effective data analysis.
For example, the worksheet below shows the difference between using a formula and a function in Excel.
When a formula is used, cell references are combined with mathematical operators to calculate the value of a cell. But some functions make it easy to perform calculations without introducing mathematical operators.
In this tutorial, we shall look at how to use Excel formulas and functions in the following headings:
- What are Excel formulas and functions?
- Inserting formulas and functions in a worksheet
- How to use Excel formulas and functions
What are Excel Formulas and Functions?
Formulas in Excel
In Microsoft Excel, formulas are expressions used to manipulate data within a cell or range. Formulas combine mathematical operators with cell references or addresses to determine the value of the selected cell.
In Microsoft Excel, users can insert different kinds of formulas depending on the calculation required. For example, the following formulas perform different calculations in Excel.
| Operator | Formula | Description |
| + | =A2+B2+C2+D2+E2 | Addition |
| – | =E2-D2 | Subtraction |
| * | =B2*C2 | Multiplication |
| / | =D2/E2 | Division |
| ^ | =C2^D2 | Exponential |
| * / | =(B2*C2*D2)/100 | Simple interest |
Formulas are tedious to enter and require carefulness to avoid violating operator precedence.
Functions in Excel
Functions are predefined formulas in Excel used to perform operations on data. Using them requires you to reference a cell or range of cells to compute cell values. There are different kinds of built-in functions in Excel. These functions fall into different categories. Some examples are:
| Category | Function | Description |
| Financial | IRR | Used to calculate the internal rate of return |
| Logical | AND | Used to check if all arguments are true |
| Text | UPPER | Used to convert text to capital case |
| Date & Time | DAY | Used to return the exact day of the month |
| Lookup & Reference | ROWS | Used to return the number of rows in an array |
| Math & Trig | ABS | Used to return the absolute value of a number |
| Statistical | AVERAGE | Used to calculate and return the mean of an array |
| Engineering | CONVERT | Used to convert a number from a unit to another |
| Cube | CUBEMEMBER | Used to return a member from connection |
| Information | ISTEXT | Used to check if a given value is a text or not |
| Compatibility | FLOOR | Used to round a number down to the nearest level of significance |
| Web | WEBSERVICE | Used to return data from a web service |
These categories provide users with relevant functions to manipulate and analyze data with ease. However, users need to understand how these functions work in order to use them effectively. One good advantage of a function is that it increases productivity more than using a formula.
Advance and Basic Excel functions list
There are a lot of functions in Microsoft Excel that can be used in computation and data analysis. No user can use all these predefined formulas at once.
There are basic and advanced formulas that are mostly used by users that you can find useful too. Some of the functions appear under the AutoSum category and make calculations easy. Some of these functions include:
| Function | Example | Description |
| SUM | =SUM(B2:B10) | Adds up values in the selected range |
| AVERAGE | =AVERAGE(B2:H2) | Computes the average value of the selected range |
| COUNT | =COUNT(A1:J1) | Counts and returns the value of the number of cells that has values within a selected range |
| COUNTA | =COUNTA(B2:K2) | Counts and returns the number of cells that are not empty within a selected range |
| MAX | =MAX(A1:D1) | Computes and returns the maximum value in the selected range |
| MIN | =MIN(B2:F2) | Computes and returns the minimum value in the selected range |
| CONCATENATE | =CONCATENATE(B2,” “,C2) | Used to join text in different cells together |
| TRIM | =TRIM(C7) | Used to remove spaces before or after a text string in selected cells |
| ROUND | =ROUND(H8,2) | Used to round a number to a specified number of digits. The example rounds a number to 2 decimal places. |
| IF | =IF(H9>50,”Yes”,”No”) | Used to check whether a logical condition is true or false and returns the necessary value. |
| COUNTIF | =COUNTIF(C2:C17,”>50″) | Used to count the number of selected cells that meets a specified condition |
| SUMIF | =SUMIF(C2:C17,”<50″) | Used to calculate the sum of values in selected cells that meets a specified condition |
| VLOOKUP | =VLOOKUP(B20,A2:C11,50,FALSE) | Looks for a value in the leftmost column and return a value in the same row from the column you specified |
There are many more functions you will meet as you continue to explore Microsoft Excel. All you need to do is to understand how the function works and use it for your analysis.
Inserting Formulas and Functions in a Worksheet
If you must use a formula or function in Excel, you would have to insert the formula in your worksheet.
To add formula or function in Excel manually, you will need to insert the equal (=) sign first. Typing the (=) equal sign prepares the selected cell to receive formula or function.
There are five (5) ways you can insert a formula or function in a worksheet, namely:
- By manually typing the formula or function. To insert a formula in this manner requires you to type in the (=) sign first. Then the cell reference, followed by necessary operators. When working with a function, after typing the equal sign, follow it by typing the function you want.
- You can also use the fx icon on the formula bar. When you click on the icon, a dialog box will appear.
- On the dialog box that appears enter the function name and click on Go. The list of functionswill appear in the select a function window.
- Select your desired function and click OK.
- Another dialog box will appear. The features of the dialog box depend on the selected function.
- Click on the collapse button to enter the cell references.
- On the worksheet workspace, select the range of cells and click the collapse button to return to the dialog box.
- Click OK on the dialog box to calculate the result.
- Another way to insert a function is to use the AutoSum dropdown menu on the Home tab, Editing group.
The dropdown menu has a list of mostly used functions that calculates automatically when selected. However, to display the dialog box as shown in (2) above, select More Functions…
- The fourth way to insert a function in Excel is by using the Insert Function command on the Formula bar. When you select this command, the dialog box as discussed in (2) above will appear.
- The final way of inserting a function in Excel is by choosing a function from its category on the Formula bar. Choosing any of the functions categories will display the list of functions. Select a function from the list and follow the steps discussed in (2c) above.
Among these methods of inserting a formula, the manual method will help you work faster and smarter.
Edit a formula
While working with a formula, there may be a need for you to edit the formula and make adjustments. When working with Excel formulas and encounter an error, you do not need to delete the formula. You can edit the formula and continue your work.
To edit a formula,
- Select the cell that contains the formula.
- Click the formula bar to activate editing (you can also double click the cell)
- Make the necessary adjustments and press Enter on the keyboard or click the Enter icon on the formula bar.
Operator precedence
When using the manual method to enter formulas in Excel, you should be mindful of the operator precedence. Operator precedence is the sequence by which mathematical operators are executed in an excel formula.
Microsoft Excel follows the general mathematical rule to carry out its mathematical operations. There are about six (6) arithmetic operators used by excel in formulas. When these operators are used together, the sequence of calculations is what we call operator precedence.
The operator precedence in Excel is as follows Parenthesis, Exponential, Multiplication and Division, and Addition and Subtraction.
This order means that whatever is in the parenthesis will be calculated first. Followed by the exponent, then multiplication and division. Addition and subtraction will be performed last in any excel formula.
For example, in the Excel formula [=C4*D4/E4+F4^2], the multiplication and division will be calculated first. That is, C4*D4/E4. This will be followed by the exponent, that is F4^2, before the addition.
However, using a parenthesis will change the whole order. For example, if the formula is rewritten thus, [=C4*D4/(E4+F4^2)] the entire result will change. This is because the formula in the bracket will be executed first before other ones.
How to Use Excel formulas and Functions
We have already discussed how to insert a formula/ function in excel. However, we shall use the manual method to illustrate how to use Excel formulas.
Addition and subtraction formula in excel
The addition formula is used to add values in Excel while the subtraction formula is used to subtract values. Because subtraction represents negative addition, the subtraction function does not exist.
For example, to subtract the following values: 10 – 2 – 3 – 1 = 10 – (2+3+1). Therefore, to implement subtraction, you can add the negative sign on values. By so doing, adding them will give you the required result.
In Microsoft Excel, the addition function is called the SUM and it is the simplest function to use in Excel. You can invoke the AutoSum or use the SUM function manually. Let us illustrate below.
- Select the cell you want to calculate addition on
- On the Home tab, under Editing, select AutoSum
- A range corresponding to the active cell will be automatically selected
- If the selected range is correct, press Enter
- If the selected range is wrong, use the mouse and select the correct range, and press Enter.
How to multiply using Excel formula
The asterisk (*) is used to perform multiplication in Excel. When a formula is entered manually, the asterisk is used to indicate necessary multiplications. For example, B3*C3.
But, if you are using the excel predefined formula, the multiplication function is called the PRODUCT. To use the PRODUCT function, do the following:
- Select the cell you want to calculate multiplication on
- On the active cell, enter the (=) sign and start typing product (=PRODUCT)
- A list of functions that begin with ‘pro’ will appear.
- Select PRODUCT from the list, a parenthesis opens with suggestions for number1, etc.
- Enter the cell references to be multiplied separated by a comma.
- Close the parenthesis when you are done, and press Enter.
When working with Excel functions manually, excel provides formula tips that will help you know which value to enter next.
When the first value is entered, separate it with a comma and continue until all the variables are entered.
Apart from these basic operations, you can use the QUOTIENT function for division and the POWER function for exponent.
Conclusion
Excel functions and formulas help us to perform basic and advance calculations in Excel. Formulas can be entered manually by using the predefined functions in Excel.
Using Excel formulas is easy when you understand how it functions. Whenever you do, pay attention to the formula tips provided by Excel. In our later tutorials, we shall discuss some of the advanced functions in excel, such as the IF, VLOOKUP, etc.
Our next edition will look at SUMIF and SUMIFS in Excel.
If you have any questions, please, kindly ask. In case you have not seen our previous tutorials, view them below:
- What is Microsoft Excel: Tutorial 1
- Formatting Numbers and Worksheet in Excel
- Conditional formatting in Excel: Uses and Applications
- What is Cell Referencing in Excel?
