Beginners Make This Spreadsheet Mistake All The Time!

Today 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 and you might be making this mistake 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.

Correct that spreadsheet mistake starting today.

Once you know what this mistake is, we can recognize when we're making it. In this tutorial, I'll walk you through some very simple changes that you can make to your spreadsheets as you're making this mistake. You'll never make this mistake again or you'll catch yourself very early on and you'll be able to catch others.

So what's a mistake? You might be wondering, “You're chomping at the bit, right?” Well, the mistake is getting the relational value of spreadsheets. You may make this mistake early on in a spreadsheet or later on in a spreadsheet. So you're putting a lot of data, some thoughts, some ideas, some notes down. But why is this a mistake?

It's all about relations, even with Google Sheets.

Rows are relations. Everything on a row, across a row in a spreadsheet is something that is related to one another. 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. You can also make the assumption that everything on a single sheet or a single tab should be related.

The mistake happens when we add data or notes or other things that just aren’t related. The new software that is sort of post-spreadsheet actually exacerbates this problem and the mistake. In fact, it sort of leans into this mistake and doesn't solve it.

Something like Notion, where you can start throwing down and data together. They allow you to make this mistake and they don't solve for it. You have some notes, some data, everything there. Something like Airtable as well might seem like a solution, where you have a record and you can view it in different ways and edit that record on many different views. But it actually makes this mistake easier to make. 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. When we can write anything anywhere, and we can even link them, this mistake is made time and time again. Now I will share with you the single easiest moment to recognize when this problem happens.

THE Mistake in Google Sheets: Unrelated columns or rows (or both!)

So in your Google Sheet, you might have some data like name, date, age, first name, and last name. Then you fill this in, right? This column will have all names and this other column will have first names and last names.

And these are related. Everything across this entire row is related to each other, and everything in this column is related to each other. This is all first names and we have a header that is perfectly fine. Now here is the mistake that people make:

You usually see someone make a smaller column. Then they will fill it with black and enter some kind of formula here. Example: =unique(A:A)

We have now created a column and a row that is not related. The simplest way to see the mistake is the black bar. This visual representation is exactly where we see this mistake time and time again.

I am fallible myself. I have done this many times. I have done this over the course of creating many sheets and it's so easy to fix!

Now, we think we're doing ourselves a favor, right? And it’s all because we're able to see the analysis in the same view as the data itself. But here we can make this change and we don't have to continue exacerbating this mistake.

Take the first formula we have and then create a new tab. Call it “unique.” Move the formula there by copying and pasting it. If you prefer to cut it, hit Command X and then cut and paste.

Now, what’s interesting about this mistake is that 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.

We know we now don't need anything over to the right of the F column.

Click Shift Command to the right and delete all these columns. We’ve improved our sheet immensely. We don't have any bar on the bottom that goes to the right and left, and we can always delete all the rows later.

Now we have a unique tab or sheet that includes all of the unique names here. But why is this a mistake? Read on to find out.

When you spot a spreadsheet mistake, do this...

I've had bosses make this mistake and it just turns into an absolute nightmare of a sheet to figure out where are we all looking. This mistake happens when in the course of discussion, you have to refer to single cells. Let’s say we don’t have the “unique” tab; we still have the data on column F. We find ourselves saying, “Oh, 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.

What I also recommend is this: I know it sounds weird but it's normal for me to have sheets that only have one column. I immediately go and delete every single column other than the A column. I have lots of tabs that have these one-column pieces of data and that is totally okay.

It's also totally okay to do some kind of analysis, like you want to count the uniques and you make use of a second column. But then we realize that the columns aren’t related to each other. This “Name” in A1 has nothing to do with the value or number in B1. This is the second way you can see this mistake.

If you have data in the header column that's not a header, then you have a problem. This is a slightly sort of gray area. If we have a header and move a row above it and have counts, it’s hard to see that this is a mistake.

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

This is not totally okay. Again, it’s a 50/50 gray area. But what I would do is put this on a different page. I would create a new tab and call it “data.” I would take this information and put it on the data page.

Take these information, these rows, and transpose them to the “data” tab. Then do =counta(Sheet1!8:8)

And now we have some data about our sheet! We have a summary of that data getting the best points and it's all labeled.

We don't have to 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. 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. This is the reason why spreadsheets exist.

The simplest way to catch a mistake in Google Sheets:

The simplest way to catch a mistake in Google Sheets is to review it. The moment you can see yourself making the mistake, or your boss, or any co-workers or even employees making this mistake, is when you have a column that is completely black. This is the number one way to see it.

How to fix this mistake in Google Sheets?

The actual fundamental way to see it is just knowing that a row should be 100% related and a column should be 100%  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 Airtable.

I hope you enjoyed this tutorial! Don’t make any sheets. Make Better Sheets.

Watch the video version:

More mistakes to avoid:

Get more Google Sheets Tutorials at BetterSheets.co

We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets. For only $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.