Make Your Headers Useful

About this Tutorial

Today we're gonna talk about headers. I'm gonna give you three really cool ways to use your headers.

Video Transcript

<div> Hey, sheet shakers. It's Andrew, your friendly neighborhood spreadsheet specialist. Oh, that's a mouthful. Today we're gonna talk about headers . I'm gonna give you three really cool ways to use your headers . This is, this is one of them. It's a big title. Put a big old title up there. Make it really impressive. The other one we'll go through is a search. So I'll, I'll help you make a little search bar so we can go like this and get our search results of data . And then I'll also show you how to do a filter . So this is pretty cool.

You can filter , say like statuses or anything you want. So yeah, let's start. So typically when we have like a set of data and we have a spreadsheet , our very first row, row number one is used for headers , right? Or column names, or I don't know, field names, whatever you wanna call these, right? They're not data themselves.

And they provide sort of categorical.  and probably one of the first things you're gonna do and, and you might have done this already, is you're gonna click here, drag it, and then now this is frozen. You've created a frozen row here, and now we can scroll up and down and our header stays the same. Great. You probably know this already, if you don't there now. Now you know how to very quickly freeze a row. The next thing we probably mostly do, most of us do is highlight that we might use a nice pastel, which I prefer. I like.

Not go too deep, but you might also be like, oh, let's go with the big red. And yeah, this is like one of my pet peeves. I actually have another video about this, how you shouldn't draw attention to the headers , but if you do, if you do, draw attention to the headers . Make them useful. And that's what we're gonna talk about today. So here, one of the fun things you can do is you can make a header , like, like lean into it, like make it. Big and like label these things like label these suckers, right?

You can even make it a little dark mode, like you can go here, you can make it nice, colorful here, like this, and then change this text. Let's do that in white. , that's pretty darn cool. And it only took two, two settings. What I prefer to do when I do this, here's one tiny little tweak to make it a little bit better, is don't use stark white use.  the color that it is, the, the header or the, the highlight color. So we were in this column, so do this. Right. And it looks almost the same, right? But it doesn't, it's not as harsh of a contrast.

It blends the colors a little bit. And also like, it just feels a little, well, a little touch goes a long way. You can up it a little more. You can, you can make it almost. In decipherable. Hmm. This, look, this might be useful in some cases, and this is really good to, let's turn this back to something we can actually read a little bit. You might even, what's, what's the opposite of a yellow? That's, that's cool. What might be useful as well is why, why this is useful as well, is you can take screenshots with this.

Like, if you're sharing a lot of sheets and you want to share amongst your, like maybe put this in a screenshot in a email, like pull people in, you know, spice up that data a little bit. And now for search, so when we add a search, we're gonna have to do one big. Change to your data is you actually have to create a data tab and that this is the tab we will reference . Let me get rid of that red. That's so annoying. We're gonna reference this.

So I like to do this, I like to separate my sheets between like how you're gonna use it and the data that you're going to use. So put it all on a data tab like,  and now we're gonna use the query function . This search thing here. This search is, this is, no, there's, there's nothing out of the ordinary here. I think actually, I just I just merged these to make it all one long search thing search bar, like, cuz that's what we normally think of. Search, right? Is a search bar, not a search cell. Made it a nice search bar up here. That's st.

A stylistic choice, not a.  underlying formula . Need the formula number . All of the magic is right here in in a seven. What we, what I did is I used a cur, a query function . I'll write it out here and I'll explain it as I go. So we go equals query , and then we're going to, we're gonna need two things. We're going need the data that we want to grab or query or find, and then we want to tell it. What do we need to. . So the data is gonna be data A to C.

And then we're gonna type in a comma, and we're going to do our query , which is, if you know a little bit of sql, you'll recognize this if you don't for this particular one, we just want to search in, say the A column, and we want to get everything. So we say select if we want to grab everything, we just use select . Asterisks where A contains. Now here's where we get tr tricky. What we can do is we can do we have to put it in a single quotation mark on, let's say August. Let's, that's like, we'll, we're just gonna test this out right now.

We're gonna end quotation marks and see if we get that. So we get our result, right? So that's August. And we can switch out. Let's say we want to search for Omar. There we got Omar. So this. But we want to be able to replace this name up here with the search, what we search for. So here's how we do that. We, we delete the name Omar. We're gonna end the quotation mark and we're gonna start it again. We're gonna put a, an and what was our cell cell? C3 is where we're gonna have the the query . Then we're gonna do another ampersand.

So what we're doing here is we're saying select everything. The asterisk, where a column A in our data contains this c3, whatever, we type in c3, but we have to wrap it in those single quotes. So we actually have to end the double quotes, put an and or an ampersand, put the query , another ampersand. Then add of the qu double quotes again, then add that last single quotation mark. This is a little bit tricky, but once you get this, you can, you can do this many, many, many, many, many times.

So now we have nothing cause we're not searching for anything. Let's say, oh, and here's one thing, one challenge you're gonna sh you're gonna have is that this header is in here because we're looking for the entire range of A to C. So if we can just do a two, add that in there, and now we don't have the head. , so let's test it out. Mabel, both of them work exactly perfectly. So that's how you do a search. We're just using the query function , querying the data and saying, select everything where A contains vault.

You can even say, let's say we just want B, we just want first names. Boom, we got it. But it'll actually be searching in the A column so we can say con. Okay. Same, same result, but we say we're asking for B instead of everything. Cool. So you can check that out. I'll delete that one. So this one is the only one that exists. Now we're gonna go to filtering. Sometimes when we're.  dealing with data .

We have sort of a grouping or a set of data in one of the columns where we want to group by or we want only wanna see something. Now there is a filter function built into Google Sheets over here, up into the right, and it is fantastic. There are two problems with this. One is not everybody knows it exists.

Not everybody can use it, but also when you create a new filter view , one of my stumbling blocks and challenges that I've had with this is that you need to grab, you have to filter the entire data and if say, I filtered only full name and first name, if I filter it, edit it, and then unfilter it, the edited. Cells are in the wrong place. Like I didn't get everything. It's like one of those weird things that like you have to remember to grab everything in a filter view in order to edit. I, that has screwed me in the past many times.

And so it, it almost is like a I have a emotional scar against using filter views. So I do like to build my own filters. And, and also this is another thing that's really cool is once you can filter . , you can create multiple tabs that are filter views of the same data . And again, you can absolutely do this in a filter view , but in order for someone else to work on the sheet and you to see the filter view , everyone is seeing this filter view . So that's a little bit of a pet peeve and a really big challenge.

If you are working on the same sheet as somebody else and they're in filter view and you're not like you automatically are in filter view , that's a. Hmm. Again, it might not be a bug, it might be a feature of Google Sheets, but here's how I build filters. So instead of a search bar up here, I've put a dropdown and I've included all of the possible possibilities that the status column is. I just have this status column in data . We're using this again, and I just have like responded. Need follow up reported. , this could be anything.

It could be people who are responsible for these tasks. It could be like, this is like a sales kind of sales leads could be anything that you want to filter or group by . And so what we're doing is we have a simple filter function and we're saying, take.  the element in C3 and filter and only give me all of the responses that filter out that for d in the data . So let me do that. So here we'll go filter and what is the range or RA range is the data sheet A to D in this case? And what's the condition? Well, the condition is that data, D to D equals C3 here on. Sheet. That's it.

And that should get us all of our answers . Oh, it automatically en, it automatically en entered a few columns when we needed it. But yeah, this is it. That's it. So now once you change this, it automatically changes the view you see. . So you can see here, obviously you can't edit these. This is just for a view, not a edit. One of the fun things about this as well is if you do try to edit, you cannot. And if you try to add, it gives you an error . It literally says Array result was not expanded because it would overwrite data in G 11 once.

If you have an office of people that are doing this constantly, they do it. , show them what this reference is. They see, oh, H 11, they can literally go point it out, delete it themselves. They understand one, once you explain it once, you never have to explain this again. I had an office of like 30 people. I had to explain it 30 times. I didn't have to explain it 60 times. So once everybody knew, okay, these filter functions are just to view, very, very easy to keep this data in, in, in check because everyone edits the data in the same. .

So just to review three things you can do in headers that are really useful. It's one, make 'em really big and, and shiny, and really lean into the fact that people are gonna look at this header . Put like a title there. You know, sometimes you put in screenshots, you include the title of the spreadsheet . Just go ahead, put it right there. Make it nice, like nice and fun. If you separate your data and your sheet, you can add a search function , a search. which is really useful if you're searching for through hundreds or thousands of data .

You can also group by or filter by over here in the header . Making headers useful, increases the usefulness of sheets like 10 X here. Thanks for watching Bite.</div>