10 Things I Hate About Your Spreadsheets

<div>00:00 This is 10 things I hate about your spreadsheets and what to do about it. So we're not going to leave you hanging. 00:06 I am 10 years professionally working in Google Sheets, working on Google Sheets, making Google Sheets for others. And there are tons of things that I hate about your spreadsheets every time you open up a spreadsheet to show me. 00:18 I hate it.

And the, This is 10 things I hate about your spreadsheets every time you open up a spreadsheet to show thing about me that's different than people you work with or your boss or your colleagues or your coworkers or even if you have employees is that they will not tell you. 00:29 They won't tell you anything. They won't tell you that they hate your sheet. They'll just struggle with your sheet and that's no good. 00:36 They may hate you actually because they hate your sheet. So here's 10 things that I hate.

About your sheet and then how to deal with it, what to do about it. 00:44 Here's everything we're gonna go through, all 10. We may come back to this slide or not, but let's start the hate. 00:52 Now, let me tell you that this hate is good for you. Feel it, let it flow through your sheets, let it flow through your- and then let it improve your sheets. 01:04 So these are little tiny pet peeves that are just exasperated when I see over the course of many, many sheets and I think your sheets could just be better. 01:13 That's why I made better sheets.

Just so you can make better sheets. So you can learn better skills, better techniques, better tips. 01:21 Better tricks. So let's get started right away. You have squeezed rows. So your data might look something like this. And all of your data , if you just start a sheet with this black and white background, and you just put a bunch of data in there, it just looks squeezed. 01:37 There is nothing you can do about that, is there? Nope, there is. Just increase row height , row height that says increase row height . 01:44 That's all you have to do.

So go over to the left side, select all your data . You can hit the shift key and select everything or you can hit all everything right here between the A and the one. 01:56 And what you're going to do is click just between like the two and the three. Drag a little bit down. 02:03 We can probably give it another 50% space. That's it. That's all you have to do. Now you have a bunch of really epic space, good padding, good design. 02:14 A little bit extra you could probably do is probably center line if you really want to, but I won't.

I won't hate you if you don't do that. 02:23 I think just adding a little bit more space is gonna help that squeeze through us.

So next one, crazy column width . 02:29 So when we're dealing with a lot of data , maybe we have different data like date, status, as yes or no, we have a code here, we have other data , we have all different types of data that has different widths. 02:41 And one easy thing we can do is select all of these four columns, and we double-click here, and it will automatically align to the, basically the text that's the biggest. 02:57 It'll make the whole column that. But there's a problem here. It's a pretty lazy.

Way to do this because now all of our data , all of our columns are different widths. 03:07 This is just really hard to sort of parse that these are all different, they are different text, but what we really want to do is we probably prioritize these similarly because they're related to each other, they're bigger motivation than any other perhaps. 03:27 And so they look like this bigger one looks more important. So this is super simple to do.

Just select the widths, the columns that you want to change the width. 03:37 We're going to actually go to Resize Columns, right click, and we're going to resize it to So let's say, I think we need to do 35. 03:46 Click OK. Nope. Let's do that again. Let's do 70. Double that. Now, all of our columns are the same width. 03:57 And we can also align these center. And this looks so much better. Cleaner. Now I won't hate your sheets if you have duplicate column width . 04:08 Just makes it nice and cleaner.

Alright next one, number eight of why I hate your spreadsheets is too many clicks. 04:17 So a common thing we will do is we will add probably a let's actually add it over. For here, we're gonna add a drop down menu. 04:25 Super simple thing that a lot of people do. Option one, option two, and they'll do it for any amount of items. 04:33 But if we have like, let's say a yes or a no, hit done here. Super, it's done.

We can click yes, and then we have to let's say So we're clicking once to select Yes, or no and then clicking to say we want to select it and then another click that's two clicks This doesn't sound like a lot right yes. 04:57 No yes. No just click here. Just click there. We want this yes. Yes Doesn't seem like a lot. But this is a Boolean . 05:04 There's only two options here. We could save 50% of our time if we actually created two columns. Let's see. I want to delete all the data . 05:14 And we just label it yes, no, and then add a bunch of check boxes. Insert, checkbox .

Really simple to decrease one click not two clicks that is 50% of the clicks No, yes, and now here's something a bonus about this is that This drop-down you might have to do conditional formatting or something to show 05:38 like oh yes or no is different But this yes and no column here is so much easier to see if it's yes or no, if it's bullion between this or that, the left one or the right one. 05:49 So much easier visually to know which one it is. Right there, yes, no.

Much easier than seeing this text, this tiny text, yes or no. 06:00 So if you have too many clicks, you have like a. Couple of options in your dropdown , add some check boxes. 06:05 That's it, super simple. All right, if you're creating, number seven is instructions in the clear, what does that mean? It means that if I'm looking at your sheet like this, and you're like, hey, just fill in these notes here over in the H column, you tell me that once, you send me an email about that 06:21 , and now I'm working and I come back. Back to this in a week, I forgot.

You told me once, but I totally forgot. 06:27 Super simple way to fix this is add instructions. If you want someone to fill in the h column, the h column only, literally add a new sheet, call it instructions, and say fill in column rows Tab only That's all you have to do now. 06:49 The communication is inside of the sheet.

You're communicating with someone Maybe that someone is not just a colleague or a co-worker But yourself in a week if you create a sheet and you're like, oh, I'll remember how to use this. 07:02 No, we Do not remember how to use this sheet, even ourselves, even if we made it. All right, I have to only do the h column. 07:10 Okay, is that it? Yes, I have my instructions here. I have some documentation. It's written in the sheet. I don't have to go to my email. 07:17 I don't have to go to Slack. I don't have to go to a voicemail message.

I don't have to go anywhere else. 07:23 You have added instructions and I love your sheet for that. Number six looks the same. What does it look the same as this Google sheet? 07:34 If we create sheet dot new, I'm gonna do slash three because it's my third workspace. Every single sheet that's ever been made. 07:43 Create a new sheet with nothing, it looks like this. It has aerial font, it has black and white text. It is black text on white background. 07:52 It is gray grid line, the grid lines are there.

The thing you can do to fix this is just make it your own. 07:59 You can add a little bit of style instead of just picking from here. They're custom colors. You can choose custom colors all around. 08:09 You can choose just a different shade of yellow. You can absolutely change this default to Carla, I would recommend.

Increase that font, change the font to a grayish, you can actually change it to yellow. 08:23 So one little note is if you want to have less contrast here White is okay But if you add a little bit of yellow or whatever the fill color is add a little bit of that it looks so much Nice, it looks so much cooler But add your own branding if you're creating a spreadsheet for clients add your own branding 08:41 put logos inside of your sheet go up to Go to, where is it, image , image , oversells or insell.

Like go up into A1 and put like your logo in there. 08:53 Put your own information in there, put your own branding and your sheets will never look the same again. I will love your sheets for it. 09:03 I hate. Sheets that all look the same. We are in the home stretch here. And so I want to review before we get to the net five and through one. 09:14 This is what we have so far. We have squeezed rows. You can just increase the row height . Crazy column width . 09:20 Just make those column width the same. Too many clicks. Add some checkbox .

This is get a little funky with your sheets. 09:26 Instructions unclear. Just add instructions to a sheet. Add a new tab, write some instructions. And all your sheets look the same. 09:31 Everyone watching this, your sheets will probably all look the same. Get a little personality into your sheets. You can learn much more at BetterSheets.co. 09:39 If you're watching this on BetterSheets, just go and watch any design videos. Tons and tons of really cool stuff at Perishitsako to make your sheets stand out and you'll be proud of them. 09:51 So let's go to number five.

We have five through one left. Garbage data . What do I mean by this? Is that you are allowing people to enter data of all sorts of things. 10:03 That is some, text, you're maybe copying, pasting from some research, you're having people enter their names, you're having people enter their emails, whatever it is, you have a lot of data validation options. 10:14 You have far beyond dropdown menus. So if we wanted to say if this is a date or not, or if somebody puts in a date or not, email address, email at gmail.com. 10:28 We have some options.

We can go up to here, format , actually no data , data validation , add a rule. We don't need drop, drop down is a email address. 10:44 First one, but we can say text is valid email. Let me move my head a little bit. And now, if the text is not email, gmail.com, it will have this little warning invalid. 10:59 So we can see right away did somebody not enter an email address. If you have a ton of data . And you're like, oh, I'm going to import a CSV file of email addresses that people hand imported. 11:09 You can add this data validation on top of it.

And you can see immediately which ones are not valid. Email address equals is email is also valid. 11:18 This is another way to do data validation . You have a bunch of falsees here. Let's do that right here. You can change this to true if we just go Andrew at gmail.com. 11:31 Now that's true. We have an awesome data validation to see all types of things. This is valid email. We can do valid URLs. 11:39 Text contains certain words. Text is word only. Lots and lots of data validation .

Do not allow garbage data to get into your sheets because you know what garbage in garbage out so just add some data valid a shion there we go number four lazy conditional formatting i've seen this too many times people 12:00 want to add conditional formatting to anything we want to turn everything if it's blank or something. Lots and lots of lazy conditional formatting because it's simple, it's easy, you think, oh, I'm just gonna show it and then I have to scroll through here. 12:15 No, that's just lazy. You use cool things like sparkline or if it's blank.

If something is blank here, somebody's gonna do this. 12:24 Let's show. Conditional formatting . Format is empty and we're going to choose some, oh we want it bright red, done. Great, now our sheets look terrible. 12:41 But and here's a bonus problem with this. It's a If you choose red, green, or any kind of simple colors, there are a certain percentage, a large amount of people who have eye problems who are still using Google Sheets today, but might be color blind. 12:58 Might be just a little bit wrong.

They're not actually blind, but they're not easily able to tell these colors apart. 13:05 Or shades of colors apart. So this could be problematic in your place of business. So let's not do that, that's super lazy conditional formatting , but let's do something more visual, like visual if it's there or not instead of a color or not. 13:20 We can do if is blank, and the value. Is going to be m4. And if the value is blank, we can write, email is blank. 13:35 And if it's not blank, we'll just do nothing. We'll just have literally comma there and do nothing.

I'm going to end that. 13:42 And now we have the words, email is blank. Instead of this Color we have to decide. Now we can just read a word. 13:49 We can also add emojis here. Let's do a letter. Emoji. A little arrow. Great. Now it's a little bit something more textual, something more visual than just. 14:06 A color shade or color grading here. But we can copy paste this formula up here. And now we have nothing. 14:13 So we have our emails here. If we write in email at gmail.com, great. Our email is truly an email. And it is gone. 14:22 We have now blank email. So that's it.

Super easy. It adds some cool things. Add Sparkline , if you don't know Sparkline , it's like a progress bar you can add. 14:33 You can sort of make a progress bar out of it. Check it out at bettersheets.co slash formulas slash sparkline , you can look at any formula there at bettersheets.co slash formulas. 14:46 Number three, where's Waldo of results? Alright, we're adding some data if we're doing forecasting or kind of like any counting, any sum-ifing. 14:55 We're like, oh, we want to know budget for next month or something.

Our human tendency is to have a bunch of information and then at the bottom put a total. 15:04 Sum- more down here. And if you're presenting a sheet to me and you're like, oh, here's this total. It's on the rose, it's on the rose tab. 15:13 And it's on J 55. I have to scroll down to J 55 and be like, Oh, here's a, here's the total. 15:20 No, please, I'm going to hate you. Your colleagues hate you. Your boss hates you. And you're, if you're a boss. 15:27 Your employees hate you. All you have to do is add a summary tab. That's it.

Just add a summary tab and have all of the information here. 15:36 Sales, revenue, forecast, count, sum, sum, sum, sum, sum, sum, sum, sum. Whatever you need to do, add your data here. 15:45 I don't even care if it looks. Terrible. Just have it on a summary tab and have it the very first tab. 15:50 The very first thing I open up, check this out. So if I go to this rows tab up here in the URL bar, it says edit hashtag gid equals zero. 15:59 This is because this is the very first tab that's ever been created in this sheet.

Every other tab instructions has a different gid. 16:06 It has equal sign 885-5 summary tab has equal sign 1716-6 sign. So you can actually direct someone directly to exactly the tab you want them to open. 16:18 So if you email this to someone and you very lazily just take this base email, not email, You are. And just email them and say, oh, go to the summary tab. 16:29 Just go to the summary tab, pick out this URL and email them, and it'll show up right away in there when they open the sheet. 16:38 That's really cool. That's really helpful. People will love you.

So just add a summary page for where 's Waldo of results. 16:45 We are coming to the home. Stretch, number two, I strain the headaches. I hate your sheets if you give me a headache. 16:52 I open your sheet, I look at your sheet, and then I work on it for eight to 10 hours a day, and all we need to do to fix this is lower the contrast. 17:00 Increase the background color and decrease the font color. Super simple, actually I created a extension and, on. Go to addons, get addons, get this sheet styles. 17:11 I'm going to show you how it works right away. Look at this background.

It is dark, not dark. It is white. 17:18 The text is black, but if I just click papyrus style here, it lowers the contrast between the text and the background. 17:26 Over here, I can do. Do exactly the same, but if I'm doing some kind of data management and I want alternating rows, I can go over to data management style, click that. 17:34 And one click turns all the rows alternating colors . If I want a dark stuff, I want, if I would prefer a dark background and white text, bam, there's dark style. 17:44 Get sheet styles by better sheets, just go up to x.

Then since add-ons get add-ons, search for sheet styles once this comes up. 17:52 Sheet styles, it'll be right here, sheet styles, get installed. It's free, the free Google Sheet add-on that will help you with eye strain and headaches, less headaches if you're working eight to 10 hours a day in Google Sheets. 18:05 All right, number one. Thing I hate about your sheet is too many cells . Whenever I get to a sheet, let's change this to paper style. 18:14 When I get to a sheet and I end up scrolling down to them, I'm like, oh no, I'm lost.

There's so many rows here. 18:20 There's a thousand rows, right? Ah, I hate this. Or even better, there's literally no data in it. There's nothing here. 18:29 Oh, there it is. There's some data and I'm lost. I'm like, where am I? I have a C column. Oh, I don't want any of this. 18:37 I made an extension to fix this. All you need to do is delete delete your Unnecessary rows and columns. That's it and what you need to do is get tiny sheets go up Good excitement. 18:48 Add-ons. Get add-ons and get the free Google Sheet add-on called Tiny Sheets. Literally type in here, Tiny Sheets.

I think it's two words. 18:58 Tiny Sheets. Let's see if one word. Nope. Do not do one word. Do two words. Tiny Sheets. There it is. 19:06 It's the only one there called Tiny Sheets. 13,000 people have downloaded and installed it already. Install it for free, absolutely free. 19:13 What does it do? Well, first off, tiny sheets will delete all your rows and columns that do not have data . 19:22 Now look at that. In one click, I deleted all the rows and columns. Do you know how long this would take me to do? 19:28 Take me a few seconds.

I have to go here, select all of these, delete all those rows, go over here, delete all the columns. 19:35 No! Extensions, tiny sheets, delete rows and columns outside of it. That is it. And if I want to create a sheet that is the world's smallest sheet, I can in one click create a one by one sheet. 19:47 Why would I? You need that. What's, what do people need a one by one cheat? This is really fun. A one by one cheat is really useful if you just need to like have a one piece of data somewhere. 19:58 You know the summary tab? Lots of stuff out here. Lots of things going on.

We have some information here and I want to focus the eye up to the left. 20:08 I can go up to 10 sheets elite rows and columns, but I don't have to do that if I start with a one by one If I'm like, oh, here's your sales Here's sales Here's the number of sales Done. 20:22 That's a number of sales.

That's the only cell you need to see Very very simple and Something I will love about your sheets is if you care about where my eyes are going, the time it takes to figure out where I'm going, you have this GID here and this unique URL for this particular tab so you can send 20:38 someone right away there. Also if you're a better sheets member and you're watching this, you can also take sheets like this or information like this embedded into websites. 20:48 I'll show you. How to do that in another tutorial.

You can take information like this, embed it into a headline, all types of cool stuff. 20:54 You can use this also to email yourself every day, a report from this particular page on this particular sheet. Check out other, maybe, video called email yourself every day. 21:05 Over on bettersheets.co. Check out that tutorial. I hope you enjoyed this.

These are the all 10 things that I hate about your sheets. 21:13 Squeeze rows, crazy column with too many clicks, instructions unclear, lazy conditional formatting , too many cells , get tiny sheets, garbage data , garbage in, garbage out, so clean up that data with data validation . 21:25 Looks the same, just put yourself into it. Where 's Waldo results just in?

A summary tab, iString and headaches just get the sheet styles free add-on and that is it and I hope I have now love your sheets. 21:39 I hope you learned enough so that now you can send me a sheet and I love it and I'll look at it and I'll love you because I love your sheets. 21:47 Bye!</div>