Create a Summary Tab with Min and Max

Intermediate

About this Tutorial

Free tutorial for finding Minimum and Maximum data points.

Featured Formulas

INDEX() MAX() MIN()

Video Transcript

<div> Hello, welcome to a free tutorial about Google Sheets. This is better Sheets. This is to help you make a better summary tab, and we're gonna talk about a few formulas. I'm not really gonna go through the aesthetics of this. I did that in another video and I do a lot more about aesthetics behind the paywall. In this free one, we're gonna talk about four formulas that I think are really important.  when speaking about a summary tab why summary tabs are important is because a lot of the work we do. With data and in Google Sheets is sort of figuring out some story.

But truly we wanna know as humans, we really are excited about the extremes, right? The most and the least. We're, we're excited and we want to talk about, you know, what makes something the most and what makes 'em the least. This data is population. Countries around the world in 2005 and 2019. Actually, it's expands, spans this time period, but I just picked these two to think maybe this was a nice project. If you wanted to find out, you know, which countries had the least growth and the most growth in these time periods and what areas as well.

So there were two sections of this . And also keep in mind I have a lot of interesting theories on how to keep data in different tabs sort of working together in this case just to share with you a little bit, I'm not gonna go too deep, but like, I like to keep all the original data in one tab and not touch it, but actually go into a different other, create other tabs where we work with the data that way. If you want to replicate this in this kind. Formulas again and again, you don't have to mess with the formulas. You have to just mess with the underlying data .

And you can create.  like updates to these kinds of things. And I like to keep the tabs pretty simple. Not naming them really crazy stuff. And then also I like creating summary tabs because a lot of the work that I do is with executives who really just wanna have the answers to their questions. They don't wanna see all the work, but if they wanna see the work, it's available to them in a tab called work. And yeah, so why I like minimum maximum. And, and in this case, the two formulas I wanna start off with are min and max. There is, here, let me show you this work table .

So this work table I created, I just grabbed each area. The total population it had in 2005 and 2019 took a delta of it. Just simple subtraction. And then I also took that number and divided it by the 2015 2005 number to figure out what percentage growth did they have between 2005 and 2019. And so this is ranked as the top 32, 30. Rows are areas. And then from Paul and Afghanistan down, we have all countries.

So so we have two sets of information here, and I separated them but kept them color coded to make it very clear, and I'm not gonna go into two aesthetics too much, but I wanted to make it very clear that these do have a relationship, even though it's the. and the country here, they're labeled differently. And I wanted to keep this consistent in case an executive wanted to copy and paste this into an email or take a screenshot of this that these two felt and looked similar is on purpose. Summary tabs aren't meant to sort of be garish.

They're they're meant to tell a story, right? So we have minimum on the top at maximum on the bottom. Obviously, you may wanna switch these. Maximum on top. But what's really good is that in this one formula , and I'll show it to you, equals max. So maximum is just m a x and you just give it a range and it'll find you the maximum. That's not the only way that it's really helpful. So how did I also get this? So if I found the maximum with this formula , how did I find another? Sort of the country or the area that it, that it represented. Right?

And this, I did it with two separate formulas to show you that you can do this in multiple ways. One is index match with which I go into great detail, but in the paywall.  At Better Sheets because it's one of the most fascinating and amazing formulas I've ever seen. And it's a combination of in the index formula and the match formula , and I go into great detail. Essentially what I'm using the max here for is I'm matching what I'm asking, what row is this on?

So I could have absolutely done this right? Just taken D six and said, okay in the match formula I'm asking, Of, of the ca of this column. Where is that? Which row is it? That's 55.62. I have to be very confident that there's only one. So you should absolutely do a gut check of these, inf this information. So you should do this on your own. Just look. Okay. One of one. Do a, a, command F. Just double check it. . And why that is, is because Match is gonna give you the first match . And I put a zero here to say, okay, give it me a exact match . Not that very first one.

It's not an order. But give me an exact match . And when I get that match , it gives me a number five. What that means is that it's on the fifth row of this  of the range that I've asked it to. So it's really, should be row nine. Let's see. Oh, must be oh, row eight. Yeah. So. Row one is four, and then row the fifth row of that will be eight. So 4, 5, 6, 7  doing the math on your head.

And so what is important when you do index match as well is to make sure that the range of your index is exactly the same as your match . So that this is getting the fifth.  The fifth row of this range , and I want to get what is in column A. So all I have to know is these two columns. I don't have to know what's in between. I don't have to do anything to it. I just can get what is in the fifth row of this range . And that's what index is for the middle. So it takes three arguments of, if you see this, this is the same. Thing, I just programmatically found this number, right?

So the fifth row of this range , and this one is a column, so it, I could have done it that, and I get the same exact answer. See it as doing a match and that's index match . I go way into more detail in the page, but I think it's a really cool thing to know and really it looks really complicated, but once you really understand that the bare, like the minimum amount of information here, you really can use use it too. Great effect. Down here though, what I did is filter because. , there are absolutely times and, and in this case, these numbers here are very exact.

So I did minimum here maximum for the top gainer. Well, what's interesting about filter is that you will get multiple answers if there are multiple answers , right? Index will give you the top one. Index match will be the top one. The filter will give them all to you. So if. , if I was looking for like the rank of things and there were ties, right? I would get all of the same rank.

And so, but here, it's funny, I'm getting the same answer that I would get with Index Match , but I'm using this filter formula , and again, I'm just showing you this because I think it's interesting that two formulas can get the same result. And it matters a little bit just to know sort of the edge cases. But what I'm asking is,  within this range of a right in this column A, where does column F equal this maximum? So I could have taken this maximum, maybe this might look a little easier to you like that.

Like just find in column A, filter out everything that does not have this same column F number . That's what filter is. Right. So knowing that we will get multiple answers , we can do indexes, we can say like, you know, get every here, let me do one change this on India. So I don't, maybe I don't wanna have the top gainer, but I want to have like, everything that was greater than 1 billion 1, 1, 2, 3, 1, 2, 3. Every, every country they've gained over 1 million. And there they are. So that a lot of countries, right? Let's do 5 million, right? That's probably still a lot.

Two 10 million. Yeah, that should be not as many there. . And so that wouldn't look very nice on a summary. All those. So we went with you know, just a very. Top one India, which gained 218 million. So, you know, you might wanna make some gut checks when you're creating a summary tab, you know what fits nicely, aesthetically.

Again, there's another video, a free tutorial you can watch how to do, sort of figure out, you know, give it some space around here, put some colors on, and, and really change the text so you have hierarchy to really understand so that the data tells you. one story and that the hierarchy tells you that same exact story. It helps tell that story of, okay, this is red, these are green, this is bad, this is good, this lost population, this gain population. Maybe that's good or bad.  And watch in another tutorial, a free one.

I talk about security, so like sometimes when you're sending sort of these summary tabs and this work and it's all within the same sheet, you may want people to be able to edit this or you may not want them to edit it, or you may want to choose what they can edit. And I share some really interesting security tips with you just to keep your data secure, how to keep the information you get secure but also how to just like, Tell your version history , like to see what changed and maybe who changed something just in case that does happen. So check that out. Thanks for watching this.

Really quick introduction to these formulas. Minimum maximum index , match and filter . Thanks.</div>