There's this term, garbage in, garbage out, and I'm sharing this with you because in Google Sheets, or databases, or spreadsheets in general, we have this very interesting term, garbage in, garbage out, but it doesn't actually tell you how to fix the problem. What happens is we start creating, Tables, spreadsheets, databases. We create headers like this, task, date, status. We need follow up and we want to type in here yes or no or no. Here we want in progress. We'll type in done, completed. We'll type in dates that are sometimes formatted. Automated Project Management in Google Sheets How to Use Google Sheets for Advanced Project Management MESSIEST SPREADSHEET MISTAKE

Like this, if we type in a wrong date, like 1 20 far, 20, that's a date. Google Sheets thinks it's a date, but did we mean 2020? Did we mean 2025? And I want to share this with you because I want to share with you actually how to fix garbage in, garbage out. At least one way to fix 80 percent of your problems is garbage data . And I want to separate two ideas of garbage data . So garbage data has two parts. One, which is the veracity or truthfulness of the data . And that I honestly can't help you with. There's another part though that is like 80 percent of our problems, and that's data validation . Data validation means, did we select the right type of data? You Should Know The Limitations of Data Validation When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa MESSIEST SPREADSHEET MISTAKE

So with dates, is it an actual date? Instead of like 22, 23, 20, 20, 25? This is not a date. Do we know it's not a date? Did we type it in wrong? Did we copy paste it from somewhere? We don't know, but we do know this is not a date. So simple data validation could be just, is it a date? We can fix this by going right click, drop down, actually view more selections, data validation , add a rule, Instead of drop down here, criteria , just select is valid date. And we can change this range from B5 to B2 colon B to make it apply to the entire range . We could also show a warning or even reject the input completely. So we'll just show a warning , click done. You Should Know The Limitations of Data Validation Add Pop Up Calendar Date To Cell

And now it has a little red dot say, Hey, this is not a date. What did you mean? Oh, and we have a picker. Let's pick something. Let's pick or copy paste this. And we actually meant like the 23rd. Second of January, right? Like, now, every single cell has a picker. So, I don't even have to type in anything. I can just pick the date. I can even just say, today, there. And it'll select the right date. Makes it way easier to enter any date correctly, because now we're not having to type it in. But let's talk about, like, statuses and say, well, we want, we want to be able to enter something. But like, even capitalization, is it important? Capitalization or miscapitalization or misspelling can be really Google Sheets Interface Changes Writer Better Prompts Add Pop Up Calendar Date To Cell

problematic for analysis , for sorting, all these types of things. And data validation helps us here as well. There's a data validation called a drop down. So let's create a drop down. Right click, drop down. And we'll apply this to the entire C column, C2 colon C. And we'll select . In progress done. And instead of done or completed, we just want done. We want not started yet. Click done. And now you see there are some that have read, so we're like, Oh, we need to change this. Oh yeah. It's misspelled done is misspelled or miscapitalized completed is not even complete correctly. It's, it's actually misspelled and it's the wrong word. So now done. Now. I have this great little picker. Create a Self-Adding Dropdown Menu MESSIEST SPREADSHEET MISTAKE

So this data validation allows me to say, Hey, I only want one of these three selections or nothing. And I want to make sure they're spelled correctly. And even this dropdown will help you with analysis because let's go back and edit this and we can add colors. We can say done is green. Not yet started is red. We need to get those started. Click done. It'll ask me, do I want to apply it to everyone else? Yes, apply. Now I have this color coded statuses. Fantastic. Even yes or no can be a drop down. We'll say yes. Add another item. No, we'll add red and dark green. Click done. If you haven't selected the entire column yet, let's go back, edit Create a Self-Adding Dropdown Menu How to Use Checkboxes for Approvals, and Change the Checkbox Text How To Color Code Data Validation In Google Sheets

it, and just select this range , D2 colon D, hit enter, done. Now let's say yes, no, no. Super simple, and now, our sheet is not garbage data . We're like, oh, it's correctly validated, we know this is going to be a date, we know this is going to be one of these three statuses, we know this is going to be one of these. Options. I want to show you one more trick because sometimes we'll like have an assignee and we all have some names of Robert and Kate and Frank and these names may change over time. Maybe they you hire new people, you get rid of some people, you add more to the team, you change the team. We can create a sheet called settings and we're going to use these three Create a Sheet of Sheets for Project Management and/or Data Management Getting Started Coding in Apps Script MESSIEST SPREADSHEET MISTAKE

names and I'm just going to call this. employees and paste these names. They're in the a column right now. They can be in anywhere. Let's go back to our sheet and our dropdown menu. Instead of having to come to this dropdown , add Kate and Frank and edit this every single time we ever have to like onboard someone off board, someone will select dropdown from a range and this dropdown from a range we'll say settings Not question mark, exclamation point, A to colon A. This is the range that we want to look at to say, what are the options? We'll hit enter, and there are our options. Let's put this on the entire E column. Click done. Now, you see there's only Robert, Kate, Frank, but let's Create a Self-Adding Dropdown Menu Are Google Sheets sexy now? Dropdown Chips might do the trick! Enter Name Get Grades

say we want to add someone. Alvin. Just add them to the settings, and now go back and Alvin's already there. I don't have to edit this drop down menu. This is amazing And I want to get rid of robert robert's up here, but we unfortunately had to let him go click delete Go back to our sheet. And now there's a red little Thing that says hey robert's assigned here. We need to fix this Automatically already done for you. Oh, yeah, let's go fix this. This is actually frank's new job or task this You Data validation is going to solve 80 percent of your garbage data problems, but unfortunately it won't change the fact that we do have to check the truthfulness of this data . Is this actually Frank's task? That will be up to humans almost probably for the end of time. You Should Know The Limitations of Data Validation MESSIEST SPREADSHEET MISTAKE

Data veracity or the truthfulness of the data is still yet to be solved. But at least I've solved 80 percent of your problems with data validation . And I hope you make your spreadsheets better all the time. Subscribe here on YouTube. Get more awesome things out of your Google Sheets every single day. You Should Know The Limitations of Data Validation When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa MESSIEST SPREADSHEET MISTAKE

Better Sheets

FIX Garbage In, Garbage Out of Google Sheets