Practice Video: Apply Names

Loading...

Del curso dictado por Macquarie University

Excel Skills for Business: Intermediate I

820 calificaciones

Macquarie University

Excel Skills for Business: Intermediate I

820 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

Named Ranges

Learn how you can create, manage and apply Named Ranges to enhance your calculations.

Practice Video: Introducing Named Ranges5:17

Practice Video: Creating Named Ranges5:01

Practice Video: Managing Named Ranges4:18

Practice Video: Named Ranges in Formulas5:05

Practice Video: Apply Names5:17

Week 3 Wrap-Up1:16

Conoce a los instructores

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

We have now seen that when we come to work with our calculations,

it's ideal if we've already created the named ranges.

Ideal, however, is a luxury we don't always have.

Very often, we inherit other people's workbooks, and we may find

we have to create the name ranges with the calculations already there.

What we are going to look at in this video is a tool that can help us do this, and

it's called, Apply Names.

Now, let's just have a look at what we did last time.

So, we came to Total Package, and we created a calculation using the cell

references rather than a name range, because we didn't have a name range.

We've now decided that it actually would be a good idea to put that name range in.

So I'm going to press enter and I'm now going to name all this package data.

Stop for a moment, can you think what would be the best method,

or the quickest method?

Well, I'm going to suggest Create from Selection, so let's use that now.

Just to remind you quickly, if you can't remember,

on the Formulas tab, Create from Selection is in the Define Name's group.

And when you hover over it, to see the shortcut is, Control Shift F3.

This time we're going to use the shortcut, so you can see how quick it is.

Right, so I'm going to click on to P3 to select my heading, remember,

Create from Selection, you must include the labels.

I then press Control Shift down arrow to select all the data, and

then Control Shift F3 to select Create from Selection, and then just press enter.

Really quick, all right, let's double check.

So I'm going to come to my Name box and click the drop down, and

there is our new name range Package, so that worked.

When I come and check my calculation here, however,

it has not replaced the cell references with the name range.

Now, while that might seem a little irritating,

actually it's a really good thing.

There are several situations where we wouldn't want to use an absolute cell

reference and so a name change would not be a good choice.

Excel doesn't want to inadvertently break our formulas so

it doesn't automatically replace them.

Okay, that's well and good, but to now have to go and

manually change every formula could be really slow, and

we don't want to do that either, so that's why we use the Apply Name.

Now the Apply Name is also in the Define Name's group, but it's slightly hidden.

If you come to the Define Name, and click the drop down,

it's the second option down, so I'm going to click on that, Apply Names.

And it's opened the Apply Names dialog,

which gives me a list of all of the named ranges in my workbook.

You'll notice at it's picked up Package and highlighted it.

That's because it's identified there's a range being used in the calculation that

is identical to the Package range, and it's offering to replace it for us.

Now that's exactly what we want, but if we didn't want Package, we could unselect it.

And if we wanted a different one, we could select it by clicking on it, and

you can select multiple ones if you like.

We're going to go back to just Package, however, and then we're going to click OK.

Perfect, it has now replaced those cell references with a much neater and

more meaningful word, Package.

One limitation though that you should be aware of,

is you can only use Apply Names in the same worksheet as the named range.

Right, now let's just have a look at one more little feature just for completeness.

The one thing we have not discussed is the Use in Formula.

Now what I'm going to do is just click on to a brand new worksheet.

So let's get a brand new worksheet open, and

we're going to come to the Use in Formula, and click the drop down.

You may remember in the previous video, when we were half way through creating our

calculation, and we couldn't remember what the name range was, we pressed F3,

well, this is the alternative.

If you don't remember the F3 shortcut, you can always come in here and

simply select the name range you want.

But what I want to show you is right at the bottom here, it's called Paste Names.

And when I click Paste Names, it brings out my Paste Name dialog,

but it gives me this option, Paste List.

And I'm going to click on that and

what it has done is pasted all my named ranges into my workbook.

As our workbooks get more complex, it may become necessary to document them so

that other users can understand what's going on, and

this is one tool that Excel gives you to help you do this.

So this week we have looked at how to create, manage, and use named ranges.

As our calculations get more complex,

we're going to need to rely on them more and more.

So start making name ranges part of how you use Excel.

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