Join Curt Frye for an in-depth discussion in this video Creating a formula, part of Excel 2007: Introduction to Formulas and Functions.
- View Offline
Excel formulas enable you to summarize the data contained in your worksheets, which helps you discover important information about your business. In this lesson, I'll show you the three main ways to create a formula in Excel: by typing, by using the controls on the Formula tab of the Ribbon User Interface, and by using the Insert Function dialog box. When you're ready to create a formula by typing, you click the cell where you want to create the formula and start typing. First, you type an equal sign, which indicates to Excel that it's to treat the remainder of the text in the cell as a formula as opposed to a number or text value.
In this case, I want to find the average of the values in cells B3 through B10, so I will start typing the word Average. When I type the first few letters, Excel displays a list of functions that start with the letters I just typed. This feature is called Formula AutoComplete. If you don't see a list of functions when you start typing, someone must have turned off Formula Auto Complete in your copy of Office. I'll press these the Escape key twice, the first time to get rid of Formula AutoComplete list and the second time to stop entering data into the cell.
Now I can click the Office button and click Excel Options to display the Excel Options dialog box. On the Formulas page of the Excel Options dialog box, you'll see in the Working with formula section a checkbox for Formula AutoComplete. If this check box is cleared, you should check it and then click OK. When you do, you will turn on the Formula AutoComplete and you'll be able to complete the rest of the lesson. Now, to enter the Average formula you type an equal sign followed by aver.
When you do, you display the list of functions that start with those letters. The Average function is first on the list and it's already highlighted. If you wanted to select another one of the visible functions, you could use the Down Arrow key to highlight it, but in this case I want to use Average so I'll use the Up Arrow key to move back up and now that it's highlighted, I can press Tab. When you do Excel inserts the word Average, which is the name of the function you want to use and also hopefully gives you your left parenthesis, so you can start typing in your arguments.
Now that I have the function ready to go, I can type in the cell range, which is B3 through B10, type a right parenthesis to end the function, hit Enter and my average appears in cell E3. To enter a formula using the controls on the Formula tab of the Ribbon user interface, you click the Formulas tab to display the controls available to you. The Excel 2007 Ribbon breaks out the functions by the type of calculation they perform, including financial calculations, Date & Time and Math and Trigonometry.
I always thought that trig was part of math, but it's been a while since my last class. In this case I'll click AutoSum and then click Average. When I do, Excel creates an Average formula and it identifies cell E3 as a potential value for me to average. That's actually not the value range that I want to summarize. That's B3 through B10. So, I will type in that cell range B3:b10. And upper/lowercase doesn't matter, so I don't need to correct that and I already have my right parenthesis, which indicates the end of the function.
I can press Enter and Excel gives me the same value as before. Just another way to create the formula. The final way to create a formula in Excel is to use the Insert Function dialog box. There are several ways to open the Insert Function dialog box. The most prominent is on the Formulas tab and it is here at the left side of the Ribbon. You can also click the Insert Function button just to the left of the Formula Bar. It doesn't matter which one you click; you end up in the same dialog box. So I'll click the Insert Function button here by the Formula Bar.
You can use the controls in the Insert Function dialog box to find functions that you want use in your formulas. If you want to search for a function, you can type what you want to do in the Search for a function box. So let's say that I wanted to find an Average. That's what I want to do. When I click Go, Excel updates the Insert Function dialog box here in the Select a function list to indicate which functions help me find an average. The one at the very top is the one I want to use, Average.
So it's selected and when I click OK, Excel displays the Function Arguments dialog box. Now, when you create an average, you can specify which cells that you want to average out. So in this case I want to average every cell from B3 through B10 except for cell B6. So I'm going to skip the value for the year 2006. So to create my arguments, I will type in the cell range B3 to B5.
That's the first range that I want to summarize and you'll notice that Excel is filling in the values in the cell range over here. That's a great way to check yourself as you're typing in your formula using the Function Arguments dialog box. If the values don't look right, then you probably have your reference wrong. I want to summarize another range, which is B7 through B10, so in the Number2 box I'll type B7 through B10. Those are the only cell ranges that I want to summarize, so I can press OK and Excel creates the formula for me.
Regardless of how you choose to enter your formula, you'll find that Excel has several ways to make the process easier for you.
- Copying and pasting the value results of a formula
- Creating formulas to count cells that meet certain conditions
- Connecting Excel to an external data source
- Building a two-input data table
- Summarizing data using an Excel table