COMBINE_ALL
Combine and flatten multiple ranges into a single column, excluding empty cells.
=COMBINE_ALL(range1, [range2], ...)Returns: any[][]
Overview
COMBINE_ALL takes one or more ranges from your spreadsheet and merges all their non-empty values into a single vertical column. This is invaluable when you have data scattered across multiple columns, sheets, or non-contiguous areas and need to consolidate everything into one unified list.
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
range1 | Range | Yes | The first range of values to include. |
range2, ... | Range | No | Additional ranges to combine (optional, any number). |
Examples
Combine two columns into one list
Merge product names from column A and column C into a single vertical list.
=COMBINE_ALL(A1:A4, C1:C3)Input
| Apple | Mango | |
| Banana | Peach | |
| Cherry | Grape | |
| Date |
Output
| Apple |
| Banana |
| Cherry |
| Date |
| Mango |
| Peach |
| Grape |
Consolidate team member names
Combine team members from three department columns into one master roster.
=COMBINE_ALL(B2:B5, D2:D5, F2:F5)Input
| Alice | Dave | Grace | ||
| Bob | Eve | Hank | ||
| Carol | Frank |
Output
| Alice |
| Bob |
| Carol |
| Dave |
| Eve |
| Frank |
| Grace |
| Hank |
Flatten a rectangular range
Extract all non-empty values from a 3x3 block into a single column.
=COMBINE_ALL(A1:C3)Input
| Red | Green | Blue |
| Yellow | ||
| Orange | Purple |
Output
| Red |
| Green |
| Blue |
| Yellow |
| Orange |
| Purple |
Merge survey responses from multiple questions
Combine free-text responses from three survey question columns into one analysis-ready list.
=COMBINE_ALL(B2:B50, C2:C50, D2:D50)Input
| Great service | Fast delivery | Good price |
| Friendly staff | Fair pricing | |
| Quick shipping |
Output
| Great service |
| Friendly staff |
| Fast delivery |
| Quick shipping |
| Good price |
| Fair pricing |
Build unified email list from multiple sources
Merge email addresses from three different sheet tabs into a single list for campaign targeting.
=COMBINE_ALL(Sheet1!A2:A100, Sheet2!A2:A50, Sheet3!B2:B30)Output
| alice@example.com |
| bob@test.com |
| carol@sample.org |
Use Cases
Master contact list creation
Combine email addresses from multiple lead source columns into a single deduplicated list for campaign targeting.
Tag consolidation
Merge tags from multiple content category columns into one list for tag cloud generation or frequency analysis.
Multi-source inventory
Combine product lists from multiple warehouse inventory sheets into a single master product catalog.
Survey data preparation
Flatten multi-column survey responses into a single column for text analysis or word frequency counting.
Skills aggregation
Combine skill entries from multiple team member columns to build a comprehensive department skills inventory.
Pro Tips
Wrap in UNIQUE() to get a deduplicated list: =UNIQUE(COMBINE_ALL(A:A, C:C)).
Wrap in SORT() for an alphabetically sorted combined list: =SORT(COMBINE_ALL(A:A, B:B)).
Use with COUNTA to count total non-empty values across multiple ranges: =COUNTA(COMBINE_ALL(A:A, C:C)).
The output spills vertically from the cell where you enter the formula, so make sure there is enough room below.
For joining values into a single text string instead of a column, use GROUP_BY_CELL.
The function automatically excludes empty cells, null values, and undefined entries, giving you a clean list with no gaps. It works with any range shape: single columns, rows, or rectangular blocks. All values from each range are extracted left-to-right, top-to-bottom and appended to the result in the order the ranges are provided.
Common workflows include combining data from multiple team columns into a single master list, merging tags or categories from different sources, consolidating responses from multiple survey columns, and preparing data for charts or pivot tables that require a single-column input. The function supports any number of range arguments, so you can combine as many data sources as needed in a single formula call.
The output is always a single column of values, making it perfect for feeding into other functions like UNIQUE, SORT, COUNTIF, or FILTER. For the reverse operation -- splitting a single cell into multiple columns -- see SPLIT_IN_COLUMNS. If you want to join values into a single text string instead of a column, use GROUP_BY_CELL.
Common Errors
Output overwrites existing dataCause: The combined result spills into cells that already contain data.
Fix: Place the formula in a cell with enough empty rows below it to accommodate the full output. Move existing data to make room.
Empty resultCause: All provided ranges are empty or contain only blank cells.
Fix: Verify that the range references point to the correct cells containing data. Check for invisible characters that might prevent detection.
Only first range appears in outputCause: Additional ranges were not separated by commas in the formula.
Fix: Ensure each range argument is separated by a comma: =COMBINE_ALL(A1:A10, C1:C10, E1:E10).
Frequently Asked Questions
No, COMBINE_ALL automatically filters out empty cells, null values, and undefined entries. You get a clean list with no gaps.
Values appear in the order the ranges are provided. Within each range, values are extracted left-to-right, top-to-bottom. So all values from range1 come first, then range2, and so on.
Yes, you can reference ranges from other sheets using standard Google Sheets notation: =COMBINE_ALL(Sheet1!A:A, Sheet2!A:A).
No, COMBINE_ALL preserves all values including duplicates. To remove duplicates, wrap the result in UNIQUE: =UNIQUE(COMBINE_ALL(A:A, B:B)).
There is no practical limit to the number of ranges you can pass. The function accepts any number of range arguments using the variadic (...) syntax.
Yes, COMBINE_ALL preserves the original data types. Numbers, text, dates, and booleans are all included as-is in the output column.
Yes, wrap the result in SORT for alphabetical ordering: =SORT(COMBINE_ALL(A:A, C:C)). You can also use SORT with a custom column or order parameter.
Related Functions
Start using COMBINE_ALL today
Install Unlimited Sheets to get COMBINE_ALL and 41 other powerful functions in Google Sheets.
