Working with Tables in Excel

Tables

in level 2, 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. A 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 gonna go to. He used the word pivot in front of table. Therefore, it's a special type of object. But if I tell someone I want to create a table, they're going to say. And because for them a table is a table is a table as a table.

Data Tables Overview

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 gonna be that much different than what you would do to create a chart. First will 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. So Excel is smart enough to figure out where all the connected cells are and will select all the information for you.

Home Tab Method

First method we'll use to create a table is located on the home tab.

On the home tab. If I had all the way over, not all the way over, but towards the middle of the sheet, right next to conditional formatting aisle seat 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. So 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.

Insert Method

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 OK, because I have one final method I'm going to show you.

Keyboard Method

And so that final method is a keyboard shortcut with my 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 p.c. 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 tables 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.

Table Design

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 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. So now this is where I'm going to sound like a used car salesman.

Features of Tables

I'm going to tell you all the wonderful features of working with a table because there are many first wonderful features 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.

Sorting & Filtering

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 dropdown on select 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.

Formatting

For instance, I want to select the entire right 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 in, even change the formatting. It's that 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. This point I can choose to copy and paste this somewhere else if I need to. So let's continue.

Headers

One of the things that you'll experience if you're working with a large tables 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 $5.

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 camouflage that 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 or ours. I want to be able to get their total pay. So I'm going to go to the right of ours 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.

Totals

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'm I would think I would be selecting cells, but looks like I'm selecting something called at rate and at ours.

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, autocomplete 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 dropdown for no formatting and choose currency. And then I'll decrease the decimal by 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 gonna 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. So 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 gonna 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'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 four hours and there's a dropdown. If I click the dropdown, I can choose some. And that calculation gives me the total amount of hours. And 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.

Averages

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 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.

Other Locations for Excel Training

Learn Excel from the comfort of your home with live online training from anywhere or have us come to your offices anywhere in the US for corporate training in Excel. Learn more about Excel training in your location:

• Excel Classes NJ
• Excel Classes Chicago
• Excel Classes Boston
• Excel Classes Atlanta