How to list the column headers of cells marked with an X in Excel
Excel formula
=TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,""))Return the header wherever the row has an X and join the results: =TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,"")), filled down.
- Result on the example
- one result per row in
G2, filled down toG6 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2, filled down to G6. Change any input and the result updates, or edit the formula to experiment.
How it works
IF(B2:F2="X",B$1:F$1,"") walks along the row and returns the header above each X and an empty string elsewhere. TEXTJOIN joins the list with ", " and its TRUE skips the empty strings. The $ on row 1 keeps the headers in place as you fill down.
=TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,""))Result"Excel, SQL, Tableau"
B2:F21 row × 5 cols: "X", "X", (empty), "X", …A range: 5 cells in one row.
B2:F2="X"{1 row × 5 cols} TRUE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
B$1:F$11 row × 5 cols: "Excel", "SQL", "Python", "Tableau", …A range: 5 cells in one row.
IF(B2:F2="X",B$1:F$1,""){1 row × 5 cols} "Excel", "SQL", "", "Tableau", …IF: Returns one value if a test is true, another if false.- logical_test
B2:F2="X"{1 row × 5 cols} TRUE, TRUE, FALSE, TRUE, … - value_if_true
B$1:F$11 row × 5 cols: "Excel", "SQL", "Python", "Tableau", … - value_if_false
""
- logical_test
TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,""))"Excel, SQL, Tableau"TEXTJOIN: Joins text with a delimiter between items.- delimiter
", " - ignore_empty
TRUE - text1
IF(B2:F2="X",B$1:F$1,""){1 row × 5 cols} "Excel", "SQL", "", "Tableau", …
- delimiter
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,""))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TEXTJOIN(", ",TRUE,FILTER(B$1:F$1,B2:F2="X","")) | Excel, SQL, Tableau, Excel, Python, Power BI, SQL, Python… | FILTER keeps only the marked headers. Excel 2021 and Microsoft 365; in Google Sheets drop the third argument and wrap FILTER in IFERROR. |
Common mistakes
- "X" matches "x" too, because the comparison ignores case. A cell with " X" (with a space) doesn't match.
- For checkboxes, test TRUE instead:
IF(B2:F2=TRUE,…). - In Excel 2019, confirm the formula with Ctrl+Shift+Enter before filling it down.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
List the column headers of cells marked with an X
G2, list the skills (headers in row 1) that have an X in the employee's row, separated by commas. Fill down to G6.Questions
How do I list the employees who have one skill?
Filter the names by that column: =FILTER(A2:A6,C2:C6="X").
How do I return headers where a number is above a limit?
Change the test: =TEXTJOIN(", ",TRUE,IF(B2:F2>100,B$1:F$1,"")).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.