Tackling Google Sheets Formulas. 3 Steps to Resolve #ERRORS

Hey, it’s Max Makhrov! 👋

I’m a professional Sheets & Script developer, and I’m here to help you solve all your Spreadsheet headaches.

Photo by Sam Field on Unsplash

In this article, we’ll cover a few common problems that you might be facing:

• Finding it difficult to audit your formulas for errors or inconsistencies
• Frustrated with inconsistent results due to formatting issues

And as always, if you have any questions or comments, drop them below.

Errors. I make 5 to 10 times more mistakes than the average Google Sheets user. However, I’ve developed a workflow that helps me effectively deal with these errors until I resolve them.

I have prepared for you three steps to handle formula errors. Let’s tackle these errors together! 😎

1️⃣ Read Red-Triangle-Message

Can you spot the error in this formula causing a REF error?

Hint: The values in cells A1 and B1 are not formulas. The answer is behind the red triangle...

A critical step in resolving formula errors is to read the error message in the red triangle in the cell with the error. Three examples of why it’s helpful to read the error message:

1. “Division by zero” — adjust your formula to avoid dividing by zero.
2. “Invalid argument” — look at the argument you’ve provided to the formula, rather than searching for a problem with the formula itself.
3. “Array result was not expanded because…”. Some error messages are triggered by common mistakes that are easy to overlook, such as a typo in a cell reference or a missing quotation mark.

Hover your mouse over the cell with the red triangle.

As you may have guessed, I have a common error:

Array result was not expanded because it would overwrite data in C2.

It was a space in C2.

By reading error messages when you encounter formula errors, you can often save time and quickly identify the source of the problem. In my case, to fix the formula, I need to delete space from C2.

2️⃣ Try Formula Smaller Parts

Sometimes, you have a big formula with a few nested functions. The error message tells nothing or even misleads you. Here’s one sample error I get a lot during my work:

I really cannot fix it, reading the error text:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

It is almost the same as saying:

Your formula is wrong, try something else

We can still easily crack the formula. The trick is to mentally simplify the formula, and see what the formula parts are.

Here you see the formula becomes simpler all the time. The final version is an array literal, consisting of 2 parts: {someHeaders ; importRangeResults}. We can try these formula parts separately.

This formula will give “someHeaders”:

={"Holiday","Date","Country","Type"}

And it works just fine. No error here.

This formula will calculate “importRangeResults”:

=IMPORTRANGE(
   "1hc-AygVMeBi57awts_O5bn16-HMlXebiQsOPfbNRuZo",
   "'Sheet1'!A1:D")

We put it in a separate cell, and run it with an “=” sign. And here it is, the real source of our error:

Now the error text becomes super helpful and straightforward:

You need to connect these spreadsheets. The first time the destination spreadsheet pulls data from a new source spreadsheet, permission is needed to be granted.

The error text design does not look like an error. The message invites us to tap the green button “Allow access” and resolve the error.

💡Pro Tip: use “VSTACK” function instead of braces “{}”:

“VSTACK” works much better for stacking arrays because it shows individual errors for each part.

#3. Check Number Formats

Please take a look at this “#N/A” error in F2 cells:

Why does my VLOOKUP formula return ‘#N/A’ even when everything seems correct? Here’s a hint for you:

notice that “date” in B2 is aligned left, while date in E2 is aligned to the right.

In spreadsheets, numbers are aligned to the right by default. Text is aligned to the left by default:

If you work with LOOKUP functions, this fact matters. The thing is Spreadsheets have to follow the convention of all computers. This convention is using of a binary system. The binary system makes calculations over numbers fast, and calculations over text slow:

Compare the text “Zero” and the number “0”

The number “0” can be represented using a single bit, which can take on a value of either 0 or 1. In most computer systems, the bit that represents 0 is set to 0, and the bit that represents 1 is set to 1.

The word “zero” is a sequence of characters or symbols and its representation in terms of bits depends on the character encoding used. In ASCII encoding, which is a widely used character encoding system, the word “zero” would be represented using 5 bytes or 40 bits, with each character being represented by 8 bits. However, in other character encoding systems, such as Unicode, the number of bits used to represent the word “zero” may be different.

The word “Zero” is at least 40 times heavier than the number “0”. This fact forced creators of Spreadsheets not to use text if possible. Here’s what we have:

• numbers,
• dates (calculated as numbers),
• booleans (calculated as numbers: 0 or 1),
• and text.

In Google Sheets, cells can also contain objects, like images, spark charts, and smart chips.

💡Pro Tip: you can create a named function to determine what data type is in cells:

To use this formula, copy my sample Spreadsheet here

Let’s now go back to my error:

Can you get the reason for this error? Data types are different in cells B2 and E2. The “VLOOKUP” formula uses E2 as a key and tries to find that key in column B. Those values look the same for a user. The computer treats them differently:

• B2 = text,
• E2 = date (saved as a number).

The computer cannot match texts with dates. This is why the formula shows “#NA”. To fix this error, I’ll need to change the values in the column “B” to dates.

💡Pro tip: covert both parts into a text using “TO_TEXT” function.

My sample is short, but it should show you the importance of double-checking your data. Knowing this will save you hours one day!

Final thoughts

Great to see you here! 👋

I just shared three steps that help you break through your formula errors! If you follow these steps in the order I gave you, you’ll have plenty of time to think and learn.

Let me know what you think in the comments below! And stay tuned for the next part of the series, where we’ll dive into the final step not covered here:

• Step #4. Ask for the Help: AI, Google, and Community

Talk soon! 👋