aprender>
Negócios>
Fundamentos em Negócios>
Excel Skills for Business: Intermediate I cursos>
Practice Video: Sorting and Filtering in Tables

Loading...

Practice Video: Sorting and Filtering in 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.

Excel Skills for Business: Intermediate I

Universidade Macquarie

Curso 2 de 4 no Excel Skills for Business Programa de cursos integrados

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.

Visualizar o programa do curso

Habilidades que você aprenderá

Concatenation, Microsoft Excel, Consolization, Pivot Table, Pivot Chart

Ministrado por

Dr Yvonne Breyer
Program Director, Design and Experience, Global MBA

Transcrição

Sorting and filtering in tables works much the same as it does in ranges.

Many of us use filters on a very frequent basis, and this is perhaps why

there are so many different ways in which you can access this tool.

So just to give you a quick overview,

you can turn them on or off in the Table Styles options, and

you can operate them from the Data tab or the Home tab.

Now we'll turn them back on.

You can also access sorting and

filtering options through your right click context menu.

And, of course, you can access them directly from the header row.

And if you'll remember, as we scroll down,

our header row appears automatically in the column headers.

And from here, too, we can access our filtering and sorting options.

Uma needs to plan the next round of staff reviews, so

she needs to find the staff that are overdue for their reviews.

So first up, we're going to sort the data by department,

last name, and then first name.

So we'll go to our Data tab, Sort,

Department, and we want it to be from A to Z.

Add Level, Last Name, Add another Level,

First Name, there, that was quick and easy.

Since you previously applied conditional formatting to the next

preview column to highlight the overdue staff,

Uma can now easily sort by color to bring them all to the top.

There, Uma is going to note down these names, but we will undo this for now.

So we can restore our original sorting order of department,

last name, and first name.

The filter options that Excel offers us are very powerful.

For example, Excel recognizes that column F contains dates.

And as you can see here,

Excel automatically groups them by year and month.

This makes it very easy to filter by a certain time period, and of course,

you have a great range of date filters available as well.

Before we get into some more detail about filters,

let me introduce you to one of my favorite table features, the total row.

You can turn it on or off in the Table Style Options, and

when you turn it on, Excel adds a row at the bottom of your table,

which sums up the final column in your table.

But what's more is that you now have a drop down menu in each of

the cells of that total row that offer a range of calculations.

We can now easily count the number of our employees, or

add the average of years of service.

We can sum up the annual salaries, and it's all done within one or two clicks.

I'm sure you can already see how this is going to save you quite a bit of time.

But it is when we combine the use of the total row with filtering

that we discover just how awesome the feature really is.

So let's take a closer look at this while we are helping Uma to extract some

important statistics from her database,

as she is preparing a quick report for her supervisor.

The filters and the total row will help her with that task.

For her report, Uma wants to identify the top five earners and

their combined annual salaries.

So we'll simply go to the filter of the Annual Salary column and

we will use the Number Filter Top 10.

We only want to see the Top 5, and then we click OK,

so here we are, here are our top five earners.

And if you take a look at our total row, you can see that it has updated

the combined amount according to the filtered content.

I love this feature, it is so handy.

If you were summing values in a range,

the sum will not update when you filter your values.

Only if you are using the subtotal function,

which is exactly what the table automatically uses in the total row.

Now let's clear the filter.

And we have one more task that Uma needs to perform.

She needs to find out the number of staff that have worked for

the company longer than average, this, again, is easily done.

All we need to do is to apply a Number Filter to the Years of Service column,

and we will use the filter Above Average.

Quick and easy, here's our result.

And, our total row tells us that there are 15 staff members that have

worked longer than average compared to the other staff.

Once Uma has noted this down, we had better clear the filter.

Remember, it is good practice to always clear filters,

especially when you work in a shared environment.

To recap, sorting and filtering tables couldn't be easier.

You can operate the filter from the Data or Home ribbon, the header row, or

through your right click context menu.

If you are using the total row, Excel will update

the summaries in that row according to the filtered data in your table.

This feature makes reporting from tables super quick and easy.

Now we have already encountered many benefits of using tables.

In the next video, I will show you the ultimate highlight of using tables.

Now how is that for a cliffhanger?

Sobre o Coursera

Cursos, especializações e graduações on-line, ministradas pelos melhores instrutores das melhores universidades e instituições de ensino.

Join a community of 40 million learners from around the world

Earn a skill-based course certificate to apply your knowledge

Gain confidence in your skills and further your career

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2019 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play