Can you use the MAX function in Excel to find the largest value across all worksheets? You can use a 3-D Reference or a spearing formula. In this episode, I will show you an easier way to create 3D References in Excel.
Also, a trick from Mack Wilk for making your 3D Reference go through a blank sheet at the end.
For more information about the book, MrExcel 2021 Unmasking Excel, visit: MrExcel 2021 – Unmasking Excel
Also, a trick from Mack Wilk for making your 3D Reference go through a blank sheet at the end.
For more information about the book, MrExcel 2021 Unmasking Excel, visit: MrExcel 2021 – Unmasking Excel
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2434. Can you do a MAX across all worksheets?
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
Today, a great question left in a comment on a YouTube video.
Is it possible to do a MAX across the entire workbook instead of just one worksheet? Well, yes, I'm going to show you a great trick from this book, MrExcel 2021, on how to use a 3D reference.
Stick around to the end, because there's some gotchas that you have to plan for. All right.
So when they say that they want to have the MAX from all worksheets I picture kind of a situation like this.
Where you have monthly sheets with maybe sales reps going down the side and products going across the top?
Is there a way to find the Max across all of those sheets instead of just a few of those sheets?
So what we need to do is essentially a Spearing formula, a formula that Spears through all of the worksheets.
I have to tell you the syntax for this is something that I used to struggle with because I can never remember where the apostrophes go and the exclamation point.
And when you have two sheets in the reference, do you need apostrophes around both?
But I finally found a way that's dramatically simplifies this.
It makes it really easy to do these 3D references.
Alright, so I want to find the largest monthly sales by one sales rep, and that means that I'm going to look through J2 to J13 on all of these sheets down here from January through October,.
And rather than try and remember that syntax, what I'm going to do is say equal MAX open parenthesis. And then this is where the trick comes in.
I'm going to click on the January sheet but not select any cells.
Aand then I'm going to Shift-click on the last sheet.
See, and there we are up here in the formula bar.
We have MAX of open paren, apostrophe, the first sheet name, colon, the last sheet name, another apostrophe, and then the exclamation point.
That's the part I would always get wrong.
Now that we have those sheets in the formula Bar, I'll use the mouse and point to the cells that I want to use J2 to J13.
And when we press Enter we come back and find the largest monthly sales by one sales Rep across all of those months is 6752.
Now if we wanted to get the numbers from the bottom so down there in row 14 B14 to I14.
In essence, I'm still going to do the exact same thing, even though it would be pretty easy to copy that formula from above and justice adjust the reference.
I think it's dramatically easier to click on January, shift click on October, then the cells that you want to point to.
Close paren, enter. Let's do it one more time.
Equal MAX. Click on January shift click on October, choose the cells you want to reference.
In this case, we're looking for the largest sale of one product by one sales rep.
Close paren. Right.
Now, I told you there's a “gotcha” here and the problem with the gotcha is that as people add more sheets.
If they add them to the middle, that's going to be fine. But no one adds them to the middle.
They're going to add another sheet to the far right hand side.
They're going to add November at this point. So what I'm going to do?
I'm going to click on October.
Ctrl+Drag to the right to create my November sheet. Rename it.
But then when we go back and look at our formulas are formulas are not picking up November because we added something outside of the range.
So let's undo that. I'm going to right click and remove November.
And show you this great trick that I learned from Mack Wilk.
He was a finalist at ModelOff 10 years ago.
He was the CFO or Controller of Polish Airlines and he had this awesome trick. So he chooses October.
Hits the new sheet icon and then calls it End. And there's nothing on this sheet.
But then when we come back here to our answers.
I am going to adjust all of those to not point to October. But instead to point to End.
So we change Oct to End. Replace all.
All done, we made three replacements. And the numbers didn't change.
But the beautiful thing is now when I take October and Ctrl+Drag it and drop it before the End.
In essence creating November. And now we'll just have some super months here.
We will have someone with a lot of sales. And then I come back so 70927.
Come back to answers.
You see that those did update because of that End worksheet.
I'm not usually adding months before January, although if you were in a situation where you're adding things before January.
Then you could have another worksheet called Start or something like that.
You'll notice here that they've used a shortcut where they don't put the apostrophes in.
That's because neither of these sheets have special characters like spaces or anything like that.
But you need to be aware that if you regularly put spaces or dashes or any other punctuation in your sheet names.
I'll do January 2021 here.
That then the formula needs to have the apostrophes. An apostrophe before the first sheet.
And after the last sheet. Before the exclamation point.
Now you don't have to remember that if you use my trick from the book.
It will work for you automatically.
Well hey, if you like these videos please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
Today, a great question left in a comment on a YouTube video.
Is it possible to do a MAX across the entire workbook instead of just one worksheet? Well, yes, I'm going to show you a great trick from this book, MrExcel 2021, on how to use a 3D reference.
Stick around to the end, because there's some gotchas that you have to plan for. All right.
So when they say that they want to have the MAX from all worksheets I picture kind of a situation like this.
Where you have monthly sheets with maybe sales reps going down the side and products going across the top?
Is there a way to find the Max across all of those sheets instead of just a few of those sheets?
So what we need to do is essentially a Spearing formula, a formula that Spears through all of the worksheets.
I have to tell you the syntax for this is something that I used to struggle with because I can never remember where the apostrophes go and the exclamation point.
And when you have two sheets in the reference, do you need apostrophes around both?
But I finally found a way that's dramatically simplifies this.
It makes it really easy to do these 3D references.
Alright, so I want to find the largest monthly sales by one sales rep, and that means that I'm going to look through J2 to J13 on all of these sheets down here from January through October,.
And rather than try and remember that syntax, what I'm going to do is say equal MAX open parenthesis. And then this is where the trick comes in.
I'm going to click on the January sheet but not select any cells.
Aand then I'm going to Shift-click on the last sheet.
See, and there we are up here in the formula bar.
We have MAX of open paren, apostrophe, the first sheet name, colon, the last sheet name, another apostrophe, and then the exclamation point.
That's the part I would always get wrong.
Now that we have those sheets in the formula Bar, I'll use the mouse and point to the cells that I want to use J2 to J13.
And when we press Enter we come back and find the largest monthly sales by one sales Rep across all of those months is 6752.
Now if we wanted to get the numbers from the bottom so down there in row 14 B14 to I14.
In essence, I'm still going to do the exact same thing, even though it would be pretty easy to copy that formula from above and justice adjust the reference.
I think it's dramatically easier to click on January, shift click on October, then the cells that you want to point to.
Close paren, enter. Let's do it one more time.
Equal MAX. Click on January shift click on October, choose the cells you want to reference.
In this case, we're looking for the largest sale of one product by one sales rep.
Close paren. Right.
Now, I told you there's a “gotcha” here and the problem with the gotcha is that as people add more sheets.
If they add them to the middle, that's going to be fine. But no one adds them to the middle.
They're going to add another sheet to the far right hand side.
They're going to add November at this point. So what I'm going to do?
I'm going to click on October.
Ctrl+Drag to the right to create my November sheet. Rename it.
But then when we go back and look at our formulas are formulas are not picking up November because we added something outside of the range.
So let's undo that. I'm going to right click and remove November.
And show you this great trick that I learned from Mack Wilk.
He was a finalist at ModelOff 10 years ago.
He was the CFO or Controller of Polish Airlines and he had this awesome trick. So he chooses October.
Hits the new sheet icon and then calls it End. And there's nothing on this sheet.
But then when we come back here to our answers.
I am going to adjust all of those to not point to October. But instead to point to End.
So we change Oct to End. Replace all.
All done, we made three replacements. And the numbers didn't change.
But the beautiful thing is now when I take October and Ctrl+Drag it and drop it before the End.
In essence creating November. And now we'll just have some super months here.
We will have someone with a lot of sales. And then I come back so 70927.
Come back to answers.
You see that those did update because of that End worksheet.
I'm not usually adding months before January, although if you were in a situation where you're adding things before January.
Then you could have another worksheet called Start or something like that.
You'll notice here that they've used a shortcut where they don't put the apostrophes in.
That's because neither of these sheets have special characters like spaces or anything like that.
But you need to be aware that if you regularly put spaces or dashes or any other punctuation in your sheet names.
I'll do January 2021 here.
That then the formula needs to have the apostrophes. An apostrophe before the first sheet.
And after the last sheet. Before the exclamation point.
Now you don't have to remember that if you use my trick from the book.
It will work for you automatically.
Well hey, if you like these videos please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
