Easiest Mistakes to Make In Spreadsheets

In today’s tutorial, I'm going to explain the most fundamental mistake beginners make when working in spreadsheets, no matter what you use – Google sheets, Microsoft Excel, Apple Numbers, or Libra Office – you might be making this mistake.

I almost think everybody makes this mistake. You might be making this. Even years after first using spreadsheets and you know what, this is a silly mistake to make, and it's not life threatening. No one's ever going to correct you unless there's someone like me who thinks that we can make better sheets thinks that we can do better with our time and get over this mistake.

But once you know what this mistake is, We can recognize it and we can recognize when we're making this mistake. I'll show you some very simple changes that you can make to your spreadsheets as you're making this mistake. And you'll never make this mistake again, or you'll catch yourself very early on and you'll be able to catch others.

What’s the mistake?

Well, the mistake the beginners make mostly is forgetting the relational value of spreadsheets. You may make this mistake early on in a spreadsheet or later on in a spreadsheet. Let's start at the very basics and why this might be a mistake.

Rows are relations. Everything on a row, across a row in a spreadsheet is something that is related to one. and columns are also relations. That means that every cell in a column should be related to each other and every cell in a row should be related to each other. In addition to that, everything on a single sheet or a single tab should be related.

Everything in a worksheet, all the data in a single Google sheets file is related.

When we forget about that, we make this mistake. We make the mistake of adding data or notes or things that just aren't related. The new software that is sort of post-spreadsheet actually exacerbates this problem and exacerbates the mistake. And, in fact, sort of leans into this mistake and doesn't solve it. so something like notion where you can start throwing down ideas and data together, they allow you to make this mistake and they don't solve for it.

This mistake happens that you have some notes, some data, everything there, something like Air Table as well might seem like a solution where you have a record and you can view and edit it in different ways. But it actually makes this mistake easier to make because on some views, you don't see the rows and columns. You start adding more information. You add a notes column and you just fill it with a ton of different things.

You'll notice this problem when you do that. I notice this problem. Whenever I see a notes column and see all different kinds of things other than notes, like notes of all different types.
When we can write anything anywhere, and we can even link them, for example, via bidirectional links in Roam, this mistake is made time and time again.

Let's go back and identify when this mistake happens.

I've seen it happen early on when we're starting a new sheet and never gets corrected.

We're in a spreadsheet where you might have some data, like name, date, age, first name, and last name. And you fill these in.

These are related. Everything over across this entire row is related to each other and everything in this column is related to each other.

Then we have a header and that is perfectly fine. Here is the mistake that people make: You usually see someone make a smaller column. They will fill it with black and then they will do some kind of formula beside that smaller column.

We have now created a column. and a row that is not related. This row across is not related to each other. We have this black bar, this visual representation is exactly where we see this mistake. The simplest way to see it is this black bar.

I am fallible myself. I have done this many times, over the course of creating many sheets and it's so easy to fix. We're doing ourselves a favor because we're able to see the analysis in the same view as the data itself. However, here we can make this change and we don't have to continue exacerbating this mistake by just doing command X on the first formula. (It’s cell G2 for this example.)

We create a new tab and call it “unique” and paste it there. If we do command X and cut and paste, we don't have to go back and delete it.

Now what's interesting about this mistake too, is now we know we don't need any more data on this sheet so we can actually do one thing better. One of the biggest pet peeves of mine is having extra columns and extra rows that we don't use.

So I'm going to hit shift command to the right and delete all these columns. We’ve improved this immensely. We don't have any bar on the bottom that goes to the right and left, and we can always delete all the other rows later.

You can also see this mistake happen when in the course of discussion, you have to refer to single cells. Let’s say we have data on column F and it was on the fifth row, and we find ourselves saying, “Go to sheet one, column F, row five. That's where I've done this analysis.”

That's where you can also catch yourself having data where data shouldn't be. The biggest way you can see that there is a mistake is when there is a row that has extra information that literally doesn't happen. And you can the black bar. That is the visual representation of this mistake. However, we can have another tab and we can call it “UNIQUE.”

What I also recommend is to have sheets that only have one column. I immediately go and delete every single column other than the A column. I also have lots of tabs that have these one-column pieces of data, and that is totally okay.

Also, it's totally okay to do some kind of analysis where say you input countif(). You have a unique, and then maybe you do countif().

Now we have accounted two columns. We have no other information here. We don't need any other information. If we've counted uniques or we have this equals count all A:A because we want to count all the uniques. Now look at this, this name is the title of this column and it has nothing to do with this.

The second way that you can see this mistake is if you have data in the header column that's not a header, then you have a problem.

There is a slightly sort of gray area. If we have a header and we insert a row above it, and we have counts across, I would say that this is also a mistake.

This is a very small mistake because it makes sense that we have these columns and we want to do some analysis. We want to view it right here, right away.

This is not totally okay. Again, it's 50/50, it's a gray area.

I would put this on a data page and take this information. I'd take these rows, transpose them, and then do counta(Sheet1!B:B).

Now we have a summary of that data, getting the best points and it’s all labeled. We don't have to redo or rethink spreadsheets.

This is where spreadsheets are very powerful: When we have rows that are related and we have columns that are related. That is quite specifically the reason spreadsheets exist, that they have cells. They have rows, they have columns, they have columns that are A, B, C. They have rows that are 1, 2, 3.

Thank you for reading this. I hope you don't make this silly mistake. Review the moment you can see yourself making the mistake or your boss or any coworkers: When you have a column that is completely black. This is the number one way to see it.

The actual fundamental way to see it is just knowing that a row should be 100% related and a column should be 100% percent related. If you have any data that's not related to that row or that column, put it on another tab. That is the number one way to fix this mistake.

The worst way to fix this mistake is using something like air table. We love Google sheets here. If you love Google sheet as well, take a look at our Facebook page and join us over at Better Sheets.

I hope you enjoyed this tutorial and learned something from it.

Watch the video for this tutorial:

Learn about how you can catch errors and resolve them:

Get more from Better Sheets

I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.

Join other members. Pay once and own it forever. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get lifetime access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.

Don’t make any sheets. Make Better Sheets.