Practice Video: Linking Workbooks

Loading...

Del curso dictado por Macquarie University

Excel Skills for Business: Intermediate I

744 calificaciones

Macquarie University

Excel Skills for Business: Intermediate I

744 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
Director, Learning and Teaching
Faculty of Business and Economics

[SOUND] In the previous video,

we saw how we could work across multiple sheets within a workbook.

In collaborative environments however,

it often is required that we work with data in multiple workbooks.

And Excel allows us to do this through linking workbooks.

We have exactly this example here, where Uma needs to collect the data from the Q1,

Q2, and Q3 workbooks, and summarize those values into a total expenses.

Now before we begin the video,

please make sure you've opened up W1_LinkingWorkbooks.xlsx and

that you've closed any other workbooks you may still have open.

Now we're going to be working with four workbooks and

we want to see them all on the screen at the same time.

And you're going to find that the ribbon starts taking up a lot of space.

So to save on space,

we're going to minimize our ribbon by double-clicking on the Home tab.

Now let's go get those other workbooks open.

So I'm going to press Ctrl+O.

[SOUND].

I'm going to come to browse.

Navigate to where your workbooks are stored.

And then can you please select W1_ExpensesQ1.xlsx,

hold your shift key down select Q3 so they're all selected.

And click Open.

Now all four workbooks have been opened.

But it'd be much easier if I could see them all on screen simultaneously.

To do this we can use the arrange all function.

This is available on the view tab.

But there's a great little feature I'd like to show you called,

Tell me what you want to do.

It's quite new.

And if you click in here and start typing what you want to do,

Excel will actually give you a short list of things that meet your criteria.

And, yeah, arrange all.

So I'm going to click on that.

I do want them to be tiled and I'm going to say, OK.

And now all four of my workbooks laid out nice and easy for me to use.

Now what I want to do here is get the total values

from each of these workbooks into this workbook.

I do not want to type them though, partly because I'm very likely to make an error

and it's going to be very slow, but also because if the values should change in

one of these workbooks I need this one to update automatically.

So we're going to use exactly the same technique we used when working across

worksheets, and you'll see it works almost exactly the same with one small exception.

So start by clicking in C7 and type =.

Now, I'm going to come to my Q1 workbook.

Click once to select the workbook and

then click on the field you want to pull through.

Now, just have a quick look at this formula.

It's got first the name of the workbook, then the name of the worksheet and

then the cell.

But it's done something different.

It's applied an absolute cell reference and

this happens automatically when working across workbooks.

Now we're going to copy this formula in a moment, so we want to get rid of that.

So, to do that just press your F4 button three times.

Absolute cell reference gone.

Now, to get to this next cell I'll going to press Tab.

And let's repeat that process.

So =, come to HR Expenses 2, click in that total field,

F4 three times to get rid of our dollars, and tab.

And the last one now.

We're going to get our Q3 total, and of course turn off our dollars, and enter.

Now what we've done there, is we've pulled through the values from the workbooks, but

we've also linked our expenses workbook to our three quarterly workbooks.

And what that means is if a value changes in one of them,

it will update here as well.

Let's test that theory.

So let's say, for example, in Q1, Carlos actually did 400 Ks in January.

So I'm going to come to his miles driven.

I'm going to change that to 400, press enter and

note that when I do the miles driven here automatically updates.

And if I were to undo that change, we're back to where we started.

And the good news is relative cell references still works, so

if I come into here, select my top three calculations,

and drag them down using my fill handle, the values are pulled through.

One small problem, I've lost my formatting.

So let's just come to here and say, fill without formatting.

Great, so that worked beautifully.

I'm just going to make this workbook maximized again so

you can actually see what's going on.

Right, let's just finish this off quickly.

So I'm going to copy these bottom four calculations.

Ctrl+C this time, Ctrl+V, and Ctrl+V.

And you can see I've very quickly completed my total expenses for Sydney.

Now linking is obviously very beneficial when you have workbooks

that are stored in a similar location and aren't likely to move around too much.

The problem is, once those workbooks start moving or

being renamed, the links can get damaged.

So the next thing I need to show you is where you can go to manage these links.

So I'm going to come to my data tab.

And just underneath the data tab you'll see edit links.

This is normally grayed out, but here we have links so

when we click on it, we can now see our three linked workbooks.

If one of these links becomes damaged, we just click change source.

And it will let us go and correct it to where the workbook actually is located or

what it's called.

I'm going to click cancel because it's fine.

The other option we have is to break a link.

I strongly recommend you don't do this unless you're absolutely certain you need

to because once you've broken the link it is a permanent move.

You can't simply undo that process.

But let's see what happens when we break the link to our Q1 workbook.

So I'm going to click Break Link.

There's my warning.

I'm going to go ahead anyway.

And when I close the dialogue and come back in to my workbook you'll see when I

go to any of the cells, the formula has been replaced by the last known value.

So I've completely lost that link and

the only way to get it back is to repeat the process we did earlier.

Take a moment to consider if there are any situations in your workplace that could be

solved by using linked workbooks.

[SOUND]

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.

© 2018 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play