See More About: , , , ,

3 Ways to Enter Formulas in Excel 2010

by

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.

enterexcelformulas2010a thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010b thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010c thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010d thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010e thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010f thumb 3 Ways to Enter Formulas in Excel 2010

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.

enterexcelformulas2010g thumb 3 Ways to Enter Formulas in Excel 2010

If you are trying to determine what values your formula is using you can use the Trace Precedents tool to trace those items.

enterexcelformulas2010h thumb 3 Ways to Enter Formulas in Excel 2010

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.

See More About: , , , ,
Originally posted May 10, 2010

We close comments after 30 days on GilsMethod.com and ask that you post questions and issues on our Answers website.

This helps us better serve you by allowing us to keep track of unanswered questions that are usually hidden in long comment threads.

Answers.GilsMethod.com is a community-powered support website where you will be able to post questions, and other users including us will respond with expert advice.

So if you are having trouble with your PC, Mac, iPad, iPhone, or other piece of technology, head over to Answers.GilsMethod.com and ask away.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    Thanks for posting and sharing your experience…it’s not ignorance…it’s being overwhelmed with Technology…there’s just too much to know…trust me. Thanks and I hope you continue to find the site useful.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    Thanks for posting and sharing your experience…it’s not ignorance…it’s being overwhelmed with Technology…there’s just too much to know…trust me. Thanks and I hope you continue to find the site useful.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    Thanks for posting and sharing your experience…it’s not ignorance…it’s being overwhelmed with Technology…there’s just too much to know…trust me. Thanks and I hope you continue to find the site useful.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    That’s pretty strange, I just tried it in a new sheet in Excel 2010 and the formula is working fine and I was able to drag it down and copy it to other cells. Have you tried using the formula in a new sheet? Could be something with the existing sheet. Let us know how it goes.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    That’s pretty strange, I just tried it in a new sheet in Excel 2010 and the formula is working fine and I was able to drag it down and copy it to other cells. Have you tried using the formula in a new sheet? Could be something with the existing sheet. Let us know how it goes.

  • http://www.gilsmethod.com Gilberto J Perera

    Tech Exec,

    That’s pretty strange, I just tried it in a new sheet in Excel 2010 and the formula is working fine and I was able to drag it down and copy it to other cells. Have you tried using the formula in a new sheet? Could be something with the existing sheet. Let us know how it goes.

  • Tech Exec

    I am having an issue with what seems like a very simple situation. I’m having a tough time finding the solution…

    I’m using Excel 2010 and when I enter a formula like “=(PROPER(TRIM(B2))” and press the ENTER key, nothing happens. Furthermore, when I double-click the lower right handle or drag it down, the iteration is not copying the formula to the subsequent cells the way that previous versions (2000, 2003, 2007) worked. What is going on??

    Thanks in advance!

    • http://www.gilsmethod.com Gilberto J Perera

      Tech Exec,

      That’s pretty strange, I just tried it in a new sheet in Excel 2010 and the formula is working fine and I was able to drag it down and copy it to other cells. Have you tried using the formula in a new sheet? Could be something with the existing sheet. Let us know how it goes.

    • Tech Exec

      Well… I feel kind of silly, but I did finally locate the issue. It was that the cell was formatted as Text. As soon as I changed the format to General, it worked fine. I thought I had tried that two or three times, but when I selected the individual cells instead of the column to make that format change, it worked.

      Hopefully, someone else may learn from my ignorance. :)

    • http://www.gilsmethod.com Gilberto J Perera

      Tech Exec,

      Thanks for posting and sharing your experience…it’s not ignorance…it’s being overwhelmed with Technology…there’s just too much to know…trust me. Thanks and I hope you continue to find the site useful.

  • Pingback: Use the NETWORKDAYS.INTL Function to Calculate Workdays Using Excel 2010 | GilsMethod.com