In this video we're going to learn how to format tables in Excel as well as a how to convert
our lists, or just our data that's in list form, into an Excel table.
Now, the reason you want to do that is, once you have a table, you have a little bit more flexibility, more options
in how you can format your data, on how you can manipulate and filter through your data. So,
to get going, we want to make sure our cursor is just somewhere in our list. And, as a side note, if you have a list of more data, which you
very likely will, um, you want to make sure that there's no skipped rows. So everything is just
one column next to the other.
So, with our cursor in that, um, we want to make sure we're here in the Home tab - if you
look at the top left, we are. So, we find the Style section. So when you're there,
you want to click on "Format as Table"
and when you do that you get this drop down with preformatted options. Now, you can create your own table style if you click on that.
You have to go to element by element and do your formatting, but
just to show you real quick: this is an option if your work has, um, your work or your client, has specific colors they want you to use.
So you would just go into Format, you're in...you go to Format Cells, Fill and here you can choose your Fonts, Borders...
In our case, if we're talking about colors, click More Colors and in Custom, and here you can put in the RGB for your specific need.
So, we're going to cancel out of this because I'm just going to choose,
um, just one of the preset colors. So, I'm going to use gold because I like gold, it's bright and sunny. And when I do that
you see that we got this Format as Tables dialog box come up. And our data is already preselected. Excel tried to figure out where that
started and ended. And you see here C4 to E10. And then it's highlighted here with the little dashes.
My Table Has Headers, Yes. That's checked off, so good. And then we click Ok.
And, when you do that, you automatically get a table formatted in the style that you chose.
So, in our case, we have a yellow, um, gold colored table with banded rows.
So, a few things to show you here before we go to see what's...what other options we have. If
you need to add rows or columns, um, you can just come to your last cell
and if you tab over, which is what I'm doing, you'll see that you can start adding more rows. So let me undo that, delete them. Now, the
other thing you'll see here, at the bottom right, it's very hard to see, but there's a little hook at the bottom right corner of the table.
Well, if you drag that to the right,
you'll add more columns, if the drag it down you add more rows. And that's another way
to expand your table and maintain that formatting that you created. So, we'll undo that.
Um, another thing to show you, real quick, you only get this if you have a table,
but notice here - our headers. Sales Person, Items Sold, Dollars. And up here the C, D, E -
column C, column D, column E.
So, as long as your cursor is anyone the table, when you scroll up and down, once you get past the headers,
the column actually changes its column name to reflect the header title. So if you have a lot of data and
you scrolled all the way down, you still haven't, um, you can still tell which header you're in. So, we scroll back down and you see that it
changes back. So,
ok, so, one thing, once we created a table you probably noticed that you got a new Design Tab. And this Design tab is just for
your table. So, I'm in it right now, it's part of the Table Tools.
Notice that if I click out of my table, it disappears. So I click back in and it's back.
So, what things can we do here?
So, you can rename your table. We don't really need to do that, but it's just a matter of typing in
there. You probably want to do this if you have a lot of tables, a lot of data - that way it's easier to find.
You can resize your table, you can increase the data range.
So, the Resize Table pops up and, um, you can just expand.
Hit Cancel. You can insert a pivot table.
So, you click on this
and the Create a PivotTable options come up. We're going to exit out of this, um, we're going to cancel out of that. If you are interested, I do
have another video on how to create Pivot Tables, how to work with them, so please do go watch that.
You can also remove duplicates.
And, once we click on that, this little dialog box shows up. Now, our data doesn't have any duplicates, but if it did,
we have the My Data has Headers checked off and you have the Columns; you would just uncheck columns you don't want duplicates
removed from. So, let's say we just wanted to check duplicates under Sales Person, um, you would just keep only that column checked.
And then what would happen is if it does find any duplicates, it just deletes them right out.
So we will cancel out of that. You can convert your data back to a range. Now the reason you'd want to
do this is because now you may have done other things to your table -
you may have added more data, and it's all formatted the way you want it, but when you do that you actually preserve the formatting.
So, I'm on the table, I convert to Range.
And, do I want to do this, am I sure? Yes.
And then now I no longer have
a dynamic table. I just have a dataset, but that data set that's in list form is formatted differently. So,
if I want to share this with someone or, um, let's say I don't necessarily want them to filter through the list, um, like I was doing, this
would be a great option. But, we're going to undo this so I can continue showing you.
Um, and another thing is, you probably saw this, I forgot to mention it, it automatically puts in filters. So, you have your little filter buttons
so you can choose whatever one you want. If I just want to look at Buddy and Emerson's performance, I can do that.
We'll undo that. So, back in our Design tab, um, you can insert a Slicer.
Now, the Slicer allows you to filter, um,
your data more dynamically.
So, for example, let's say I want to insert a slicer for Sales Person
and Items Sold. So I click Ok.
And now I get 2 different Slicers.
Now, when I have my slicers and, actually,
before we go, I was going to say that these options show up, but before we talk about that,
what this does is it lets you filter more. So, for example,
under Sales Person, if I click on Emerson,
it only shows me information for Emerson.
I'm going to clear this.
If I click on this, I can select multiples. So, I can say I want to see Emerson and Jill.
Or, excuse me, this filtered out Emerson and Jill - backwards.
So that, um, I'm only now seeing information for Buddy, Jamie, John and Sasha.
So, we'll clear out of that. But another thing that I wanted to show you is, up here in the Slicer, you
can change your Slicer caption. So we can say, I don't know...Sales Peeps
and that changes how my Slicer, um,
is headed. So it reads differently now, but it didn't change what's on my table.
Um, you can see additional Slicer Settings, um, here is the Display Header that we changed. You can sort, hide data, etc - if you have
blank cells or non-blank cells.
Cancel out of that. You can change the color of your Slicers.
So, let's say I want to choose this light, um, let's see what we have... We'll choose this one.
It complements our other, our table, a little bit better. So that changes the color of our Slicer.
Let's see...
Now you can also bring it forward, backward,
that just really depends, um,
if you have a lot of objects in your sheet how, you know, how to access it. Now, you can bring up the Selection Pane and this brings this up.
So, for example, we have 2 slicers. So if you have more, obviously, you will see them all listed down here.
Then you see this little eye ball to the far right? That hides them so you can say, you know what, I only want to see Sales.
So then you can hide
Items Sold. And what that's helpful in, if you have a lot of data that you're trying to filter through, a lot of Slicers,
you can only, you know, you can set this up so you're only looking at what you need. That way you're,
you know, while you're working, it's a little bit less, um, chaotic.
So,
we will close out of that.
And you can change the size of your Slicer. So, I'm on this one, so you see how the height, I'm making it smaller, bigger, um, and same thing
the sides. So, you can play around with that a little bit. I'm going to actually delete my Slicers - I just highlighted them. Hit Delete.
And we're back in our table.
So, the other thing we can do is, you can work and refresh your data. Export it - this is more if you're working with external data that you
pulled out of a database,
um, or something else. Now, here, the Style Options, you can do, um, you can
change to look of the table little bit more. You can get rid of your header row,
if, for whatever reason, you don't want that.
You can uncheck it. You can add a Total to your row. So if I do that, we see that the total dollars sold is $288,000.
You can unband your rows.
I kind of like banded looks, um, bold the first column,
bold the last column.
Do banded columns. So, and you can get rid of your Filter button.
So, you can do a lot of things with tables, um,
that'll help you if you go to present this information to someone else.
And, while you're manipulating the information through your table you can filter, um, in different ways. So I hope that you were able to
find this video helpful and, if you did like it, make sure to give it a Like and subscribe to this channel. Thanks!