Combining Filtered Rows Into a Single Cell Using Excel Formulas

Manually consolidating filtered row data into a single, comma-separated cell is a tedious, error-prone chore that disrupts reporting workflows. While standard manual copy-pasting or basic lookup functions offer temporary workarounds, they fail to adapt to live data changes. Fortunately, deploying dynamic array formulas grants you seamless, automated data aggregation that updates in real time.

Stipulation: This streamlined approach requires Excel 365 or Excel 2021 to leverage modern engine capabilities. For instance, combining active project names is easily achieved with: =TEXTJOIN(", ", TRUE, FILTER(A2:A10, B2:B10="Active")).

Below, we will break down the exact syntax and guide you through implementing this solution step-by-step.

When working with large datasets in Excel, filtering data is one of the most common tasks. However, a frequent challenge arises when you want to summarize that filtered data by combining values from multiple rows into a single cell. Standard concatenation functions like TEXTJOIN or CONCAT are incredibly useful, but by default, they evaluate the entire range-including hidden or filtered-out rows.

To combine only the visible rows after a filter has been applied, we need a formula that can dynamically recognize row visibility. This guide will walk you through the best methods to achieve this, ranging from modern dynamic array formulas to classic workarounds, Power Query, and VBA.

The Core Challenge: Why Standard Formulas Fail

Suppose you have a simple list of employee names in column B (range B2:B10). If you apply a standard filter to display only a subset of these employees and write the following formula:

=TEXTJOIN(", ", TRUE, B2:B10)

Excel will return a concatenated list of all names in the range, completely ignoring your active filter. This happens because standard string functions look at the underlying cell grid, not the visual state of the rows on your screen.

To bypass this limitation, we must integrate a function that is natively sensitive to hidden rows. In Excel, that function is SUBTOTAL (or its modern counterpart, AGGREGATE).

Method 1: The Modern Excel Formula (Office 365 & Excel 2021+)

If you are using a modern version of Excel, you have access to dynamic arrays and helper functions like MAP and LAMBDA. This allows us to build the cleanest, non-volatile formula to combine filtered rows.

The Formula

=TEXTJOIN(", ", TRUE, FILTER(B2:B10, MAP(B2:B10, LAMBDA(row, SUBTOTAL(103, row)))))

How It Works

This formula works by generating an array of 1s (visible) and 0s (hidden) for each row, and then filtering our target range based on that array:

• SUBTOTAL(103, row): The function number 103 corresponds to COUNTA while ignoring hidden rows. If the referenced cell is visible, it returns 1. If it is filtered out, it returns 0.
• MAP(B2:B10, LAMBDA(row, ...)): Standard SUBTOTAL cannot natively evaluate an array of cells individually; it normally aggregates them into a single number. The MAP function forces Excel to evaluate SUBTOTAL row-by-row, returning an array of 1s and 0s (e.g., {1; 0; 1; 1; 0}).
• FILTER(B2:B10, ...): The FILTER function takes the original list of names and filters it, keeping only the rows where our MAP array returned 1.
• TEXTJOIN(", ", TRUE, ...): Finally, TEXTJOIN joins the filtered, visible values together, separating them with a comma and space, while ignoring any empty cells.

Method 2: The Classic Offset Array Formula (Excel 2016 / 2019)

If you are using an older version of Excel that has TEXTJOIN but lacks dynamic array functions like MAP and FILTER, you can use a classic combination of OFFSET, ROW, and SUBTOTAL.

The Formula

=TEXTJOIN(", ", TRUE, IF(SUBTOTAL(103, OFFSET(B2, ROW(B2:B10)-ROW(B2), 0)), B2:B10, ""))

Note: If you are on Excel 2016 or 2019, you must press Ctrl + Shift + Enter to commit this as an array formula.

How It Works

• ROW(B2:B10)-ROW(B2): Generates a sequential array of offsets starting at 0: {0; 1; 2; 3; ...}.
• OFFSET(B2, ..., 0): Dynamically creates a series of individual single-cell references: {B2; B3; B4; ...}. This bypasses SUBTOTAL's limitation of not being able to handle arrays.
• SUBTOTAL(103, ...): Evaluates each individual cell offset, returning 1 for visible cells and 0 for hidden ones.
• IF(..., B2:B10, ""): If the cell is visible (value is 1), it returns the text from B2:B10; otherwise, it returns an empty string ("").
• TEXTJOIN: Glues the visible values together, ignoring the empty strings.

Method 3: Combining Filtered Rows with Additional Criteria

Sometimes, filtering the rows visually isn't enough; you may also want to apply a logical condition (like a SUMIFS or FILTER condition) directly inside the formula.

For instance, if column A contains "Department" and column B contains "Employee Name", and you want to combine only the visible employees who belong to the "Sales" department:

=TEXTJOIN(", ", TRUE, FILTER(B2:B10, (MAP(B2:B10, LAMBDA(r, SUBTOTAL(103, r)))) * (A2:A10="Sales")))

Why This Works

In Excel, multiplication (*) acts as an AND logic gate. The formula multiplies the visibility array (1s and 0s) by the criteria array (1s for "Sales", 0s for others). Only rows that are both visible AND belong to "Sales" will result in a 1, allowing them to pass through the FILTER function.

Method 4: The Power Query Alternative (No Formulas)

For exceptionally large datasets, complex formulas using OFFSET or nested lambdas can slow down workbook performance. Power Query offers a robust, formula-free alternative that updates at the click of a "Refresh" button.

1. Select your data range and convert it into an Excel Table by pressing Ctrl + T.
2. Go to the Data tab and click From Table/Range to open the Power Query Editor.
3. Filter your data using the column drop-down menus in Power Query just as you would in standard Excel.
4. Select the column containing the values you want to combine.
5. Go to the Transform tab and click Group By.
6. In the Group By dialog:
   • Set the Operation to Sum (we will manually edit this to handle text shortly).
   • Click OK.
7. In the formula bar, locate the auto-generated code, which looks similar to:
   Table.Group(..., {{"Count", each List.Sum([Employee Name]), type text}})
8. Change List.Sum to Text.Combine, and add your delimiter. It should look like this:
   Table.Group(..., {{"Combined", each Text.Combine([Employee Name], ", "), type text}})
9. Click Close & Load to return the merged, filtered list back to your spreadsheet.

Method 5: A Lightweight VBA Solution (User Defined Function)

If you are using legacy Excel versions (such as Excel 2013) that do not have the TEXTJOIN function at all, you can easily create a custom formula using VBA.

The VBA Code

Press Alt + F11 to open the VBA Editor, click Insert > Module, and paste the following code:

Function ConcatVisible(RefRange As Range, Optional;
String = ", ");
String
    Dim Cell;
Range
    Dim Result;
String
    
    For Each Cell In RefRange
        ' Check if the row and column are not hidden
        If Cell.EntireRow.Hidden = False And Cell.EntireColumn.Hidden = False Then
            If Cell.Value <> "" Then
                Result = Result & Cell.Value & Delimiter
            End If
        End If
    Next Cell
    
    ' Strip the trailing delimiter
    If Len(Result) > 0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    ConcatVisible = Result
End Function

How to Use It

Once the code is pasted, close the VBA window. You can now use this brand-new custom function directly in your worksheet just like a native Excel formula:

=ConcatVisible(B2:B10, ", ")

This macro loop checks the Hidden status of each cell's row and column, safely ignoring any data hidden by filters or manual row-hiding operations.

Summary of Methods: Which One Should You Use?

By implementing one of these solutions, you can build dynamic dashboards and interactive reports that instantly adapt and summarize data as users apply filters.