All formulas begin with the equal sign and typically do not contain values but are based on cell addresses. Click a cell and observe formulas in the Formula bar.
- [Instructor] For many Excel users writing formulas is one of the most common things they do. Many times formulas are straight forward, simple, easy to use. But, they start with a different concept than what you might be familiar with. In this worksheet here called Formulas, and were in the workbook 03 Creating Formulas and Functions. We're about to put in a formula in cell B4. We want to subtract the two entries there. Now, in once sense we could be saying, we want the cell B4 to be equal to whatever's in B2 minus whatever's in B3.
Now, you don't always think it out that way, but you might think, well do we have to type that all in? B4 is equal to? Well, we're at cell B4 so that's implied. Excel formulas begin with the equal sign. Now, I could type in 120 minus 100 and get an answer. but nearly always in Excel, when we refer to numerical information, if it's in the worksheet, we refer to it by cell location. And what we really like to say is, whatever's in B2, so we can do this in three major ways.
The most common way, that most people get started with formulas in Excel, is to type the address. Whatever's in B2, so we type it. We don't have to capitalize the B. Minus whatever's in B3. And we press enter or tab and we have an answer. Get use to the idea that when you click on a cell, if it has a formula in it, you see different information in the formula bar than what you see in the cell. And that becomes second nature after a while as you click on various cells in a worksheet. It's almost the way you'd look out your rear view mirror in a car when you're driving.
Now, there's another way to type this. But, this doesn't involve typing the addresses. I'm gonna do this all over again. I'll simply press delete to accentuate the idea. Equal, now I'm pressing the arrow key to get up to that cell that we need. And then I'm gonna type minus, and now I'll press the arrow to get to the other cell, that one. And then enter. Advantage, we didn't type a cell address. Perhaps it takes a little longer to get there. And there's a third way to. And often it involves using two hands, but it could be faster. Type equal, click the first cell, type minus, click the second cell.
And in all three cases we complete by pressing enter or tab. Pretty straight forward, pretty easy. And the big advantage if we're typing in the numbers 120 and 100 is, if these numbers need to get adjusted, our formula does the work for us. Maybe the overhead here was only 90. We change it to 90, we get a different answer. Or in a more ominous case, what if the overhead was 140? Enter, and of course, sometimes it's negative. I'm going to undo those last actions. Control z, control z.
Now, if we wanna do a total in cell H2, based on what you've seen so far, here's what you would probably start to do. Equal, and then you're either gonna be typing B2, or clicking on it, then you'd put in a plus. And then you go to C2 and a plus and go to D2. And you're already getting tired. This is not the best way to do this. So, I'm simply gonna press escape. A better way to do this is to use what's called, a function. And the best known, probably the most widely used function in Excel, is called sum, S-U-M.
Left parentheses. You wouldn't necessarily know what to do next. But, with the mouse point to the beginning cell or the ending cell, it makes no different on either edge. Hold down the left mouse button and drag across the cells you wish to add. And in the example here all we need to do now, is to press enter and that's a total. Once again, click on the cell, look in the formula bar, remind yourself what has been done. You'll quickly get use to the notation style that we're doing there. We also want to calculate an average.
And you probably know how to do that manually. We need to add up the cells and divide by the number of entries. But, we've already added them and that's in cell H2. So, we can type equal, this time I'll type it. H2 and the division symbol in Excel is the slash. The one that looks like this, not the one that goes in the opposite direction. And the number six and enter. So, as you begin to work with formulas, you get pretty comfortable with the idea, all formulas begin with the equal sign. And most formulas involve referring to other cells.
And not only have we seen simple examples of using just one cell or two cells, we've also seen for the first time the use of a function in Excel. These are important concepts as we work with formulas and functions in Excel.
- Navigating Excel tabs and menus
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros