In this tutorial, we will know the basic excel formula prior to indulging in calculation and manipulating data in your worksheet.
As we know Microsoft Excel is a good choice for working with data. Given its popularity, there are likely only a few people who have not used it. This application is widely used as a software application in many industries today, it is utilized to build and generate reports and insights. Excel supports a number of built-in applications that make it more user-friendly.
As an overview the most powerful feature of Excel is to calculate numerical information but with the use of formulas. Excel can add, subtract, multiply, and divide just like a calculator. In this lesson, we’ll familiarize the functions of each formula, thus showing you how to use cell references to create simple formulas.
What is Excel Formula?
Excel formula is an expression used to operate the given values in a range of cells. These formulas enable you to todo mathematical operators such as addition, subtraction, multiplication and division.
Further, it allows you to calculate percentages and averages in a range of cells even to manipulate the date and time values, considering the functions it performs a lot more.
In Excel formulas, “function” is another commonly used term. Sometimes, “formulas” and “functions” can be used interchangeably. They are closely related but nonetheless distinct. The equal sign is the first symbol in a formula.
In the meantime, functions are utilized to perform calculations that cannot be performed manually. Excel functions have descriptive names that reflect their purpose.
Excel Mathematical Operators
As mentioned above, excel formulas enable us to do mathematical operators, now let’s consider exploring each operator. So these are…
The plus sign(+) for addition, minus sign for subtraction, an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents. Importantly all formulas must begin with an equal sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.
| Sign | Mathematical Operators |
| + | Addition |
| _ | Subtraction |
| * | Multiplication |
| / | Division |
| ^ | Exponent |
| = | Beginning of Formula |
Now let’s try to create a formula using one of the operators, for instance 10+20 well most of the time we addressed formula directly on the cell to create formula. Using this cell reference will ensure the formula you create is accurate when calculated. You can change the value of referenced cells without having to rewrite the formula.
In addition, cell reference allows you to create various formulas by combining your operator with the cell reference, including numbers.
How to Create Formula
In the figure above, here are the steps on how to create it; we’ll use a simple formula and cell references to calculate.
- Select the cell that will contain the formula.
In our example, we’ll select cell B3.
- Type the equals sign (=).
Notice how it appears in both the cell and the formula bar.
- Type the cell address of the cell you want to reference first in the formula.
In our example cell B1, a blue border will appear around the referenced cell.
- Select and input the mathematical operator you want to use.
In our example, we’ll type the addition sign (+).
- Input the cell address of the cell you want to reference second in the formula
In our example cell, B2 will be our reference. Thus red border will appear around the referenced cell.
- Press Enter on your keyboard.
The formula will be calculated, and the value will be displayed in the cell.
Excel Formula and Functions
Excel provides a lot of formulas and functions considering the operation you want to work out and perform in so your set of data. Let’s now look at the basic Excel formulas you must know.
SUM
The sum function is one of the functions that helps in creating formula; its purpose is to give a total of the selected range of cell values. This function executes the addition mathematical operation.
I’ll give an example below:
As you can see above, to find the total amount of sales for every unit, we had to simply type in the function “=SUM(C2:C4)”. This automatically adds 1000, 500, and 300. The result is stored in C5.
AVERAGE
The AVERAGE() function is designed to calculate the average of the range of cell values. As you can see in the example below, it yields the average income. All you need to do is input the function (=Average(Cellnumber,..).
It automatically calculates the average and stores the result in the location of your choice.
COUNT
This Excel count function counts the number of cells or ranges that contain a numeric value. Apparently, it does not include blank cells or any range that contains other formats unless it’s number format.
COUNTA
This COUNTA function is like COUNT however it counts all ranges given regardless of its type. Includes dates, times, strings, logical values, errors, text or empty strings. To use this formula the format goes like this…
=COUNTA(value1, [value2], …)
COUNTBLANK
The COUNTBLANK Function[1] is categorized under Excel STATISTICAL functions. COUNTBLANK will count the number of empty cells in a given range of cells.
The COUNTBLANK Function falls under the statistical function of Excel. Thus this function counts empty cell or range that have empty values. Further, it is useful in analyzing data in financial matter in highlighting and counting empty range.
The syntax works like this…
=COUNTBLANK(range)
MIN Excel
This Excel basic function is used to get the minimum value in cells or ranges. The syntax of min function has the following arguments Number1, number2, … Number1 is required, and subsequent numbers are optional 1 to 255 numbers for which you want to find the minimum value.
The syntax of this function is written like this:
MIN(number1, [number2], …)
MAX Excel
This basic Excel function is used to get the maximum value in cells or ranges.
The syntax of this function is…
=MAX(number1, [number2], …)
Number1 and number2 are the function’s arguments, with Number1 being required and the subsequent values being optional.
Further, the max and min functions basically used to find the maximum and minimum number of range values.
LEN Excel
The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.
Let’s now move on to the next Excel function on our list of this article.
TRIM Excel
The TRIM function makes sure your functions do not return errors due to extra spaces in your data. It ensures that there are no empty spaces. In contrast to other functions that can operate on a variety of cells, TRIM operates only on a single cell. Consequently, it has the disadvantage of adding duplicate data to your spreadsheet.
The syntax is like this…
=TRIM(text)
Example:
TRIM(A2) – Removes empty spaces in the value in cell A2.
IF Excel
This IF excel function is used to sort data according to the logic given. The best part of the IF formula is that you can embed formulas and functions in it.
The syntax of this function is:
=IF(logical_test, [value_if_true], [value_if_false])
Summary
In summary, we covered formulas in excel we used; prior to this we also learned the operators that make up the formula creation. This formula is crucial in manipulating data and data analysis in each worksheet especially in complying with work tasks and reports.
This free excel tutorial considers beginners and professional who wants to be efficient in MS Excel, so if you have any clarification feel free to leave a comment below.
Thank you for reading!