Excellence with Excel: Functions, Formulas and Keystrokes

Formulas and Functions are an important part of this spreadsheet program. Without them, Excel would be almost useless. In this blog, we’ll explore how to use these tools to increase our efficiency.

Formulas

Excel uses formulas to accomplish mathematical and data operations. Most of us think of a formula as 1+1=2 or 4x5=20. In Excel, formulas are usually written with references to cells. For example, if cell A1 contains the value 35 and cell A2 contains the value 4, we would write a formula in cell A3 like the following: =A1+A2. The result in cell A3 would be 39. This is quite a simple formula, though, and can be calculated quickly without the need for a sophisticated software program.

Calculations that are more complex lend themselves well to Excel. We need to know a few rules before entering formulas that are more complex. First, there is the “Pardon My Dear Aunt Sally,” or Order of Operations, rule. In math class, we learned that 5+2x3=21. In Excel, it will calculate to 11. Why? Excel processes math in the following order: Parentheses, Multiplication, Division, Addition and then Subtraction (Pardon My Dear Aunt Sally). Therefore, in our example, Excel will first multiply 2x3 and then add 5. In order to get our result of 21, we need to place parentheses around the 5+2. Our formula will then be (5+2)x3. Knowing that formulas in Excel begin with an =, our cell would look like =(5+2)*3. The asterisk is the multiply symbol in Excel.

AutoSum

A nice feature in Excel is AutoSum. In Excel 2003, it is on the Standard Toolbar as an icon that looks like a capital E. In versions 2007 and 2010, AutoSum is on the Home tab, in the Editing Group. To use AutoSum, select the first blank cell at the bottom of a column of numbers and then click the AutoSum icon. A marquis will float around the cells that Excel thinks should be summed. Pressing the Enter key will accept the AutoSum suggestion and place the calculation in the selected cell. If you’re a keyboard person, press ALT + = (ALT key and the = key) to insert the AutoSum. Much more can be done with the AutoSum feature. A quick search on the Internet will reveal more options.

AutoFill

AutoFill is something every Excel user should use. To try AutoFill, type January in cell C1, and then press the Enter key. Select cell C1 and look at the lower right corner of the selected cell. You’ll see a small black square. This is the AutoFill  handle. Rest your mouse cursor over this AutoFill handle until it turns into a small, black plus sign. Click and hold your left mouse button while you drag your mouse down for about 7 or 8 cells.When you release the mouse button, you’ll see that Excel has filled in the months in proper order. AutoFill also works with days of the week, Quarter 1, and some numeric values. If AutoFill doesn’t provide the results you seek, click the Options icon in the lower right--after releasing your mouse--and choose one of the listed options.

Functions

I’ve helped thousands of people in the Valley learn Excel. Most of them had never used functions before taking one of my classes. They were missing out on some very useful, yet easy to use, calculation tools.

Functions are formulas that are built into Excel. They are used to calculate everything from dates and times to mortgage payments. Functions always begin with an = and have the function name followed by parentheses. Most functions need arguments in the parentheses. Arguments tell the function what to use for the calculation.

TODAY()

Do you need the current date to display in your worksheet? Select a blank cell. Type =TODAY() in the cell and press the Enter key. The current date will appear. When you open the worksheet tomorrow, tomorrow’s date will appear.

AVERAGE()

The Average function does just what its name implies--it averages numbers. To use the Average function, type some numeric values in cells A1 through A5. Select cell A6. On the Home tab of Excel 2007/2010 or on the Standard toolbar of Excel 2003, look for the AutoSum icon. It looks like an uppercase E. To the right of this icon you’ll notice a small triangle (it’s called a drop-down icon). Click this drop-down and select Average. Excel will create a marquis around your numbered cells. Press the Enter key. You’ll see the average of the numbers. The formula in cell A6 should be “=AVERAGE(A1:A5)”.

Sometimes Excel doesn’t guess correctly at the values to be averaged. If this happens, do the following: While the marquis is still moving, select the range that should be averaged. You’ll notice the cell references in parentheses change to your selected range. Press the Enter key.

CONCATENATE()

The Concatenate function merges two or more cells together into one cell. Try this: In cell C1, type John. In cell C2, type Doe. In cell C3, type CONCATENATE(C1,C2) and then press the Enter key. You’ll see JohnDoe. Not quite right, is it? It would look much better with a space between the entries. Let’s fix it. Select cell C3 and press your Delete key (this removes the current formula). Type CONCATENATE(C1,” ”,C2) and press the Enter key. The trick here is to make sure you have a space between the quotation marks. If you’ve done this correctly, you’ll see John Doe in cell C3 with a space between the names. Think about how many times you could use this function to merge two or more cells together.

PMT()

Did you ever wonder what the monthly payment would be for a car loan or a mortgage? The PMT() function can help you. Try this: In cell D1, type Amount. In cell E1, type $23,000. In cell D2, type Rate. In cell E2, type 6%. In cell D3, type Months. In cell E3, type 60. You’ve just listed the amount you want to finance, the interest rate and the length of the loan for a new car. Now, select cell D4 and type Payment. In cell E4, we’ll place the PMT() function. This is a bit tricky, so follow the steps carefully. Select cell E4. We’ll use the function wizard for this function.

To the left of the formula bar is a small con that looks like an fx. Click it. The Function wizard will open. In the Select a function area look for the PMT listing. If PMT is not listed, select the Search for a function and type PMT, then click the Go button. When you see PMT in the list, select it and click the OK button. The Function Arguments dialog box will appear. The first argument is Rate, so type E2/12 in the field. Why do we include the /12? Because our interest rate is for a year and we want to calculate a monthly payment. (The / is used for division in Excel.) Select the Nper field. This is the number of periods, or payments, we will be making. Type E3 in this field. Select the Pv field. This is the present value of the loan, or the amount we will be borrowing. Type –E1 here. Why the minus sign? Because we want the monthly payment to appear as a positive number. If we did not include the minus sign, we would see the monthly payment in parentheses, indicating a cash outflow. Not a big deal, but it looks better if there are not parentheses. OK, now click the OK button and you’ll see the result--$445.65. I hope you have a good enough job to afford your new car.

Many other functions can save considerable time when calculating in Excel. I teach a functions class at Lehigh Carbon Community College. I show the students how to use the Function Wizard, search for functions they can use and understand the simplicity of using this feature. Visit my website, www.absupportweb.com, and click the Teaching Schedule link to view the dates and times of classes.

Keyboard shortcuts

Most Excel users use the mouse to navigate around their worksheet. However, using keystrokes can save a considerable amount of time. Following are some of the most useful keystrokes in Excel:

CTRL-Home – Go to cell A1

CTRL-End – Go to the end of the used portion of the worksheet

CTRL-PageUp – Go to Previous worksheet tab

CTRL-PageDown – Go to Next worksheet tab

CTRL-A – Select the entire worksheet

SHIFT-Spacebar – Select a Row

CTRL-Spacebar – Select a column

CTRL-P – Print a worksheet

CTRL - ; (semicolon) – Insert the current date

CTRL - : (colon) – Insert the current time

CTRL-N – Start a new workbook

Conclusion

Excel is a very powerful program that allows users to quickly perform many calculations. I’ve only touched on a few of the features in this blog. If you would like a “Tips on Using Excel” file (tell me your Excel version--2003, 2007 or 2010), or have any questions about using Excel, send me an email at support@absupportweb.com. And remember--it takes time to learn software. Be patient and you’ll soon be using Excel like a pro.