It is a real pain to try to trace precedents that are on other worksheets or in other workbooks. Episode 400 shows some formula auditing tricks, including a trick to see all of the precedents on other worksheets. This is our 300th episode!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here we are, it's episode 400, and we started at episode 101 so this is actually my 300th podcast.
Want to thank you for hanging around watching, you know, the podcast got started over a conversation with Leo Laporte, when I was filming, call for help.
I also want to thank Amber MacArthur and Mike Lazazzera who do the CommandN podcast, for helping me out early on.
And then also Laura White, Laura has been around for all 300 podcasts and does all the post-production, make sure that all of the things that I screw up most of the time get taken out.
So want to thank everyone, and thank you for watching, thanks to everyone who's called in, with great calls.
Now every 75 episodes, I always point out how I want to be overly commercial here, that there is a printed episode guide “Learn Excel from MrExcel”!
You can get this book, it has almost every topic that we've talked about, and talks about it in complete detail.
I know, sometimes it's frustrating with the podcast, you say “I know we talked about something 30-40 episodes ago, but I can't remember what it is.” You get the book, great way to overcome that.
Now, just for you, for watching this 300th episode, we have a special URL, mrexcel.com/podcast300, go there special deal on the book.
And also once you buy that book at the special price, you get some other books at incredibly low price, so check that out.
Please don't share that, it's only for the people, the regulars who are watching the podcast.
So quick tip here today, talking about when you have a spreadsheet that someone sends to you, and you're trying to figure out how the spreadsheet works, a couple of cool tricks.
The first one is: if you need to see all the formulas on the spreadsheet, you hold down the Ctrl key and hit the tilde if you're on a US keyboard.
Or if you're in a European keyboard, you actually need the backwards accent, it's called a grave, and it will show you all the formulas.
Now the cool thing, I'm going to select a large formula here and go to Tools, Formula Auditing, and say Trace Precedents.
A precedent is all the cells that make up this cell.
Now, usually, we have a problem, if there's one cell that's on another worksheet, the Trace Precedents doesn't work.
It shows you the arrows for the current worksheet, but it doesn't show you the other cells on the other worksheets.
Recently learned something at one of my power Excel seminars, someone showed me that in the lower-right hand corner of this icon, there's a small round dot, and it's difficult to actually master it.
But if you can double-click on that dot in the lower-right hand corner, Excel will bring up a list of all of the other precedents that are on other worksheets, a very cool way to see the precedents on the other worksheets.
Now of course, to get rid of the precedent arrows, you can always go back to Tools, Formula Auditing, Remove All Arrows.
I think I've talked about this trick before, but the thing that's really new is being able to find the precedents on the other worksheets.
That's one of the greatest frustrations when you're trying to use Trace Precedents, you can't see the cells on the other worksheets.
Just double-click that small dot at the lower right hand corner of the icon.
Hey, thanks for stopping by, appreciate whether you're new to the podcast or have been around for all 300, really appreciate you watching.
We'll see you tomorrow for another podcast from MrExcel!
Here we are, it's episode 400, and we started at episode 101 so this is actually my 300th podcast.
Want to thank you for hanging around watching, you know, the podcast got started over a conversation with Leo Laporte, when I was filming, call for help.
I also want to thank Amber MacArthur and Mike Lazazzera who do the CommandN podcast, for helping me out early on.
And then also Laura White, Laura has been around for all 300 podcasts and does all the post-production, make sure that all of the things that I screw up most of the time get taken out.
So want to thank everyone, and thank you for watching, thanks to everyone who's called in, with great calls.
Now every 75 episodes, I always point out how I want to be overly commercial here, that there is a printed episode guide “Learn Excel from MrExcel”!
You can get this book, it has almost every topic that we've talked about, and talks about it in complete detail.
I know, sometimes it's frustrating with the podcast, you say “I know we talked about something 30-40 episodes ago, but I can't remember what it is.” You get the book, great way to overcome that.
Now, just for you, for watching this 300th episode, we have a special URL, mrexcel.com/podcast300, go there special deal on the book.
And also once you buy that book at the special price, you get some other books at incredibly low price, so check that out.
Please don't share that, it's only for the people, the regulars who are watching the podcast.
So quick tip here today, talking about when you have a spreadsheet that someone sends to you, and you're trying to figure out how the spreadsheet works, a couple of cool tricks.
The first one is: if you need to see all the formulas on the spreadsheet, you hold down the Ctrl key and hit the tilde if you're on a US keyboard.
Or if you're in a European keyboard, you actually need the backwards accent, it's called a grave, and it will show you all the formulas.
Now the cool thing, I'm going to select a large formula here and go to Tools, Formula Auditing, and say Trace Precedents.
A precedent is all the cells that make up this cell.
Now, usually, we have a problem, if there's one cell that's on another worksheet, the Trace Precedents doesn't work.
It shows you the arrows for the current worksheet, but it doesn't show you the other cells on the other worksheets.
Recently learned something at one of my power Excel seminars, someone showed me that in the lower-right hand corner of this icon, there's a small round dot, and it's difficult to actually master it.
But if you can double-click on that dot in the lower-right hand corner, Excel will bring up a list of all of the other precedents that are on other worksheets, a very cool way to see the precedents on the other worksheets.
Now of course, to get rid of the precedent arrows, you can always go back to Tools, Formula Auditing, Remove All Arrows.
I think I've talked about this trick before, but the thing that's really new is being able to find the precedents on the other worksheets.
That's one of the greatest frustrations when you're trying to use Trace Precedents, you can't see the cells on the other worksheets.
Just double-click that small dot at the lower right hand corner of the icon.
Hey, thanks for stopping by, appreciate whether you're new to the podcast or have been around for all 300, really appreciate you watching.
We'll see you tomorrow for another podcast from MrExcel!
