Excel's functions save you the trouble of creating complex formulas. These plug-and-play options let you drop your specific data and cell references into pre-existing formulas that do things such as calculate interest rates or count days between two dates. You'll find most of Excel's formulas (the program has about 350) under category buttons on the Ribbon's Formulas tab. With these drop-down lists, you can locate functions under headings such as Auto-Sum, Logical, and Date & Time. Most users rely on a handful of functions, but problems still crop up with the most familiar of them. Here are solutions to a few of those predicaments.

I can’t find a function for the job I want.

The Insert Function button leads to all of Excel’s functions. You’ll also find an Insert Function option at the bottom of most of the drop-down function lists. Either avenue leads to a dialog box that helps you track down the right function. If you think there’s a function that does what you have in mind, you can start typing its name, then let Excel guess at which function you’re thinking of and help you write the formula. For example, you may know there’s a function that checks whether cell values match. You might remember its name involves something about exact matches. Click the cell, then click Insert Function on the Formulas tab. Type exact and press ENTER. The dialog box selects the EXACT function.

I always wind up typing information into my formulas incorrectly.

I want to check which cells an AutoSum function is adding up, but I can’t see the details of the formula anywhere.

You can check the range the function is using in a couple of ways. When you click the cell containing the formula, you should see the formula’s details in the Formula Bar between the main window and the Ribbon. If the bar isn’t there, click the Ribbon’s View tab and check the box beside Formula Bar. The formula’s arguments will indicate the range, such as “E5:E16.” If you click in the Formula Bar, you’ll also see a visual representation of the active range, as Excel puts a border around the cells referenced in the formula.

I need a quick way to change which parts of a list AutoSum is adding.

As mentioned above, you can click a cell containing an AutoSum function and then click inside the Formula Bar to make Excel highlight the range being added up. You can adjust this range by clicking one of the sizing handles on this cell border and dragging it up or down. Or you can move the entire range up or down (so that it includes different cells, but the same number of cells) by clicking one of the border’s sides and dragging it up or down. You can use the same technique with similar functions such as AVERAGE, MAX, MIN, and others.

I’d like a SUM formula to add up cell ranges that aren’t adjacent to each other.

Start a new SUM function with the Insert Function button. After you select SUM from the list, you’ll see two Number boxes that let you enter cell ranges. Click in the first Number box and then drag the cursor to highlight a cell range on the worksheet. Then click in the second Number box and highlight another range. When you’re done, click OK. Now you can use the technique in the previous tip to adjust the cell ranges at any time.

I have a SUM function adding up a mixed range of nonadjacent cells. Now I want to average those cells, but I don’t want to mess with reselecting that mix of cells again.

You can easily replace the SUM function with an AVERAGE function in the existing formula without changing anything else. Click the cell containing the formula, then click to put the cursor in the Formula Bar. Press the BACKSPACE key to delete SUM and type AVERAGE. The cell references all remain in place; only the operation changes

Home Excel 2007 Functions Troubleshoot Popular Functions
Search MS Office A-Z   |   Search Web Pages/ Design A-Z