Academy ↓
Hey there stranger!
Sign up to get access.
Job Application Tracker Template | From TheLandOfRandom - Sheet Improvement!
About this Tutorial
Video Transcript
He's building this job tracker, job application tracker, and he's made it into a template. So this is actually available for anyone to use if you are looking for a job. If you're trying to get a job and you're trying to track those jobs you can go and get this from his newsletter. Go to the land of ran the land of random.dot com and go and grab it.
If you cannot find it I should be putting a link somewhere in the. Description. This video will eventually go on YouTube, so hello YouTubers. But just to let you know, this is a better Sheets video. It's made by me. I'm Andrew. I run better sheets and people are paying for lifetime access right now.
I don't know how long this will last, but you get access to over a hundred videos where I teach you how to do better Google Sheets. Typically in these video. I will go, there'll be five to 10, 20 minutes, and I'll go through one specific thing, one challenge, get you over that hump, get you through that challenge, get you through some roadblocks.
I share with you my experience of working in Google Sheets all the time and how I've made it better, made myself faster, made myself. Wealthier by gaining experience in Google Sheets and then being able to use that in, in, in real life situations where you might not think you could. I've built SaaS in Google Sheets.
I've built I've built my own trackers, crm. In Google Sheets, all kinds of stuff. And just wanted to let you know all about that. We're not here to pitch you too much because most of the people actually watching this are probably gonna watch it as a member members get videos much more in advance than you're getting.
All right. I have talked far too long. Let's work, let's start getting into this. We're gonna make this job application tracker so much better. We're gonna add a couple of cool things to it. I have a list here to my right, but I'm gonna go through that list with you. But first, let's just make a copy and then I'll share this with David before before you get access to it.
I'll share it with him. Better sheets version. Of, there we go. What's really cool about David and the land of random is like I've discovered a really a few awesome things through there. I think I've found waves, I think I've found like color pickers or color palette, things like it's every single week.
It's really different stuff. So I'm really excited to help David here with this tracker. I think he's doing a wonderful job. I think he. To, I hope I don't get this wrong, but I think he is literally looking for a job right now. And so in, in indie hacker mode and helping others, he wanted to go through this process himself of finding a job, tracking his jobs, and he wanted to help others by giving you the template to track your job as well.
If you're not so familiar with how to do. I am jumping in and saying let me help you so much more. Spruce this up. Make it better. We're gonna make it look better. We're gonna make it perform better. We're gonna make it perform some really cool things. So first, let me make a notes. I'm gonna write some notes here.
Okay. So what are we gonna do? First off, I noticed that he has. Let's talk about it a little bit. He has a bunch of text up here. He has some instructions like to delete this and it gets in the way. It adds a friction to using it. What I prefer is having some type of start here or read me page and then have some settings page if you wanna change anything, like here he says, link your cover letter here, and it doesn't really have instructions on what to do or why to do it.
So we wanna have a place where we can give instructions of Hey, what is this? Who made this? We wanna make it in the. What do you call it in the style of the land of random. So we're gonna go grab some images. We're gonna go grab some colors. We're gonna make a settings page because there is one thing that you, as like a user of this will want to do for a cover letter.
We're gonna make it also really easy to make coverlet multiple cover letters so you can do more with less. You can create one temp. Of a cover letter and then duplicate it for each job that you are applying to. We're gonna create a pipeline view, I think, because he has a couple of really cool things like yes, applied here and no, not yet.
And then application date, waiting for response, and all this other stuff. But I think we can put, we can combine all that into a status column. And obviously you can make this your own if you want to. I'll try to explain how you can extend it and change it once I am done with it. And if we have time, we are going to also do some conditional formatting on on the status.
Like, when should I follow up? So maybe you take this application date and add. Thanks. So let me rewrite that down. Let's let's insert a few check boxes. Not that many. Let's insert a few check boxes and get through them. So first I'm gonna add a read me, I'm gonna add a read me page. I'm gonna add a settings page.
Then we're going to, let's do the letter. Cover letter template first, and then we're gonna, we're gonna create it in Google's docs, create in Google Docs,
and then we're gonna create a copy. So I'm gonna sneeze. I'm gonna pause and sneeze and come. Okay. Trying to, Save your ears there. So what we're gonna do is we're gonna create a template in Google Docs, and then I'm gonna add, I'm gonna do slash copy at the end, and you'll see what that does. You can create basically two clicks.
You'll be able to create a copy based on your own template. It'll be really cool. What else did I say? We'll create and yeah, we'll do that first and then we'll create a status column with dropdown. We'll what else? We'll create a pipeline view from that pipe line view, maybe with some stats.
Add stats to pipeline if we have time. And then one more thing, if we get to it, if we have time to get to it, we will. Conditional formatting to the application date. And this is important because we wanna make this sheet active. I'm gonna just delete a whole bunch of stuff. I love doing this, making these sheets much cleaner and easier by just doing that.
We're gonna view grid lines. And one more thing. I do not like aerial. I do not like it at all. Let's change it to consoles. Let's make it bigger as well. Double it. There we go. That looks nice. Alright, so that's our that's what we're doing today. Let's see how long this is gonna take. I hope this is only gonna take an hour.
This should be pretty simple. If you don't if I don't explain too much of this all of these things. That I can see here I have gone over in like a specific detail, like five to 10 minute video showing you exactly how to do it. Actually this pipeline one that I'm gonna do, I've done that a couple times.
It's a really awesome way to make a quick crm. If you are create doing a project and you have to outreach to say 50 people. And you wanna know where they are and how that's going. And you don't wanna be sorting things around, creating pipeline, almost like a Trello board out of using a transpose function we'll use it's going to be cool.
But first let's make a read me page to give some instructions. We wanna move all of this stuff up here into a read me page. Let's do that. And if you have a lot of develop and you'll know your audience better. If you're creating a template for a group of people, this might actually be start here or introduction, intro page.
But for for instance, if you have developers read me is like literally what they'll know. All right, I'm gonna move this around so I can get to it. So what is the Read me page going to entail? Let's just copy paste this stuff and then we'll move this all the way to the left. We will eventually move this to the left.
Oh, that's frustrating.
All right, we finally got that. Oh my God, that is okay. We might not be moving that anywhere. We'll copy paste this here. Let's make that bigger. Hi, David, from, let's move this up here. We can split this all up and make it a nice hi. De make a copy. Go to file, make a copy. So here we're gonna make a little bit of a change here.
We're gonna change this to a button and it's gonna say make a. We are going to delete most of these rows and sell and columns cuz we don't need these, we don't need all this stuff. So we can do that. This is gonna be a button and what it's going to do is take this And it's gonna be a command K to add a link, but at the end, instead of edit, we're gonna write copy.
We're gonna apply that. We're gonna command you. I'm gonna change the, oh, we need to get colors. Let's get colors. So I have his website here. Or actually this is a sub. So go to the land of random doub.com to subscribe to his newsletter. Oh here's his newsletter. Mushroom Buildings. Rick, roll in Zoom.
Oh. Sounds awesome. 56 marketing tools. Google's missed opportunity. Rollable smartphone. Wow. Yeah, totally random stuff. 24 hour meat vending machine. That sounds awesome. Okay, so this is the line of random, he has a little image here and it cannot tell if you can see this, but yeah, I was looking for other stuff oh yeah, he has his, So let's, this is beige, so he doesn't actually have white background.
I'm gonna click on this image. I'm gonna download this image and we're gonna use the, yes, let's call it land. I think I actually have it already. The land of random Twitter. Okay, we're gonna go grab, get these colors. So what is it? I always have to, I always have to Google this color. Picker online. There it is.
Can literally just do color picker from image. Google that. And we're gonna upload an image. Just gonna grab, we can do this also with Photoshop, I think. But let's just do it online here. Where is our image? There it is. Yeah.
We're gonna wait a second for that to come. Okay. We want this beige. We definitely want this beige. So let's grab that. Let's put it on our notes page. Okay. Let's put it on our notes up here. Beige. Oops. That's not how you spell beige. I don't know if that's how you spell beige. That's what we want. And then we want this black, so there's two blacks here, a grayish.
And then a blacker black here. Actually, three blacks here. Let's see what is this a gray here? I like this gray. Ooh, yeah. That's gonna be nice. Maybe this should be all the text. Black. Let's look some more. He has these green, yellow, red. But here's one thing I did do a little bit of research before.
So he has this image everywhere, right? On his Twitter, on his Instagram. So if you are on any of, oh my God, $800 sushi pox, vapor wave sounds, what is that? Wow. Oh my God. His, the Instagram is awesome, right? So he has this interesting fun and I don't know what font that is. I'm not gonna spend some time right now on it, but he has this very dark black background here.
Let's look at this. He has a nice. Gradient here. This is website, the land of random.com, which I don't think he wants you to go to. I don't, he doesn't have that really linked anywhere, but check this out. He has this purple, he has this pink or purple-ish, and that's pretty funky. He's chosen that for some reason.
So let's grab that as well. Let's see if this is the same as this. I'm just taking a screenshot of it and I'm gonna throw it into the color picker as. I wanna see if this is the same color.
So he ha he likes this black and pink and I like black and pink a lot. I think it's a really cool, I think it's a really cool co cool color combination. It makes it look really fun. So this is pink.
And then let's go grab the other pink and just see if it is the same color. Pink Now. I already forgot the color. Oh, looks different. Let's see. We're going to use also a different black than dark black. So those are slightly different. Pinkish. Okay, let me see what these all look like. Let's do this.
We're gonna. And solus bold. We're gonna make these a little bit bigger. We're gonna color them. The color of the color. So this beige custom, what this does, two things. One, we get to see all these colors right here, right now, but also as we do this custom, it saves these custom ones. Oh, I need to change this this to white.
And then, we'll, we have these very quickly available to us when we want to use them in different places. But this is gonna be confusing because we're gonna have these two pinks. Let me see how this looks. I like these two. Against each other. Instead of that, like really pink and really black, we'll have a lesser, more purpleish and a grayish charcoal ish.
That might be pretty cool with this beige, let's let's use this beige everywhere for now. We are going to do this just to get it, our eyes accustomed to it. I love beige because it, as we work in Google Sheets, if we are in here, like I've been in Google. Hours a day on for years and you start to have, if you, if your eyes get accustomed to the whitest white and the blackest black, it's a very high contrast environment and you end up getting used to that and there's no turning back.
But if we use beige on our sheets, then it's less, if we use a beige background and a less black like a 4 44 we use it's a much less contrast. So much nicer on our eyes. Cool. So let's change this text as well. We want this charcoal text. See, like now we are rocking in a rolling. We're gonna change this to that purple-ish.
We're gonna change the let's use that gray. Let's, yeah. We got, we have, we would love one more color, but let's use this for now. Let's use white there. Okay. We are rocking and a rolling. We might want to change this up, this button up a little bit, but we wanna make this a big button, really different than everything else.
We want this as, let's go text wrapping. Cool. We can also, what was the font we were using here? Oswald's. I like Oswald a lot. I like it for headers. It makes it, you can keep a lot of stuff. Let's use Oswald up here. Definitely. Where are you Oswald? Come on, Oswald. There you are. Let's make it much bigger.
Hi, David from The Land of Random here, make a copy. Good luck with your job search. If you're interested to join, let's put this right. And we will move this one. I'm gonna just like that and we will under undo the grid lines. Let's wrap this. Let's make it a little bit bigger. Actually. We wanna make it a different text as well.
Let's do, I like, I always like Oswald and comforta. Yeah, that looks. Let's make this, we're gonna do something really fun right now, right at this moment. We are gonna use that other pink. I'm gonna use this and this, and it's gonna not take too much time out of our hands, but let's do resize and we're gonna do 20, and then we want two.
These, I'm not telling you what we're doing, just. But you're gonna be surprised. We want this to be as 20 as well. Same. Same. So we were trying these two rows up above and under and next to them is gonna be the same. We want this here, and here. And I bet you don't know why yet. I'm gonna merge all this.
You probably are thinking, I am crazy right now for merging all this, but check this out. Watch what happens when I take this one and this one. And I'm gonna just change a highlight to that other pink. Wow. Now that looks like a drop shadow. Doesn't that? So if you thought I was going crazy right there with some roses this makes it look really cool.
It's a it looks like it's a drop shadow. I've done this a couple times. I made this recently a recent video for better sheet members. If you're watching now, you can go check it out. I show you all kinds of ways, all kinds of drop shadows, you. In this way we want to use a thick black look at that.
Now that makes you want to click oh, that looks like I can click it, doesn't it? That looks awesome. It looks like a button. You can decrease this too if you want. You can just change all of these. Actually, we can't not do the rows and the columns at the same time, but if we change this to 10 and this to 10, Let's resize that room.
See, now that looks a little tiny here, but it looks really cool. It looks like you compress it. Depress it, right? It looks like up. It's up. It gives it a 3D look, right? All right. That was fun. Okay. We also want to like make, so you can make a copy, like you're coming here, making a copy, but let's really show off his Newsletter and put a cool image here.
Actually, let me add one to the left. Could we want to put it right here? What do we wanna do? We wanna make this a square, so we need to resize the column 100 and then this resize real row to 100 that might go down. So we might need to make this a little smaller. And let's insert an image. We want, we have an image in cell.
We're gonna grab this, right?
And what's gonna, it's gonna look cool because the background, yeah. So the background is the same color as our, the background of the image is the same color as the background of our. So there is no square here. It really brings like a round image, like a round shape to your sheet. Oh, this is like a really cool trick.
I like doing this a lot. Alright, and we gotta make this so this really big, because this is the whole point. Join my newsletter. Let's make, let's do one more up here. I wanna make this a little thin, thinner. Let's do. I don't know if this is gonna work for, so we have, oops. We have a link here. That is a link to his, what I like to do, I'd like to use this.
What color should we use that pink? Let's see what it looks like. Okay. It doesn't really stand out to too much. What I might want to do though is make it not the same width as this one. So we could do that. We could spend some time on it. Not a huge deal though, right now let's move this into the center.
Give it a little more white space. I want to decrease this white space a little bit. Make it even 20 10, 10. This is thought this was 20. No, this is like something else. So this is a cool Readme like, Hey David from the Land of Random here. Good luck with your job search. If you're interested, join my newsletter for fund links and marketing stuff.
Really simple ad, but make a copy is the big thing here. And once people make a copy, they'll have this as well. So it's not a total loss if they skip going to the newsletter here. If you are building a some type of lead magnet or something where they, a template that they can copy, don't worry too much about.
Trying to get them to go to that thing that you want 'em to go to. Just give them the value right away. They can make a copy, make that really the main hierarchy thing here. Make a copy. We can actually, let's make this like super bigger. Make a copy. Oh, wow. Whoa. Make a copy. That is big, right? That is the biggest thing we see here.
Cool. So when somebody clicks on this, by the way, let's let me show you copy link. Let's open in a incognito tab. I think I need to do one more thing. Yeah. I need to log in. So I need to make my sheet available to everyone that's gonna fix that. Actually I go over here to share. You wanna share it, change to anyone with the link done.
And now when I click make a copy, I'm gonna copy that link. Now let's see what happens. Ah, no. I think it's, oh, you know why I have to, okay. I'm gonna just change my people. I'm going to open a new, I know why. Cause I'm not logged into any Gmail. Google account. So when it's making a copy, it actually does have to log people in.
There you go. Copy document. Better sheets version right there. Make a copy. So in copy that. Go to that link, click this button, make a copy, and now people have a copy. This makes it super easy. As long as you're, you are logged into a Google Drive, Google account, you can make a copy. You might not need to make that a thing there, but, okay, so we have a Readme.
Okay, let's go back to our notes. We have a read me page. Now we want a settings page. This is super important because let me just duplicate. This is super important cuz we want to know where things are that we might need. We're gonna just delete everything in here and make it this beji. Beige blue.
Beji blue, no beige background. We're gonna change everything. Let's just use Conta. They say like a Spaniard. Just gonna conform everything here. Conform everything here. I'm saying conform, just cause I said conform weirdly. Let's just make everything the same. Okay, cool. So what do we need on the settings page?
These are our job left. So right now, actually, we can delete this. We don't need to give this instructions on how to delete this thing because it's gonna be done. So what we need to do is tell people where, oh, we are going to need, what are we. We're gonna need someone to be able to change the what is it, statuses?
One. We are gonna need someone to be able to enter their what is it? Cover letter. So let's put that on the settings page. Let's do this status statuses, okay. And then enter your cover letter. Google Doc url and what we want to do here, this is really cute. You can get cutesy with this. You don't have to.
I'm gonna actually do white background. I'm not gonna get too cutesy. I'm gonna make it a sort of a, just that, that's it. So that's where we're gonna enter our, we're gonna merge. But what, how we define this so someone can copy and paste their U URL here. D three. I'll show you this later when we do it. But we're always gonna, even though this is all merged, we're just gonna look at D three here, statuses, okay, what are the statuses of jobs we need to apply?
Let's make this 10 what we knew. So that's the first step we need to apply applied. First interview set. Actually before that we need a setting interview set, first interview done. And then you can add more things, right? I think
We have waiting for response interview. Second interview. Offer accepted. Probably by the time you get accepted, you're not gonna need this. So you're probably not gonna have to say accepted, but let's just put it on there just in case. Waiting for response. So I don't particularly like having inaction but let's keep it Let's just keep it because David is the one applying here.
And that might mean something that I don't know. And everybody's job search is going to have a different layers to it. Whenever I do job searches, I usually try to try to contact multiple people in the company, try to make sure you know that company is some, a company and culture that I.
Work at that. I might know someone there. So even though it's not as, for me, it's not straightforward of applying to an open job and then going through that process. Cuz most open jobs we don't get right. So we gotta do as much as we can to get that job. And if more people and you can ingratiate yourself on that like culture and also you'll see if you.
Apply. And if you wanna continue going towards that, you can see that. So I might want to go talk to a couple of people in that company and make sure that it's a company I wanna work at. So it'd be a little less linear than this for me. For you, it might be something else. But let's say it's linear, cuz this is a pretty good e easy way to show you how to build a pipeline, how to do a dropdown menu for sales, for interviews, for anything.
We have our settings. We know we're gonna put a URL here. So let's do this. Let's go to doc.new. Let's just create a new document and I'm gonna just show you how this works. Cause this is really fucked. Actually already showed you. We're gonna do the copy thing again. We're gonna have this letter, so this is gonna be called cover letter template,
so I'm just gonna name it. First name, last name.
Mr. First name, last name, he has this cover letter and we don't need to write it, but you can write a nice basic letter template and what we're gonna do is for each job, you're gonna copy it and then make, some kind of changes to it. I'm not gonna do that right now, but I just want to have this shared just in case.
Actually, I don't, you don't need to give it to anyone with a link. But if you are like saying, building this for someone, then you might wanna make this available for everyone. So we have this, let's go back here. Let's fill it out here. Okay. And we want to take this edit off and we want add copy. There we go.
So right there. Cool. So now what can we do? First off, we can get rid of this entire first line, right? And now we have all of these, so we have job opportunity. Good job. Let's make it a little more in the vein of David's land of random, right? We wanna keep that theme going through the entire thing.
So let's do that. It's actually. Do something strange here, do that. And then that Cool. That looks like fun, right? That's a little more on brand or off brand. Now we don't need any of this. We just need status, right? And this is just yes or no if it's applied, but we know once it's applied we don't need any of this now cause it's all gonna go into that one.
One of my pet peeves is having a bunch of columns over on the right that you get like all mixed up. So I'm just gonna delete all that real quick. Just a couple of keystrokes. I'm never going to apply to a thousand there. Every document always has a thousand rows. Let's, I'm probably gonna apply to, let's say 21 jobs, let's say.
So now this is really. So you have a job opportunity or a company, right? Company. I don't know what good job means, but let's keep it just in case we want to keep it cover letter link. So to create it, we're gonna do equals go to settings there and now all you have to do let's Don't like this, how it looks.
Let's do this. Maybe wrap it. That doesn't look too hot, but it doesn't really matter to that too much. Basically, this gives us a link that then we click it and it's gonna copy. Cause we have that slash copy. If we want to make a link, we make a copy and then we'll bring the new one over.
So let me double check that, that is that.
So we'll replace this with any copies that we make and we want to have this, see if you copy and paste this all the way down, it's gonna change, but we want it to stay the same until we replace it, right? Okay, so let's use that. You know what this is getting, this is annoying. Let's do this and then this way, and so we can see, oh, I don't think the copy's gonna work.
Wait one second. All right, so I figured it out. So why it wasn't copying is because the text of this link said copy, but the link was still the normal link. So now if we go back to applied, now we go to this. It will automatically bring us here and we'll make a copy. So we will make a, we'll make a new copy every single time.
I'm gonna close some of these. We'll make a copy. Every single time we wanna create a new cover letter. We'll use this template, right? We'll rename things we need to rename, but I'm not gonna go through that right now. But let's. Get you that copy. So now we know. And also, why do this, why made it text wrapping is not, it's not wrapped.
It's the la the end of here is because if we ha, this is a weird user thing. If I had this, even if I replaced this url, it would still show the same. I'd have to hover over it to see if it's different. If I do it this way, I now see the end and I see this copy when I replace this link with the right link for a kda cover letter.
Then I can see it won't have this copy here. It'll be a completely different URL here and I'll know it's different. But now look, all these we know immediately without hovering, without doing anything. We know these are a copy and we can click and copy them. Cool. Okay. Cool. Status, we need to change this from yes to.
What we're gonna do is data validation. We're gonna do a, not a list of items, but a list from a range. We're gonna go to our setting. I'm gonna give it one more spot here so we can change this around. I'm gonna say save. Go back to applied copy. This is actually gonna just delete that so it's blank and now can say applied or to apply.
There you go. We can delete all that. We'll keep that the way he wants. So now we're ready. And what's interesting is this data, this is going to show up even if we don't have a company here. What if we only want it there, if is blank A two? If there's a, if there's something here, we want to be able to make a copy immediately.
We. If there's nothing there, right? So now we get that clear that away from our iSpace, our get around everything that's nece unnecessary. We've added our settings page, we've created in Google Docs, our cover letter. We've created a copy, we created a status column. Okay? I get, I didn't come back here.
We did a lot there. Okay, let's create a pipeline view. All right then let's do this. Let's duplicate. I'm gonna call it pipeline view. And then I'm gonna, I'm gonna just delete all of this. I'm gonna delete, I'm actually gonna, oops. All I want to do is copy and paste everything, so there's nothing there. I want to delete all of this, and I need all of these here.
So what I'm gonna do is I'm gonna copy. I'm going to paste transposed. Whoops. Actually, I'm gonna do this. Paste transposed. That's not right. Paste transposed. Oh no, I lost it. Go back to settings. Copy, and then. Paste trans posts. There we go. Cool. Now I'm gonna take all of this and pace values and there we go.
Now we have headers. I want this background, but what's gonna happen is we want only company one. Let's do. This. Let's see if this works. There we go. We got a few companies. We want these statuses. Like once this, once we get through this, we're gonna be looking for a few, only a few of these types of statuses.
It's gonna be hard to find them. Maybe there's gonna be a bunch of applied right. And we wanna see which ones are, do we need to really worry about where's the pipeline? Are we doing well? Like, where are they in that phase that pipeline, I don't know all this to explain. So what were you gonna do?
We're gonna filter. I don't need that. We're gonna filter. What are we gonna filter the companies and we're gonna filter by if their. This equals this. Okay, and one more thing, as we copy paste this across, we don't want any of these columns to move, so we're gonna use that dollar sign. Except we do want this A to move.
So if we copy and paste now, all of these are applied and we're gonna get some Nas. So what happens in those with those nas, we really don't want anything. So we're gonna add in front of this if error, and then at the end a comma, end of parenthesis. And so if there's an error, do nothing. And now we get this really quick pipeline that say like we, here's all of the thing.
Here's all of the. And so instead of going here and searching through this column can be anywhere you want it to be. It could be over here on in the A column actually, but we don't really need it to be very apparent if you just wanna see the pipeline, right? What else do we need to do? So our pipeline's done, let's add some stats to it, right?
Before we add conditional formatting to the application date. I wanna add stats to the pipeline. This is fun. So sometimes we wanna see. How are we doing? So let's do, this is equal to count all a three to a, and it's divided by count all a three to G. Cool. And we get a percentage and we say, okay, that's 7% is in.
So as we copy and paste this though, if you notice this B to H move, so we actually need to use that dollar sign to hold it and we want it to change each way. So now we see where are our stats for each of these rows or columns. But what if we don't care about these stats? Like we don't really, we only wanna see the stats when we wanna see them.
One cool thing to do is we can insert one above. Let me command C. Let's see, command V, and we're gonna actually change this to a four and we wanna change this to a four copy there. Why do we do this? This is a funny thing to do, right? Is to not hold, freeze this row. We have to think about the user here and say, let's put it at the top because this might be the most important thing we look at.
We wanna see this. We don't want it at the bottom. We don't have to scroll through it. But if we don't wanna see this number and we really just wanna look at each of these individual companies, we can scroll down one and it goes away. And it's outside of our purview. We don't, we're not really caring about it.
Once we're looking at these companies, we don't care how. Are there we want to read this number. So this allows us, this is a really funky little way to optimize a sheet. You essentially think about what do you wanna see and when do you wanna see it? If we're searching through a sheet for individual items, we don't care about the meta number, right?
That's just a funky thing to do. You might be able to use this in your sheets as you wish, but think about the user. Think about what are you looking at and what do you care about in the moment that you're looking through it. At this moment, we care about the companies. If we're looking at the top, we care about the meta numbers.
Okay? What's the last name? We're almost done. We wanna add conditional formatting to the application date. So this is funny. Sometimes we are going to have a. Like April,
what is this? Oh, so here we go. I'm gonna take a break to sneeze. One second. Alright, so we have these dates. It is July 15th. Right now, let's say it's July 11th, and these are all ap. Let's say we applied yesterday to all of these. Nope. Okay. But this one we applied, let's see, it's the 11th and we don't want to go more than two days without working on this.
And maybe this is an application date. Maybe it's like the last day we did anything. You can add some maybe. I actually, one of the videos I just made recently in the last. For members is to show how to do a timestamp over here, so that's really cool. You could do that. But let's just say maybe this is an application date.
Maybe it's we just wanna see the ones we want it to pop up whenever it's after, like a couple days, right? We wanna be reminded visually to go back and check those. Okay, so let's do conditional formatting format. Conditional formatting dates are funny. Let me move my face over here.
Let's see. We want it to be, oh, blue, let's do bright blue. Cause that's really awful. Date is, are we gonna have to do this? Maybe the date is before today. Yesterday. So we can do that. We can say done. We can. Okay. So every two days basically this date has to be moved. If I change it to 13th, does it change?
No, because did on my computer it's date is 15th. So maybe 16th. And it'll change, right? So we're safe there. If it's 11th, it move. If you notice as well, we can do something like dates before in the past week. So that means after a week it's gonna go. Actually let me do that. So maybe we wanna wait a week before let's just do remove, do 7 1 20.
There we go. So this is probably better is date is before in the past week, meaning if it's been a week, it's gonna show up Blue be like, Hey, you gotta do something. What we can also do is we will add another rule. I'm gonna show you a little funky thing about conditional formatting. Date is before I've done this before.
Where I go date is before in the past month. Really, if we have, after a week we want one color, after a month, we want another color, let's go red. So let's do that and then let's do 4 23 like that. And actually let me go here. I want this 6 23 20. Let's do 5 23, 20 20. So if you notice there's something funky going on and it's a really weird thing about conditional formatting that I don't necessarily like, but it happens.
What it does is conditional formatting rules go in the order in which they literally appear here on the right. Okay? So that is why all of these are. When they should be read and look, you cannot move them around. It is really hard to move them around. So what you need to do is you have to be very careful about which, oh, which order they go in here.
So we're gonna add one more rule, or we're gonna, we're gonna delete those rules and we're gonna add the rules again. And we're gonna do date is before a month and we want it to be. Done. Then we want to add another rule. Date is before in the past week, we want that to be blue for some reason. Done. And now this five three is showing up.
So let's say 6, 2, 20, 20. And now that's showing up. Right now we're cooking with fire because, oh, that the date doesn't exist. Because. Now the date that is earlier than a month ago is read first and then it checks if it's a week ago. So this actually, so that is just a weird pitfall challenge you're gonna have to overcome with conditional formatting.
There's a couple of others, if you do, if you have too much data on your sheet and you have a ton of conditional formatting, which we are want to do, your sheet's gonna super slow down. I've seen it happen, especially when we're, I was u creating a CRM for eight people to use and we were moving rows around and basically when moving rows around, this conditional formatting follows them.
So every time you move to row it created like eight more conditional formatting. And every week I had to go in and clean this up. It was a pain, but. Condition has its challenges, but it's also very helpful because it can visually tell us what is going on in the sheet. Visuals are really good in Google Sheets.
If you can really make them apparent, they help you out they give you information. Man. Woo. We have gone a far away. We have, let's go back to our notes and check off the last thing. Okay, so let's see where we started. We started at the job application tracker from David at the Land of Random here.
He had pretty much half of his view, port me, used up by this information at the top. We've changed it to a Readme here that has his image here is in this style of his newsletter has a big make me a copy, has a nice link to his newsletter. We have a settings page to get those, that information out of our view port, cuz we don't need it all the time.
We just need to use it and then keep on going. Our applied has, what is this? 80% is being used for these companies. That's awesome. It's in this nice cool colors of pink and charcoal. We have added this cover letter link so that we can in two clicks, create a copy of a. We created a status column here where we can change this as it, as we go through the pipeline of applying, of getting our interviews, of getting accepted or offered or waiting for responses.
And if we're ever looking, one of the most often things we do is we'll sort by this status column and then have to go find it. But this pipeline view gives us a, almost like a Trello board style. Viewport here. A or a Can band style, like you can create your own Trello board here again, I have a video at Better Sheets.
If you haven't tried it out already, go to Better sheets.co. Check it out. There's some four, there's four free videos for you. I have a YouTube channel with tons of videos. Actually, this video is gonna go on YouTube eventually. I have literally no idea what day. Hopefully you are more. Able to do some cool sheets.
We, we made this nice pipeline. We added these stats here and we're hiding them when we're actually not using them. What else? Is that it? We've added a readme, we've added the settings. We created a copy. We da did a status column. We created a pipeline view. We added stats to the pipeline interview, and we did, oh, and we talked about conditional formatting, and I helped you through a pitfall.
You had to do that in order. You have to do the conditional formatting in order of you that you want. Wow. That was a lot of stuff. Thanks for taking the time out of your day. Hopefully you can do Google Sheets better. You're not just using Google Sheets, you're making better sheets. I'm Andrew Campy.
I'm hosting videos all the time. There's a hundred videos waiting for you to learn@bettersheets.co. Make sure you subscribe to David's, the Land of random, ck.com. It's wonderful. I'm a subscriber. It's a fantastic newsletter and if you're looking for a. Feel free to go make a copy of this Google sheet and hopefully David's using it.
Maybe he even made some more improvements from this. Go and check it out. Go check out the land of random.dot com. Thanks so much for watching. Have a great day. Bye.
Topics
Courses
Better Than Happy | Redesign of The Feelings Wheel
How Starter Story Designs Data
Learn to Love Your Sheets
Best Header Font Ever
Sheet Review! 150 Active VCs by LemonIO
Anika Asks: How To Set Text Overflow All The Time
Introducing: Brutal Calendar
Add a Checkbox to Turn on Dark Mode
00:05:10
Create Drop Shadows! This makes your dashboards pretty.
00:12:04
Merge Cells for Dashboards
00:05:29
10 Things I Hate About Your Spreadsheets
Dark Mode / Better Font Color
00:09:35
Better Font Colors
00:02:45
Magical Things You Can do with Checkboxes in Google Sheets
00:12:31
How To Export Your Beautiful Sheets to PDF
00:04:06
Consider Labels as Opposed to Headers
00:04:02
Add Icons To Your Sheets With a Domain Name
00:04:21
How To Color Cell Blocks So Others Enter Data Easily
00:10:30
Great Sheets! Corona Hiring Sheet
00:10:43
Great Sheets! Community Information Board by Seedtable.com
00:07:35
Roast: Hotel PPC Channel Cost Calculator
00:11:25
Better Header Fonts - Best Fonts To Use In Google Sheets
Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell
00:09:49
Basic Keyboard Shortcuts To Speed Up Your Productivity
00:13:44
Basics - 5 Ways to Change Row Height
00:05:08
Anders Asks: Can I Highlight Whole Row if Certain Columns have text?
00:09:31
How to Refer to Other Cells - A1 and R1C1 Explained
00:13:22
Change the Default Font
00:03:33
Biggest Flaw In Dashboards with Dark Colors
00:07:45
Basics - 4 Ways to Change Column Width
00:05:48
Basics - Structure of a Sheet: Index() Row() and Column()
00:06:49
Communicate Better with Gridlines, Border Styles, and Border Colors - Google Sheets
00:08:55
Use Cmd + Y To Do It Again, and Again, and Again
00:03:03
Create an Auto-Update Sales Chart: Trailing 12 Months
00:09:57
Google Sheet Basics - The Absolute Basics
00:09:48
Secure Your Sheets by BetterSheets.co
00:11:07
How To Create An AutoFill in Google Sheets
00:06:07
Build a Thermometer for Savings Goals
Make Your Lists Spicy Hot in Google Sheets
00:01:47
Restrict Access to a Cell if Another Cell is Blank
How to Use Smarket
00:03:28
Combine Data from a Tab and a Totally Different Sheet | ImportRange and Curly Brackets!
00:06:23
Job Application Tracker Template | From TheLandOfRandom - Sheet Improvement!
00:53:31