Join Dennis Taylor for an in-depth discussion in this video Working with formulas in tables, part of Excel Tips Weekly.
- [Voiceover] If you work with large lists, you might wanna consider converting them to tables. The table concept treats the data as an entity, and it also introduces some different concepts as you work with formulas. I've got a list here. I've scoped it out ahead of time. There are no empty rows within it. If you do wanna convert data to a table, make sure you have no empty rows within the data and if there's other data off to the right, but if there's other data below this or to the right of it that you don't want to become part of the table, make sure it's isolated by at least an empty column, something like what we're seeing over here.
Now, I work with data a lot and I'm curious here as to how many people do not have benefits. I'd like to be able to count the blanks here. So, I'm going to type in a function for that. It's called COUNTBLANK. As soon as you see it in the list here, just tab it into place. There it is right there, COUNTBLACK, Tab, left parenthesis. I'm clicking Column G and Enter. Well, immediately I recognize, it's looking through the entire list. I can't do that quite as easily as I might want. Now, I certainly could drag across Column G or type in G2:G699, I think, is the last entry, but I'd have to know that ahead of time.
Not a major problem, but not something quite as easily accessible as I would want it to be. Let's hold that thought because we are gonna convert this data to a table and we will see some interesting things happen and we'll come back to this Blanks example and show you how that will work directly with a table. So, clicking anywhere within the data, we can do this one of four ways. We can press ctrl + t, think of T for Table, ctrl + l, think of L for List. On the Home tab, you see it referred to over here, Format as Table. It doesn't quite give you a good reason for doing it but at least it references it.
On the Insert tab, we also see it and here, perhaps, some rationale. "Create a table to organize and analyze related data. "Tables make it easy to sort, filter, and format data "within a sheet." Click it, Excel scopes out your data, figures out the extent of it, nearly always gets this right. Looks good. Do be sensitive to the fact that it either recognized or didn't recognize the idea that you've got a header row across the top. Click OK. We see a display. If we're not too happy with this, we see Table Styles up here to the right, 61 choices, lots of images here as you slide over this one, slide over that one, so on.
Keep an eye on row one. If you've never seen the table concept, as soon as I start to scroll, notice that the column letters and the field names change somewhat. What happens? As we're scrolling up and down, we see the field names, the column headers instead of the letters. That's handy. Now, filter arrows are present to perhaps encourage you to use them, but you don't have to have them there, and there's a button up here, uncheck them if you wish. Now, in a table, first of all, recognize that look that we have, the so-called banded rows. If you wanna change the look of that, you could remove that feature, Banded Rows.
You could try Banded Columns. Don't try them both together. It looks kinda weird, but anyway, make your choice there if you wish. A lot of people working with tables focus heavily on the visuals, I mean, it certainly looks different and if this is particularly wide, this one isn't, but if it goes across multiple screens, you can easily scroll left and right and keep in mind the person you're looking at. And if we insert a row here, I'll just right-click row nine and Insert. Notice how the color scheme readjusts. It actually copies formulas down, so that's handy too.
I have no date to put in here just yet, but automatically, this formula here that calculates the date difference is copied into here automatically. If I had other formulas, the more I had, the more this would be powerful and we'd see how that works too. Notice how the recoloring of the rows has already taken place. I'm gonna change my mind, press Control + Z. Don't need that. If I go to the bottom of the list here, if I add new information here, as soon as I finish this entry, you see how the row scheme has already picked up. It's part of the table. So adding rows to the bottom of the table automatically make it part of the table.
You don't need to do anything special there. Here, too, I'll just undo that with Control + Z. Now, zooming back up top, I can click on any cell and double-click the top edge, back up top. Now, I might be wanting to put in New Comp. here instead of Salary, but I want to type it in right here or possibly move this over. I can do that. Put it right there and it too becomes part of the table automatically. Pull that Comp. to be consistent with the other entry over there, there we are. Now, I'm going to write a formula. I'll write it in two ways. First of all, the way I might do it in regular Excel, if this were not a table, I'll type equal.
I'm going to click the compensation number over here. Look what happens. Now it looks a little strange. It might look strange but it will work properly. But more important about the formula is what will happen when I press Enter. So, everybody's going to get a 2.43% increase. That's outside of the table, but I'll refer to it, asterisk, the cell right there, and I want that to be an absolute address, so I'll press F4 and then add on to this or add it onto for it, either way, the current compensation. I'm going to press Enter. It filled it the entire column.
Now, a lot of people working with tables find this to be cumbersome looking. I do too in a certain sense, but it's certainly not wrong and it works and the field is called Comp., in other words, Excel recognizes the field name and so H1, that's what is referring to there. We've got brackets in there and at signs. It looks a little bit different. If we don't like this or it somehow doesn't work for us, we could write this by putting in the cell addresses. So instead of this information right here, I'm just going to type H2 and over here, H2, and as I press Enter, that completes the entry all the way down the column.
Any one of these looking the same way. So you can essentially override that. But we can also write formulas a little bit differently though if we have this feature. Now earlier, when I try to count blanks, I use this function COUNTBLANK right here. I clicked Column I and what I got was a huge number and that's gonna happen here too, same idea. But I'm going to approach it differently now since this is a table. I'll simply edit this. Now, instead of putting in the address here, I'm going to start typing T. Now, what I didn't say earlier was when you create a table, Excel gives it a name.
If you've done this in the same workbook earlier, you might see Table3, Table4, I've done one earlier. I see a Table2 in here. I don't see a Table1. In other words, the name of the table appears there. We can change that name if you wish, but there it is, Table2. It's the only one in the list. Therefore, it's the only one in this workbook, so I'll click Table2, tab it into place right there. And now, and this is the unusual step, I'm going to press left bracket. That's typically one key to the right of the keyboard, left bracket, and see what pops up, all the different field names that we see in this list and I'm looking in the Comp. Column G, so that's called Benefits.
In fact, I'm not necessarily even seeing the column letter, although I am in this case. I'll click Benefits, tab that into place, put in the right bracket. Excel will take of the other parenthesis at the end. I'll simply press Enter. And that truly is counting the blank cells. So that's much better than what we saw before. And as we work with other functions here, doing totals, for example, I could press Alt + Equal right now. Now, I could of course click Column H if I wanted to. I'll get an answer there. That's gonna work okay and maybe that's reasonably fast.
But another way and perhaps better in terms of documentation, and I'll do what I did before, I'll type T, there's Table2, tab it into place, and then I'll put in the left bracket, it's Comp. I'm looking for right there, tab it into place, and right bracket, and Enter. And again, a little more documentation than what we saw based in the column letter. So, even though this might seem awkward to you, I think if you're a little bit patient with this, you'll discover it works pretty well. Another aspect to this too, as we scroll up and down, remember, if we're scrolling with the active cell in the table, real quick, watch a real one there, watch the field headings.
If I click out in Column L, which is outside the table, I'm scrolling with the mouse wheel, keep an eye on the list to the left, we're seeing the column letters. So that's a little bit different too. That might unnerve you and sally you for a bit as we scroll up and down this way. I'm going to go to the bottom by double-clicking the bottom edge and consider adding, and if you click within the table, you will see the Design tab, the contextual tab, click outside of it, it disappears. So with the active cell in the table, Design tab gives us the option of adding a Total Row, the bottom. Now, this other data, like I forgot to delete that, I will delete it now, but we're seeing a total over here and you might not necessarily see a total at all.
I'm seeing one here as I double-click here. It looks like it's using subtotal here. What's the 109 mean? I could go in a big tangent here. This means a sum as opposed to an average. Let me take that out though and consider how we would add it ourselves. When you first add a Total Row, you can click in any of these, click the drop arrow, and if it's Job Rating, I would wanna do an Average. I certainly wouldn't want to add them. Now, I can double-click here and see what's being used. The SUBTOTAL function does the job of adding up the data here, but if it's filtered, it only adds up the visible cells.
So, this case, not adding, it's averaging, but it's only dealing with the visible cells. What if we look at the data here? Maybe I will use the Total on this just for the moment, Sum, and readjust the column width. It's 43 million. But if I drag across some rows here and hide the data, so instead of 43,324,000, that's 42,796,000. So the SUBTOTAL function that's automatically put in place here only counts the visible cells. Now, you don't have to do any of the work here. In other words, when you click the arrow, and most of these columns, they probably wouldn't be doing this, maybe average years of service could be interesting, do this over here, and we see 13.
And we could certainly use a comma format there, single decimal maybe, 12.8, that sort of thing. So the issue here might be, what do we when we add new records? So I have hidden some rows over here. I'm simply going to click in the upper left corner, double-click the row boundary, that will bring back all hidden rows, but we're at the bottom here. Suppose we want to add a new record, now, it might seem a little cumbersome, after working with this for how long, I've decided it isn't, I occasionally add new records. I'll just disable the Total Row, add that name that I had before, put in some of the information, I don't wanna do much typing right now.
Maybe this is a rehire and I'll adjust the hire date, 4/5/2012 is the adjusted date, so I'll type that in. There's the years of service, yeah, maybe it's Full Time and maybe I'll just put in a Comp. number over here and so on, a couple of these, they're filled in. Notice how the New Comp. automatically adjusts as I put in the Comp. number here and now I'll bring back our Total Row. So on the Design tab, Total Row, there it is. So we can make it appear and disappear at different times. It's always going to be added at the bottom.
And although you don't necessarily need to understand the SUBTOTAL function, it's a function that does use different numbers here. 109 is average. I'd like to say, "How would you know that?" You wouldn't unless you looked it up in the Help system. SUBTOTAL 109 means sum. SUBTOTAL 101 means average. You don't know that instinctively. You could click function number here, that's not going to help. So, in a certain sense, you don't necessarily need to know how this works, but it gets the job done as we saw earlier and you can make these appear and disappear, as I said, just by clicking the box for Total Row, make this appear or disappear.
So, in using functions and formulas in tables, again, maybe a little awkward at first as we see some of these notation style. This one, remember the earlier version, equal, this, we see this, multiplying by this again, pressing F4 and adding onto this. Even though that might seem a little cumbersome, it works, copies the formulas properly. We also saw the advantage over here with working with blanks, and as we work with other features too. For example, I wanna know how many Full Time people are here. Let me use countif. Once again, I could press T, there's Table2, tab it into place, left bracket, I'm looking in the column that has status over there.
I don't need to see the column on the screen necessarily. Here's Status right there. I'll tab that into place, put in a right bracket, comma. How many of these people are Full Time? Out there to the right, Full Time, I'll click on that, there it is, 380. Double-click, copy this down into the next few cells. So, require some getting used to. The more you work with these, the more familiar you will become with them and remember, each table has a name and you'll see it in the list there. Now, if you click within a table, and we are right now, we could go over to the left side here. We can see the drop arrow there. If you click the Design tab, you'll see the Table Name over here, you might wanna change it.
Maybe I'll just call this, instead of Table2 here, HR Table, underscore Table, there we are. So the next time we're writing a formula or perhaps we're looking at some of these formulas over here, it says HR_Table, and of course we can use that in our list. If we're typing, maybe we wanna do a sum here with this way, begins with H, I'll type H, there's our list of functions that begin with H but including the name here HR_Table. Tab that into place, left bracket, it's New Comp. this time, click there, tab that into place, right bracket, and Enter.
So, writing formulas using math with tables, a challenge perhaps at first, but ultimately, some real benefit in using this feature.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.