Ms excel formulas

How to create formulas in MS Excel- Master Tips

How To Create Ms excel formulas.

How To Create Ms excel formulas If you are asked what is Microsoft Excel, what would be your answer? These are for storing numbers and calculating. correct.

You also use Excel to do Percentage, Compound Interest calculations, Sum and Average in Excel.

All this can be done using Excel formulas.

So can Microsoft Excel formulas be easy to learn? Yes! This tutorial explains the basics of Excel formulas for the novice. Along with this, you will be able to learn how to write and use Excel’s formula with detail steps.

Along with this, advanced formulas are also given here with examples. You’d be surprised how easy it is to create formulas in Excel.

Topics

• Basic of Microsoft Excel Formulas:
• Elements of Microsoft Excel Formulas:
• Operators in Excel worksheet formulas:
• Using Arithmetic Operators in Excel Formulas:
• Comparison operator in Excel formulas:
• Text Concatenation Operator:
• Reference operators in Excel formulas and functions:
• How to write a formula in Excel using cell references:
• How To Create An Excel Formula By Using Defined Names:
• How To Make Excel Formulas By Using Functions:

Basic of Microsoft Excel Formulas:

In MS Excel, formulas are equations that perform various calculations in your worksheet. Although Microsoft has continuously introduced many new versions of MS Office, adding many new functions, the Excel concept has the same concept in Excel 2007, 2010, 2013 and 2016 versions.

All Excel formulas begin with an equal sign (=).

After this equal symbol, you enter either a calculation or a function.

For example, if you want to add the values ​​of cells B1, B2 and B3, you can either

= B1 + B2 + B3

Enter this formula, or

= SUM (B1: B3)

In this way, we will use the SUM function.

After this you just have to press Enter. Bus!

Elements of Microsoft Excel Formulas:

When you create a formula in Excel, you can use different elements to supply the source data for the formula and indicate what operations should be performed on this data.

Depending on the formula you created, it may include any or all of the following parts:
i) Constants –

Constants are numbers or text values ​​that you enter directly into the formula, such as 3 * 4

ii) Cell references –

Cell references to values ​​used in Excel formula, e.g. = SUM (A1, A2, B2)

To refer to data for two or more attached cells, use range references.

For example, to perform a SUM of the values ​​of all cells between A1 and A6, use this formula:

= SUM (A1: A6)

iii) Names:

The defined name of the range of cells, constant, table, or function.

For example = SUM (Math_Marks)

iv) Functions –

Excel has predefined formulas that perform calculations using the values ​​given in their arguments. For example Excel has many functions like SUM, AVERAGE, COUNT.

v) Operators –

Special symbols that perform operations or calculations.

Operators in Excel worksheet formulas:

To tell Microsoft Excel which type of operation you want to perform in a formula, you use special symbols where the operator technically goes.

Excel has 4 types of operators:

i) Arithmetic – For Basic Mathematical Operations.

ii) Comparison (logical) – to compare values.

iii) Concatenation – To join the values ​​of the text in a single string.

iv) Reference – To create a range of cells and separate arguments in Excel functions.

Using Arithmetic Operators in Excel Formulas:

How to use arithmetic operators in Excel formulas:

These operators are used to perform basic mathematical operations such as Addition, Subtraction, Multiplication, and Division.

Comparison operator in Excel formulas:

In Microsoft Excel formulas, Comparison or Logical operators are used to compare two values.

Comparison always results in logical values ​​of TRUE or FALSE.

The following logical operators are available in Excel:

Text Concatenation Operator:

The text concatenation operator in Excel is ampersand symbol (&). You can use it to join two or more text strings to a single string.

For example, if you have STD Code in Column A and Telephone Number in Column B, then you can use this formula to combine these two in the same cell –

= A1 & “” & B1

In this formula, a “blank” space is added between them to understand the numbers better.

Reference operators in Excel formulas and functions:

These operators are used to give a range of cells in MS Excel formulas.

i) Colon (:)

It is a range operator in which you can give references to all the cells between two cells.

For example, the cells range A1: A10 includes 10 cells from A1 to A10.

To find the average of these 10 cells values, you use the following formula: = AVERAGE (A1: A10)

In addition, you can refer to the entire column (A: A) or the entire row (1: 1).

For example, you can use the formula = SUM (A: A) to do the SUM of the values ​​of all the cells in column A.

ii) Comma (,)

Comma is used to separate arguments in Excel spreadsheet formulas.

For example, in the formula = IF (A1> 35, “PASS”, “FAIL”), if the value of A1 cell is greater than 35, then PASS, otherwise FAIL

iii) Space

It is an intersection operator representing the common cells of two references.

For example, if you are a list of items in column A and some related data in others, you can remove the values ​​of intersection of these given columns and rows.

= B3: D3 C2: C4

How to write a formula in Excel using cell references:

Instead of entering values ​​directly in your Excel formula, you can refer to cells containing those values.

For example, if you want to add the value of C2 to cell B2, then you write the following Addition formula: = B2 + C2

While creating such a formula, you can type the cell reference directly in the formula, or click on cells and Excel will insert the corresponding cell reference in your formula.

A major benefit of using Cell References in Excel formulas is that whenever you make a change in the value of reference cells, the formula automatically updates all calculations.

How To Create An Excel Formula By Using Defined Names:

To take a step forward to create a formula, you can give a name to a cell or range of cells. You can then use this name in your formula.

The fastest way to name a cell in Excel is to first select the cell / cells and then type the name directly into the Name box in the Formula bar. Or press Ctrl + F3 size.

For example, if you give Math names to B columns and English names to C columns, then you can use these names in your formula.

How To Make Excel Formulas By Using Functions:

How to create excel formulas using functions

Excel functions are pre-defined formulas that perform calculations.

Each function has its own specific arguments and syntax.

For more information, please see the previous post Advanced Excel 10 Formulas