Practice Video: Converting to Range and Subtotaling

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate I

1158 classificações

Macquarie University

Excel Skills for Business: Intermediate I

1158 classificações

Curso 2 de 4 em 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.

Na lição

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

Conheça os instrutores

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

Uma has just received a call from her supervisor to quickly bring him a break

down of staff names and salaries with subtotals for all departments.

She needs a quick and

easy solution, What she is after, will look a little bit like this.

There is a feature in Excel that summarizes subsections of your data,

it is called the subtotal tool.

You can find it on the data tab.

But when we try and use it with our table, you will notice that it is unavailable.

In order to apply this tool, we first need to convert our table back to range.

Now, while tables are absolutely brilliant,

they're not suited to all tasks.

We are really looking to use them whenever we are working with raw data entries.

In other words, every row contains an entry to your database.

When you are looking to create summaries of sections inside your table,

you start running into trouble.

The subtotal function works brilliantly in the total row at the bottom of your table.

But if you try to use this function in the middle of your table,

you would effectively break your table.

Now, before we do this, let's make a copy of the database worksheet for

this task, so that we don't compromise our original data set.

Keep in mind that the report will be just a snapshot of the database as it is now,

and will not be updated from here.

As a reminder to our future self and

others, we will name the worksheet accordingly.

Uma's supervisor, wants this break down by department.

So let's quickly sort the table before we convert it to a range.

You could do this afterwards, but it's just as easy to do it now.

Then let's remove the banded rows because they will become messy

once they no longer automatically update in the range.

And the total row should also be turned off.

Then we head over to the table tools, and click on convert to range,

confirm the dialog box, and here's our data back as a range.

And you also notice that the table ribbon is now gone.

You can see that the subtotal feature is available now, and we click on it and

there are a few settings we need to check.

What we want is a summary for each department, so

we need to tell excel that whenever there's a change in

value in the column that we select, we want to see a subtotal.

So in the first setting, we select department.

And in a minute,

you will see why we needed to sort our data by department first.

Now, we need to choose which function to use.

And as we are looking to add up the salaries, we will choose the sum function.

We can select multiple columns to summarize In our case,

we want the subtotals for annual salary, pension, and package.

Generally, the defaults for the next three options will give you what you want.

Yes, we want to replace current subtotals.

Well, we don't really want to create a page break between the groups.

This may be useful to you, but it really depends on your needs.

In our case, we want to keep the data together.

The third option provides you with a grand total.

And I'm going to leave that selected to have the total displayed

at the end of our report.

So within a few clicks, we were able to create the breakdown by department for

Uma's boss.

For all departments, you can now see each of the staff, their salaries,

pensions, and packages, as well as the total for that department.

To quickly improve the layout,

let's hide the columns in between the department names and the salaries.

One last feature of the subtotal tool that I want to show you is the outline on

the left hand side that the subtotal creates automatically.

There are three levels, and

you can use these little buttons here to navigate between them.

We are currently at level three which shows the complete data set.

Level two collapses the detail, and shows the subtotals for each department.

And finally, level one shows only the grand total.

Now you can see why the subtotal feature is not supported by tables,

because we now have summary data mixed with our data entries.

We made a copy of our range to create this snapshot report.

But in case you want to remove the subtotal

from your range to keep working in it, it is quite easily done.

Go back to the subtotal tool,

click on remove all, and here we are back where we started.

As we have seen, converting a table back to a range is easy.

I recommend that you remove banded rows or columns before you convert the table.

The subtotal feature is really handy, and

helps to create quick snapshot reports of your data.

But remember, you can only use this feature with a range, not a table.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

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