Google Sheets has evolved beyond basic spreadsheets. With the introduction of dynamic array formulas, users can now manipulate and analyze data more efficiently. These formulas automatically expand results across multiple cells, streamlining data workflows and reducing the need for repetitive tasks.
In this blog, we’ll explore five powerful dynamic array formulas you can start using today—along with real-life use cases for each.
1. SEQUENCE: Automatically Generate Number Series
What it does: Creates a list of numbers in a range of rows and columns.
Use cases: Generating a calendar, auto-numbering tasks, or creating custom date ranges.
=SEQUENCE(7,1,1,1)
This formula generates numbers 1 to 7 vertically. You can also customize rows, columns, start numbers, and step values.
Check the formula in the spreadsheet
Pro tip: Combine SEQUENCE with TEXT to generate date ranges:
=ARRAYFORMULA(TEXT(SEQUENCE(7, 1, TODAY(), 1), “dddd”))
2. SPLIT: Break Strings into Columns or Rows
What it does: Splits text based on a defined delimiter.
Use case: Splitting full names, parsing CSV data, or extracting data from combined fields.
=SPLIT(“John Doe”, “ “)
=SPLIT(A5, ” “)
This returns “John” and “Doe” in separate columns. You can split by commas, semicolons, slashes, etc.
Bonus: Use SPLIT with ARRAYFORMULA to apply it across a column.
3. FLATTEN: Collapse a Range into a Single Column
What it does: Converts a 2D array (rows and columns) into a single vertical list.
Use case: Normalizing data across multiple rows/columns into one list for analysis or filtering.
=FLATTEN(A5:C7)
Useful when you need to analyze or count values that are scattered across a grid.
4. BYROW: Apply a Custom Function to Each Row
What it does: Applies a custom LAMBDA function to each row.
Use case: Calculating row totals, applying conditional logic, or creating row-based summaries.
=BYROW(A5:D7, LAMBDA(row, SUM(row)))
This will return a column with the sum of each row.
Advanced: Use IF inside the LAMBDA to include conditions per row.
5. BYCOL: Apply a Custom Function to Each Column
What it does: Similar to BYROW, but works column-by-column.
Use case: Generating averages, max values, or other summaries across multiple columns.
=BYCOL(A5:D7, LAMBDA(col, SUM(col)))
A fast way to summarize columns without writing separate formulas for each one.
Final Thoughts
Dynamic array formulas in Google Sheets aren’t just for techies or advanced users—they’re incredibly useful for anyone who wants to save time and create smarter spreadsheets. By incorporating these five formulas into your workflow, you can build more flexible, scalable, and insightful tools.
Try combining these with FILTER, UNIQUE, and ARRAYFORMULA for even more power.
Stay tuned for more advanced formula tips from Sheetgo!
