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 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 very 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 from 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. The first method we'll use to create a table is located on the Home tab. On the Home tab, if I head not all the way over but toward the middle of the ribbon, right next to Conditional Formatting, I'll see Format as Table.

When I click the dropdown, 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 begin.

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 OK. I’ll 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 also where 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, the dialog box is the last thing I will see before the table gets created.

I'm not going to click OK 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 OK 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 blue tones. 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 dropdown here and I'll go for something that's fairly 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 this provides 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. The first great feature that you have that's automatically included in the table is the integrated AutoFilter 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 the filter button and the first option is 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 directly within 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 dropdown. 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 dropdown 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 currently set to Currency.

I'll change it to Accounting. I may decide I want to go back to Currency. I'll click the dropdown 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 selects 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 might temporarily forget what each value represents because you can't see the headers. So what you eventually have to do is scroll 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 inside the table and scroll down, I will always see the headers. I'm going to do this now and observe whether you can still 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 somewhat 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 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 table 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 yet 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.

The total pay will 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 [@Rate] and [@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 method we looked at earlier—selecting the entire column.

I'll go over to the dropdown 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-headed arrow.

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 calculated 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 checkbox 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 calculates totals 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 dropdown. If I click the dropdown, I can choose Sum, and that calculation gives me the total amount of hours.

Then I'll head over to Rate. I'll also click the dropdown 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 dropdown, choose Connecticut, click OK. Then I'll go over to the Department column and just choose Sales. As soon as I click OK, 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 Filter (funnel) button right here.

So I’ll go and click Clear and it clears both filters at the same time instead of 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.