After formatting cells next step that makes your perfect in Excel is formulas. Lets know how to do complex calculations using formulas in Excel.
The Microsoft Excel is one of most commonly used software in offices and other institutions not only for tabular representation of data, but also for performing calculations using different formulas. You can apply formulas on number of rows and column to easily calculate result that takes much time if you perform the calculations manually.
Using Formulas In Excel
Write A Formula
To apply a formula you have to first create a formula by typing in formula bar. The formula starts with the equal to (=) sign, and you have to type in cell addresses while creating the formula manually. You can also use built in formulas just click on the cell or define the cell range.
In above image you can see the formula written manually in the formula bar, the formula is applied on the cell where the result will be displayed. As soon as the formula will be written, Excel will return the result. If you have written a wrong formula excel will suggest how to resolve the issue.
Copy A Formula
If a formula is required in other cells then you do not have to write the formula again just drag the formula from the previous cell, Excel will paste the result automatically.
Cell References In Formulas
The formulas you create include cell or range reference, and reference help formulas to work dynamically with data present in the cell. If you change a value of cell on which the formula is applied the result will automatically display new value.
There are three types of cell references
Relative Cell Reference- In relative cell reference row and column reference change when formula is copied to another cell. By default all cell references are relative.
Absolute Cell Reference – In absolute reference the row and column reference do not change when formula is copied.
In the above image you can see when I copied the formula from F3 to F4 it automatically adds the value in E3 to the result. I have used absolute reference in E3. To define absolute reference $ sign is used ($E$3).
Mixed Cell Reference – In mixed cell reference one part is absolute other part is relative example – $E3 or E$3.
In above example you can see cell reference for the column is absolute and for the row is relative. When the formula is copied from F3 to F4 it only show sum of C4 and D4.
Functions Used In Formulas
Worksheet function enhance the power of formula you created, complex calculations can be done with an ease using function in formulas. Here is an example how to use functions
In above image suppose I want to see who gets the maximum salary, Max function can easily tell who gets maximum salary. I have added a max function and define the cell range and It has returned the highest value for the salary.
Built In Functions
You can also use Built in function in MS Excel, to access the built in functions go to the formula tab and click on insert function. A new window will open where you can select different type of text, mathematical, logical etc functions.
This is all about working with formulas in Excel, for any query leave your comment and I will revert back