What are Basic Excel Formulas?

Everyone would agree that Microsoft Excel is one of the best tools for working with data. It is widely used in almost every other industry today. Although not everyone is following its complex and detailed formulas, some are taking advantage of its basic calculations and planning their monthly budget efficiently. However, no one would deny that Excel can generate detailed business reports with a simple input of data.

What are Excel Sheet formulas?

These formulas are functions or expressions used inside cells to manipulate data, automate processes, or perform calculations. These formulas allow users to perform simple arithmetic tasks and complex data analysis. Here, we are listing some basic Excel formulas everyone should know, learn, and use.

Excel Basic Formulas

We bet learning these formulas can help you perform your business's small financial functions effectively. Here is the formula name and the procedure for how they will function:

1.     SUM

If you want to add or subtract numbers you can use this formula. Select a cell where you want to see the sum and apply the formula starting from the “is equal to” symbol and type Sum. It will seem like =Sum(number1,number2,). After putting in the formula, you can select the range of cells you want to sum. 

There are different ways of selecting the range of cells. You can manually select a cell from top to bottom or vertically. Also, you can mention the range from cell-like A1:A10 (vertically) or B2:D2 (horizontally). You can also select multiple ranges by separating them with commas example, =Sum(A1:A4, C1:C3). 

To explain it further, suppose you have the values mentioned from cell A1 till cell A15, so the formula will be =sum(A1:A15).

2.     COUNT

Count function comes under MS Excel basics. It counts the total number of cells in a range that consists of a number. It excludes cells that are blank or cells that hold data in any other format apart from numeric. This formula also excludes text, logical values, or errors. To place this formula, you have to write "=COUNTA()."

The best thing is that it gives you choices, so you can count cells based on criteria. For example, COUNTIF or COUNTIFS functions choose only cells that match the required conditions. It is a handy formula that enables you to perform a quick count of numeric values. For example, you have a huge set of data and you want to know the number of cells occupied and exclude the missing cell. This formula can help you identify in a few minutes. 

3.     AVERAGE

You will find this formula a bit similar to sum formula. The difference here is that we will select “average” instead of sum. Manually select the cells by clicking and dragging, or add contiguous data by selecting the cell number for which the average needs to be performed. For example, =average(A1:A10). Again, if you want to take multiple average ranges, you can do so by separating them with commas. While applying these formulas, never forget to close the formula with a closing parenthesis. 

Let’s suppose you have the values in the cell from C1 to C8. You have to click on the cell where you want the average, put the formula "=average(C1:C8), and press enter. Here is your average calculated. 

     4.     MODULUS

This function is used to determine the remainder in Excel calculation. For instance, if you want to find the modulus of the number 20 divided by 4, you can write it as =MOD(20,4). The result will be “0” because 20 divided by 4 is 5 with a modular of 0. This function is specifically used to determine remainders in Excel calculations or when you need to recognize periodic patterns.

5.     POWER

When you want to raise a number to a specified power, you can use this formula. The formula is simple, as you mention the number you require a raise, and the exponent to which you want to raise the base number.

For instance, you have to compare the power of 3 with the exponent of 4. You will execute the formula as "=power(3,4),” the result of which will be 81. There is also a different way of writing it; you can simply write like “=3^4”

This formula can be used when you are working with growth rates, compound interest, or any other scenario that includes exponential decay and growth.

6.     FLOOR

The purpose of the Floor function in Excel is to round a number down to the nearest specified multiple. To explain with an example, the nearest lowest multiple of 5 for 36.3 will be 35. This function can be confusing; however, it is used for an evenly or manageable distribution of values. The scenario in which it is mostly used is financial modeling or data analysis.

There is a concept of positive and negative significance in this function. If the significance value is positive, this function rounds the number towards negative infinity. For a negative significance value, the floor function rounds the number towards positive infinity.

7.     VLOOKUP

The VLOOKUP function in excel is one of the powerful tools which stands for “Vertical LOOKUP”. It can turn a value in the same row from a column you require by just looking for particular value in the leftmost column.

Let’s say you have a table in which you want to look for “books names” and their corresponding writers in columns A and B, separately (A2:B10), and you want to look for a specific book writer in these columns. The “lookup value” will be the name of the book, “table array” will be the range of “book names and writer, “col Index num” will be the column number in which writer name is mentioned, and “range lookup” can be false OR true and omitted. This formula is often used by the Best Research writers to perform their research effectively.

8.     CEILING

It is contrary to the Floor function. It rounds the number up to its closest multiple of significance. Take the sample example stating in the function of the Floor. The closest highest multiple of 5 for 36.3 will be 40. All the functions of the Ceiling are opposite to the floor and are used in scenarios where one needs to round numbers to precise multiples, intervals, or values to reach a certain threshold.

9.     CONCATENATE

This function is used to merge numerous text strings in to one. To use this syntax it should be applied as “=CONCATENATE(A1,””,A2). For instance, in A1 the first name of a person is written – John and in A2 second name is mentioned -Mathew, this formula will combine these together “John Mathew”.

10.  SUBSTITUTE

The substitute function replaces the existing text with a new text. This function can be applied with the syntax “=SUBSTITUTE(text, Old text, New_Text, [instance_num])’. In this syntax, [instance_num] is optional and it means that the index position of the current present texts is more than once. Suppose you want to change the text “I like apple” to “He likes apple” in a different Excel cell. The syntax will be applied on the cell it is required as, “=SUBSTITUTE(“A20,”I like”,”He likes”)”, it will make a replacement in the cell in which syntax is entered. The word apple will remain there while “I like” will be replaced with “he likes."

Presented in this article are just a few basic Excel functions that can make a difference in sorting data. They make the job easy, and everyone should learn to use these basic formulas to perform their work efficiently. Assignment Help UAE will come up with more formulas in their next articles to help those who want to learn Excel.