Practice Video: Creating Named Ranges

Loading...

Del curso dictado por Macquarie University

Excel Skills for Business: Intermediate I

741 calificaciones

Macquarie University

Excel Skills for Business: Intermediate I

741 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

So we've now seen how we can name a single cell using our Name Box.

There are, however, several ways we can create

named ranges and each of them has their own advantage.

So we can look at creating named ranges now in a bit more detail.

Uma has been asked to generate some stats from her HR database.

This will be much easier for her if she can name each of the columns.

Let's start with our Employee ID.

So I'm going to click on A4 and then good shortcut key,

CTRL + SHIFT + down arrow,

to select all the data till you hit a blank cell.

Now let's recap on Name Box.

We're going to click into the Name Box,

type EmpID for Employee ID and click Enter.

And we've now named that entire range.

So that was the one method.

But, as promised, there are others.

If you come to the formulas tab on your ribbon,

you will notice a Defined Names group.

These are all the tools for working with named ranges,

and you can see there are actually quite a few.

Now, the next tool we are going to look at is Define Name.

This allows us to define name and it has some advantages over the Name Box.

Let's do the Pension column this time.

So I'm going to click into O4 and note,

I'm not including my heading.

It's not actually part of the data.

So with Name Box and Define Name,

we do not click on the headings.

Now, once again, CTRL + SHIFT + down arrow,

come to Define Name and it's opened up the Define Name

dialog and even worked out from the label what I probably want call my range.

So that's already great.

I can also adjust the range if I need to.

But both of those are kind of nice-to-haves.

The important thing is the second one, the Scope.

The Scope defines where this named range can be seen.

By default, it can be seen throughout the entire workbook,

which is great, because I can refer to it even if I'm on the Stat sheet.

If, however, I wanted to duplicate my sheets,

I'm not allowed to have duplicate named ranges.

So I could choose to change my scope to only be,

for example, in the Staff sheet.

That means I can have something called Pension in

Staff and something called Pension in Stats.

If you need to change your Scope,

it is important you do it upfront as this cannot be changed later.

And that's why we use the Define Name.

We don't actually want to change our Scope

and we probably won't use this option very often,

but at least you know where it is.

So we are going to say, OK. Now the last option is my favorite.

I'm looking at the state I'm thinking,

still 13 columns to name.

So much for efficiency.

Well, I wouldn't lie to you.

Excel gives us a fabulous tool,

called Create from Selection.

This allows us to name multiple ranges simultaneously,

using the labels already in the workbook.

So I'm going to name each of the columns,

using the column headings.

Too easy. Let's start by selecting our data.

Now, I've already done something different.

This time, I have included the headings.

I'm now going to press CTRL + SHIFT + down arrow,

so I've selected all the data and the headings.

We only do this with Create from Selection,

and that's because it needs the headings to work out what to call the ranges.

Now I come up to Create from Selection,

I'm going to hover for a moment,

so you can see the shortcut key,

CTRL + Shift + F3.

What a mouthful, but it's actually worth getting to know.

And then I'm going to click.

Now it's asking if I want to name each of the columns,

using the label in the top row or each of the rows,

using the label in the left column.

I don't want to label my rows,

so I'm going to untick that and click OK. Let's go and check our Name Box.

Click the dropdown and there are all of my new named ranges.

Wasn't that quick?

And if I'd check it out so I'll just click on Annual Salary,

you can see it has named the entire range but not included the heading.

So to recap, we've looked at three ways of creating named ranges.

The Name Box, Defined Name,

and Create from Selection.

We only select the headings if we're using Create from Selection.

The benefits of named ranges might not be immediately obvious,

but they will become more obvious as we start using

these in our formulas and functions throughout this course.

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