Harsinchh asks if there is a way to highlight all formula cells and the precedents of those formulas. There are a lot of techniques discussed in this video:
Show Formulas in Excel
Select all Formulas in Excel
Go To Special Precedents in Excel
Direct versus All Precedents in Excel
VBA to select formulas in Excel
VBA to select precedents in Excel
VBA to union two ranges in Excel
Table of Contents
(0:00) Excel: Select Formulas & the precedents
(0:25) Excel Show Formulas
(0:55) Selecting all Excel Formulas
(1:16) Selecting Formula Precedents with Go To Special
(1:40) All Precedents versus Direct Precedents
(2:52) Four-step is a kludge
(3:08) VBA Macro to select formulas and precedents
(4:49) Personal Macro Workbook
Show Formulas in Excel
Select all Formulas in Excel
Go To Special Precedents in Excel
Direct versus All Precedents in Excel
VBA to select formulas in Excel
VBA to select precedents in Excel
VBA to union two ranges in Excel
Table of Contents
(0:00) Excel: Select Formulas & the precedents
(0:25) Excel Show Formulas
(0:55) Selecting all Excel Formulas
(1:16) Selecting Formula Precedents with Go To Special
(1:40) All Precedents versus Direct Precedents
(2:52) Four-step is a kludge
(3:08) VBA Macro to select formulas and precedents
(4:49) Personal Macro Workbook
Transcript of the video:
Here's a tool that feels like it should be illegal to know.
Question from Harsinchh. Check this out.
He has three constants here and a formula that's adding up those three constants.
He wants to be able to select the formula and the cells that the formula's referring to.
All right.
Boy, we got a lot of tools here to talk about.
On the right side of the Formulas tab, we have the Show Formulas mode, which is Control and the grave accent.
Most people think it's Control and tilde, but that's because in the US the tilde and the grave accent are on the same key.
So if you want to see where all the formulas are, Control and the grave accent. We have one formula here.
That formula's referring to four cells. I'll press Control and the grave accent again.
All right, so to select the formula cells, that's easy.
And there's two different ways to do it.
On the right-hand side of the Home tab under Find & Select, you can choose Formulas or you can choose Go To Special and then choose Formulas.
It's actually a lot easier just to choose Formulas.
So Home, Find & Select, Formulas. And I've selected the formula cells.
Now he wants to highlight them. So let's select some sort of highlight.
And then from this cell, check this out.
Home, Find & Select, Go To Special, choose Precedents, click OK.
And it chooses the four cells that one formula's referring to, and I can select the same color for those.
All right. Now that's a simple one.
One formula, no multi-level precedents at all, all right?
But sometimes we have precedents. Those are called direct precedents.
And then precedents of the precedents. Here's an example.
So formulas, I want to trace the precedents.
And you see there's two cells being referred to there, B9 and C12.
But C12 also has precedents, and C14 has a precedent.
So the question is, are we looking for just those two cells or all four cells?
So this time I'm going to select the range where I want to find the formulas, Home, Find & Select, Formulas.
All right, so we have four of those.
Now the big question is, will Go To Special, Precedents work from multiple cells? And the great answer is, yes, it does.
So Control+G Alt+S is my shortcut to get to Go To Special.
And I want to choose the Precedents cells Direct only, which should pick up this number three right here. And it does.
So I can apply it, the formatting there.
The other way to go is Control+G, Alt+S, Precedents.
And we want all levels, which should also pick up the two and the one, click OK. And it does that.
All right.
Now, this is a kind of kludge method, to have to choose Home, Find & Select, Formulas, Home, Find & Select, Precedents, and which kind of precedents.
And it seemed to me that it'd be really short and easy VBA.
I define two ranges, the formula range, the precedent range.
The formula range, I just start from the selection special cells, ask for all the formulas, and that 23 is the code for all formula types.
And then the precedent range is going to be start from the formula range and ask for the direct precedent.
And then a great bit of code that I actually had to look up because I hardly ever use it, but it is so useful. Application.Union of the formula range and the precedent range .Select.
So I have two different macros here, one for just direct precedents.
And then the only change down here on all precedents is instead of asking for direct precedents, we ask for range formula .Precedents.
All right? So here's how I envision this working.
I want to see all of the formulas and their direct precedent from this range.
So I press that button and it selects all the formulas and the precedents that I can apply whatever color I want to do.
Or if I want to see all of the formulas and all of the precedents, then this macro here will add in those additional items.
Now, of course, VBA is only going to work in Excel for Windows or Excel for Mac.
It's not going to be in Excel for online, Excel for the iPad, Excel for your Commodore 64.
If you're in any of those, you're going to have to switch over to type script.
And that is beyond the scope of this video. But I'm pretty happy.
I'm pretty happy with this item.
I'm not sure why Harsinchh needs to select the items and their precedents, but there's a nice little four step process that won't be bad.
And then, if you put these macros in your personal macro workbook, it'll be even easier.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Question from Harsinchh. Check this out.
He has three constants here and a formula that's adding up those three constants.
He wants to be able to select the formula and the cells that the formula's referring to.
All right.
Boy, we got a lot of tools here to talk about.
On the right side of the Formulas tab, we have the Show Formulas mode, which is Control and the grave accent.
Most people think it's Control and tilde, but that's because in the US the tilde and the grave accent are on the same key.
So if you want to see where all the formulas are, Control and the grave accent. We have one formula here.
That formula's referring to four cells. I'll press Control and the grave accent again.
All right, so to select the formula cells, that's easy.
And there's two different ways to do it.
On the right-hand side of the Home tab under Find & Select, you can choose Formulas or you can choose Go To Special and then choose Formulas.
It's actually a lot easier just to choose Formulas.
So Home, Find & Select, Formulas. And I've selected the formula cells.
Now he wants to highlight them. So let's select some sort of highlight.
And then from this cell, check this out.
Home, Find & Select, Go To Special, choose Precedents, click OK.
And it chooses the four cells that one formula's referring to, and I can select the same color for those.
All right. Now that's a simple one.
One formula, no multi-level precedents at all, all right?
But sometimes we have precedents. Those are called direct precedents.
And then precedents of the precedents. Here's an example.
So formulas, I want to trace the precedents.
And you see there's two cells being referred to there, B9 and C12.
But C12 also has precedents, and C14 has a precedent.
So the question is, are we looking for just those two cells or all four cells?
So this time I'm going to select the range where I want to find the formulas, Home, Find & Select, Formulas.
All right, so we have four of those.
Now the big question is, will Go To Special, Precedents work from multiple cells? And the great answer is, yes, it does.
So Control+G Alt+S is my shortcut to get to Go To Special.
And I want to choose the Precedents cells Direct only, which should pick up this number three right here. And it does.
So I can apply it, the formatting there.
The other way to go is Control+G, Alt+S, Precedents.
And we want all levels, which should also pick up the two and the one, click OK. And it does that.
All right.
Now, this is a kind of kludge method, to have to choose Home, Find & Select, Formulas, Home, Find & Select, Precedents, and which kind of precedents.
And it seemed to me that it'd be really short and easy VBA.
I define two ranges, the formula range, the precedent range.
The formula range, I just start from the selection special cells, ask for all the formulas, and that 23 is the code for all formula types.
And then the precedent range is going to be start from the formula range and ask for the direct precedent.
And then a great bit of code that I actually had to look up because I hardly ever use it, but it is so useful. Application.Union of the formula range and the precedent range .Select.
So I have two different macros here, one for just direct precedents.
And then the only change down here on all precedents is instead of asking for direct precedents, we ask for range formula .Precedents.
All right? So here's how I envision this working.
I want to see all of the formulas and their direct precedent from this range.
So I press that button and it selects all the formulas and the precedents that I can apply whatever color I want to do.
Or if I want to see all of the formulas and all of the precedents, then this macro here will add in those additional items.
Now, of course, VBA is only going to work in Excel for Windows or Excel for Mac.
It's not going to be in Excel for online, Excel for the iPad, Excel for your Commodore 64.
If you're in any of those, you're going to have to switch over to type script.
And that is beyond the scope of this video. But I'm pretty happy.
I'm pretty happy with this item.
I'm not sure why Harsinchh needs to select the items and their precedents, but there's a nice little four step process that won't be bad.
And then, if you put these macros in your personal macro workbook, it'll be even easier.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
