From the course: Excel: Advanced Formulas and Functions

Tables

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial Buy for my team

Tables

“

- [Instructor] Let's talk about tables and how they're different from ranges in Excel. Here we've got identical sets of data. Starting in row four, we've got Date, Category, and Expense, for both the table side and the range side. And we're going to do some comparing here. First, I am going to put this data into a table. I'm in the Home Tab. I'm going to go over to Format As Table. And we can pick any of these colors that we want. Let's grab the blue. Okay, and then I'm going to get rid of the filter buttons. And notice, when my cursor is in the table, we have this Table Design Tab. And it has things like Show A Header Row; Show A Total Row; Filter Button, you saw me toggle that off. There are a lot of options in here. As well as changing the table style, we can name the table, and that's what I'm going to do right now. Expenses. Okay. And move away from there. Okay, and notice when I have my cursor outside of the table, we no longer have that Table Design Tab. All right. So, that's one way you can know that your data is in a table. Another way, is that hard corner that's on the bottom right corner of the table. Now, over on the right side, Columns H, I, and J, that's just regular normal Excel, and that's called a range. It is not a table. Now let's check some things out. Up here, I am going to get a total of the meals. Equals, sumifs, open parentheses, sum range, sum this column. See, I did not have to drag like I would in a range, but we'll get to that. So now you see I have sumifs, Expenses, that's the name of the table. And Expense is the name of the column. Comma, criteria range 1. The criteria range is here. And it's showing Expenses table, and in the square brackets, the category column. Comma. What's the criteria? The criteria is meals, and is hidden by the formula that I'm writing. So I'm going to go over to C1, and slide over. Okay, so it's D1, and I need F4 for the absolute cell reference Close the sumifs and Enter. All right, this is the amount of the meals. Okay, and I'm also going to convert this into currency. Okay, now let's do this on the other side. The meals. Equals, sumifs, open parenthesis, sum range, I have to copy this range, comma, criteria range here, comma, the criteria is in J1, and then F4, and close parenthesis, Enter. All right, good. $144.91. Let's also convert this into currency. Now let's add some new data. Ah, this updated. Now with the new data, we're up to 161.41. Over here, drag this in. Now the number did not update, but we have this error message, "Formula Omits Adjacent Cells." Okay, so now we've got to deal with that. What's the problem? All right, so I'm going to go up here. And then, one troubleshooting method for advanced formulas, you can go into the formula bar and it will show you what's highlighted. Ah, okay. These did not adjust. Okay. Next, under the agreement, lodging is only reimbursed at 85%. So I'm going to add a column, Lodging. Enter. And the table grew to accept the new column. Okay. Over here, Lodging, nothing happens. Okay, now this is sweet. Equals, if, open parentheses, this value equals Lodging, it's right here, and F4, comma, then this value times this 85%, and F4 to lock it down. And notice what the formula looks like. We've got the at symbol and category. That's telling us that we are inside of the table looking at the category column in that row, alright? And then at expense, we're looking at the expense value in that row. And multiplying it by the F2. Okay, comma, if the value in the category column is not lodging, then, go ahead and grab the expense value. Close the if statement and Enter. Okay? And then I'm going to highlight here, and turn this into currency. All right. Did you notice the formula went all the way down the table column? And we look at Lodging, its calculating the 85%. Now, let's do this over here. Equals, if, open parentheses, that value equals Lodging, and F4, comma, then this value times the 85%. F4 to lock it in place. Comma, if false, then bring us back the $3.20, Enter. Great, we've got the value we want, but we have to drag this down. And let's turn this into currency. Now, another thing that I can show you about tables, let's go over here. Go back to the Table Design Tab. You can have a total row. Okay, so it's giving us a total of the expenses. You can get an average, a max, a lot of other possibilities there. You can also do this over here. Maybe you want to get a count, all right? But if you do want to add more data, you have to get rid of the total row and then add that data, and then bring the total row back if you want it. That's a quick overview of tables. There's a lot more that I invite you to explore with tables in this Table Design Tab. But those are the main things that I wanted to show you for the purpose of this Advanced Functions and Formulas course.

Contents