Transcripts
1. Introduction: Welcome to this class on
tables in Microsoft Excel. My name is Julian and I've
been working with and teaching excel over the past ten
years as a digital marketer. In my experience,
I've found people don't always get the
most out of Excel and the spending just a bit of time learning about it can make it a lot easier and quicker
to conduct data analysis. Tables are a great
example of a simple, underrated and underused
feature in Excel. They're easy to make, provide a more user-friendly
way to view and analyze data and using them also reduces the chance of
making errors in your work. In this class, we'll cover what tables are and
how to create them. How tables differ from a
normal range of cells. The benefits of using tables, table formatting, working with formulas
and tables and more. Class has been designed to provide you have all
the information you need to get started with
working with tables in Excel. Once you finish
the class lessons, you'll be able to
test and reinforce what you've learned
with the class project. Thanks for enrolling in this
class and let's get started.
2. What is an Excel Table?: So before we dive into the
ways in which you can use a table and the many
benefits of using tables. I'm going to take a look
at what we actually mean when we say table
in Microsoft Excel. So here we've got
two sets of data, and the data is
exactly the same. So we've got a list of products, T-shirts, polo shirts,
sweatshirts, et cetera. The size is extra, extra small all
the way through to extract your large price per unit in dollars and the number of sales of
each of those products. This set of data
here is exactly the same as this set of data here. So if I was to, for example, take this data and put
some filters on it, I could do Control Shift L. Then I could even put
these headers in bold. I could format this data with borders all around
it, just like that. What we have here, it looks very much like
a table and you'd be forgiven for thinking
that this is what we mean when we say
table in Excel. But this isn't actually a table in the sense
that it doesn't have all the features of
Microsoft Excel table in Excel doesn't define
this object as a table. So to create an actual table, what you need to do is be
selected anywhere in the data. So we're going to use this
data range to create a table. And then we come up to the
insert menu and select Table. Now, then you'll have
this box create table. We can also do this just
by pressing Control T. And then we'll
also get this box. And you can see that we've
got our data selected. And we're going to leave this. My table has headers as selected because
we do have headers. If we didn't, we could take this off and Excel would add some headings in for us. Select, Okay, and then
now what we have here, this isn't actual
Microsoft Excel table. So that's how you
create a table. In the next lesson, we're going to start
to look at what the key differences
are in benefits are between this table here
and this data range here. That looks like a table, but isn't actually a table.
3. Why You Should Use Tables: Now we've created a table and we've got the
table in place. I want to look at some of the key differences
and benefits between a table and a regular range of data that kinda
looks like a table. So first of all, when I'm in
the table and I scroll down, you'll notice how the
column headings stay in view even as I keep
scrolling down. So product size, price
per unit, and sales. We can see all of these. Even as we go down with
our regular data range, the column headings disappear
so we can no longer clearly see what each of
these columns refers to. Also, if I want to add
additional data to my table, you'll see what happens if I put revenue because I want
to add a revenue column. The formatting carries across and we also have the
drop-down there. So Excel considers this
column part of the table. If I was to put revenue here, you'll see that the formatting
hasn't carried over, so we don't have that drop-down. And all of these cells here
don't have the borders. And it's the same if we
were to do extra row. So if I was to put shorts
or an additional product, the formatting hasn't carried over reservoirs to
put shorts here. Excel will consider this
row part of the table. Now, I'm just going to go
back and remove these. The final thing I
want to talk about in this lesson with tables, you'll notice we have
the Table Design menu in the ribbon at the top here. And there's various different
options and we're going to cover off some of these
in the future lessons. But over here, we don't have these features because
it's not a table. It's just a regular range of data that kinda
looks like a table.
4. Finding Insights with Tables : In this lesson,
we're going to look at a few of the features that tables have that
allow you to quickly find out information about
the data inside your table. If I select into the table, and then I'm gonna go to the Table Design menu
and select Total row. Now what you see here, we have a row that's been added to the bottom
of our table. And if I select into this cell, you'll see there's various
different options. So at the moment we've got some, so that's the total
number of all the sales. But I could change this to
the maximum, for example, to give us the max
number that we have in this column or minimum. So I'm just gonna put
that back to some. And then we can also add
this here for example. So let's say we want to look at the average price per unit. I can just select
average just like that. Now, this is great because
it really quickly allows us to find this information out without having to
write out formulas. Now, another great features of tables is obviously
we get the filters. So say we want to quickly look at just the large products. I'm just going to select
large and then select Okay. And we can see we've just changed our view just
to those products. Something else to bear in mind, and I'm just going to
clear this is that we can also filter our data with
something called slicers. Now to insert a slicer, I just need to select
Insert Slicer, which is in the table
Design Options. And here we have a list of
all the column headings. So let's say we want to insert a slicer so that we can take a look at the different
sizes and you'll see what a slicer
can do in a sec. So I'm just going
to select size. Then I'm going to select, Okay? And then you'll see we have
this box appear and it kinda has like a button for
each of the different sizes. So let's say I just wanted
to filter for medium. All I have to do is select this box and it instantly
changes the table. So now we're only looking at
the products in medium-size. I could quickly change this
to extra large, for example. And now you can see we're
just looking at extra large. And I also have the option to select multiple sizes at once. So I can select this
on here, multi-select. And then for example,
I could look at extra large and extra, extra large. And then you'll see again that the table changes based
on our selections.
5. Formatting Approaches & Tips: In this lesson, we're going
to take a look at some of the formatting options and
approaches for tables. If we select into
our table and then go to the Table Design menu, you'll see we have the
table style options here. So we can, for example, remove the header
row and then you'll see our column
headers disappear. We can add the total row, also remove it pretty quickly. And then banded rows. If we unselect this, you'll notice how we have
a more cleaner format where we don't have the
alternating colors for each row. And then we can also
select this one. And this will add bold
formatting to our first column. We could do the same for
last column as well. And we can also alternate the formatting for columns
by selecting banded columns. And now you can see
these are all blue rows, these are blue and white, etc. Then lastly, there's the
Filter button option. So what this does, it
allows us to remove the filter if we don't feel the need for that and
we just want a cleaner. More simple look. Next up, let's take a look at the table styles we have here. So as you can see, there's many different
styles to choose from. And it really depends what type of look you're going
for with your table. But if we just go back
to the one we had, something that's
really powerful is the ability to customize
one of these styles. So what I can do is right-click
and duplicate this. And I'm going to name
my table style product. And what I'm gonna do is
change it so that each product alternates on the
color that they have rather than alternating
every other line. So I'm gonna go with first
row stripe and we need to select seven because we've got seven rows for each product. And then I'm gonna go to second row stripe and
select seven here. Then I'm going to select, okay. And if we select
this drop-down here and select the custom one
that we've just chosen. What you'll see now is all
of the t-shirts are in blue, then all the polo
shirts are in white, and all the sweatshirts
are in blue, etc. So this is just a nice, clear way to see where one product stops and
where another product starts.
6. Formulas & Tables: In this lesson, we're
going to take a look at working with formulas
when working with tables. So we've got a new dataset here. What it is, It's departments
of an electronic store, so appliances, audio camera, computers, phones in TV. It's the sales across
different years from 2019 through to 2021. And we also have
the revenue figures to so let's say we want to add a column to this table that shows the average
transaction value. So that would be the
revenue divided by sales. So first of all, let's
add a new heading in. So I'm going to put
average transaction value, hit Enter, and I'm just
going to resize that. And as you can see, because
we're working with a table, excel has automatically expanded the table range to
encompass the new column. And then what I'm gonna do
in this cell is reference the revenue and then divide it by the sales to get the
average transaction value. Now what you'll notice
is rather than having equals E4 divided by default, which is what you're
typically get an Excel. Because we're working with a table format is different
and we actually have the brackets and
the at symbol and revenue divided
by sales instead. Then when I hit Enter, you'll also notice that
what's happened there, all of these cells
have populated, so you didn't have to
drag the formula down, which is automatically populated
in all of these cells. Now we're going to take
a look at working with formulas outside of tables. So here we have total sales written out and in
this cell here. So I4, what I wanna do is populate the total of all
of these sales numbers. So to do that, I'm just going
to write out equals some. Come along to these
cells and select them. And you'll notice
how our formula says Table one brackets sales. Now at this point, it's
worth pointing out how you can change
your table name. Because at the moment our table, it's called table one, which is the default
name excel will give to the first table you
create in a workbook. So what I'm gonna do is just
come out with the formula, click into the table and go
to the Table Design menu. And here we have our table name, which is table one. And I'm just going to call that dept sales as a shortcut for
what this table is about. To. Now, when I write out the
sum function and I come back to the table and highlight all the cells
that I want to add up. We've got our new
table name there, so DAP sales and then the data, the column that we are
referencing to add up is also in brackets,
just like that. Hit Enter and I get
the total 154259. Now, the benefits of tables obviously is that
when we add extra data, so if I just put more data
down here or down here, it's automatically included
in the table range. And what that means is is that any formulas you
have referencing the table will automatically encompass that data as well so you don't
need to update them. So just to give you an example
of what I mean by this, if we were to copy these
data here, paste them here. And if I was to write
a sum function, summing up all of this. So we have our
traditional sort of reference cell L4 to sell L 21, hit enter, we get
the total the same. I'll just make it look the same. If I am to add an
extra value here. So let's just say 5 thousand. The formula doesn't update, so we're still only
referencing those cells here. So the value hasn't changed. If on the other hand, I add
more data to this table, the reference range will actually change because
it will include the cell that we've added because it's basically referring to everything in the table
in the sales column. So if I put 5 thousand here, you can see how
this one updates. That's just another advantage
of working with tables rather than keeping your data outside of the table format.
7. Deleting Cells & Converting to a Range: In this lesson, we're
going to look at how you can delete data from a table, and also how you can convert a table into a normal
range of cells. So let's say I want to
delete the appliances and audio data by highlight all of these cells
and press Delete. What you'll notice is all of
the data has been deleted, but the table rows
actually still remain. So to delete the table rows,
I'm just gonna go back. I need to make sure all
of the data is selected. Right-click and then select
Delete and table rows. And then you'll notice now that both the data and the rows
themselves have been deleted. For deleting columns. It's the same process. So let's say I wanted to delete the average transaction
value column. I just need to select
anywhere in this column, so I don't actually
have to select the whole column like that. I just need to select
anywhere in the column. Right-click, go to Delete, and then table columns. Lastly, to convert this
table into a normal range, I just need to select
anywhere in the table. Go to the Table Design menu
and select Convert to Range. Get this menu pop up. Do you want to convert the
table to a normal range? Yes. Then now the table is no
longer actually a table. So we've got the
formatting leftover, but you'll see the
Table Design menu at the top there
has disappeared. And to convert the
formatting back to normal, I can just highlight
all of this. Make sure I'm in the home
ribbon and then select Normal. Just like that.
8. Next Steps: Thanks for taking this class on tables in Excel, and I
hope you enjoyed it. If you've got any questions, just let me know
and you can find the class project
instructions and falls below to test
what you've learned. If you want to learn
more about Excel, check out my other
classes and follow me for updates on
my new classes. Once again, thanks for taking the class and I hope to
see you in the next one.
Excel Classes, Excel teacher
