Practice Video: Multiple Worksheets

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate I

1166 classificações

Macquarie University

Excel Skills for Business: Intermediate I

1166 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

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

Conheça os instrutores

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

Many of the workbooks we encounter in business contain more than one worksheet.

And this is the situation Uma has.

She now has to work with the Q3 expenses.

Let's look at some of the tools that will help us work with multiple worksheets.

All the tools we're going to look at can be found in the Cells group on the ribbon.

But these are quite cumbersome to get to, so

we usually use the tools located closer to the Sheet tabs.

The first thing Uma needs to do is to add another sheet to put her summary

expenses in.

We're going to come to the plus, just to the right of the worksheet tabs and

click on it, and a new blank worksheet has been added.

You will notice it automatically adds it to the right

of the sheet I had previously selected.

So it's gone to the right of Sheet1.

Unfortunately, this is not where I want it.

The good news is, you can easily move your worksheets around.

To do this, click and

hold on the worksheet tab and drag to where you want it.

Notice, however, there are two icons, a little white sheet and a black arrow.

If I drop the white sheet now, it will actually put the sheet after Sheet2.

The icon to look for is the black arrow.

When that gets the right position, release your mouse and the sheet is moved.

The problem is starting with blank sheet is going to be quite a lot of work.

Uma has realized that basically her summary will look

very similar to the staff expense sheets.

It'll be quicker for her to make a copy of one of those.

One way to make a copy is to right-click on one of the sheets, and

it brings up a menu for working with worksheets.

Four down is Move or Copy.

We're going to click on that, and you'll see from here you could move it or

make a copy.

You can choose to move or copy to a different workbook altogether.

We're going to stay with this workbook.

And then choose where you would like the new copy to go.

We would like it to go at the end.

And importantly, make sure you tick create a copy.

Now click okay.

And there's an identical copy of Sheet1 at the end.

You can also use the drag option to copy.

The only difference is you have to hold your control key down.

So I'm going to come to Sheet1 again, hold my control key down,

and drag it to the very end, release, and there's another copy.

I now have way too many worksheets, so I'm going to need to delete a few of them.

To delete a worksheet, again, you can right-click and

press Delete, and it's removed.

Now when I come to delete this one, you'll notice it has data on it.

It's not empty, and I get a warning message.

This is really important.

You cannot undo a sheet delete.

So if a message pops up, please check it.

Make sure you are happy to delete it, and only then press the delete button.

Notice my undo arrow is grayed out.

No undo.

But that's fine.

I now have the sheets I want.

I just have no idea what's on which.

So the next thing I'm going to do is label my sheet tabs.

You can use the right click, but even quicker, just double click the label.

And without clicking it again, so with it still highlighted, type the name you want.

This is Sean's sheet.

Sheet2 is for Uma, and then Carlos, and finally, this is my summary.

So this is going to be HR Q3.

And when you're done, you can just click away or press enter.

Another visual clue you can give people is color coding the sheets.

To do this, write click on the tab, come to Tab Color, and

choose the color you'd like to add.

I won't do them all.

I'll just do a couple so you can see what it looks like.

So there's Carlos and we'll do one more.

Great.

Now you'll notice when I'm clicked on the sheet, it doesn't go fully pink.

It goes white with a hint of.

When I take away, I get that full color.

So the sheet that is predominantly white is the one you currently have selected.

Uma now has to make another change.

She's decided that this heading that appears on each of the sheets

is not very descriptive.

She, however, would prefer not to make the change four times.

So quite a powerful little option we have is to group sheets.

This means selecting multiple sheets simultaneously.

I'm going to click on Sean, hold my shift key down and click on HR Q3.

And you'll notice they've all gone white.

Now any change I make to one sheet will occur to all.

So I'm going to come up to my heading, and replace it with something more meaningful.

To unselect the sheets, click away from the original tab, and

you'll now notice that the heading has changed in all three.

So really powerful, but be careful that you don't make changes with your sheets

accidentally grouped, or you might not like the results.

Finally, while my staff are actually entering their expenses,

I don't want them to accidentally change my summary sheet.

So I'm going to hide that away.

And you may have many reasons to want to hide things.

Maybe it's extraneous data, maybe you would just prefer people didn't see it.

Simply right click, come to Hide, and click.

The sheet has not been removed.

It's just hidden.

And to get it back, all we need to do is right-click on any of the sheet tabs,

say Unhide, select the ones you want to Unhide, and okay.

We've now looked at several tools for managing our worksheets,

from deleting to inserting, copying, moving, and even renaming.

Make sure you have a practice with all of these, and then move on to the next video,

which will show us how to create calculations across multiple worksheets.

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