Pin Me

Microsoft Excel: Three Methods of Entering Formulas

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

Problem: There are three basic ways of entering simple calculations in Excel. Knowing all three ways will allow you to enter formulas faster, according to the situation. Consider the worksheet shown in Fig. 336.

  • slide 1 of 2

    In Fig. 336, you want to calculate total cost in E3 as the Case Quantity in B3 times the Unit Cost in C3.

    Strategy: You can simply type the formula.

    1) Put the cell pointer in E3 and type =b3*c3, as shown in Fig. 337, and then hit Enter.

    2) The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculation, as shown in Fig. 338.

    Advantage: If you are a good typist, you only need to type seven keystrokes.

    Disadvantage: This method gets complicated when you are dealing with complex formulas.

    Alternate Strategy: Use the arrow keys. Anyone who was using spreadsheets in the days of Lotus 1-2-3 often used this method. Once you have mastered this method, it is very fast and very intuitive.

    1) Move the cell pointer to E3. As shown in Fig. 339, type an Equal sign to let Excel know that you are about to enter a formula.

    2) Hit the Left Arrow. As shown in Fig. 340, a dotted border surrounds the cell to the left of E3. Excel starts to build a formula of =D3.

    3) Hit the Left Arrow key two more times. Your provisional formula is now =B3, as shown in Fig. 341.

    4) On the keyboard, hit the * key. You can either hit Shift+8 or use the Asterisk key on the numeric keypad. The dotted border will disappear from B3 and be replaced by a solid-colored border, as shown in Fig. 342. Hitting any operator key, such as Plus, Minus, Asterisk, or Slash, tells Excel that you are moving on to the next part of the formula.

    5) Next, hit the Left Arrow key. The dotted border reappears. You now have a provisional formula of =B3*D3, as shown in Fig. 343. This isn’t quite right, yet, but you’re getting close.

    6) Hit the Left Arrow key one more time. As shown in Fig. 344, the provisional formula is now correct.

    7) Next, hit Enter. The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculation.

    Advantage: You never have to type cell references with this method. You merely point to them, using the arrow keys. If you are building formulas that are based on cells near the formula cell, formulas can be entered very quickly with this method.

    Alternate Strategy: Use the mouse. Follow these steps.

    1) As shown in Fig. 345, type an Equal sign. This tells Excel that you are about to enter a formula.

    2) Using the mouse, touch cell B3. Excel starts to build your formula, as shown in Fig. 346.

    3) Using the keyboard, hit the Asterisk key on the numeric keypad or the Shift+8 keys. See Fig. 347.

    4) Using the mouse, touch cell C3. The provisional formula now looks correct, as shown in Fig. 348.

    5) Hit the Enter key. The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculation.

    Advantages of the Mouse: It is easy to use the mouse to directly touch the cells you need in the formula.

    Disadvantage of the Mouse: It takes a long time to move your hands back and forth from the keyboard to the mouse. To enter the above formula, you have to hit a key, use the mouse, hit a key, use the mouse, and hit a key again. That is four movements back and forth from the keyboard to the mouse and back.

    Summary: There are three basic methods for entering formulas in Excel. Using the right method for the situation can radically improve your efficiency.

    See all Microsoft Excel tips

  • slide 2 of 2

    Images

    Fig. 336Fig. 337Fig. 338Fig. 339Fig. 340Fig. 341Fig. 342Fig. 343Fig. 344Fig. 345Fig. 346Fig. 347Fig. 348





© Copyright 2016 brighthub.com.