START A FORMULA WITH = OR +
Problem: Every Excel formula has to start with an equals sign. There is no equals sign on the numeric keypad. It’s a pain to type the equals sign.
Strategy: In order to make the transition from Lotus 1-2-3 to Excel less painful, Microsoft allows you to start a formula with the + sign. Because there is a huge plus key on the numeric keypad of most desktop computers, it is often easier to start the formula with plus than with equals, especially if you’re entering the rest of your formula using arrow keys. You simply type the plus sign and your formula.
Figure 148 Start a Formula with = or +
Gotcha: After you press the Enter key, Excel will edit the formula to add an equals sign before the initial plus. This will cause people to ask why you are using =+2+2 instead of just =2+2.
Figure 149 Excel accepts the formula, but adds an equals sign.
THREE METHODS OF ENTERING FORMULAS
Problem: I’d like to enter formulas faster. What are the three ways of entering simple formulas?
Strategy: There are three ways of entering formulas. Learning the arrow key method will dramatically improve your efficiency with Excel. This topic will compare all three methods.
Say that you want to calculate total cost in E3 as the case quantity in B3 multiplied by the unit cost in C3.
Figure 150 Calculate unit cost times case pack.
One way to make this calculation is to simply type the formula:
71
72 |
POWER EXCEL WITH MR EXCEL |
|
|
1. Put the cell pointer in E3 and type =b3*c3 and then press Enter.
Figure 151 Typing takes only seven keystrokes.
2. The formula will calculate. You will see the original formula in the formula bar above E1. The work- sheet will show the result of the calculation.
Figure 152 After pressing Enter, Excel calculates the formula.
This method is great for short functions that require only a few keystrokes. However, this method gets complicated when you are dealing with complex formulas.
Alternate Strategy: Another way to enter calculations is to use the arrow keys. Anyone who was using spreadsheets in the days of Lotus 1-2-3 often used this method. When you have mastered this method, it is very fast and very intuitive. Here’s how it works:
1. Move the cell pointer to E3 and type an equals sign to let Excel know that you are about to enter a formula.
2. Press the Left Arrow key. As shown in Figure 153, a dotted border surrounds the cell to the left of
E3. Excel starts to build the formula =D3.
Figure 153 Type equals, press left arrow.
3. Press the Left Arrow key two more times. Your provisional formula is now =B3.
Figure 154 Press left arrow two more times.
4. Press * on either the keyboard or the numeric keypad. The dotted border will disappear from B3 and be replaced by a solid-colored border. Pressing any operator key, such as +, -, *, or /, tells Excel that you are moving on to the next part of the formula.
Figure 155 Typing an operator returns focus to E3.
5. Press the Left Arrow key two times. The dotted border reappears. You now have a provisional for- mula of =B3*C3.
PART 2: CALCULATING WITH EXCEL |
73 |
|
|
Figure 156 Left arrow twice.
6. Press Enter. The formula will calculate. You will see the original formula in the formula bar above
E1. The worksheet will show the result of the calculation.
Additional Details: With this method, you never have to type cell references. You merely point to them using the arrow keys. If you are building formulas that are based on cells near the formula cell, you can enter them very quickly using this method.
Although I used several paragraphs and five screen shots to show this method, it required only eight key- strokes, many of which were repeats of the same keystroke. Further, because you are allowed to start a formula with a plus sign instead of an equals sign, you can enter the entire formula using the keys on and around the numeric keypad on a desktop computer (that is, +←←←*←←Enter).
Alternate Strategy: Another way to enter calculations in Excel is to use the mouse. Normally, people use the keyboard to type the equals sign, math operators, and enter and the mouse to click on cell references. Moving your hand from the mouse to the keyboard takes a lot of time and dramatically slows the entry of formulas. Adding a few icons to your Quick Access toolbar can dramatically speed formula entry. Follow these steps.
1. Using steps from "Make Your Most-Used Icons Always Visible" on page 12, add icons for equals, 2 plus, minus, multiply, divide, exponents, left parenthesis, and right parenthesis to the Quick Access Toolbar. These icons are found in the “Commands Not in the Ribbon” category.
2. Right-click the Quick Access Toolbar and choose Show Quick Access Toolbar Below the Ribbon.
3. Start in cell E3. Click the equals sign icon.
Figure 157 Add these icons to the QAT.
4. Click on cell B3 with the mouse.
5. Click on the * sign in the QAT
6. Click on cell C3 with the mouse.
7. Click the checkmark icon to the left of the formula bar to accept your formula.
Figure 158 Complete the formula with the checkmark.
8. The formula will calculate. You will see the original formula in the formula bar above E1. The work- sheet will show the result of the calculation.
There are three basic methods for entering formulas in Excel. Using the easiest method for the situation can radically improve your efficiency.
WHY DOES EXCEL 2013 LOOK LIKE A SLOT MACHINE?
When you type a number in Excel 2013, you will see all of the dependent cells in the visible window animate with the new result dropping in to the cell as if it were a slot machine.
74 |
POWER EXCEL WITH MR EXCEL |
|
|
This is one of those beginner features introduced in Excel 2013 to show new Excellers that when you type a value in one cell, Excel is going to the trouble of updating all of these other cells. It seems silly, doesn’t it? The feature was removed from Excel 2016.
USE PARENTHESES TO CONTROL THE ORDER OF CALCULATIONS
Problem: In what order does Excel perform calculations? For example, is 2+3*4 equal to 20 or 14?
Strategy: In Excel, if you do not use parentheses, the default order of calculations is as follows: 1. Unary minus operation
2. Exponents
3. Multiply and divide, left to right
4. Add and subtract, left to right
Thus, with the formula =5+4*-5^3/6, Excel will do the following:
1. Figure unary minus on -5.
2. Raise -5 to the third power (-5*-5*-5 = -125).
3. Do division and multiplication from left to right (4*-125 is -500. Then -500/6 is -83.3). 4. Add 5 (-83.3 + 5 is -78.3).
The answer will be -78.3.
You can control the order of operations by using parentheses. For example, the formula =(5+4)*-(5^(1/2)) will yield the answer -20.1246.
Figure 159 Use parentheses to override the order of operations.
Additional Details: In math class, you may remember that nested parentheses use regular parentheses, then square brackets, and then curly braces. In math class, you might have written:
{(5+4)*[-5*(3/6)]}+3
Forget all that. In Excel, you use regular parentheses throughout.
((5+4)*(-5*(3/6)))+3
When you get the formula error message, it is often because you’ve missed a closing parenthesis.
The best trick is to watch the color of the last parenthesis. If it is black, then you have a balanced number of left and right parentheses. If it is any color, then you are missing a parenthesis.
Gotcha: As you enter or edit a formula, when you type a closing parenthesis, Excel bolds the correspond- ing opening parenthesis. However, this bolded condition lasts for only a moment and disappears before you can figure out what is going on.
LONG FORMULAS IN THE FORMULA BAR
Problem: In legacy versions of Excel a long formula would spill out of the formula bar, covering part of the grid. This is particularly annoying in row 1 because the formula covers the value from the formula.
Strategy: You can expand or collapse the formula bar. If you choose to expand the formula bar (with Ctrl+Shift+U), the grid actually shifts downward so that you can see the formula bar and the cell in the grid.
Here the formula bar is collapsed. You see only the first line of the formula.
Figure 160 Only one part of the formula appears.