Join Dennis Taylor for an in-depth discussion in this video Using simple formulas, part of Excel Essential Training (Office 365).
- [Instructor] For many Excel users, writing formulas is one of the most common things they do. Now, many times formulas are straightforward, they're simple, easy to use. They do start with a concept, though, that might surprise you a little bit. We want to calculate profits on this worksheet. Worksheet's called Formulas. It's in the workbook 03 - Creating Formulas and Functions. The total here is going to be 20. We're going to be subtracting these two cells. But we don't type 120 or 100. All formulas in Excel begin with the equal sign, and though we can occasionally and want to use external numbers, most of the time we use addresses.
We want to say, in effect, whatever is in B2, from that we want to take away whatever's in in B3. So we write the formula, and this is one of three major ways to write a formula, we type in the location of the first cell, B2, minus B3, Enter or Tab, we have our answer. Another way to type this, so I delete this entry, equal, then use the arrow keys to go to the cell you're interested. Writing a formula this way could be better, but the cells have to be somewhat nearby, otherwise it would take a long time to get there.
Minus, and we press the up arrow, the B3 pops in, Enter. And a third way, might involve two hands, but equal, this time we'll click on cell B2, minus, we click on cell B3, Enter. Same result in all three cases. No matter how you write the formula, though, you want to learn to keep an eye on what we call the formula bar. It's at the top of the screen. In a certain sense, this cell doesn't really contain that 20, it contains a formula. Click on the cell above it, yes, that does contain the value 100.
We keep an eye on the formula bar in much the same way we keep our eye on the rear-view mirror in a car, look there frequently. Now, we want a total in cell H2. If all we knew about formulas is what we previously saw, then we would do something like this. Equal, type or click on B2, plus C2, plus D2, and on and on and on. That is not the best way. Imagine trying to do that for 12 months too. I'll simply press Escape. Built into Excel are over 450 functions. There's a ton of them.
On the Formulas tab in the ribbon, you can track them down this way. A lot of times, you're not sure what you need. There's some math and trig functions, some more out here, some statistical functions, just a lot of them, all over the place. The most common function in Excel for most people is probably sum. And like a formula, this begins with equal as well, sum. You don't have to capitalize it. Left parentheses. You wouldn't know to do that instinctively, but that's what we do. And then we highlight the cells in question, either dragging left to right, B2 to G2, or right to left, makes no difference.
Click and drag across. That notation means all the cells from B2 through G2. And we press Enter, and we have an answer. And in cell I2, we need to calculate an average. Of course, that's the total divided by six. We need to add these up. But we've already done the addition right here. So the formula here is simply equal this cell, H2, and the division symbol is the slash, that looks like this, not the one that goes in the opposite direction, divided by six, and Enter. And we'll adjust that format, perhaps, later.
Now, when we have formulas in place like this, and although we're not finished with this particular sheet, if I change this number 120, I'll change it to 125. Keep your eye on the 20 down here. It will change, we saw the formula, and these totals and averages to the right will change as well. And you can begin to see how powerful these formulas are and how we can make adjustments at different times. Also, if I go back to cell B3, I'll make a change here, this is gonna change the profits. It won't change anything to the right, since we don't have anything over there just yet, but sometimes we will see negatives and that's what happens.
The formula is reacting all the time. So in this movie, we've seen different techniques, using mouse and keyboard, for creating simple formulas. It's a task that's very important as you work with Excel.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.