Unpivot a Small Monthly Table in Google Sheets Without Add-ons

What source layout does this Google Sheets unpivot formula require?

The Google Sheets unpivot formula requires project names in A2:A4, text month headers in B1:D1, and corresponding hours in B2:D4.

Enter this sample in A1:D4:

Project	Jan	Feb	Mar
Project Moonkite	12	15	18
Project Cloudturnip	8	0	10
Project Pebblefox	20	16	14

The project names are fictional, and the numbers represent hours. Type Jan, Feb, and Mar as text, not actual dates formatted to display month names.

No source value, project name, or month header may contain |. The formula uses that pipe character to separate fields.

Three projects multiplied by three months produces nine data rows. Unpivoting turns month columns into month entries; transposing would only rotate the grid.

How do you set up the unpivot formula without add-ons?

Place the Google Sheets unpivot formula in F2, add headers in F1:H1, and reserve F2:H10 for its nine-row, three-column output.

1. Confirm the source coordinates.
   Check that Project is in A1 and the final sample value, 14, is in D4. If the source starts elsewhere, adjust every source reference.

2. Add output headers.
   Enter Project in F1, Month in G1, and Hours in H1. The formula returns data without headers.

3. Clear the destination.
   Remove existing values and formulas from F2:H10 and unmerge that area. Keep the source table intact.

4. Enter this formula in F2.
   Press Enter once; do not fill down or copy across.

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&"|"&B1:D1&"|"&B2:D4),"|",FALSE,FALSE))

5. Check the output boundaries.
   The data should occupy F2:H10, beginning with Project Moonkite and ending with Project Pebblefox. Project Cloudturnip’s February zero should remain present.

6. Format hours if needed.
   Select H2:H10 and choose Format > Number > Number. Adjust decimal places as needed; formatting does not repair invalid source text.

The formula uses US-locale comma argument separators. Other spreadsheet locales may require semicolons.

What output should the sample monthly table produce?

The sample Google Sheets table produces nine project-month records in F2:H10, with the manually entered headers in F1:H1.

Project	Month	Hours
Project Moonkite	Jan	12
Project Moonkite	Feb	15
Project Moonkite	Mar	18
Project Cloudturnip	Jan	8
Project Cloudturnip	Feb	0
Project Cloudturnip	Mar	10
Project Pebblefox	Jan	20
Project Pebblefox	Feb	16
Project Pebblefox	Mar	14

The output follows source row order, taking all three months for one project before advancing to the next. The formula neither sorts months nor alphabetizes projects; keep source month columns chronological for calendar order.

Changing B2 from 12 to 13 updates the matching output hours. Edit source cells rather than individual output cells, which belong to one array formula.

Why do ARRAYFORMULA, FLATTEN, and SPLIT turn this monthly grid into project-month records?

ARRAYFORMULA, FLATTEN, and SPLIT work together by pairing each hours value with its project and month, then reshaping those pairs into separate records.

First, A2:A4&"|"&B1:D1&"|"&B2:D4 combines the vertical project names, horizontal month labels, and matching hours into a three-by-three array of text records. Joining all three fields before reshaping keeps their relationships together, rather than flattening hours alone and reconstructing their labels afterward.

Next, FLATTEN turns that array into one column, reading each row from left to right. Project Moonkite therefore contributes Jan, Feb, and Mar before Project Cloudturnip begins. Unlike transposing, this creates one entry per project-month combination.

SPLIT then separates each record at |, producing Project, Month, and Hours columns. Its first FALSE treats the delimiter as a complete string; its second preserves empty fields rather than removing them.

The enclosing ARRAYFORMULA supports the range-based calculation and expanded output, so one formula produces all nine rows without copied formulas.

How do ARRAYFORMULA, FLATTEN, and SPLIT unpivot the monthly grid?

ARRAYFORMULA, FLATTEN, and SPLIT unpivot the Google Sheets grid by building delimited records, flattening those records into one column, and separating their fields.

The expression A2:A4&"|"&B1:D1&"|"&B2:D4 combines the vertical project range, horizontal month range, and aligned three-by-three values range.

For the first project, the temporary records are:

Project Moonkite|Jan|12
Project Moonkite|Feb|15
Project Moonkite|Mar|18

These records initially occupy the corresponding values-grid positions. FLATTEN reads across each row before moving down, and SPLIT separates each record at the pipes.

The two FALSE arguments have different purposes:

• First FALSE: Sets split_by_each to false, treating the delimiter as a complete string. This matters if a replacement delimiter contains multiple characters.
• Second FALSE: Sets remove_empty_text to false, preserving empty fields instead of collapsing them.

ARRAYFORMULA enables the range-based calculation and expanded result. The temporary delimiter should not appear in the output.

How does the unpivot formula handle zeroes and blank hours?

The Google Sheets unpivot formula preserves zero hours and retains a project-month record with an empty Hours field when the source hours cell is blank.

Project Cloudturnip’s February zero represents a value. Clearing C3 instead leaves the February record present with blank hours; the formula should neither remove that record nor convert the blank to zero.

Define blank values before reporting:

Source hours	Possible meaning	Output treatment
0	Confirmed no hours	Keep zero
Blank	Not entered yet	Keep empty Hours field

For a small table, filter the generated Hours column to hide blanks if needed. Exclude blanks specifically, not zeroes.

Fill missing project names or month headers in the source. Preserved empty fields cannot compensate for missing identifiers.

How do you fix spill errors, extra columns, date numbers, and parse errors?

Fix Google Sheets unpivot errors by clearing blocked output cells, removing delimiter collisions, using text month headers, or correcting formula syntax.

Why does the formula show #REF!?

Symptom: Sheets says the array cannot expand because it would overwrite data.

Cause: Destination cells contain values, formulas, or merged cells. A formula displaying an empty string still occupies its cell.

Exact fix: Keep F2’s formula, clear every other cell in F2:H10, and unmerge that area. Alternatively, move the formula to an empty area with nine rows and three columns available. Do not clear the source.

Why does a project name create extra columns?

Symptom: Project Moon|kite splits into extra fields, shifting Month and Hours rightward.

Cause: Source data contains the delimiter.

Exact fix: Remove or replace the source pipe. If it must remain, choose a delimiter absent from every source value and replace all three delimiter strings.

If ~ occurs nowhere in the source, use:

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&"~"&B1:D1&"~"&B2:D4),"~",FALSE,FALSE))

Changing only the SPLIT delimiter is insufficient; inserted and splitting delimiters must match.

Why do month labels appear as date serial numbers?

Symptom: Month contains numbers instead of Jan, Feb, and Mar.

Cause: Real date headers have month-only display formatting; concatenation can expose their underlying numbers.

Exact fix: Replace B1:D1 by entering 'Jan, 'Feb, and 'Mar. The leading apostrophes enforce text and are not displayed. If actual dates must remain, revise the formula to convert dates explicitly to text.

Why does Sheets report a formula parse error?

Symptom: Sheets rejects the formula before producing an array.

Cause: Common causes include curly quotes or locale-incompatible argument separators.

Exact fix: Use straight double quotes. For a semicolon-argument locale, use:

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&"|"&B1:D1&"|"&B2:D4);"|";FALSE;FALSE))

Keep the quoted pipe characters unchanged.

How do you extend the formula for more projects or months?

Extend the Google Sheets unpivot formula by keeping project and values ranges aligned vertically and header and values ranges aligned horizontally.

The original references exclude entries outside A1:D4. To include a fourth project in row 5, use:

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A5&"|"&B1:D1&"|"&B2:D5),"|",FALSE,FALSE))

Four projects multiplied by three months produces twelve data rows, requiring F2:H13 when headers remain in row 1.

For another month, extend both the header and values ranges to the same ending column. Avoid entire-column references: extra source rows can generate unwanted records from empty cells. Bounded ranges make output size predictable.

When should you avoid this delimiter-based unpivot method?

Avoid delimiter-based unpivoting when source text can contain the chosen delimiter, exact data types must survive unchanged, or the table grows rapidly.

• Unpredictable text: Use a field-selecting reshape method, such as an index-based array formula, rather than joining and splitting strings.
• Strict type requirements: Intermediate text conversion does not guarantee original types or preserve source formatting. Use explicit type conversion and validation.
• Rapidly growing recurring data: Store entries in long format from the start, with one project-month record per row. A pivot table can provide the wide monthly presentation without making it the underlying storage layout.