Work with formulas in tables

- [Voiceover] If you convert data to a table, you get some visual advantages, but you also get some formula capability that's not exactly obvious. I've got a worksheet here and it's not a table, but I'm going to convert it into a table. There are four ways to convert data to a table. Before doing that, make sure that the data you're working with is in a solid cluster, no empty rows, no empty columns within the data. There are empty cells out there in Column I, that's okay. I'll click on one of the cells and press Control+A. Then press Control+. a few times. This moves the active cell around the corners.

If you're not sure that that's the entire list scroll a little bit more. Looks like it is. So that data is all together as we say. If there's other data on the worksheet off to the right, you would make sure ahead of time there's at least one empty column separating that from this data. So clicking within the data, we can either press Control+L or Control+T for creating a table. We could on the Home tab create a table by way of this button. In the Style's group, Format as Table. Doesn't give us a reason for why we might want to, but on the insert tab where we see Table, that provides the rationale.

Create a table to organize and analyze related data. Tables make it easy to sort, filter, and format data within a sheet. Sounds good, click it. Remember, we could have gotten here faster with Control+T or Control+L, same idea. Here's a dialogue box. Excel figures out the extent of the data, looks just fine. Be sensitive to the line my table has headers. Normally it does and normally Excel picks this up, recognizes it. Click Okay, we've created a table. Notice what happens when we scroll. We'll see the column letters disappear.

Row 1 becomes our headers as we scroll up and down using the mouse wheel. Now, when we write formulas this throws people sometimes, and it's one feature about tables that some people don't like. And I can understand. We're gonna create a new column here. Another aspect of tables is that when you add new information on the right side of the table or below, it automatically becomes part of the table. We're gonna calculate new compensation. I'm going to abbreviate that term because of the length of it. New Comp. Watch what happens when I press Enter.

That column is part of the table. Now off to the right and outside of the table, I'm gonna use a percent increase here. 2.71%. And I'm going to write a formula here. I can write it in one of two ways, I'll show you both of them. Column doesn't have to be wider here. =, now I'm gonna use the math that uses cell H2 and N1. Look what happens when I click on cell H2. It picks up that term. It looks a little strange maybe, but it is in the compensation column. * the cell to the right. Now that needs to be an absolute address so I'll press the function key F4.

To complete the formula we've already calculated the increase by multiplying the initial compensation by the percent. That gives us the amount of the increase, and now + or add on to the original compensation. That seems like a long formula, but very little typing there. I typed =, I typed the *, I pressed F4 to make it an absolute address, I put in the +, but the other two entries were simply clicks on cell H2. So look what happens when I press Enter. Automatically copied down the column. On second thought, I should have done rounding.

Let's get rid of those pennies. It's not uncommon to see yearly salaries expressed as whole numbers. Let's redo this first formula here by adding the function called round. If we wanted to round this to the nearest penny at the end of it we would put in ,2, nearest whole number ,0) Here too I'll press Enter. Watch Column K, cleans it up nicely. Quick adjustment on the format, Click column K Comma would be okay. Maybe we don't need to see those decimals. There we go, so looking pretty good.

But we can also make reference to data here in different ways, and here it's easier to work with than you might expect. I want a total of say the compensation. Maybe the old compensation, the new compensation, I might want to do averages and so on. So what happens if I type, I'll start with Alt+= just to save a little bit of typing here, and I don't want to add up those cells. Now what we didn't see and I didn't show you is when we create a table it has a name. It's got a temporary name like Table 1, Table 2. Now I haven't changed this one yet, but I want to use the table, data in this table, and I'll press the letter T.

And we'll see in here table, now there probably were tables before, but they're no longer there. Any other tables here? Here's one. So in the midst of all these functions here, here's a table, and I'll Tab it into place. And you'll see that it's highlighting the data. If I want to get to the fields, I'll use a [. This is the symbol just to the right of P on most keyboards. Not a squiggly bracket but the angle bracket, the left one. And that brings up all the field names. So I'd like to tabulate the total for the compensation. I can Tab that into place, and then a ], we're all done, Enter.

If I happen to drag this to the right, and I'm working with Column H, I'm going to get totals for Columns I, J, and K, we probably don't want that in this case, but at certain times that would work as well too, even Column P there by the way. So that number there, and it actually picks up the new Comp field name. Here we'd be saying Benefits and of course it doesn't make sense to add those. Job Rating we probably wouldn't add either, but we could quickly change that for example to be an average, so we'll know the average job rating. We'd see that entry there. And of course we need to format these to make them look better.

So for these two for example. On the Home tab, the comma button probably makes sense. Don't need to see the decimals and so on. And here too, show fewer decimals. But we've got that capability. Now the name Table 3 isn't very descriptive and we might not want to keep it. So if you click inside of a table and go to the Design tab that's a contextual tab specifically devoted to features in a table, you'll see the Table Name off to the left hand side. So I'm going to change that name, Table 3 to HR_Table.

I'll use an underscore there to separate the words. So I've given this the name HR_Table. And over here in our formulas, we see how that's been adjusted. It's called HR_Table. Now here's another advantage, not one that everybody needs, but I want to know how many people do not have benefits. So if I'm just using normal cell formulas, I could use this function called countblank. And I'm looking at Column I here, so I'll just click Column I to count the blank cells. Unfortunately what this does though, it counts all the blank cells in the worksheet, all the way down to the bottom.

Now I could probably do some math here and come up with the real answer, but that's over a million. I'm just looking for the blank cells within the data. So let's try this a different way. =countblank( Now I'll press the letter H because I want the table name to pop up. Now I could be typing it, I could type HR_Table, but I'll type H, there it is in the list. Click it, Tab it into place, [ to bring out the field names. And I'm about to count the benefits column right there. Tab that into place, ], Enter.

That's how many blank cells are in this list. So 254 people in our list do not have any kind of benefits here. And that's much easier to do than doing the math here that would allow us to somehow come up with the number of blank cells within the list. This is looking through the entire column. Of course there are other ways to do this too. We could highlight all the cells or type in I2:I700 or whatever it is here too to get to it. Different ideas. Now going back to this formula that we saw earlier. Whatever reason this doesn't quite appeal to you, don't like the look of it, and you're used to writing formulas in a different way or you're used to seeing cell addresses.

We can type, for example, and I'll do it now =H2, I'll simply type it, *, and I'll click on this cell that's outside the table over there. N1, press F4 to make it an absolute address, +, and I won't click on H2, I'll simply type it. As I press Enter, the formula will be correct, the data will be correct, and I should as I did before use the round function, make sure we don't have any pennies out there, and round that to either zero decimals for whole numbers or two, if we want to round it to the nearest penny.

I'll use this one, I'll press Control+Enter so the active cell does not move, but even pressing Enter will work just fine. I'll just press Enter, and that gets copied all the way down the column. And now these have formulas that you're more familiar with. And I'm not suggesting in any way that the previous formula that we saw, even though it was longer, was bad. It's just a question of familiarity and who might else be working with this worksheet. So as I press Control+Z a few times here we go back to this look. So even though that might seem awkward to you. You might experiment with it a little bit, get comfortable with it, and then you begin to recognize how you can use these field names in calculations, as we saw over here, and in the other examples here, and also here too.

So working with formulas inside of tables. At first perhaps a challenge, looking a little bit strange, and yet ultimately there's some features here that may be beneficial to the way you work with data.