Join Dennis Taylor for an in-depth discussion in this video Creating simple formulas, part of Excel 2016 Essential Training.
- For many Excel users, writing formulas, creating formulas, one of the most common things they do. In this worksheet called Formulas, in the workbook Creating Formulas and Functions, we need some formulas in row 4, also over in column H and column I. Formulas many times are straightforward, simple, easy to use, but they start with a different concept than you might be familiar with. In cell B4, we want to substract the two numbers above it. Now, if that's all you were told and you never worked with Excel, you might be typing something like: 120 minus 100.
But in Excel, typically we write formulas based on location, and that implies content. One way to state this is we're about to say that cell B4 equals whatever is in B2 minus whatever is in B3. And we don't need to type B4, we're already at that cell, we type =, formulas in excel begin with =. Now there are a few different ways to enter formulas. The most common way, at least initially, is to type addresses.
B2. And what we're saying, in effect, is whatever is in B - whatever is in B3. We'll type B3. This opens a door for the possibility that later if we need to adjust those sales or overhead numbers, our formula will react automatically. So, we complete the entry with enter, or tab, or any of the arrow keys. That's it. We got a formula. Now, get used to the idea that when you click on a cell, keep your eye on the formula bar. And it really starts to become important as you work with formulas.
Anytime you're looking at a worksheet, you click on a cell, you see in the formula bar, is it a formula, is it not? You develop a sixth sense at times about which cells have formulas but which don't, but that immediately confirms it when you click on a cell. Now, another way to write this, not necessarily better, but it does not involve typing addresses, so I'm going to delete this entry. I'm going to type = and then press the up arrow twice to get to cell B2. And we see that in the formula already: =B2.
Then I'll type - and press the up arrow once, and you see the formula: =B2-B3, that's all we need. Enter. So the advantage? It might've taken a tad longer but on the other hand we didn't type an address, or, possibly worse, we didn't mistype and address. Same formula here. If you look in the formula bar you see it, if you double click in the cell you see it as well. A third way might involve using two hands, it could be faster even though that might sound a little unwieldy.
I'm going to delete this and start over. This time I'll type =, click cell B2, type -, click cell B3, and press enter. Same result in all three different methods here for entering the formula, it's just a question of what works best for you. Either typing the entry, using arrow keys, or using a combination of typing and mouse. Now, if that's all you had learned about formulas, and you went to cell H2 to put in a total here, you would probably begin this way: type =, we want to add up those cells to the left, we could put in B2 and then a +, and then C2, and a +.
I'm not going to finish this, but I could, if I wanted to, put in D2+E2+F2 and so on. Imagine how unwieldy this is going to be, not only here, but what if we were doing 12 months? This, although it could produce a correct answer, is certainly not the best way to do this. So, let's get rid of this. Built into Excel are what we call functions. There are over 450 of them. A capsule description of what a function is: it's a shortcut for a formula. That doesn't apply in every single case, but that's a general definition of what a function is.
On the Formulas tab in the ribbon you'll see all kinds of functions listed here, in various categories. Now, the most common function in Excel is sum, for nearly everybody. I'm going to start typing in cell H2: =sum, and notice what happens when I type the letter S. There are lots of other functions that begin with the letter S, sometimes you see one here and there and you know what it is, other times you get a little curious, we're not say at that point just yet, and we don't have to necessarily scroll up and down and see these.
And this is so short I'm not going to take advantage of any way to type this in any faster. Just type: =sum(, all functions are followed by an open and close parentheses. Now, you wouldn't know what to do next necessarily, but, with the mouse let's highlight cells B2 through G2. And you can either drag leftward or rightward, where ever you start, for example I'm going to start in G2. I'm pointing in here, hold down the left mouse button, drag leftward into B2.
I said that all functions are followed by open and close paren, but Excel does this for you if you're only using a function by itself. So I'll simply press enter. And we've got an answer there. And you could certainly verify that. By the way, if you do highlight these cells your status screen at the bottom of the screen probably gives you that total as well. Now, if you deactivate a status bar, or if you don't have some of the checkmarks set up, you won't see the total there, but that verifies that it's working. For an average, right here, if we want to know the average of these first six months, we need to add up these entries and divide by six.
But we've already done the addition right here. Now remember, when you click on a cell, get in that habit of looking in the formula bar to remind you. That may look like 1505, but in a certain sense it's not that at all, it's a formula, in this case, to be more specific, a function that adds up the six cells to the left. The average here, we simply need to take that value, H2, now you can type it, or in this case, possibly, =, click this cell, /6.
If you are typing you don't need to capitalize the H. If you click on it, it's automatically capitalized. / is the division symbol. Now this is the / that on most keyboards is in the bottom row, usually a few keys to the right of the letter M. You cannot use the other slash, the one that goes in the opposite direction. =H2/6. So in our examples here we just used division here, we used a sum function here, our original formula over here in cell B4 used subtraction, we haven't used the multiplication symbol, it's an asterisk, but we're seeing some of the basic building blocks here, of how to build simple formulas.
And again, remember different techniques. Many people simply type the cell addresses, but whether you use that technique or the technique of using the mouse and the keyboard together, writing formula's something that's very important as you work with Excel.
- Working with the Excel interface
- 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