Excel Formula Expert 1

 
 
 

What is Excel Formula ? 

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things that can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=). 
 
This Article is written for the people who does not know about Excel formula. This article answers following questions about Excel Formulas:
 
 
1. What is Excel Formula?
2. How to Use Excel Formula – a step by step tutorial.
3. How an Excel Formula works in Excel?
4. How to refer a Cell in Excel Formula?
5. What is Cell Address?
 

How to Use Excel Formula – Step by Step 

1. Select the Cell where you want to place your formula. 
2. Type the Equal (=) sign. 
 
Note: After typing = sign if you Click on Any other Cell then the Address of that Cell will be automatically placed there. So be careful while writing a formula in Excel. While writing formula DO NOT select any other cell, unless you want to refer that Cell in to your formula. Therefore you can refer a cell in to your formula in two ways: 
 
i) By typing the Address of that Cell (like A1, B8 etc) 
 
ii) By Selecting that Cell while writing your formula. 
 
3. Type the address of the cell to include in the formula or Select that Cell. 
 
4. Type the Formula as per the Syntax of that Formula 
 
5. Press Enter or Press Tab Key. (Again after writing the Complete formula, do not click somewhere else, instead you need to press Enter or Tab key Only)
6. And now you are done. 
 
 
Let’s take an example of Simple Formula in Excel. 
 
In Cell A1, I have value as 10 and in Cell B1, I have value 5. I want sum of these two Cell values in Cell C1. 
 
1. Click on Cell C1
2. Type Equal to Sign (=)
3. Now Take your mouse at Cell A1. Automatically the Cell Address A1 will be typed in Cell C1.
4. Now Type Plus Sign ( + )
5. Now Click on the Cell B1. (Same as previously this time B1 Cell Address will be typed)
6. Now It will look as same as shown in below: 
 
7. Now press Enter
8. Value of Cell A1 (i.e. 10) and Value of Cell B1 (i.e. 5) will be added and the Result will be shown in Cell C1 (Where formula is written). 
 
 
Note: In the above Example, you can see that the Value of Cell is referred by the Address of that Cell. It means in Excel Formula if you want to Refer the Value of Cell then you can refer it by passing the Address of that Cell.
Address of Cell is formed by Column Name and Row Number. It means if you want a Cell which is in Column J and Row 3 then the Address of the Cell will be J3.

The Power of Excel Formulas

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
 
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.
 

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

To create a formula:

In our example below, we’ll use a simple formula and cell references to calculate a budget.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell B3.

 

2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.

3. Type the cell address of the cell you want to reference first in the formula: cell B1 in our example. A blue border will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
 

5. Type the cell address of the cell you want to reference second in the formula: cell B2 in our example. A red border will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. 

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell D3.

2. Type the equals sign (=).

3. Select the cell you want to reference first in the formula: cell B3 in our example. The cell address will appear in the formula, and a dashed blue line will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the multiplication sign (*).
 
5. Select the cell you want to reference second in the formula: cell C3 in our example. The cell address will appear in the formula, and a dashed red line will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. 
 
 

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.
 

1. Select the cell containing the formula you want to edit. In our example, we’ll select cell B3.

2. Click the formula bar to edit the formula. You can also double
-click the cell to view and edit the formula directly within the cell.

3. A border will appear around any referenced cells. In our example, we’ll change the second part of the formula to reference cell B2 instead of cell C2.

4. When you’re done, press Enter on your keyboard or click the checkmark in the formula bar.
 
 
5. The formula will be updated, and the new value will be displayed in the cell.
 
 
If you change your mind, you can press the Esc key on your keyboard to avoid accidentally making changes to your formula.
 
To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the top-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.
Advertisements

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.