Hi, this is Wayne again with a topic “All About Excel Tables”.
In this article, we’re going to learn all about excel tables, including how they work and some of the great benefits that they give you and we’re going to start by focusing on the anatomy of an excel table. Here in this spreadsheet, which you can get in the description below this video you’ll, see that i have two sheets, one that is formatted as a table and the other that is formatted just as a range of data. So what are the individual components that make up this table at the top? We have the table header row in this case. It’S row number one. Each column has a header that explains the kind of data that follows below so product name and then there’s some board games or card games listed below price and we have some prices listed below and then we have some data for each month of the year and Then yearly total sales over here at the right, but the table header row explains or describes the kind of data that follows below.
There are a couple of very powerful features that come with the table header. When you create a table, the first happens automatically without you doing anything special. You can see that my data in this particular spreadsheet is really not that long. I don’t have a hundred rows a thousand rows, but if i did look what would happen as i scroll down the page, the table header row gets pinned basically here at the top, so that i always know what goes in each column. It doesn’t matter how far down the table i go as long as i’m still in the table. The table header row will be visible. The other nice thing about the table header row in excel tables is that it automatically includes sort and filter options. That’S what these little buttons are that appear in each column heading, so i could, for example, click this button here to reorder my spreadsheet, based on the highest yearly total sales to the lowest or largest to smallest, as it’s stated here.
So i click that and now i know right away which product has brought in the most total sales. So that’s a nice feature. I can also use those buttons to filter, in other words, to hide some data temporarily.
So let’s say i want a report of only a few of the products and i want to hide the data for the other products. I click ok and i’ve filtered out some of the data based on the product name. I could also filter based on the product price or some of these other options, but i want to just put everything back at this time, so i’ll just click to select all click, ok and my filter has been removed.
So those are the two main benefits that the table header row gives you in excel tables. It stays visible if you’re within the table still, and it gives you sort and filter options with these buttons. The second part of the anatomy of an excel table is the table data and it’s here and, as you can see, by default, the table data comes with banded rows.
In other words, we have a blue background here. White background here blue background here. That just makes it really easy to track your data and your rows across the entire table.
I know for sure that this number of yearly total sales goes with the game telestrations, because those banded rows help me to clearly identify which product it goes with. One of the other nice things about the table data in an excel table is that, if i need to add more data to this table, all i have to do is go to the lower right corner of the table, data and tap tab on the keyboard, and It gives me a new row of data and i can put in additional information, so the table data part of the table can auto expand to include new data as needed. The final part of the anatomy of an excel table that you need to know about is this: this is the table totals row and as long as it’s enabled in your settings, it will automatically add up at least part of the numerical data in your table.
I’Ll show you more about how that works as we get further into the video. So let’s look now at how to convert a range of data like this into an excel table. To begin, you just need a range of data.
That’S all together. It’S not separated by blank columns or blank rows. It’S just data, that’s all together and then you can simply click anywhere in that range and then go here to insert table excel pops up with a dialog box. Where is the data for your table? It automatically identified that data, here’s the range that it automatically identified, and i can also see it with these dashed lines around my data.
If, for some reason, there’s a mistake here, you can click this button and then select the data manually, but you shouldn’t have to do that in most cases. At this point i could click this button here. Next, it’s important to notice this check box here my table has headers here. Excel is asking if my table has column headings like product name, price yearly totals etc, and it does – and so it’s important that i have that checked.
If not, it will cause a little bit of trouble with my data i’ll go ahead and click ok, and it’s now converted my range into a table. Now i’m going to hold ctrl and tap z to show you that you can skip a couple of steps. If you just hold ctrl on the keyboard and tap t for table, that takes you right to this, create table dialog box and you can click ok to convert the range to a table. At this point, you’ll notice that a new tab and ribbon have been enabled in your spreadsheet. As long as you have selected at least part of your table, you’ll see the table design tab here at the top, with the table design, tab selected. It’S a good idea to now go to the properties group and give your table a name, and the name should have no spaces in it and while it’s not totally necessary to name your tables, it can come in handy later.
If you want to use formulas using your table and also there’s some other reasons, it might come in handy, so it’s a good practice to get into of naming your table. So now my range is a table. You can see. My table. Header row looks really good. You can see my table data is here and has banded rows, just like i expected, but the table totals row is missing. Why is that? Well by default it may not be included. So if you look here at the table design tab here in the table style options group, you can see that there’s an option for total row now that i’ve checked that the total row appears and you can see it automatically – totaled the yearly total sales for each Game sold by this company now it did not total each month’s number of sales.
If i want that, i can just click here underneath january, for example, click this button and choose what i want to do. Do i want to identify the highest number of items sold. The average, how about the sum i just want to add it up, and then i could do that with each additional column or i could just use the auto fill handle to extend that formula across my table. So there’s my table totals row. Let’S look next at some of the other table design options i have by default. I’Ve been given banded rows. If i prefer, though i could change that to banded columns, i do prefer banded rows myself. If you want, you can choose first column, which will give you some special formatting for that first column, it basically bolded the names of the games.
You can also do the same thing with the last row. If you would like, in my case i’m going to stick with the last row, you can also turn on or off the filter buttons in the table header row here in the table. Styles group. You can change up the color scheme for your table.
If you would like, if you click this button here, it gives you even more options, and so, depending on the look and feel that you want for your table, you really have many many options to choose from some other table design options. You should be aware of include the insert slicer option if you haven’t watched my tutorial on excel slicers. You should definitely watch that, but this is a powerful feature same with summarize with pivot table. I have a video on that topic, but basically, with the click of a button, you can begin the process of creating a pivot table using your excel table, there’s a button to remove the duplicates and there are other options as well that are worth checking out.
One final thing i want to point out about excel tables is that now that this is a table, it makes it a little faster and easier to apply excel formulas. Let me demonstrate i’m going to right click on o and choose insert to insert a new column and i’ll title. This fourth quarter sales, and i just want to create a formula that will add up the total number of sales from october through december. So i click on that cell type.
Equals sum left parenthesis and then i just want to add up these three numbers. So i clicked and dragged to select those numbers. I tap enter on the keyboard and look what it did it calculated the total fourth quarter sales, not just for my boggle game, but it automatically applied that same formula down through the rest of the table. So this is a nice little time saver. It automatically assumed that i want that same information for everything else in the table now, despite the advantages to using excel tables. There are times when you want to convert your table back into just a simple range: to do that you can just click anywhere in the table, go to the table, design, tab and then click this button convert to range.
Do you want to convert the table to a normal range? Yes, so now this is just a simple range of data. The formulas still work, but you can see that the sort and filter buttons are gone. If i go to the end of the data and tap tab, it doesn’t create a new row for the table, even though it’s formatted, as if it were a table with the banded rows, etc.
It’S really not a table anymore, so i hope you can see some of the advantages of using excel tables for your data thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you subscribe click the bell so you’ll be notified.
When i post another video, if you’d like to support my channel, you can do that through my patreon account or by buying channel merch, and you can find out more about those options below this video .