Practice Video: Absolute Cell References

Loading...

Aus dem Kurs von Macquarie University

Excel Skills for Business: Essentials

3879 Bewertungen

Macquarie University

Excel Skills for Business: Essentials

3879 Bewertungen

Kurs 1 von 4 in Specialization Excel Skills for Business

In this first course of the specialization Excel Skills for Business you will learn the Essentials of Microsoft Excel. Within six weeks, you will learn to expertly navigate the Excel user interface, perform basic calculations with formulas and functions, professionally format spreadsheets, and create visualizations of data through charts and graphs. Whether you are self-taught and want to fill in the gaps for better efficiency and productivity, or whether you have never used Excel before, this course will set you up with a solid foundation to become a confident user and develop more advanced skills in later courses. We have brought together a great teaching team that will be with you every step of the way. A broad range of practice quizzes and challenges will provide great opportunities to build up your skillset. Work through each new challenge with our team and in no time you will surprise yourself with how far you have come. Spreadsheet software is 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. At a time when digital skills jobs are growing much faster than non-digital jobs, make sure to position yourself ahead of the rest by adding Excel skills to your employment portfolio.

Aus der Unterrichtseinheit

Performing calculations

The syntax of formulas is very important in Excel. In this module, you will get introduced to formulas and functions - learn how to write them, use them to perform calculations and understand the different cell references.

Practice Video: Formulas3:32

Practice Video: Formulas in Context4:18

Practice Video: Functions I: SUM and AUTOSUM4:19

Practice Video: Functions II: AVERAGE, MIN and MAX4:21

Discussion: Absolute Cell References4:09

Practice Video: Absolute Cell References3:51

Practice Video: Calculations across sheets4:51

Week 2 Wrap-up1:29

Treffen Sie die Kursleiter

Dr Yvonne Breyer
Program Director, Design and Experience, Global MBA
Faculty of Business and Economics

Shawn sheet is coming together really nicely.

He has just one more thing he needs to do,

and that's give each account manager a five percent commission on the total sales.

Now before we start,

stop and think what type of calculation you're trying to do.

Can we get away with a simple formula,

or should we be using a function?

Well, this is a very simple calculation,

so we're going to use a simple formula.

We're going to type equals,

click on our total,

type our multiply, and click on our five percent.

Let's check if that worked.

Enter, and there's Aanya's five percent commission.

So, that was perfect.

Now, we don't want to type that calculation in for everybody,

but we know we can use our fill handle so we drag it down

and- oh dear, that's gone wrong.

The question is why?

Let's come back and have a look at the second one,

and you can see it says F5,

which is Charlie's total good times by J2.

We actually want to be multiplying by J1.

Why has Excel done this?

We're going to take a step back and talk a little bit about relative references.

When we've been copying our formulas,

we've been very happy that it's worked and we haven't

stopped to think about what Excel is actually doing.

But when we type F4 times J1,

Excel understands this as multiply the cell

four to the left of me by the cell three above me.

Which is why when we copy it down,

Excel says multiply the cell four to the left of me by the cell three above me.

Now most of the time,

relative cell references is what makes Excel so awesomely powerful.

But we can see in this situation it's preventing us from copying our formula.

So what's the solution, write them all manually? Not at all.

What we want to do is use an absolute cell reference.

What that means simply,

is that when we put in the J1,

we tell Excel that that is locked,

not relative. How do we do that?

Well, we simply put a dollar in front of the J and a dollar in front of the one.

Doesn't sound too simple, does it?

But actually there's a great shortcut key that will help us,

and it's the F4 key at the top of our keyboard.

So, if I first click on J1,

you have to have the J1 there.

So, click on your J1,

and then immediately press F4.

You'll see the dollar had been put in for you automatically.

Do be a little careful.

If you press F4 more than once,

you will notice you actually get what are called mixed cell references.

We'll look at these later but for now,

just make sure you've got both dollars on.

Then, press enter, come back to your calculation,

double-click to copy down,

and this time the copy worked perfectly.

If I check my second one,

you'll see it says F5 times J1.

So, we have effectively locked the J1,

so when we use the fill handle,

it doesn't drag, it stays put.

This is what we call absolute cell references.

You will need to use it occasionally when we have this situation of

a little outlier that's not sitting in our columns of data.

Takes a bit of practice to get it right,

but it's well-worth getting your head around it. Have fun.

Durchsuchen Sie unseren Katalog

Melden Sie sich kostenlos an und erhalten Sie individuelle Empfehlungen, Aktualisierungen und Angebote.

Coursera arbeitet mit erstklassigen Universitäten und Organisationen zusammen, um Online-Kurse anzubieten und dadurch universellen Zugriff auf die weltweit beste Ausbildung zu ermöglichen.

© 2019 Coursera Inc. Alle Rechte vorbehalten.

Aus dem App Store herunterladen

Erhältlich bei Google Play