Study Better With Google Sheets

Beginner

About this Tutorial

Made Ali Abdaal's study google sheet better.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNTA() GOOGLETRANSLATE() INDEX() RANDBETWEEN() VLOOKUP()

Video Transcript

<div>00:00 So in Ali Abdul's study, for example, with Google Sheets, he talks about this flashcard system in Google Sheets he built, and I think I can make it better. 00:09 So what he described is have all of your questions in column A and your answers in column B.

So what we'll do is set up a little flashcard system for, say, learning a language, but you can do this with anything. 00:20 Here we have English, and we're going to translate this to Spanish for the case here, But we'll actually be able to do this for anything equals Google Translate. 00:30 I'll use text here, source language EN, target language, I think it's ES for Espanol, and yeah, we'll do that for all of those. 00:41 So now what's interesting is we can create a little system here.

Actually, this is a little side quest, but we're going to have a language, and we need a two-letter code. 00:53 So, for example, Spanish is E-S. German is D-E, I think. What's Mongolian two-letter country code? Country code or M-N? 01:13 Let's see if we can do M-N, Mongolian. And instead of this being E-N, or actually E-S here, we'll do a drop down menu here from a range we're going to drop down. 01:31 Languages. Great. And now we can take this E-S and do a VLOOKUP .

For, we're searching for whatever is in B1, and we're going to look in this languages tab for the first column here, and we'll get the second column. 01:52 Is it sorted? I'm going to say false here. I don't, we may want to mess this, mess around with this. 01:58 It's okay. But, um, see, It's the exact same thing. So let's add dollar signs in front of here and the B1 as well to lock that in. 02:09 A2 will change as we copy paste this. Okay. So now we can change this to German and study for German. 02:15 200 most common words in English, and I have them translated here.

Ah, let's see if Mongolian works. This will be very interesting. 02:25 Yep, it works. So, what he did is what Ali Abdaal did in his video on how to study. For exams, and we're going to be doing it better is he color coded each one like red, green, yellow, had his own coloring system, um, color coding, like using the color itself in the sort of change the background color 02:47 is a we can make it better.

we can make it much easier to do and easier if you're sharing this with other people that they may not know you're color-coding. 02:59 What I like to stress is that a lot of people, are color blind or there's different types of color blind if you have different shades. 03:05 So I don't rely on, um, color coding. So what I'll do here is I'll create a, actually we'll delete this drop down and we'll add a new drop down. 03:16 And we're going to have three options, got it, or actually good, meaning we're good on it. We got it. Uh, somewhat got it somewhat. 03:27 We'll use yellow.

So we'll use a different shade of green here. And then we're going to add another item. Nope. 03:34 Don't got it. So we'll have red. We'll click done. We will make sure that this is copied all the way down. 03:41 And this is like how your level of understanding, like, do you got it or not? Or somewhat. So like, if we also, we need to hide this. 03:55 So we're going to use white text.

So here now, if we, if we have the answer, we know V is D, uh, we can't even see it here. 04:08 Actually, funny enough, um, we can set ourselves as like, okay, we know that if we don't know it, we can look at it here. 04:21 Okay, cool. And we can set our colors here, but we want to set the color for the entire thing. So we do need conditional formatting . Uh, and this is only because sometimes people really do like the colors. 04:37 I don't necessarily like the colors.

I do like words here, so we can filter , you'll see later, we'll filter it. 04:42 What we're going to do is we're going to say custom formula is , and our, we're going to apply the entire range A to C. 04:55 Our custom formula , however, will be C1 equals, it's It's actually the eco-science. C1 equals good. And if that's the case, we want to apply it to everything, so we'll actually put a dollar sign in front of the C, and now everything is highlighted green. 05:13 Uhm, we actually don't want the B column highlighted, so we'll just use A column here. There.

So that our b column with the answer isn't highlighted and we don't have the the answer right there. 05:31 Actually, we could. If we, if you have the answer, it's good. We can see the answer, it's okay. But nope, we want it bad. 05:37 Oh, we want it red. So we want to add another rule. We will do that again. A colon A, and we will switch custom formula is equals to dollar sign C1 equals nope. 05:52 And we'll change this to red. And so now our Color coding is not just us having to click the color, but it is based on. 06:01 The drop down menu here.

So what that allows us to do is say, here's the words, the study words. Here's the languages. 06:14 And if we want to study different languages, we can move them here. If we want to, we can now filter for not the color, but the level of understanding. 06:26 happening. So sometimes we may want to filter out everything that is not happening. Sorry, filter out everything that's good. So we just click there. 06:37 And now, if we're like, oh, I know this. It is, er, what? It is was. It's definitely was. I know it. 06:47 I'm good. I click good, and this filter will filter out good. See?

So now, every time I come in, I can just filter by, no, I don't want to see the good ones. 06:59 I only want to see the bad ones. That's way beyond better than the system of just color coding. One thing that Google Sheets flashcards, in this case, uh, are bad at, that physical flashcards are good at, is sort of randomization. 07:14 What if we want to just see a random word? Instead of going from the top to the bottom, these are all sort of stuck in this order. 07:22 So a few things we can do is we can randomize this order if we wish.

Um, I'm going to actually take this filter off . 07:30 But we can also create a***. For ourselves, a s- a random one to study. So what we're going to do is go index . 07:38 And we're going to take the index of anything here. That's our reference . Our row is going to be a random number between , uh, one and the high is we're going to count the rows. 07:53 Count all. So that means we're not going to get a number higher than, you know, the amount that is there. 08:06 And the column is going to be one. And so now we have a random word.

We also have the ability to see its, uhm, if we want to see the answer. 08:20 Here, we can do VLOOKUP . Search key is here. Our range is going to be A to B. Index one. I'm going to say false first. 08:35 actually, in fact, we need not one, we need the second column. And so this is going to be our word. 08:41 We can take whatever German here. This is equal to whatever is in study A1. So we can see this. And let's say we need to change this RANDBETWEEN. 08:59 What we can do is anytime there is a. edit, it's going to change it.

So we can insert a checkbox . 09:07 So see, every time I edit. So I just need a new one. I just need to check this checkbox and a different one comes up. 09:14 But let's say we don't want to see the answer here. We can color this in. So an interesting thing here is that this looks like VLOOKUP . 09:28 So we cannot think, oh, we can't see the answer, Right? But it actually is doubly hidden . One is it's not the correct text color , right? 09:37 It is the same text color as the background.

But if we double click in here, we now see the answer. 09:43 So it's a little bit more hidden even than just going here and seeing this. So here, we can double click on it and see the answer if we want. 09:53 It's right here above the word Google Translate. So I think this is a little bit better than Ali Abdaal's how to study for exams with Google Sheets. 10:03 But let me know if you don't think so or if you want another feature such as categories or other things. 10:12 I'm happy to keep adding to this.</div>