Top Excel Formulas You Should Know

When it comes to working with data, Microsoft Excel is the tool of choice. Given Excel's widespread use, there are probably just a few people who haven't used it. Excel is a commonly used software application for generating reports and business insights in today's industries. Excel comes with a number of built-in applications to make it easier to use.

TABLE OF CONTENT:
What is Excel Formula
Excel Formulas and Functions
 

What is Excel Formula?

A formula in Microsoft Excel is an expression that works with values in a range of cells. Even if the result is an error, these formulas return a result. You can use Excel formulae to execute operations like addition, subtraction, multiplication, and division. In addition, you may use Excel to calculate averages and percentages for a range of cells, manipulate date and time variables, and much more.

Free Demo Classes

Register here for Free Demo Classes

Select Course CUET Digital Marketing NDA Graphics Designing MS Excel (10 Hrs.) Interview Skill Course Entrepreneurship Programme Content Writing

The term "function" is another phrase that Excel formulas are quite familiar with. The terms "functions" and "formulas" are occasionally used interchangeably. They're linked, but they're not the same. An equal sign is used to start the formula. In the meanwhile, functions are employed to execute sophisticated calculations that are impossible to accomplish manually.

Excel Formulas and Functions-

Depending on the type of operation you wish to conduct on the dataset, Excel has a plethora of formulae and functions. We'll look at mathematical operations, character-text functions, data and time, sumif-countif, and a few lookup functions, among other things.
 
1. SUM
The SUM() function returns the sum of the selected range of cell values, as the name implies. It conducts addition like a mathematical procedure. The result is stored in C5.

2. AVERAGE
The AVERAGE() function calculates the average of the cell values in a specified range. It calculates the average for you automatically, and you may save the result wherever you choose.

3. COUNT
COUNT() calculates the total number of cells in a range containing a number. It excludes the cells that are blank and those that contain data in any other format than numeric.
The function 'COUNTA()' must be used if you need to count all the cells containing numerical values, text, or any other data format. COUNTA(), on the other hand, does not count any blank cells.
COUNTBLANK() is used to count the number of blank cells in a range of cells.

4. SUBTOTAL
Let's have a look at how the subtotal function works now. The SUBTOTAL() function returns a database's subtotal. You can choose between average, count, sum, min, max, min, and others, depending on your needs.

5. MODULUS
When a given number is divided by a divisor, the MOD() function returns the residual.

6. POWER
The "Power()" function returns the result of raising a number to a certain power.

7. CEILING
Then there's the ceiling function. The CEILING() function takes a number and rounds it up to the next significant multiple.

8. FLOOR
The floor function, unlike the ceiling function, rounds a number to the nearest multiple of significance.

9. CONCATENATE
This function connects or merges multiple text strings into a single string.

10. LEN
LEN() calculates the total number of characters in a string. As a result, the whole number of characters, including spaces and special characters, will be counted.

11. REPLACE
The REPLACE() function replaces a section of a text string with another text string, as its name implies.
"=REPLACE(old text, start num, num chars, new text)" is the syntax. The index position at which you wish to begin replacing the characters is denoted by start num. The amount of characters you want to replace is indicated by num chars.

12. SUBSTITUTE
In a text string, the SUBSTITUTE() function replaces current content with new text.
"=SUBSTITUTE(text, old text, new text, [instance num])" is the syntax.
[instance num] is used more than once to refer to the index position of the current texts.
 
13. LEFT, RIGHT, MID
The LEFT() function returns the number of characters in a text string starting from the leftmost character. Meanwhile, the MID() method, given a starting location and length, retrieves the characters from the middle of a text string. Finally, the right() function returns the number of characters remaining after a text string has been terminated.
 
  SUM is a function. =SUM is probably the most commonly used function in Excel (or any other spreadsheet programme) because it does exactly what it says: It adds up the numbers in a column, row, or range, but it doesn't just add them up. It can also add, multiply, divide, and utilise any of the comparison operators to return a value of 1 (true) or 0 (false) (false).