In this video we're gonna be talking about tables in Excel and why it's important to use excels internal
table functionality as well as the benefits that you get out of excels internal table functions.
So to begin with I've got some data that I put in here and we'll go ahead and extend these columns so
we can see everything.
And an easy way to do that is to click this upper left arrow here to highlight all of the cells in the
workbook and then double click on one of the separator lines between columns so you'll see your mouse
turn into a vertical line with two arrows pointing outwards.
And if we double click on that it'll go ahead and fix the columns throughout our entire worksheet to
fit in all of the text that appears in those cells so we can see now we can see all of the data that's
in our table.
So I'll go ahead and I'll give you a second to pause and copy this data if you'd like to.
So now we're gonna go ahead and we're gonna look at why we should turn this into a table.
And so first we're gonna highlight all of our information and we're going to go to insert and click
on table.
Now it's going to ask you where's the data for your table.
And you've actually already highlighted this so we can see that this corresponds to what we have highlighted.
So this is the data that we want to turn into a table and we can leave this box checked for my table
has headers.
And that's because we've already we've already built in the headers for our table this first column
here from name two average grade.
So we'll click on OK and we see that now we have this table that's been automatically formatted for
us and this formatting is very easy to change which is one of the nice things about tables.
If we click into the table any of those tables sells any anywhere in the table.
We'll see that we get an additional tab up at the top of our screen.
And when we click out of the table that tab goes away so we'll click into the table and we'll go ahead
and click on table design this new tab and we can see if we get some new options of things that we can
do with this data with this table that we didn't have before when this was just raw data sitting in
the Excel file.
And the first thing is this table styles over here which makes it really easy for us to reform at this
table into a whole bunch of different presets that Excel has given us.
There's a whole bunch of different styles and things that you can choose from.
So for now we'll just go ahead and leave it as we have it on this particular option here we can also
see that if I scroll down and I click into my table I can keep the names the headers of my columns all
visible and they'll go away when I click out of my table they'll turn back into my regular column letters.
But as long as I'm clicked into the table I can always see what the header name of this column is throughout
my entire table which can be really helpful when you're dealing with a lot of data a really long table
a really large table.
It can help to be able to scroll down and still see what column you're looking at and what column you're
working in.
Another nice thing about the tables is you get these automatic filters and sorters that are placed onto
your head or columns.
So for example we can go ahead and we can sort on this column and we can say we only want to see the
people who selected dogs click OK and this will automatically sort our data for us to only show us the
dog options.
I'll go ahead and alter and cash back on so we can see everything.
This also has a sort functionality where we can sort from a to z so we can go ahead and say sort this
from a to z and we can see our name column has been sorted from A to Z but all of this data is tied
to whatever the particular name is.
So if I were to reverse reverse this and sort from Xena a we'll see that all of this data follows the
name.
So these entire rows are saved as records by sort from Xena a we can see my entire table has updated
so that this data is sorted from Z to A.
And all of this data.
These records are still tied to this particular name.
So we can sort out where we want to sort.
We can sort on any column that we want to sort on.
We can also filter out various items or filter in various items which makes manipulating your data and
finding particular data very easy.
One of the other nice things about tables is again when you have a huge table and you have lots of columns
you have lots of rows we go over to table design we can use this insert slicer and we can actually build
in these little easy filters based on various things that are in our table.
So let's select a slicer for our graduation year and four cats or dogs and click OK now we see I get
these two little boxes that pop up that are called slices and these just make it easy for me to quickly
filter and and filter out options so let's say I only want to see the dogs and I only want to see graduation
year 2023.
This automatically filtered my table down to this data that I had selected here.
And if I add back in some more options I say 20 21.
Now showing me the 20 21 option I come back and I say cats in 2020 it'll quickly show me the cats in
2020 option and you can multi select different things by holding control so I can go ahead and turn
all of these back on if I want to or lead them off.
And these are just quick and easy filters slicer filters for manipulating the data in your table and
to get rid of them I'm just clicking them and clicking delete on my keyboard so I'll go ahead and I'll
clear out all my filters that I just put on so that we can see our root data again some of the other
nice things about the tables is if again if we click in here and we go to table design we have the option
of building this total row and if we click turtles here if formulas in this table are complex if you
have a table that has a lot of no information no data in it the total row is a great way to quickly
summarize all of that numbers information.
So for example I have over here the average grade that this person has received so far.
If I click on this total so here and I click the dropdown I can choose what I want this totals to show
up as and in this case maybe we want to see an average of what everyone has in the class so I click
on average and now I can see my average grade across the entire class.
This totals row will automatically incorporate any new data that we put into this table or any data
that we take out of the table will automatically be reflected in our total row.
So it's a quick and easy way to auto summarize your chart.
We can also auto summarize this table data as an actual chart.
So if I go back over to insert we can see Excel has a whole bunch of charts that it's capable of creating
and we can actually make these dynamic.
Now if we base them on our table and that's because our table will automatically name these columns
for us so it'll become a named range.
And the easiest way to see this is if we just look at let's say taking a sum and I go ahead and I start
to highlight some of these cells we can see initially the formula is behaving as it would normally it's
giving us cell G3 to g six.
But as I continue to drag down as soon as I highlight this entire column of the table it becomes this
table 1 average grade which is a named range of the table.
And what that means is if I add rows to this table or take rows out of this table this will automatically
be accounted for in my named range here.
And the named ranges are something that your table will automatically create for you.
Which is really really helpful as you're building dynamic ranges of data and what that means is if I
build a pie chart or I build a chart that's based off of my table here usually you would point that
chart at a specific range of cells.
In this case we might point it at G3 to G8.
But if I add an extra row if I take out a row my table doesn't necessarily my table might reflect that
but the chart wouldn't necessarily reflect that change.
And that's because the chart is still looking at cell G 3 to g 9.
However if I add ranges or if I add a row or take out rows into my table or out of my table this named
range already accounts for that.
And so we point a chart at this named range and we start changing the data that will all be reflected
in your chart which ultimately just goes to say that tables are great for dynamic data they're great
for a lot of data that you need to organize quickly and they're great for quickly summarizing or reformatting
your data.
So tables are definitely something that you should use and excel regardless of whether or not you're
using Visual Basic but in the case of Visual Basic tables make it a lot easier to interact with your
data.
And that's because this table this whole table is going to be treated as an object in VBA which means
it's easy for us to reference this table.
We can treat it as its own object instead of trying to locate it on the sheet.
There's a lot of different things that make this beneficial in VBA.
And so the important thing to note about using tables and VBA is that the VBA object name is going to
be based off of the tables name in excel and we can find out the table name by clicking in the table
going to table design.
And over here on the left you'll see a field for table name and your table name will default to something
like Table 1 or table 2 depending on how many tables you have in your file.
But we can actually change this and we can call this something else.
So we'll call this main data table.
So this is now the name of my table it is called main data table and when I reference this in Visual
Basic or if I use this as a named range somewhere else.
Excel will now know what main data table refers to.
It's referring to this particular table here.
And one thing to note is I did not put spaces in between the name here same as we do with everything
else in VBA.
You generally want to avoid spaces.
So now if I click back into this table go over the table design we can see my my table name has stuck.
So we definitely want to use tables and we can definitely use them in VBA and it's very helpful.
Now we know how to rename our table and next we can take a look at how to actually interact with this
table data within Visual Basic.