Practice Video: Named Ranges in Formulas

Loading...

En provenance du cours de Macquarie University

Excel Skills for Business: Intermediate I

646 notes

Macquarie University

Excel Skills for Business: Intermediate I

646 notes

Cours 2 sur 4 dans 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.

À partir de la leçon

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

Rencontrer les enseignants

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

We have now spent some time looking at creating and managing named ranges.

It's time to start seeing some benefit, and you'll see this when we start

actually working with those names in our calculations.

Now Uma's been asked to create a few calculations.

First of all, she wants to work out total package for

each employee by adding salary to pension.

She's also been asked to get a few quick facts from the database.

Let's start with our package, now,

you could use conventional calculations here, absolutely correct.

But I'd like to show you the alternative, using a named range.

So click in P4 and type = then instead of grabbing your mouse and

clicking on N4, we're going to carry on with our keyboard.

And I'm going to type the name of the range, so I'm going to type an.

And before I've even finished, you can see in my autocomplete it has

identified all the names ranges that start with an, that's brilliant.

So I can use my down arrow to highlight it, and Tab to enter it.

So I've got there without going anywhere near my mouse.

Now I'm going to type +, and now I want to do the same with pension,

so pen, and Tab to select.

You will notice it has actually highlighted the entire range

annual salary and the entire range pension.

It's not going to add them all up,

it's just going to add the corresponding values in each column.

Now normally we'd press Enter, which is fine, but

here's another little shortcut key, we're instead going to press Ctrl+Enter.

You'll notice that it has done an enter, but instead of dropping us onto the cell

below, it's kept us in the cell where our original calculation is.

And this is quite efficient if you want to then copy it down,

because you don't have to click back.

So I'm now going to come to my fill handle, and double click to copy down.

Now let's just have a look at that formula again, first of all, no mouse.

Second of all, we get a nice consistent formula, we don't need to worry about

accidentally selecting N4 and O5, so it's just a neat way of doing things.

Let's now look, though, at a more typical example.

Now, I also want to calculate a few quick facts about this data,

starting with our total package outlay.

I haven't named this column yet, so I'm going to have to

use the conventional method, and this will serve as a good comparison.

So I'm going to click in S4 and type =sum,

to get a total and press my Tab key to open my bracket.

And I'm going to manually select the data, so click P4, Ctrl+Shift+Down Arrow.

Then if I'm concerned I'm going to be copying this formula,

I might want to add my $.

So let's put on a F4 on, ugly formula,

very cumbersome to create, but not wrong, so I'm going to click Enter.

All right, next let's now do that again, but this time using a named range.

So I now want to get my average salary, very similar, start with =av,

for average, down arrow, and Tab, to select the average function.

This time, however, I don't need to go and select the data,

because I've named the range salary.

If I start typing ann, there it is, Tab,

you can see it's been highlighted, and Enter, much quicker.

Let's do the same for our next review date, now, our next review

date is going to be the smallest review date, so I'm going to use my min function.

So =mi, Down arrow, Tab to select MIN, and

nex, there's Next Review, press Tab and click Enter.

The last one I have to do is my most recent start date, so that's going to be

the biggest start date, so I'm going to need my max function for this.

So I'm going to type =ma, Down arrow and Tab to select MAX.

But now I just had of those moments where I cannot for

the life of me remember what I called that column.

That's okay, we have a great shortcut key that can help.

It's our F3 key, so give it a go now,

press F3, and there are all the name ranges in my workbook.

And I don't even have to use my mouse,

I can use my down arrow to get to the one I want.

There it is, date of hire, click Enter, and Enter again.

So even with these really simple calculations,

we're seeing that by using on our name ranges,

we're much more efficient, we're not having to use our mouse at all.

We're getting much better looking calculations, and

this is just the beginning.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play