Practice Video: Consolidating by Position

Loading...

Del curso dictado por Macquarie University

Excel Skills for Business: Intermediate I

1604 calificaciones

Macquarie University

Excel Skills for Business: Intermediate I

1604 calificaciones

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

De la lección

Working with Multiple Worksheets & Workbooks

This module is all about working with multiple worksheets and workbooks. Learn how you can combine data, manage datasets and perform calculations across multiple sources. And don't forget the Toolbox with handy shortcuts and ninja tips.

Practice Video: Multiple Worksheets6:23

Practice Video: 3D Formulas5:16

Practice Video: Linking Workbooks6:46

Practice Video: Consolidating by Position5:58

Practice Video: Consolidating by Category (Reference)3:58

Week 1 Wrap-Up1:27

Conoce a los instructores

Dr Yvonne Breyer
Program Director, Design and Experience, Global MBA
Faculty of Business and Economics

In the last video, we saw how we could work with data in different workbooks by

linking those workbooks.

Linking, however, can cause problems of its own, and Uma has a situation now.

Where she wants to take the regional expenses from three different regions.

And sum them in a single workbook, that she can send to head office.

She wants to avoid the complication of working with links.

A tool that can be really useful to her is the consolidate tool.

It can be found on the data tab of the ribbon,

in the Data Tools group, and you'll see the consolidate tool.

Now before we get started, as we did last time, I suggest you minimize your ribbon,

and then let's get out other three workbooks open.

So Ctrl+O, click Browse, and the three workbooks

we needed are the expenses for Melbourne, Perth, and Sydney.

So use Ctrl to select them, and then click Open.

Once again, all four workbooks have opened, but

it's easier if we can see them.

So I'm going to come to my view tab this time, and click Arrange All, and then OK.

And there are my four workbooks, ready to go.

Now when we want to do a consolidate, the first and possibly most important step,

is to make sure you're clicked in the cell where you want the consolidation to begin.

A common mistake is to accidentally click in one of these workbooks, and

you risk overwriting your data.

Now we're going to find our consolidate tool, so

I'm going to come up to Data, come to Data Tools and

there's our consolidate, tricky to find, but still there.

This is the consolidate dialog,

the first thing you're allowed to do is choose which function you want to use.

So you don't have to sum the data, you can get a count or

an average, or any list function, really.

We're going to stick with sum, and

now we need to tell Excel where to get this data from.

And that's what we put in this reference box, so click in the reference box.

We'll start with Perth, so I'm going to come to the Perth workbook,

and I'm going to select everything from C7 all the way down to F28.

Now, the next important thing, make sure you click the Add button after adding each

reference, otherwise, you will end up overwriting them.

So, that was Perth, let's do Melbourne, it's the same thing.

Come to your Melbourne workbook,

select everything from C7 down to F28, and click Add.

And then finally, we will do Sydney as well, so everything from C7 down to F28.

And click Add, we have now told Excel to add up the values from those three

workbooks and place them into this one.

So now we just press OK, and there is all of the data from those three workbooks,

added together into our new workbook.

This is, however, a snapshot, if I click on one of the cells,

you'll see no formula, simply the value at the time that the consolidation was run.

What this means is if I come back to Perth and change my miles driven for

Q1 to 1741, this does not update.

That said, once you've set up the consolidation, it actually remembers it,

and you can simply rerun it anytime you need to.

So I'm going to click back into C7, come to my Data,

come to Data Tools, click Consolidate.

Check if everything's okay, OK, and now it's picked up the changes.

So once you've set up the consolidate, you can run it as many times as you need to,

and you don't need to have to worry of links.

That said, if you want links, you can add them, so

I'm just going to make this a little bigger so you can see what's happening.

And I'm going to duplicate this worksheet, because Uma doesn't actually want links,

so this is just for demonstration.

I'm going to hold my Ctrl key down, and drag the worksheet tab to get a copy,

and then I'm just going to delete the data out.

Now we're going to run exactly the same consolidation again, so

Data > Data Tools > Consolidate.

But this time, I'm going to have a look at this little check box at the bottom,

called Create Links to Data Source, and

I'm going to put a tick in that and say OK.

Now at first glance, it looks like an identical consolidation, but

have a look to the left, and you'll notice gaps in the row numbers, and

these funny little pluses.

What has happened is, Excel has actually created an outline, and if I

click on one of these pluses to expand it, you'll see it's actually pulled through

each individual parking toll from each of the workbooks, and then summed them up.

So while the links is quite powerful, it is also a little bit messy.

And you should be warned up front, you cannot undo a consolidation of links.

So now if I needed to get rid of this outline,

it's going to be a bit of a manual job.

Quick heads up, select all the cells affected, come to your Data tab,

come to Outline, come to Ungroup, and then Clear Outline.

That will get rid of the outline for you, but then you'll still have to manually go

and remove those extra rows that have been added.

So just think carefully about whether this is what you want before you go about

adding it.

You will have noticed that the workbooks had to be identical in structure, and

that is because we have used Consolidate by Position.

In the next video, we're going to look at an even more powerful consolidate option,

called Consolidate by Reference.

Which allows our workbooks to be slightly different.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play