Spreadsheets & Data

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

How do I unpivot a small monthly project table into a refreshable three-column list in Google Sheets without add-ons?

Put the formula in an empty area. Use A2:A4 for project names, B1:D1 for text month headers, and B2:D4 for hours. It returns a Project, Month, and Hours row for each project-month combination.

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

What you need

  • Google Sheets with the source table in A1:D4
  • Text month headers and source values without pipe characters
  • An empty, unmerged F2:H10 area for the output

What source layout does this Google Sheets unpivot formula require?

Put project names in A2:A4, text month labels in B1:D1, and the corresponding hours in B2:D4. Each month belongs above its hours column, and each project belongs beside its hours row.

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; the hours are sample values. Type Jan, Feb, and Mar as text, not as dates with month-name formatting. The formula returns the labels in the form used by the source cells.

Do not put the | character in project names, month headers, or hours values. The formula uses pipes to mark the boundaries between fields. A pipe in a source value can be mistaken for another boundary.

This grid has three projects and three months, so it represents nine project-month records. Unpivoting puts the months into rows, with one record for every project and month. Transposing the grid only turns it around; it does not make a separate record for each project-month pair.

How do I split a cell in Google Sheets?

Enter the formula in F2, where it can return three columns and enough rows for all project-month combinations. Google’s SPLIT function breaks text at a delimiter. FLATTEN takes a range or array and lays it out in a single column.

  1. Confirm the source coordinates.
    Verify that Project is in A1 and 14, the final sample value, is in D4. If your table starts in different cells, update the formula’s source references.

  2. Add output headers.
    Type Project in F1, Month in G1, and Hours in H1. The formula supplies the data rows; add these labels yourself.

  3. Clear the destination.
    Clear any values or formulas from F2:H10 and unmerge cells in that range. Leave the source table as it is.

  4. Enter this formula in F2.
    Press Enter once and leave the formula in F2. Do not drag it down or copy it across.

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&"|"&B1:D1&"|"&B2:D4),"|",FALSE,FALSE))
  1. Check the output boundaries.
    The results should occupy F2:H10, starting with Project Moonkite and ending with Project Pebblefox. Check that Project Cloudturnip’s February value is zero.

  2. Format hours if needed.
    Select H2:H10, then choose Format > Number > Number. Set the decimal places if needed. Number formatting will not repair invalid text in the source.

The formula uses commas between arguments. Some spreadsheet locales expect semicolons instead. If yours does, use the alternate formula in the troubleshooting section.

What output should the sample monthly table produce?

The sample returns nine project-month records in F2:H10. Enter the column labels separately 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

Rows in the output follow the source order. The formula lists all three months for the first project, then moves to the next project. It does not sort months or alphabetize project names. Arrange the source month columns in calendar order if you want the results in that order.

To check the output row count, multiply three projects by three months: the result is nine records. Change B2 from 12 to 13, and the matching output value should update because B2 is inside the referenced range. Edit the source table to make changes. A single array formula fills the output; individual formulas do not populate each result cell.

A long-format list is useful when a report needs a row for every project and month. It can also feed views such as counting open tasks by owner with Google Sheets QUERY or calculating a dashboard completion rate without averaging percentages. The other spreadsheets and data guides cover more ways to prepare this kind of input.

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

The formula first joins each project, month, and hours value into a text record. It puts those records into one column, then splits them into separate fields. Keeping the labels and value together during that reshaping makes each output row easier to interpret.

The expression 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 the fields first keeps every project, month, and value together. If you flattened only the hours, you would have to match each value with its labels afterward.

For the first project, the temporary records look like this:

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

FLATTEN puts the array in one column, reading across a row before moving down. That puts Project Moonkite’s Jan, Feb, and Mar records ahead of Project Cloudturnip’s. SPLIT then uses the pipes in each record to make the Project, Month, and Hours fields.

The two FALSE arguments set separate options. The first makes split_by_each false, so Sheets treats the delimiter as one complete string. The second makes remove_empty_text false, which keeps empty fields instead of dropping them. As a result, a project-month row remains even when its source hours cell is blank.

How does the unpivot formula handle zeroes, blanks, and month labels?

The result keeps zero as a value and leaves a blank source hours cell empty in its project-month record. Enter month labels as text so they remain readable in the output.

Project Cloudturnip’s February entry, 0, means zero hours. Clear C3 instead, and the February row stays in the output with no Hours value. Whether a blank should count as zero depends on the rules for your report.

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

For this small grid, filter the generated Hours column to hide blank cells. Do not filter out zeroes. Add missing project names or month headers in the source table; preserving empty fields does not fill in missing identifiers.

Actual dates in the month-header cells can also cause trouble. A cell may display an abbreviated month name, but concatenation can return the date’s underlying number. For this example, enter 'Jan, 'Feb, and 'Mar as text headers. The leading apostrophe makes the entry text, but does not appear in the cell display. If the source must keep actual dates, revise the formula to convert them explicitly to text.

The array formula owns its result cells, so those cells cannot be edited as independent records. Change the source table to update the linked output. For a separate editable copy, copy the output and paste values only into another area.

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

Start by checking whether anything blocks the output area. Then inspect source text for the delimiter and confirm that the formula has straight quotes and the argument separators your locale expects.

Why does the formula show #REF!? Sheets can report that the array cannot expand when destination cells contain values, formulas, or merged cells. A formula that returns an empty string still occupies a cell. Keep the formula in F2, clear the other cells in F2:H10, and unmerge that range. If needed, move the formula to an empty area with room for nine rows and three columns. Do not change the source table.

Why does a project name create extra columns? SPLIT treats a delimiter inside a value as a field boundary. For example, Project Moon|kite has a pipe in the project name. Remove that pipe or replace it in the source. If it needs to stay, choose a delimiter that does not appear in any source value, then replace all three delimiter strings in the formula. For example, if ~ is absent from the source, use:

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

Use the same delimiter in both places. The strings inserted between fields must match the string SPLIT uses to separate them.

Why do month labels appear as numbers? The header cells may contain actual dates formatted to show only the month. Concatenation can reveal the underlying date numbers. Replace B1:D1 by entering 'Jan, 'Feb, and 'Mar. If you need actual dates in the source, revise the formula to convert them explicitly to text.

Why does Sheets report a formula parse error? Curly quotes or separators that do not match your spreadsheet locale can stop the formula from parsing. Use straight double quotes. If your locale separates arguments with semicolons, enter:

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

Leave the quoted pipe characters as written.

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

When adding projects, extend the project and hours ranges together. For additional months, extend the month-header and hours ranges together. Only cells inside the formula’s references appear in the output.

To add a fourth project in row 5, use:

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

Four projects and three months make twelve data rows. Since the headers remain in row 1, the output range must extend from F2:H13. To include another month, extend both the header and values ranges to the same final column.

I would skip whole-column references for a table this small. Extra source rows can create unwanted records from empty cells, while bounded ranges make the output size predictable. If the table grows often or source text may contain the delimiter, maintaining a delimiter-based formula becomes less attractive. An index-based reshape avoids joining fields into strings. For recurring data, it is often simpler to keep one project-month record per row from the start.

Sources

Esc