1
00:00:00,600 --> 00:00:07,140
In this video we're gonna be talking about tables in Excel and why it's important to use excels internal

2
00:00:07,140 --> 00:00:13,260
table functionality as well as the benefits that you get out of excels internal table functions.

3
00:00:13,290 --> 00:00:18,450
So to begin with I've got some data that I put in here and we'll go ahead and extend these columns so

4
00:00:18,450 --> 00:00:19,500
we can see everything.

5
00:00:19,500 --> 00:00:26,340
And an easy way to do that is to click this upper left arrow here to highlight all of the cells in the

6
00:00:26,340 --> 00:00:32,280
workbook and then double click on one of the separator lines between columns so you'll see your mouse

7
00:00:32,280 --> 00:00:36,140
turn into a vertical line with two arrows pointing outwards.

8
00:00:36,240 --> 00:00:41,970
And if we double click on that it'll go ahead and fix the columns throughout our entire worksheet to

9
00:00:41,970 --> 00:00:47,730
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

10
00:00:47,730 --> 00:00:48,610
in our table.

11
00:00:48,720 --> 00:00:54,500
So I'll go ahead and I'll give you a second to pause and copy this data if you'd like to.

12
00:00:54,510 --> 00:00:58,850
So now we're gonna go ahead and we're gonna look at why we should turn this into a table.

13
00:00:58,940 --> 00:01:05,470
And so first we're gonna highlight all of our information and we're going to go to insert and click

14
00:01:05,470 --> 00:01:07,520
on table.

15
00:01:07,570 --> 00:01:10,210
Now it's going to ask you where's the data for your table.

16
00:01:10,210 --> 00:01:15,910
And you've actually already highlighted this so we can see that this corresponds to what we have highlighted.

17
00:01:15,940 --> 00:01:21,250
So this is the data that we want to turn into a table and we can leave this box checked for my table

18
00:01:21,250 --> 00:01:22,420
has headers.

19
00:01:22,420 --> 00:01:26,470
And that's because we've already we've already built in the headers for our table this first column

20
00:01:26,470 --> 00:01:29,530
here from name two average grade.

21
00:01:29,620 --> 00:01:36,490
So we'll click on OK and we see that now we have this table that's been automatically formatted for

22
00:01:36,490 --> 00:01:41,660
us and this formatting is very easy to change which is one of the nice things about tables.

23
00:01:41,770 --> 00:01:46,740
If we click into the table any of those tables sells any anywhere in the table.

24
00:01:46,870 --> 00:01:50,360
We'll see that we get an additional tab up at the top of our screen.

25
00:01:50,450 --> 00:01:55,390
And when we click out of the table that tab goes away so we'll click into the table and we'll go ahead

26
00:01:55,390 --> 00:02:01,120
and click on table design this new tab and we can see if we get some new options of things that we can

27
00:02:01,120 --> 00:02:06,700
do with this data with this table that we didn't have before when this was just raw data sitting in

28
00:02:06,700 --> 00:02:07,960
the Excel file.

29
00:02:07,970 --> 00:02:12,640
And the first thing is this table styles over here which makes it really easy for us to reform at this

30
00:02:12,640 --> 00:02:16,720
table into a whole bunch of different presets that Excel has given us.

31
00:02:16,720 --> 00:02:19,960
There's a whole bunch of different styles and things that you can choose from.

32
00:02:19,960 --> 00:02:27,810
So for now we'll just go ahead and leave it as we have it on this particular option here we can also

33
00:02:27,810 --> 00:02:37,890
see that if I scroll down and I click into my table I can keep the names the headers of my columns all

34
00:02:37,890 --> 00:02:43,640
visible and they'll go away when I click out of my table they'll turn back into my regular column letters.

35
00:02:43,710 --> 00:02:49,740
But as long as I'm clicked into the table I can always see what the header name of this column is throughout

36
00:02:49,740 --> 00:02:54,960
my entire table which can be really helpful when you're dealing with a lot of data a really long table

37
00:02:54,960 --> 00:02:56,580
a really large table.

38
00:02:56,640 --> 00:03:01,530
It can help to be able to scroll down and still see what column you're looking at and what column you're

39
00:03:01,530 --> 00:03:02,700
working in.

40
00:03:02,700 --> 00:03:08,380
Another nice thing about the tables is you get these automatic filters and sorters that are placed onto

41
00:03:08,420 --> 00:03:09,960
your head or columns.

42
00:03:09,960 --> 00:03:15,960
So for example we can go ahead and we can sort on this column and we can say we only want to see the

43
00:03:16,020 --> 00:03:22,980
people who selected dogs click OK and this will automatically sort our data for us to only show us the

44
00:03:22,980 --> 00:03:24,700
dog options.

45
00:03:24,750 --> 00:03:28,040
I'll go ahead and alter and cash back on so we can see everything.

46
00:03:28,110 --> 00:03:34,860
This also has a sort functionality where we can sort from a to z so we can go ahead and say sort this

47
00:03:34,860 --> 00:03:41,820
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

48
00:03:41,820 --> 00:03:43,560
to whatever the particular name is.

49
00:03:43,560 --> 00:03:49,170
So if I were to reverse reverse this and sort from Xena a we'll see that all of this data follows the

50
00:03:49,170 --> 00:03:49,640
name.

51
00:03:49,650 --> 00:03:57,360
So these entire rows are saved as records by sort from Xena a we can see my entire table has updated

52
00:03:57,360 --> 00:04:00,640
so that this data is sorted from Z to A.

53
00:04:00,750 --> 00:04:02,110
And all of this data.

54
00:04:02,250 --> 00:04:05,390
These records are still tied to this particular name.

55
00:04:05,700 --> 00:04:07,500
So we can sort out where we want to sort.

56
00:04:07,500 --> 00:04:10,080
We can sort on any column that we want to sort on.

57
00:04:10,080 --> 00:04:17,010
We can also filter out various items or filter in various items which makes manipulating your data and

58
00:04:17,010 --> 00:04:20,540
finding particular data very easy.

59
00:04:20,550 --> 00:04:25,920
One of the other nice things about tables is again when you have a huge table and you have lots of columns

60
00:04:25,990 --> 00:04:34,310
you have lots of rows we go over to table design we can use this insert slicer and we can actually build

61
00:04:34,310 --> 00:04:38,990
in these little easy filters based on various things that are in our table.

62
00:04:38,990 --> 00:04:47,250
So let's select a slicer for our graduation year and four cats or dogs and click OK now we see I get

63
00:04:47,250 --> 00:04:53,550
these two little boxes that pop up that are called slices and these just make it easy for me to quickly

64
00:04:53,550 --> 00:05:00,030
filter and and filter out options so let's say I only want to see the dogs and I only want to see graduation

65
00:05:00,030 --> 00:05:01,740
year 2023.

66
00:05:01,800 --> 00:05:06,850
This automatically filtered my table down to this data that I had selected here.

67
00:05:06,990 --> 00:05:10,350
And if I add back in some more options I say 20 21.

68
00:05:10,360 --> 00:05:16,920
Now showing me the 20 21 option I come back and I say cats in 2020 it'll quickly show me the cats in

69
00:05:16,920 --> 00:05:23,130
2020 option and you can multi select different things by holding control so I can go ahead and turn

70
00:05:23,130 --> 00:05:25,950
all of these back on if I want to or lead them off.

71
00:05:25,950 --> 00:05:33,150
And these are just quick and easy filters slicer filters for manipulating the data in your table and

72
00:05:33,150 --> 00:05:38,280
to get rid of them I'm just clicking them and clicking delete on my keyboard so I'll go ahead and I'll

73
00:05:38,280 --> 00:05:44,880
clear out all my filters that I just put on so that we can see our root data again some of the other

74
00:05:44,880 --> 00:05:52,980
nice things about the tables is if again if we click in here and we go to table design we have the option

75
00:05:52,980 --> 00:05:59,580
of building this total row and if we click turtles here if formulas in this table are complex if you

76
00:05:59,580 --> 00:06:06,270
have a table that has a lot of no information no data in it the total row is a great way to quickly

77
00:06:06,270 --> 00:06:08,780
summarize all of that numbers information.

78
00:06:08,790 --> 00:06:13,890
So for example I have over here the average grade that this person has received so far.

79
00:06:14,100 --> 00:06:20,370
If I click on this total so here and I click the dropdown I can choose what I want this totals to show

80
00:06:20,370 --> 00:06:24,750
up as and in this case maybe we want to see an average of what everyone has in the class so I click

81
00:06:24,750 --> 00:06:30,460
on average and now I can see my average grade across the entire class.

82
00:06:30,630 --> 00:06:37,920
This totals row will automatically incorporate any new data that we put into this table or any data

83
00:06:37,920 --> 00:06:42,250
that we take out of the table will automatically be reflected in our total row.

84
00:06:42,330 --> 00:06:45,840
So it's a quick and easy way to auto summarize your chart.

85
00:06:45,930 --> 00:06:50,180
We can also auto summarize this table data as an actual chart.

86
00:06:50,190 --> 00:06:56,310
So if I go back over to insert we can see Excel has a whole bunch of charts that it's capable of creating

87
00:06:56,640 --> 00:06:58,670
and we can actually make these dynamic.

88
00:06:58,680 --> 00:07:05,160
Now if we base them on our table and that's because our table will automatically name these columns

89
00:07:05,700 --> 00:07:08,360
for us so it'll become a named range.

90
00:07:08,370 --> 00:07:14,380
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

91
00:07:14,380 --> 00:07:20,010
to highlight some of these cells we can see initially the formula is behaving as it would normally it's

92
00:07:20,010 --> 00:07:22,680
giving us cell G3 to g six.

93
00:07:22,680 --> 00:07:28,740
But as I continue to drag down as soon as I highlight this entire column of the table it becomes this

94
00:07:28,740 --> 00:07:33,430
table 1 average grade which is a named range of the table.

95
00:07:33,510 --> 00:07:39,720
And what that means is if I add rows to this table or take rows out of this table this will automatically

96
00:07:39,720 --> 00:07:42,740
be accounted for in my named range here.

97
00:07:42,840 --> 00:07:46,770
And the named ranges are something that your table will automatically create for you.

98
00:07:46,770 --> 00:07:52,770
Which is really really helpful as you're building dynamic ranges of data and what that means is if I

99
00:07:52,770 --> 00:07:58,410
build a pie chart or I build a chart that's based off of my table here usually you would point that

100
00:07:58,410 --> 00:08:00,780
chart at a specific range of cells.

101
00:08:00,780 --> 00:08:04,170
In this case we might point it at G3 to G8.

102
00:08:04,410 --> 00:08:11,400
But if I add an extra row if I take out a row my table doesn't necessarily my table might reflect that

103
00:08:11,400 --> 00:08:15,240
but the chart wouldn't necessarily reflect that change.

104
00:08:15,300 --> 00:08:19,850
And that's because the chart is still looking at cell G 3 to g 9.

105
00:08:19,860 --> 00:08:27,840
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

106
00:08:27,840 --> 00:08:29,580
range already accounts for that.

107
00:08:29,580 --> 00:08:36,450
And so we point a chart at this named range and we start changing the data that will all be reflected

108
00:08:36,540 --> 00:08:42,660
in your chart which ultimately just goes to say that tables are great for dynamic data they're great

109
00:08:42,660 --> 00:08:49,110
for a lot of data that you need to organize quickly and they're great for quickly summarizing or reformatting

110
00:08:49,110 --> 00:08:50,090
your data.

111
00:08:50,160 --> 00:08:55,290
So tables are definitely something that you should use and excel regardless of whether or not you're

112
00:08:55,290 --> 00:09:02,340
using Visual Basic but in the case of Visual Basic tables make it a lot easier to interact with your

113
00:09:02,340 --> 00:09:02,730
data.

114
00:09:02,760 --> 00:09:09,000
And that's because this table this whole table is going to be treated as an object in VBA which means

115
00:09:09,000 --> 00:09:11,210
it's easy for us to reference this table.

116
00:09:11,310 --> 00:09:15,550
We can treat it as its own object instead of trying to locate it on the sheet.

117
00:09:15,540 --> 00:09:18,960
There's a lot of different things that make this beneficial in VBA.

118
00:09:18,960 --> 00:09:25,800
And so the important thing to note about using tables and VBA is that the VBA object name is going to

119
00:09:25,800 --> 00:09:31,740
be based off of the tables name in excel and we can find out the table name by clicking in the table

120
00:09:32,190 --> 00:09:34,260
going to table design.

121
00:09:34,260 --> 00:09:39,240
And over here on the left you'll see a field for table name and your table name will default to something

122
00:09:39,240 --> 00:09:43,770
like Table 1 or table 2 depending on how many tables you have in your file.

123
00:09:43,770 --> 00:09:46,640
But we can actually change this and we can call this something else.

124
00:09:46,650 --> 00:09:50,660
So we'll call this main data table.

125
00:09:50,960 --> 00:09:55,850
So this is now the name of my table it is called main data table and when I reference this in Visual

126
00:09:55,850 --> 00:09:59,510
Basic or if I use this as a named range somewhere else.

127
00:09:59,600 --> 00:10:03,010
Excel will now know what main data table refers to.

128
00:10:03,020 --> 00:10:05,870
It's referring to this particular table here.

129
00:10:05,870 --> 00:10:11,060
And one thing to note is I did not put spaces in between the name here same as we do with everything

130
00:10:11,060 --> 00:10:12,370
else in VBA.

131
00:10:12,470 --> 00:10:15,140
You generally want to avoid spaces.

132
00:10:15,140 --> 00:10:21,590
So now if I click back into this table go over the table design we can see my my table name has stuck.

133
00:10:21,590 --> 00:10:26,570
So we definitely want to use tables and we can definitely use them in VBA and it's very helpful.

134
00:10:26,570 --> 00:10:33,140
Now we know how to rename our table and next we can take a look at how to actually interact with this

135
00:10:33,140 --> 00:10:35,270
table data within Visual Basic.