Creating Formulas using cell Reference or Constants in Microsoft Office Excel
In a previous article, we have seen how to apply formulas using functions. In this article I will explain you how to create simple formulas and how to create formulas by cell reference.
A simple formula can be created on a cell by the following steps:
- Click the cell on the formula has to enter
Type = either in the formula bar(right hand side of fx ) or in the cell
Followed by that enter the constants and the operation you wish to perform
See the screenshot below.
Now click enter, the result will appear in the cell.
Cell reference is also the same procedure, but instead of entering the constants the reference cell Id is used.
Click the cell on the formula has to enter.
Type = either in the formula bar(right hand side of fx )
Click on the first cell which has input value, in the example C2 will now appear in formula bar.
Enter the operator and click on the next cell,D2 will appear in formula bar.
Click on Enter, result will appear in the selected cell,(E3 in the below example)
Troubleshooting Formulas in Excel
Even though working with formulas is quite easy, sometimes many of us end up in trouble due some common and silly errors. My aim is to give you light to avoid some of the common errors occurs while working with formulas.
Use Colon to Indicate Range :If you are referring to a group of cells in a formula ,always use colon.Eg:- D1:D10
Refer to other worksheets name using single quotation mark(‘).Eg:’Sheet1’.
Make sure you have entered all required arguments for functions.
You can next only up to 64 levels of function inside a function.
Match all open and close parentheses in a function.
Do not format the numbers when you enter them in formulas Eg:enter 20000 instead of $2,0000
Delete a formula
Sometimes you may end up in a situation to delete a formula. When you delete a formula, most of the times the resulting values also will lose. You can delete a formula without removing the result value also.
To delete a formula and the resulting values, just click on the cells or cell ranges containing the formula and Press DELETE.
To delete just the formula, select the cells or cell ranges, Do a copy and click on the arrow below Past icon in Home tab and then click Paste values.
