Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
After a while, you might find you're spending a lot of your time in Excel not creating worksheets but auditing sheets that have already been created. So I want to show you tools that Excel has to help you along. Well, if you take a look at this worksheet here, we have a couple of groups of products and we have a total for the first group and average for the first group, you might need to scroll down, and we have total and average for the second group and then grand total and grand average for this. And what's happening is you have your data, one cell is feeding into another one.
So like January is feeding into here and feeding it to there and then it's also feeding down here on the bottom. This is what auditing is going to help us with. So, let's go into the Formulas tab and you see over here we have the Formula Auditing section. There's some terminology that you need to know first is precedents and dependents and a dependent is a formula that a cell feeds into and a precedent is data that feeds into a formula. So it kind of depends on what your perspective is.
Are you data or are you formula? Well, right now we're in some other data. It doesn't matter. Click in any of these data cells, doesn't matter, and then click Trace Dependents. You see Excel draws in these arrows. So what this is telling us is that February for Begentier olives feeds into total, feeds into an average, and feeds into this average over here and to this total here, but nothing down here. Well, let's choose another one. Let's choose maybe total for Cantera and click Trace Dependents and this is a formula where this is data but this formula is feeding into this cell and it's feeding into this cell and it's feeding into this cell.
Well, let's look at the opposite perspective. Click Allora here and then choose Trace Precedents and this is telling us okay, this average formula is getting its data from up here. Let's maybe click for average sale of Cantera olives and Trace Precedents and it tells us, okay, January, February, March is feeding in there. Now when you see all of these arrows and boxes, these actually print. Let's take a look at Print Preview. You could either press Ctrl+P on your keyboard or go to the File tab and down to Print and you see these will all print out.
Just press the Escape key so we're back here. Now, if this is too much of the mess, you could just click over here, Remove Arrows, and they're gone. Now let's go down here to the bottom where we have our grand total and grand average and I'll click, let's say, over here at the end of Column F and Trace Precedents. Now you can see that this is taking from this specific cell and that specific cell and feeding in here or maybe the grand total for both groups and Trace Precedents and again it's taking from this specific cell and this specific cell and feeding in here.
So again, let's remove the arrows. Well, using the Trace Precedents and Trace Dependents commands on the Ribbon bar are great for drawing in these arrows and boxes, but sometimes you might just want to select the cells, that is you might want to select the dependents or the precedents. Now, what's in the Formula Auditing selection here on the Ribbon bar does no tell you is that there are direct precedents and direct dependents and there are indirect precedents and indirect dependents. The only way to select them is with a couple of whacky keyboard shortcuts. Let me show you.
Let's click maybe over here in March. Now if you press Ctrl+Right Square Bracket and the Right Square Bracket is little to the left of the letter P on your keyboard. You notice what it does. It selects the cells that that cell is feeding directly into. No circles, arrows, and as soon as you click, that selection goes away. Now those were direct dependents. If we want to select direct and indirect dependents, press Ctrl+Shift+Right Square Bracket and now it selects the cells that that cell feeds into and the cells that those cells feed into, kind of like the grandchildren of that cell.
Well, let's go down over here, maybe to the Group 2 total, and if we want to find the direct precedents, press Ctrl+Left Square Bracket and it selects this entire column of numbers because those are the numbers that feed into that formula. Well, let's click on Cell F25 and if we want to find the direct and indirect precedents, press Ctrl+Shift+Left Square Bracket and it finds the children and grandchildren that are feeding into it. And I'm sorry, there really is no other way than with those four whacky shortcuts.
Now, what we could also do with Auditing is we can find errors and there's some error checking that it will help us out. Well, let's say we don't have any data yet for Group 2 and let's just select from B16 all the way down to E21. Now we haven't selected any of the formulas. We're just selecting the data. And just delete. Now when you delete, you notice you get some divide by zero errors over here, you get some divide by zero errors over there, and the reason is that it's trying to create an average. Well, you can't really create an average of the bunch of zeroes because what happens? You divide it by zero and Excel recognizes that as an error.
Well, these really aren't errors in our thinking or errors in logic but according to Excel, it's an error. So when you look at this, you might say well gee, why do I have errors in my worksheet? Where are they coming from? Well, click any of these divide by zero errors and over here click Error Checking > Trace Error and it tells you, oh, okay, the errors coming from these three cells. And then you might decide oh right, because there's no data, or click one of these divide by zero errors and go to Error Checking > Trace Error and it also tells you, oh okay, because there's no data there, we have that error.
And the same thing, we could remove all of the errors and that's gone. So if you have a worksheet that has a lot of data, maybe a lot of complexity to it, you might want to look into the Formula Auditing features because they can really help you make sense of things.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91290 Viewers
80 Video lessons · 138223 Viewers
59 Video lessons · 57048 Viewers
52 Video lessons · 70705 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.