Practice Video: Trace Precedents and Dependents

Loading...

From the course by Macquarie University

Excel Skills for Business: Intermediate II

242 ratings

Macquarie University

Excel Skills for Business: Intermediate II

242 ratings

Course 3 of 4 in the 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 this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

From the lesson

Formula Auditing and Protection

If you are worried that errors have crept into your worksheet, this module will show you how to check for errors, trace precedents and dependents, resolve circular references, and finally, protect your worksheets and workbooks from further harm.

Practice Video: Error Checking9:20

Practice Video: Formula Calculation Options6:28

Practice Video: Trace Precedents and Dependents5:23

Practice Video: Evaluate Formula, Watch Window8:50

Practice Video: Protecting Workbooks and Worksheets9:36

Week 4 Wrap-Up1:24

Meet the Instructors

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

Alex has already corrected a lot of errors in his workbook,

but there are still a few outstanding.

We still have one green triangle that we have not

addressed and where there are a lot of errors in a workbook,

it's always a good idea to do a double-check to make sure you've pick them all up.

One good way of auditing a workbook is to create cross-check totals.

In other words, to try and get to your totals using

different methods and then to make sure that those totals still agree.

For example, in this workbook we have worked out

the Sales by Account Manager for each Year and then totalled them up.

In this section, we have worked out

the Total Sales by Year for each Region and then totalled them up.

But both of these totals should now show the Total Sales

for 2013-2016 and they should be the same.

But if you have a look at this Total figure and this Total figure,

they don't match and that is

a clear indicator that there are still problems in our workbook.

When you're trying to locate more subtle errors like this,

we have two great tools to help us and they're

called Trace Precedents and Trace Dependents.

A precedent is a cell that is referred to in a formula.

A dependent is the opposite -

it is a formula that refers to your cell.

So what we're going to do is come down to G26,

and we're going to find out which cells are being referred to by this formula.

So we're going to click Trace Precedents and it's

summing up the three cells above which looks absolutely fine.

But fortunately, we can keep going back so we can find out which cells these refer to.

So we're going to click Trace Precedents again.

And here, you can see the problem.

We have not included the 2013 Sales figures in our totals.

Now before we fix the error,

we'd like to remove the arrows so we're going to come back

up to Formula Auditing and click Remove Arrows.

And here's a nice way to fix the problem.

We're going to select those three cells and press Delete,

and then we're going to come up to AutoSum on our Formulas tab,

and single click, and it will immediately AutoSum each of the rows.

And if we double-click,

that formula is now correct.

Our total is looking much closer to the one over here,

but they're still not the same.

So we still obviously have a problem and it seems

quite likely that it's to do with this green triangle.

But we'd like to double-check,

does the cell in any way impact this total.

So let's click on it,

and this time we're going to Trace Dependents.

So I'm going to come back up to Formula Auditing and click Trace Dependents.

And it's quite interesting,

we can see it's being used in that total.

It's being used in this total and it's even being used down here.

So this cell, being an error is actually impacting quite a lot of different cells,

but so far we haven't seen a direct connection to this total.

So let's click Trace Dependents again,

and there you go.

This total is feeding into this total over here,

so the error is impacting that cell.

So, let's once again remove our arrows and go and fix the problem.

This time we have a little green triangle,

but we'd like to first identify where it is,

so let's Trace Precedents.

What we're trying to do here is sum up all the totals,

where our employee ID is equal to this value and our year is equal to 2015.

And so far, it looks like it's referring to the correct cells,

but we have this new symbol.

It's this dash black arrow,

and that indicates that we're referring to a value in a different worksheet.

If you double-click on the black arrow,

it will not only tell you which worksheet or worksheets,

but it will give you an exact cell reference

and you can actually go to that cell reference.

So I'm going to come to the first one and

double-click and that shows us what the problem is.

Instead of adding up the Totals,

we are adding up the Order Quantity,

and that's why it's gone wrong.

Okay, let's come back to our Sales Dash and quickly fix the problem.

We're going to remove our arrows and we're just going to come

to our dropdown to the right of our green triangle and we'll say,

"Copy Formula from Left."

and you see, problem fixed.

If you have a look at your total over here,

it now matches our one over here.

So clearly, we have corrected those problems.

So just to summarize then,

both Trace Precedents and Trace Dependents

can be used to help you identify where the errors

are happening in your workbook and what impact

an error cell might be having on the rest of your workbook.

We still have a couple more errors that we need to address,

but we're going to look at that in the next video.

When we look at another great tool called Evaluate

Formula as well as the Watch Window. So keep watching.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play