Practice Video: Working with Tables

Loading...

From the course by Macquarie University

Excel Skills for Business: Intermediate I

488 оценки

Macquarie University

Excel Skills for Business: Intermediate I

488 оценки

Course 2 of 4 in the Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In the United States alone, millions of job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digitals skills lead to higher income and better employment opportunities at a time when digital skills job are growing much faster than non-digital jobs. In this second course of our Excel specialization Excel Skills for Business you will build on the strong foundations of the Essentials course. Intermediate Skills I will expand your Excel knowledge to new horizons. You are going to discover a whole range of skills and techniques that will become a standard component of your everyday use of Excel. In this course, you will build a solid layer of more advanced skills so you can manage large datasets and create meaningful reports. These key techniques and tools will allow you to add a sophisticated layer of automation and efficiency to your everyday tasks in Excel. Once again, we have brought together a great teaching team that will be with you every step of the way. Prashan and Nicky will guide you through each week (and I am even going to make a guest appearance in Week 5 to help you learn about my favourite tool in Excel - shh, no spoilers!). Work through each new challenge step-by-step and in no time you will surprise yourself by how far you have come. This time around, we are going to follow Uma's trials and tribulations as she is trying to find her feet in a new position in the fictitious company PushPin. For those of you who have done the Essentials course, you will already be familiar with the company. Working through her challenges which are all too common ones that we encounter everyday, will help you to more easily relate to the skills and techniques learned in each week and apply them to familiar and new contexts.

From the lesson

Tables

Tables, tables, tables. Start with creating, formatting and managing tables and then move on to sorting and filtering tables to get the data you need. Finally, wrap up this module automating your tables to make your work more efficient.

Practice Video: Creating and Formatting Tables5:06

Practice Video: Working with Tables5:53

Practice Video: Sorting and Filtering in Tables6:06

Practice Video: Automation with Tables5:17

Practice Video: Converting to Range and Subtotaling5:19

Week 5 Wrap-Up1:28

Meet the Instructors

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

In the last video we saw that tables are quick and easy to

create and that they have some very useful and versatile formatting options.

But tables in Excel have many more benefits,

and what's more, they're really easy to work with.

To show you the differences between working with ranges and working with

tables I have prepared a little playground for us on this worksheet.

What you see here is our data in a table and then the same data in a range.

Now let's look at some of the differences.

Selecting data is something we do all the time so let's explore that first.

To select a row in a table we have a number of options.

We can select the first cell and then drag to the

last or we click in the first cell and then shift click to the last.

That works the same as in ranges but it can

become rather tedious especially if your table is very large.

But we have an additional feature in tables and that

is a little black area that appears when we go into

the first cell of a row in the table and click on

it it perfectly selects our entire entry.

Now I hear you say why don't you stick to using

Control shift arrow to just select the data in your row.

Well yes that does work until you encounter a blank cell and then it

doesn't and that's the same for a table and a range.

So to have this additional option of selecting just your data in a row it's very handy.

It works the same way in a column as you can see.

Here's the little black arrow,

I click once and it has selected just the data.

And a second click and it includes the header row.

If you need to select all of the data in your table you

can of course use control A and in a table,

again this will give you the option of selecting

only your data or with the second use of the shortcut.

Include your header row.

You can use the mouse to do this too.

Move your mouse cursor into the top left hand corner until it turns into

a little black diagonal arrow click once and you've selected your table data,

click twice and you have included the header row in that selection.

Now I hear you say again,

control A works just fine inside a range.

It does, you are right.

However if you had to clear some content in your range,

even if just temporarily,

you can now no longer select your range with control A.

And this is because you have broken that block of contiguous data.

If you do the same thing in your table however,

Excel will remember that this table represents one entity

and when you use control A it will select all of your table.

The reason for this behavior goes back to our discussion video about tables.

Once you convert data into a table it effectively

converts the rows in your table into database records.

All the information in that record belongs

together and Excel treats the cells accordingly.

Wow, I hear you say, that's impressive.

But wait, there's more.

Now that we have a better understanding of how to work with

tables let's go back to Uma's HR database.

She's had to take some time off and when she got back she noticed that

someone had made changes to her table and they aren't very good ones.

It seems that somehow a number of records were duplicated.

Well, let's fix this.

Firstly, let's apply conditional formatting to

the employee ID column to see how big the damage is.

So we use the mouse pointer to select just the data in

our column and then we go over to conditional formatting,

highlight cell rules, duplicate values, and OK.

There, now we're getting a better idea of what's going on.

Now we could go ahead and manually remove these entries but I'm sure you've

guessed it by now there is a much better way of doing this in our table.

And the solution is located right here in the Design tab of the table tools.

All you need to do is click on remove duplicates,

confirm that Excel is looking in the right spot.

Click OK and yes it found three duplicate values and it removed them for us.

Perfect. Let's scroll down to the end to confirm this.

I think we better leave the conditional formatting on just as a future safeguard.

By the way, one last thing.

Did you notice what happened at the top here?

As soon as the headers of our table disappear when we scroll

down the column row displays the head instead.

No need to worry about freezing panes when you're working with tables.

We have seen that tables makes selecting data much easier,

that we no longer need to use freeze panes to keep the header row visible,

and that it is no problem at all to remove duplicate entries from a table.

And of course there is still more.

In the next video we will discover how sorting and filtering tables

works and we will also look at one of my favorite features of tables,

the total row.

So stay tuned.

Ознакомьтесь с нашим каталогом

Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.

Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.

© Coursera Inc., 2018 Все права защищены.

Загрузить из App Store

Загрузить в Google Play