When Your Formula Doesn’t Work: Formula Parse Errors in Google Sheets

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious.

In this post, I’ll explain what a Google Sheets formula parse error is, how to identify what’s causing the problem, and how to fix it.

What is a formula parse error?

Before we get into the different types of errors, you might be wondering what does formula parse error mean?

Essentially, it means Google Sheets can’t interpret your formula. It can’t fulfill the formula request so it returns an error message.

There are a variety of ways this can happen — everything from typos to mathematical impossibilities — and we’ll explore them all in detail below.

Understanding the meaning behind the error messages, and learning how to fix them, is a crucial step to becoming a formula pro in Google Sheets.

Auditing and Debugging Formula Parse Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

  1. An formula parse error message popup prevents me entering my formula
  2. I’m getting an #N/A error message
  3. I’m getting an #DIV/0! error message
  4. I’m getting an #VALUE! error message
  5. I’m getting an #REF! error message
  6. I’m getting an #NAME? error message
  7. I’m getting an #NUM! error message
  8. I’m getting an #ERROR! error message
  9. I’m getting an #NULL! error message
  10. Other strategies for dealing with errors
  11. Functions to help deal with formula errors in Google Sheets
  12. Help! My formula is STILL not working

Here’s a Google Sheet with all these examples in.

1. A formula parse error message popup prevents me entering my formula

You think you’ve finished your formula, so you hit enter and boom! You get slapped with a popup message box "Houston, we have a problem" or similar:

Formula parse error in Google Sheets

It’s reasonably rare that you’ll experience this, and it usually points to some fundamental problem with your formula.

For example, imagine that as you hit the Enter key, you also accidentally struck the “\” key (which is right above the Enter key) and inadvertently added that to the end of your formula:

Unwanted character causes formula parse error

This will result in the popup error message. It’s easily corrected by removing the unwanted character.

How to correct this error?

Try to avoid these in the first place by checking your formula prior to hitting enter. Make sure you’re not missing a cell reference and you don’t have any unwanted characters lurking.

2. I’m getting an #N/A error message. How do I fix it?

The #N/A formula parse error signifies that a value is not available.

#N/A error in Google Sheets

It happens most frequently when you’re using a lookup formula (e.g. VLOOKUP) and the search term isn’t found. This is exactly what has happened in the exact match VLOOKUP in the image above. The search term A-051 is not in our data table so the formula returns #N/A.

This formula is not wrong or broken, so we don’t want to delete it. However, it would be cool if you could display a custom message, something like “Result not found”, instead of #N/A error message, especially if you have a lot of these errors showing. It gives the spreadsheet user much more information and reduces confusion.

Thankfully we can:

How to correct an #N/A error?

Well, there’s this super handy formula IFERROR:

=IFERROR(original formula, value to display if the original formula gives an error)

In this VLOOKUP example, the full formula would look like this:

=IFERROR(VLOOKUP(Search Term, Table, Column Index, FALSE),”Search term not found”)

as shown in this example:

iferror and vlookup Formula parse error example

Instead of showing the #N/A formula parse error when a value is not found, the formula will output our custom message instead “Search term not found”.

3. I’m getting an #DIV/0! error message

This formula parse error happens when a number is divided by zero, which can occur when you have a zero or a blank cell reference in the denominator.

In layman’s terms, what this means is that we trying to compute something like this:

= A / 0

which has no meaning (because there is no answer, which, when multiplied by 0, would return A).

Read more about division by 0 here, although it gets super technical super quickly.

Division by 0 error

Another example is using a formula like AVERAGE with a blank range.

So, = AVERAGE(A1:A10)   will cause a #DIV/0! error if the range A1:A10 contains no numerical values.

How to correct an #DIV/0! error?

Well the first thing to do is determine why your denominator is evaluating to zero.

You can select the denominator and see what it is evaluating to by highlighting it in the formula bar, and seeing what the result is in the little popup box, as shown in this image:

Divide by 0 error evaluation

In this case, the formula in the denominator SUM(A1:A7) evaluates to 0, which causes the error. So check whether your denominator result is 0.

Next, check whether you have linked to blank cells or a blank range in your denominator. Then you can either fill in the blank cell or range, or select a different cell or range for your formula.

If your formula is correct and your cell/ranges are not unintentionally blank, then you’ll want to handle the #DIV/0! error. It looks unsightly and makes your spreadsheet look unfinished if you leave these errors floating around.

As with the #N/A error example, use the IFERROR formula to wrap your current formula and specify a result for when a #DIV/0! error occurs. You might want to output an error message, e.g. “Division by 0 error”, or maybe a specific value, e.g. 0:

Iferror to handle div 0 error

4. I’m getting an #VALUE! error message

This formula parse error typically occurs when your formula is expecting a certain data type as an input but receives the wrong type, for example trying to do math operations on a text value instead of a numerical value.

Spaces in your cells can also cause this error message.

In this example, cell B1 contains a space, which is a string value and causes the #VALUE! error because Google Sheets can’t perform a math operation on it, as seen in this error message:

value error in google Sheets

In general, Google Sheets do a pretty good job of coercing text into numbers when needed. If you enter a value into a cell with some spaces, format it as text and then try to do math on it, Google Sheets will actually force the text into a number and still perform the calculation.

Another cause of #VALUE! errors is mixing US and Rest of World date formats.

US dates have the form MM/DD/YYYY whilst the Rest of the World goes for DD/MM/YYYY. If you have a mix of the two and try to subtract them to get the number of days between them for example, you’ll get the #VALUE! error.

(In fact, it’s the same text/number issue happening underneath the surface. Dates are stored as numbers, but if you’re date is in the wrong format for the country setting for your spreadsheet, it’ll be stored as a text string and Google won’t know it’s meant to be a date.)

Value error caused by dates

Here the correct answer should have been 59, the number of days between the 28 Feb 2017 and the 31st Dec 2016.

How to correct an #VALUE! error?

The error message should give you some information on which part of your formula is causing the problem.

Search for any possible text/number mismatches, or cells containing errant spaces. If you click into a cell and the flashing cursor has a gap between itself and the element it’s next to, then you’ll have a space there.

Cells can look empty but still contain spaces:

Value error explained

Dates with spaces in the middle won’t work either:

Date Value error explained

5. I’m getting a #REF! error message

The #REF! formula parse error occurs when you have an invalid reference.

Missing reference: For example when you reference a cell in your formula that has since been deleted (not the value inside the cell, but the whole cell has been deleted, typically when you’ve deleted a row or column in your worksheet).

In this example, the original formula was = A1 * B1, but when I deleted column A, the formula went haywire because of the missing reference:

Ref error message

Another way that a formula can refer to missing references is when you copy a formula with a relative range at the edge of your sheet. When you copy and paste, it’s possible the relative range moves as if it were outside the bounds of the sheet, which is not allowed and will cause a #REF! error.

In this example, the sum function adds the cells in the 3 rows above. When I try to copy-paste the sum function into a new cell with fewer than 3 rows above, it’ll give me the #REF! error:

Ref Formula parse error caused by copy

Lookup out of bounds: You’ve probably seen the #REF! error if you use lookup formulas frequently, when you’ve tried to return a value outside of ranges you’ve specified. In this VLOOKUP example, I’m trying to return an answer from the 3rd column of a search table that only has 2 columns:

Ref error message lookup out of bounds

Circular dependency: You’ll also get a #REF! error when a circular dependency is detected (when the formula refers to itself).

Ref error message circular dependence

In this example, I have numbers in the range A1 to A3, but the SUM formula in cell A4 tries to sum from A1 to A4, which includes itself. Hence, we have a circular argument where cell A4 is trying to be both an input and output cell, which is not allowed.

How to correct a #REF! error?

First of all, read the error message to determine what kind of #REF! error you’re dealing with. This should give you a big hint on how to correct the error.

For deleted references, look for the #REF! error is inside your formula, and replace the #REF! with the correct reference to a cell or range.

For out-of-bound lookup errors, look through your formula carefully and check your range sizes against any row or column indexes you’re using.

For circular dependencies, find the reference that’s causing the problem (i.e. where you refer to the current cell inside your formula too) and modify it.

6. I’m getting a #NAME? error message

The #NAME? formula parse error signifies a problem with your formula syntax.

The most common reason for this error is a misspelling in one of your function names.

In this example, I misspelt the SUM function as SUMM, which Google Sheets didn’t recognize, so returned an error:

Sum error from misspelling

Another reason for a #NAME? error is referencing a named range which doesn’t actually exist, or is misspelt.

So

=SUM(profit)

will give you a #NAME? error if the named range profit does not exist

Missing quotation marks around a text value, as shown in this simple formula, will also cause a #NAME? error:

=CONCAT(“First”,Second)

(The word Second is missing quotation marks.)

How to correct an #NAME? error?

Check your function names are correct. Use the function helper wizard to reduce the chances of errors happening, especially for the functions with longer names. As you start typing your formula, you’ll see a menu of functions, which you can select with the up and down arrows and Tab.

Check you have defined all named ranges before using them in your formulas and that they all have the correct spellings.

Check any text values are entered with the required quotation marks.

Lastly, have you missed the colon in your range references? It’ll be obvious because it won’t be highlighted correctly.

This formula =SUM(A1A10)

is missing the colon between A1 and A10 and will throw a #NAME? error.

It should of course read =SUM(A1:A10)

7. I’m getting an #NUM! error message

The #NUM! formula parse error is shown when your formula contains numeric values that aren’t valid.

The classic example is trying to find the square root of a negative number, which isn’t allowed:

Num error in google sheets

(For any math geeks out there, you’ll know that you can resolve square roots of negative numbers with imaginary numbers, but these are outside the realm of spreadsheets.)

Some other functions that can result in #NUM! error messages are the SMALL and LARGE functions. If you try to find the smallest n-th value in your dataset, where n is outside the count of values in your dataset, you’ll get a #NUM! error.

For example, you ask Google Sheets to find the 10th smallest number in a dataset that only has 5 values in it:

Num error caused by small function

(Why this doesn’t return a #REF! error like the VLOOKUP out of bounds example, I don’t know.)

How to correct a #NUM! error?

You need to check the numeric arguments in your formula. The error message should give you some hints about which part of the formula is causing the issue.

8. I’m getting an #ERROR! formula parse error message

This formula parse error message is unique to Google Sheets and doesn’t have a direct equivalent in Excel. It means that Google Sheets can’t understand the formula you’ve entered, because it can’t parse the formula to execute it.

For example, if you manually type in a $ symbol to refer to an amount, but Google Sheets thinks you’re referring to an absolute reference:

Error Formula parse error

or you’ve missed a “&” when concatenating text and numerical values:

Error error concatenation

In this case the formula should be: =”Total “&sum(A1:A3)

Another case, caused when we messed up the closing brackets of a formula:

Error Formula parse error

How to correct an #ERROR! error?

Carefully check your formula for accuracy.

You want to ensure you’ve got the correct number of brackets and correct join syntax between text and numerical values (e.g. using “&”).

When you want to show values with currency symbols or as percentages, don’t manually type in the “$” or the “%”. Instead enter a plain number and then use the formatting options to change it to the style you want.

9. I’m getting an #NULL! error message

I haven’t been able to recreate a #NULL! formula parse error in the wild but theoretically, it exists!

Null Formula parse error

(If you have one showing in your sheet, let me know! I’d love to update this article with an example here.)

10. Other strategies for dealing with a formula parse error

Look for red highlighting in your formula as this will help identify the source of your error e.g. in the case of too many brackets, the extra, superfluous ones will be highlighted in red.

Peeling back the onion: this is a technique to debug errors for long, complex formulas. Unwrap the outer functions in your formula one-by-one, until you get it working again. Then you can start to add them back one-by-one again, and see exactly which step is causing the issue and fix that.

Different syntax in different countries: Some European countries will use semi-colons “;” in place of commas “,” so this could be a cause of your error. Compare these two formula, which have identical inputs and outputs, but the syntax is different for users in different countries (locales).

=ArrayFormula(VLOOKUP(A1;Sheet2!A:I;{2\3\4\5\6\7\8};FALSE))

is the same formula as this:

=ArrayFormula(VLOOKUP(A1,Sheet2!A:I,{2,3,4,5,6,7,8};FALSE))

(This is an example of a VLOOKUP returning multiple values (an array) instead of just a single value.)

Pro tip:

Use apostrophe at the start of a formula to turn it into a text string, which won’t execute. This is sometimes useful for seeing your whole formula for debugging, keeping a copy of your formula so you can copy and paste bits of it elsewhere for testing.

11. Functions to help deal with formula parse errors in Google Sheets

A few other functions related to formula parse errors are worth knowing about.

In fact, there is even a function to generate #N/A errors. It’s of limited use, but can be helpful for doing data validation in more complex formulas.

=NA()    will output an #N/A error. (Google Docs Help on NA)

=ERROR.TYPE(value)    will return a number corresponding to the error type:

  • 1 for #NULL!
  • 2 for #DIV/0!
  • 3 for #VALUE!
  • 4 for #REF!
  • 5 for #NAME?
  • 6 for #NUM!
  • 7 for #N/A
  • 8 for all other errors

(Google Docs Help on ERROR.TYPE)

=ISNA(value)
checks whether a value is the error #N/A, and will give the output TRUE for a #N/A error and FALSE otherwise. (Google Docs Help on ISNA)

=ISERR(value)
checks whether a value is any error other than the #N/A error. (Google Docs Help on ISERR)

=ISERROR(value)
checks whether a value is an error, and will give the output TRUE for any error. (Google Docs Help on ISERROR)

These functions can be summarized in the following table:

#N/A error functions

13. Help! My formula is STILL not working

Take a deep breath, don’t panic! There’s an army of Google Sheets super users out there who would love to help you fix your issue, free of charge, in the active help forums.

Try posting your problem into the forum and someone will likely help you out.

To make it easier for people to help you, please share your Google Sheet (either view-only or create a redacted copy if sharing is a concern), what error message you’re getting and what you were expecting the correct answer to be.

Google Sheets Help Forum

Unpivot In Google Sheets With Formulas, Or How To Turn Wide Data Into Tall Data

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

We want to transform that data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

Much harder than going the other direction, pivoting tall data into wide data tables.

This article looks at how to do it using formulas, which is challenging and obtuse.

The formulas are complex and difficult to read so it’s hard to recommend this method in a production setting.

But it’s a fascinating look at advanced formulas in Google Sheets and I’m certain you’ll learn something new along the way.

If you need to do this in a production setting, then you might want to consider using the Apps Script code or example sheet from the first answer of this Stack Overflow post.

But if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets – Solution 1

We’ll use the wide dataset shown in the first image at the top of this post, in Sheet1 of our Google Sheet.

Remember, what we’re trying to do is transform the wide data table into the tall data table. The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.

Template

Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Customers Column

To start, create a second Sheet and add a simple header row in row 1, with “Customer”, “Product” and “Value” in cells A1, B1 and C1 respectively.

Let’s create an array formula to populate the customers column. In cell A2, enter this:

=COUNTA(Sheet1!$1:$1)

This formula gives the count of the number of columns — 4 — in our wide dataset (assuming cell A1 in our original dataset is empty, per the first image of this post).

QUICK NOTE: when copy-pasting these formulas into your own Google Sheets, paste them directly into the formula bar to avoid any issues.

Similarly, this next formula would give the count of the number of rows — 3 — in our wide dataset (again assuming cell A1 in our original dataset is empty, per the first image of this post).

=COUNTA(Sheet1!$A:$A)

Multiplying these two together gives us the number of values in our table — 12 — which corresponds to the number of rows we’ll need in our new tall data table:

=COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1)

So let’s create those 12 rows!

Wrap this with the SEQUENCE function, starting from 1:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

Now divide that by the count of rows:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)

Hmm, it gives an answer of 0.3333333 but we’ve lost our 12 rows…

…so turn it into an Array Formula silly!

=ArrayFormula(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))

Ah, that’s better. “But how does it help us?” I hear you ask.

Let’s round all those decimals up to the nearest integer, like so:

=ArrayFormula(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)))

Nice!

We now have the column vector 1,1,1,2,2,2,3,3,3,4,4,4 with repeating positions, which is exactly what we needed.

For the moment, leave this formula alone and let’s move to cell B2 to construct the next piece. We want to create a table of the column headings that we can “lookup” with those repeating positions. Don’t worry, it’ll make more sense in a moment!

Ok, so start with this formula in B2:

=ArrayFormula(COLUMN(Sheet1!$1:$1))

And try this formula in B3:

=ArrayFormula(Sheet1!$1:$1)

Can you see what we’re doing yet?

Let’s combine these in cell B2 as follows:

=ArrayFormula({COLUMN(Sheet1!$1:$1);Sheet1!$1:$1})

and delete the formula in cell B3.

The output should look the same, but it’s created with a single formula.

Now we can use the HLOOKUP function to lookup those positions into this data array we’ve created.

Change our formula in cell A2 to:

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)),{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

It’s nearly right, but the answer is offset slightly. Hmm.

Ah ok, it’s that blank cell in A1 of the original data that we didn’t account for. Our repeating positions really start from 2. It’s a simple fix to just add 1 to them.

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

That’s the customers populated in column 1.

What about the products in column 2?

Products Column

Well, it’s an almost identical formula, so I’ll just share it here and leave it to the reader to use the Onion Method to build it in steps.

Actually, no I won’t, that’s just me being lazy. Let’s walk through it together.

It’s a similar idea, but it looks a little different because we do a vertical lookup.

So, if you haven’t already, clear out cells B2 and B3.

Start with this SEQUENCE formula in cell B2:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

This time we want a sequence that looks like 1,2,3,1,2,3,1,2,3 etc. i.e. repeating. This calls for the MOD squad, I mean MOD function.

=MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A))

Oops, make it an Array Formula:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A)))

Ah, that’s better. But it gives 1,2,0,1,2,0 etc. so it’s not quite right. Fix the ordering by subtracting 1 from the dividend of the MOD function:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A)))

Now we have 0,1,2,0,1,2 etc.

Add 2 to this to get the repeating positions we want 2,3,4,2,3,4 (again, we start from 2 to account for the blank cell in A1 of our original dataset).

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2)

Leave this formula sitting pretty for a moment, and begin a new one in cell C2. Build an array for the vertical lookup with this formula (feel free to build in steps, I’m jumping straight to the array version):

=ArrayFormula({ROW(Sheet1!$A:$A),Sheet1!$A:$A})

Now we can combine this into the formula in cell B2, using a VLOOKUP:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2))

Woohoo!

There’s our products in repeating order and paired correctly with the customer column.

That leaves the values associated with each pair.

Values Column

Thankfully this is much simpler, using a standard INDEX / MATCH / MATCH construction to look up each pair.

The row offset in the INDEX function is found by matching the product with the product categories in column A of our original data, i.e.

=MATCH(B2,Sheet1!$A:$A,0)

The column offset is found by matching the customers, i.e.

=MATCH(A2,Sheet1!$1:$1,0)

Plug these both into the INDEX function:

=INDEX(Sheet1!$1:$1000,MATCH(B2,Sheet1!$A:$A,0),MATCH(A2,Sheet1!$1:$1,0))

which gives the value of 61 for the first pair, Customer 1 and Product 1.

Drag this formula down the column to fill in all the rows.

“Wait, what? Where’s the array formula? Can’t I just wrap this INDEX / MATCH / MATCH with an array formula wrapper?”

No bueno, I’m afraid.

The INDEX function does not play well with the Array Formula, so this option cannot be turned into an array formula.

Be patient, in solution 2 we’ll generalize this to use an array formula, but we have to approach it in a different, more verbose way.

Unpivot in Google Sheets – Solution 2

Leaving the Customer and Product array formula columns well alone, let’s focus purely on the Values column.

We left solution 1 with a somewhat unsatisfactory INDEX / MATCH / MATCH formula for the values that, ahem, had to be dragged down the column — oh the horror! — because it wasn’t an array formula.

Gasp! We don’t like such manual work.

So let’s create an array formula to grab the values we need.

Think of the standard VLOOKUP:

=VLOOKUP( search_key, data, column_index, false )

The search_key is the repeating array 2,3,4,2,3,4,2,3,4 etc. created using the same formula construction as the first part of the Products formula from Solution 1.

The column_index is the repeating array 2,2,2,3,3,3,4,4,4, etc. created using the same formula construction as the first part of the Customers formula from Solution 1.

When you plug these into the VLOOKUP, you’re searching for 2 and returning column 2, then searching for 3 returning column 2, searching 4 returning column 2, then searching 2 returning column 3, etc.

In other words, traversing the array of values and grabbing each one in turn.

The data needs to be setup by adding a search column at the front, which is done using the curly brackets array literal construction, like so:

=ArrayFormula({ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000})

All that’s left is to combine them into the VLOOKUP, like so:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1))

Voila! Clear as mud, huh?

Unpivot in Google Sheets – Solution 3

In this solution, all we do is combine the three columns together into a single, giant array formula, using the curly bracket array literal construction.

Starting with the three columns combined:

= ArrayFormula({ Customer_Formula, Product_Formula, Values_Formula })

Next, we’ll wrap it with a QUERY function to remove null values:

= ArrayFormula( QUERY( { Customer_Formula, Product_Formula, Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" ))

The full array construction, with a static header row added, is:

=ArrayFormula( {"Customer","Product","Value";
QUERY( { Customer_Formula , Product_Formula , Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" )})

We can then simply plug in the Customer_Formula, Product_Formula and Values_Formula to create a one-stop shop for unpivoting our data:

=ArrayFormula({"Customer","Product","Value"; QUERY({HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1)
},"SELECT * WHERE Col3 IS NOT NULL")})

Unpivot in Google Sheets – Solution 4

This one really blew my mind when I first saw it and picked it apart.

Kudos to this person on Stack Overflow for the original amazing answer.

I’ve modified it slightly, but have merely contributed a minor update to an ingenious and original solution.

Here it is, in all it’s mysterious detail:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )), , 500000)), , 500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

First off, what on earth are those fish 🐠 and chili peppers 🌶 doing in this formula? Is this some kind of joke?

No, no, my friend. Read on and you’ll find out!

But before we do that, let me show you the amazing trick with the QUERY function that is key to this formula.

Taking our dataset again:

Unpivot in Google Sheets wide table

Try this formula in cell H1:

=QUERY(A1:E4,"SELECT A",4)

See what it does?

Query headers trick

It joins the values in column A into a single string, because we’ve told the QUERY function to treat all 4 rows as headers. Crazy!

Even better, you can skip the SELECT statement altogether, like this:

=QUERY(A1:E4,,4)

which results in all of the columns being concatenated:

Query headers trick

Now that is interesting!

And it’s at the heart of how this crazy formula works.

Let’s build it up in steps, following the Onion Method.

The innermost IF function is (note the value_if_false argument is empty):

=ArrayFormula(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", ))

which gives the following output:

Array If formula

For every row of data, the formula joins them such that each cell has a unique combination of product, customer and value.

Next we transpose this array and join using the funky QUERY-header row trick above:

=ArrayFormula(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000))

This gives a #REF! error, with the message “Result was not automatically expanded, please insert more columns (699).”

The array output is too wide for our current Sheet.

Wrap it with a transpose function to fix this and get all the data in a single column:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)))

Use a second QUERY function with this headers trick to bring these values together:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)))

Now we basically just split this up based on the fish “🐠” and chili pepper “🌶” symbols that we used to separate the data packets.

Here’s the first split and transpose:

=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶")))

By now, our data looks like this, which is getting closer:

Unpivot in Google Sheets

Use the TRIM function to fix those unsightly spacing issues.

Next, split it again across the tropical fish:

=ArrayFormula(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶"))),"🐠"))

Unpivot in Google Sheets

Nearly there now!

Remove the #VALUE! error with an IFERROR wrapper function. Use a QUERY wrapper to re-order the rows and columns as required. The OFFSET removes a blank row from showing up in the table. The formula now looks like this:

=ArrayFormula(QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1"))

And the output like this:

Split function Google Sheets

The final step is borrowed from Solution 3 above, namely combining a static header row with array literals.

=ArrayFormula({"Customer","Product","Value"; MAIN_FORMULA })

Now we can insert our formula into this construction, in place of the MAIN_FORMULA placeholder:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )), , 500000)), , 500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

Crazy formula in Google Sheets

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.