Excel Tables: Tips for Level One Students

Tables. In this section we're going to talk about tables. Tables, I consider one of the most important topics in a level one class.

In level two we're going to take a look at pivot tables, but if you're a level one student, learning tables is your version of pivot tables. You can do a lot of very unique things and you'll get unique advantages when you use a table. I'll first start out by saying that tables is not a really good name because if I tell someone I'm creating a pivot table, they're going to go, oh you use the word pivot in front of table, therefore it's a special type of object.

But if I tell someone I'm going to create a table, they're going to say and, because for them a table is a table is a table is a table. But the type of table that I'm talking about is a table that is an object in Excel that acts like a database. Now I'm going to go over three different ways you can create a table.

It's not going to be that much different than what you would do to create a chart. First we'll click somewhere in the data. This makes it very simple because you don't have to select the entire table and scroll down and select everything.

If you just select one cell, Excel is smart enough to figure out where all the connected cells are and will select all the information for you. First method we'll use to create a table is located on the home tab. On the home tab, if I head all the way over, not all the way over, but towards the middle of the sheet, right next to conditional formatting, I'll see format as table.

When I click the drop down, I see a gallery. Here I'll find several different types of table themes I can apply to my table. If the cosmetic appearance of the table is really important to me, this is where I'll start.

Let's say I like the color blue. I'll click here. This dialog box that says format as table is an indication that I'm on the right track.

It's the last thing I will see before the table gets created. Now I'm not going to click okay. I'm going to cancel because I want to show you the two other methods and they're listed right here.

You'll go to insert, just like you would for a chart, but instead of going to the chart area, you'll go to the table section. Ironically, this is the same place you would go to create a pivot table. So tables and pivot tables are part of the same family.

They're related. I'm going to click table. Again, this dialog box is the last thing I will see before the table gets created.

I'm not going to click okay because I have one final method I'm going to show you. That final method is a keyboard shortcut. With my cell selected anywhere in the data, I'll simply press control T as in table.

It's the same keyboard shortcut for Mac as well as PC. Now I will finally click okay and now I have a table. One of the ways I'll know I have a table is the formatting has automatically been converted to a table's default theme.

In this case, a lot of dark blues. This may make it difficult for me to see my data, so I'm going to go over to the new tab that shows up automatically when you create a table. You may have different names for it on the Mac.

It might just be table or design. Here in my version of Excel, it's called table design. If I take a look at the options that I have here, I see I have a second opportunity to choose a different type of theme.

So I'll click the drop down here and I'll go for something that's pretty light. I'll go for this blue. This is the one I originally selected when I used format as table.

I'm going to click here and now I'm going to click outside of the table and now this is a better look for my table. I can see the numbers a little bit better here. Now this is where I'm going to sound like a used car salesman.

I'm going to tell you all the wonderful features of working with a table because there are many. First wonderful feature that you have that's automatically included in the table is the integrated auto filter and sort functionality. You'll notice that there are filter buttons on each of the header names.

These filter buttons allow me to sort and filter my information. For instance, if I wanted to alphabetically sort everyone in this table by first name, I would click that button and one of the options, the very first option, is to sort A-to-Z. With one click, I've now sorted everyone in the table alphabetically. I did not have to go over to the ribbon tab.

I can do that right inside the table. Let's say I only want to look at a subset of the information. I'm only interested in Connecticut at the moment.

I'll go to division. I'll click the drop down. I'll unselect everything else and simply choose Connecticut.

When I click OK, I am now looking at a subset of the information and I'm only seeing people who are working in Connecticut. I'm going to clear the filter. I'll click the drop down here and choose clear filter from division.

Next feature we'll talk about is easy selection. If I need to select an entire column, I can do so with one click. I will not need any fancy keyboard shortcuts to do this, so people who like using the mouse will really like this option.

All you have to do is hover your mouse directly above any of the header names. For instance, I want to select the entire rate column, so I'll move my mouse just above the word rate and I see a black arrow pointing down. As soon as I see that, I'll click once.

I've selected the whole column. On the home tab, I can go and even change the formatting. It's at currency currently.

I'll change it to accounting. I may decide I want to go back to currency. I'll click the drop down and choose currency.

The entire column is selected from top to bottom and maybe I want to decrease the decimals, so I'll take out those extra decimals. I could also select an entire row. If I move over to the left of the first name, I see an arrow pointing to the right.

One click and then I've selected that one row and all the information in that row. At this point, I can choose to copy and paste this somewhere else if I need to. So let's continue.

One of the things that you'll experience if you're working with a large table is that when you scroll down to the bottom of your data, you'll experience temporary amnesia. What do I mean? Well, you get to the bottom of the table and then you forget what these numbers represent because you can't see the headers. So what you eventually have to do is scroll all the way back up to the top, just so you can remind yourself what those values are.

If you're working in a table, you will never ever have to worry about that. Now you'll only get these superpowers if you're in the table, so if I click in the table and I scroll down, I will always see the headers. I'm going to do this now, see if you can see the headers as I scroll down to the bottom of the table.

Now when we do this in class, people are a little puzzled. They say, I don't see the headers. I don't know what you're talking about.

So I usually tell those people, I'll make a bet with you. If I come to your desk and we don't see the headers, then I'll give you five dollars. But if I'm able to show you the headers, then you owe me five dollars.

So maybe people will look a little bit more carefully and you'll notice that what has happened is that the column headers have taken the place of the column letters. And it's kind of camouflaged, so you might not see it at first, but this is a very convenient feature. I don't have to worry about freezing my rows in order to see the headers, no matter how far down the table I go.

All right, so that's pretty cool. Let's take a look at some other features of working with a table. This contains a list of employees, their division departments, their rate, and their hours.

I want to be able to get their total pay. So I'm going to go to the right of hours and I'm going to type total. As soon as I press ENTER, that's another feature of the table.

The column automatically expands to include that new column. Now I want to be able to come up with the total pay for all of the employees. I'll start with the first one.

Now the total pay is going to be equal to the hourly rate multiplied by the total number of hours. Now something weird is happening here. I would think I would be selecting cells, but it looks like I'm selecting something called at rate and at hours.

Well, I know I selected the right cells, because they're highlighted. So let me just press ENTER and see what happens. That is another wonderful feature of working with the table.

The calculations auto-complete all the way down the column. Now I want to change the formatting. I'll use the feature that we looked at earlier, selecting the entire column.

I'll go over to the drop down for number formatting and choose currency, and then I'll decrease the decimal. As I scroll down, we're going to add some new employees to the company. They're listed right here, so I'm going to select them all, and I'm going to use the ability to move over to the green border and look for the four arrows.

This will give me the opportunity to click and drag and add those new employees at the bottom of the table. Now notice I didn't get their total pay yet, because I only have their hourly rate and total hours, but as soon as I let go, that is automatically taken care of by the structure of the table. Now that I have the total pay for each employee, there's something that the payroll department might be interested in, and that is the total pay for all employees.

So that's going to be very simple to do, because if I click in the table and go to the table design tab, there's a feature I can turn on called total row. All I have to do is click the check box for total row, and it creates a total at the bottom of the total column. Now you might say, wait a minute, so why is it called total row? Because it's giving me a total for a column.

Well it's called total row because each cell within that row is able to give me a total. If I want the total amount of hours, I'll simply click underneath the column for hours, and there's a drop down. If I click the drop down, I can choose sum, and that calculation gives me the total amount of hours.

And then I'll head over to rate. I'll also click the drop down there, but I'm not interested in getting the sum of all the hourly rates. I want to be able to come up with the average hourly rate, so I can choose that.

And now I have the average. One more feature I'll go over relates to filtering your table and getting useful information depending on what you're filtering for. I want to filter for the Connecticut sales department.

So I'll go to division, click the drop down, choose Connecticut, click okay. Then I'll go over to the department column and just choose sales. As soon as I click okay, if I take a close look at the total row, I'll see that it automatically adjusts for what I'm filtering for.

This allows you to get subsets of the information by simply filtering. No new calculations are needed. This is very convenient if I want to investigate values for different divisions and departments.

All I have to do is filter and each time I'll get a new calculation. If I want to clear the filters, I can click within the table, go to the data tab, and I want to clear both at the same time. I'll simply choose clear filter or the clear funnel button right here.

So I'll go and click clear and it clears both filters at the same time rather than individually. So in this section we took a look at a lot of things and that's why I say this is one of the most important parts for a level one student. We saw how we could create a table, filter the information, select columns and rows, make sure the headers remain at the top, witness automatic expansion of the table both vertically and horizontally, and also take a look at column-based calculations as opposed to cell-based calculations.