All language subtitles for 8. Formatting Data as a Table

For me, Excel tables are one of the best things in Excel.

And the reason why I love Excel tables so much is that they take a lot of the hard work out of formatting

and organizing your data.

And also, they help you update data's super quickly.

So we've already seen how we can put data into an Excel table a few times throughout this course, but

I just want to recap it in case you did miss that part.

And I also want to show you why it's so important that you put your data sets into excel tables.

And this is my number one recommendation.

Whenever you import data and you clean it, the last step should be to not only spell, check it, but

also put it into an Excel table.

It's going to make your life so much easier when you then start to build pivot tables and charts based

off of this data.

So I want to start out by showing you a quick example of creating a table and why it can be so useful.

So I have a very tiny dataset here.

I have a few countries listed and I just have some sales figures for these countries.

What I want to do is I want to put this little piece of data into a table.

Now all I need to do here is click within my data and press the control T keyboard shortcuts.

Now really important, make sure that you select my table has headers if it does, in fact, have column

headings and click on.

OK, now that is going to format your data as a table.

Now how do I know it's a table apart from the obvious formatting?

Well, I can see that I now have a table design ribbon appear at the top.

And this is one of those contextual ribbons if I'm clicked somewhere else on the worksheet.

The table design ribbon disappears if I'm clicked with in my table.

It reappears basically on this ribbon.

I have all of the commands, all of the formatting that I need for this table.

Now, I don't particularly like banded rows, so the first thing I'm going to do here is turn those

off.

Now my next step is to always name your tables, because by default, when you create a table, Excel

is going to assign it a very generic name and you can see here on the table design ribbon in the properties

group.

This table is currently just called Table one.

If I was to create another table, it would be Table two.

So on and so forth.

Now these table names are very generic.

They're not very meaningful.

When you get a bit further down the track and you start trying to identify different tables to use and

things like formulas.

It's going to be really hard to know what table you're referring to if you just have them called Table

one two three four five.

So you want to make this more obvious as to what the table contains.

So I'm going to just call this.

I'm just going to call this sales on the school data and enter now a really important point when you're

naming your tables, you can't have any spaces in your table names.

So if you have two words like I do here, you need to separate them with an underscore or you just need

to remove that underscore and have it all as one word.

So super important to name your tables.

Now what is the biggest advantage of a table?

Well, one of the behaviors of a table is that they auto expand.

And what that means is that if you add any additional data onto the bottom here, the table will expand

to accommodate that new data.

And the new data will essentially become part of this sales data table.

And what that means is that if you've created maybe a chart based on this table data, the chart will

automatically update with the click of one button to include the new data that you've added, so it

makes everything a lot quicker.

So let me show you that in practice.

Now I'm going to quickly create a column chart.

So let's go on to the Insert tab into the Charts Group, and let's just select this one here.

Now, if you've never used charts before, don't worry, we have an entire section dedicated to building

charts a bit later on in the course.

But this is a fairly standard chart is just showing my figures for these different countries.

Now, I'm not going to do anything fancy here.

I'm not going to start formatting it because the entire point of this exercise is really just to show

you how easy it is to update charts when you add new data.

So because my data is in a table, if I was to click underneath and add a new country, so let's say,

let's just say Brazil, press the tab key.

Notice as soon as I do that, Brazil becomes part of the table and it's automatically been updated in

the chart.

I haven't yet added my sales values, but as soon as I do, let's just say 20000.

It's going to add that bar to my child.

Now, if I hadn't put my data in a table, this child would have behaved like that.

So let me show you what I mean.

I'm going to undo a few steps here.

So this data is no longer in a table.

Now what I could do is I could select the data, go up to insert and create a column chart.

Everything looks the same so far.

But if I then add Brazil onto the bottom, I press the tab key.

Notice my chart does not update.

And that's because I've created this chart based on this range of cells and by default, a range of

cells doesn't auto expand to accommodate anything new that you add.

Whereas a table does.

So that is one of the reasons why I love using tables so much.

They just take a lot of the hard work out of it for you.

So let's jump across to our format as table worksheets, and we're just going to finish off our sales

data spreadsheet by putting it into a table.

So let's click in the dropdown.

Now remember, you can press control T to create your table.

Alternatively, you can jump up to the Home tab and go to the format as table dropdown and choose one

of the tables from the gallery.

Both of these do exactly the same thing.

So what I'm going to do here is let's just select this table.

Yes, my table has headers.

Let's click on.

OK.

And if you look at that table design ribbon, we have numerous things we can do here.

So if I decide I want to change the table style, I can click here and I can change it to something

else entirely.

I'm fairly happy with it being green.

I can then choose some table style options.

Now I could toggle off the head arrow if I wanted to.

I'm not going to do that because the header is extremely important.

I can add a totals row from here.

I can toggle off and on banded row.

So if I don't like that banding, I can turn that off.

I can make the first column bold.

I can make the last column bold.

I can even choose banded columns.

And if you're wondering what these banded rows and columns are a lot of the time these can really help

with the readability of your worksheet.

So some people love these.

Some people don't.

I can't say I'm a fan.

I normally turn these off.

And if you don't like that filter button at the top there, you can toggle the filter off as well.

Now, some other things of importance on this ribbon.

We can do things like insert slices.

Now a slice is simply a visual filter, and we're going to be talking about slices when we get to the

Pivot Tables section.

So I'm going to hold off from speaking about that too much at this moment.

Now what I can also do from here is I can create a pivot table.

I can remove duplicates.

So I think we did this already.

Let's click on.

OK, yeah, we go to duplicates found I can remove those at this stage.

And as we've seen, if we want to take this data out of a table and just convert it to a regular range,

we've got the convert arranged button just here.

And then finally, we have the properties group where we can give our table a name.

So I'm going to call this table product sales and enter now.

The final thing I would probably do here prior to putting this into a pivot table or creating a chart

is perform a spell check so we can very quickly invoke spelling by pressing the F7 key.

I'm going to say yes to continue checking, and I don't have any spelling errors in this way.