Excel’s power lies in the vast array of formulas that allow you to calculate the sum of a given list all the way to determining the interest rate on a loan. But in order to use these powerful formulas you must become familiar with the several ways that formulas can be entered and used. While we are at it, I’ll even show you some tricks to help you save time and be more productive with Excel 2010.
If you have any comments or questions, please post them in the comments section below.
Note: Although the demonstration is in Excel 2010, most instructions will apply to previous versions of Excel (Excel 2007, Excel 2003, and Excel XP).
Method 1 – Use the Cell or the Formula Bar to Compose a Formula Using a Function
The method describe below will show you how to use Excel’s Formula bar to compose a formula. I will also show you how to compose a simple formula outside of the formula bar.
1. Click on the cell where you would like the results to appear in and click on the Formula bar [it’s followed by the insert function button f(x)].
When you compose your formula you enter the function followed by the expression. This means that if you are using the SUM function which is used to add cells, you would start the formula by entering the following:
=SUM(
All formulas must be preceded by the “=†sign.
To continue composing the formula, enter or select the cells you wish to include in the expression. So let’s say we were adding the sales figures for January 2010 and January 2009, ([a] 100 and [b] 67 respectively). You would enter the following:
=SUM(b2,c2)
Each cell reference represents the value you wish to include in the formula, so b2 refers to the 100 sales in January 2010 and so on. You can include additional values to be added in this function simply by adding a comma after each cell reference.
2. You can also compose your formula outside of the Formula bar by double-clicking on the cell where the results will appear. Repeat the steps above for the composition of the formula.
3. Hit Enter to see the results for the formula you just entered.
Method 2 – Use the Cell or the Formula Bar to Compose a Formula without a Function
You can also compose a formula using common operators like multiplication, division, subtraction, and addition.
1. Click on the cell where you would like the results to appear in and click on the Formula bar [it’s followed by the f(x)].
When you begin to compose your formula enter the following:
=
All formulas must be preceded by the “=†sign.
To continue composing the formula, enter or select the cells you wish to include in the expression. So let’s say we were adding the sales figures for January 2010 and January 2009, ([a] 100 and [b] 67 respectively). You would enter the following:
=b2+c2
Each cell reference represents the value you wish to include in the formula, so b2 refers to the 100 sales in January 2010 and so on. You can include additional values to be added in this formula simply by adding the addition operator after each cell reference. You can also subtract, multiply, and divide within the same expression.
Note: When using multiple operators within the same expression, make sure that you are aware of the order of operations and that you use parenthesis to separate expressions.
2. You can also compose your formula outside of the Formula bar by double-clicking on the cell where the results will appear. Repeat the steps above for the composition of the formula.
3. Hit Enter to see the results for the formula you just entered.
Method 3 – Use Insert Function to Compose Expressions in Excel
When you are working with complex functions that have multiple values, the Insert Function tools makes the task much simpler. In the example below I use the SUM function which is pretty simple in nature, but if you try some of the more complex functions like BETA.DIST, ACCRINT, and others you will see how much easier composing a formula is using the Insert Function window.
1. Click on the cell where you wish to compose the formula.
2. Click on the Insert Function button next to the Formula bar f(x).
3. The Insert Function window will open. Select the function you wish to use from the functions list.
4. Click OK.
5. Click on the Number1 field.
a. Click on the cell you wish to use or type in the cell reference.
6. Click on the Number2 field to enter the second number, repeat for additional values.
7. Notice that as you select the cell references, Excel provides you with preview of the result.
8. Click OK when you are done composing the expression.
The result should appear in the cell you composed the formula in.
Tips and Tricks
A couple of tips and tricks to help you with formulas in Excel 2010.
If you select more than one cell containing numerical values, Excel will automatically calculate the Average, Count, and Sum for the selected cells.
1. Select a couple of cells.
2. The bottom right corner of the Excel window will automatically display the Average, the Count (how many items were selected), and the Sum of the items selected.
When composing a formula using a function, Excel will automatically display a tool tip describing the elements in the expression.
If you are trying to determine what values your formula is using you can use the Trace Precedents tool to trace those items.
1. Click on the cell you’re interrogating.
2. Click on the Formulas tab in the Office Ribbon.
3. Click on the Trace Precedents button in the Formula Auditing group.
A set of arrows will appear showing you the cells used to compose the formula in question.
