Horizontal Filter in Excel: How to Filter Across Columns

Formulas & functions · Updated November 20, 2026

To filter data arranged across columns in Excel for Microsoft 365, use the FILTER function with a horizontal condition row; ordinary Data > Filter works down rows, not across columns. Put the same number of columns in the data range and condition range, enter the formula in a clear cell, and Excel will return only the matching columns.

This distinction matters when each column represents a record—for example, a month, project, or person—and each row contains a field such as region or status. If your records are already arranged as rows, use the standard Filter command instead. The guide below covers both the formula-based horizontal method and the transpose workaround for people who need Excel’s dropdown filters.

What You’ll Need

  • Excel for Microsoft 365 or Excel 2021 for the dynamic-array FILTER function. The function is also available in Excel for the web.
  • A rectangular range with one record per column and consistent fields in rows. Include labels so you can identify the results.
  • An empty area large enough for the filtered results to spill. Avoid placing the output formula inside the source data.

Step-by-Step

  1. Check how your data is arranged. In a horizontal layout, each column is one record and each row is one attribute. For example, row 1 might contain IDs, row 2 regions, row 3 statuses, and row 4 sales amounts. If one row instead contains field names and each following row is a record, your data is vertical; select it and use Data > Filter for the built-in dropdowns.
  2. Identify the row that contains the field to filter. Choose the row that has the values you want to test, such as Status. Make sure the field values line up with the records in the other rows. Check for inconsistent spelling, extra spaces, or different labels such as “Open” and “open”; text comparisons in the formula should use the actual cell values.
  3. Choose the complete data range. Include every row you want returned, including the ID or label row, and every record column. For the example in this guide, records are in B1:G4, with Region in B2:G2 and Status in B3:G3. Including the complete range means the output keeps the matching records’ other fields as well as the field used for filtering.
  4. Pick an output cell outside the source range. Select a blank cell with clear space to its right and below. The result will spill horizontally and vertically from that cell. Do not type a separate formula into the expected spill area, and do not put the output over data you need to keep.
  5. Enter a FILTER formula for one condition. With record IDs in B1:G1, regions in B2:G2, statuses in B3:G3, and sales in B4:G4, enter this in an empty cell. The formula returns every row of the source range for columns whose status is Open: =FILTER(B1:G4,B3:G3="Open","No matches"). In the sample data, if columns B, D, E, and G have status Open, the result contains records A101, A103, A104, and A106, with their region, status, and sales values.
  6. Press Enter and inspect the spilled result. Excel displays the results in a rectangular range beginning at the formula cell. The filtered records remain in their original left-to-right order; FILTER removes nonmatching columns rather than sorting the remaining ones. The formula cell is the only cell you edit to change the result.
  7. Filter with more than one condition if needed. Use multiplication between condition tests to require both tests to be true. With regions in B2:G2 and statuses in B3:G3 as above, this formula returns columns where the status is Open and the region is North: =FILTER(B1:G4,(B3:G3="Open")*(B2:G2="North"),"No matches"). If A101 and A103 are the only records meeting both conditions, those two columns appear. Multiplication here combines TRUE/FALSE tests as an AND condition; do not use it to add together criteria when you want either condition to match.
  8. Change the condition to update the view. You can replace the text in the formula with another exact value, or refer to a cell where you type the criterion. For a more reusable setup, put the desired status in J1 and use =FILTER(B1:G4,B3:G3=J1,"No matches"). With Open entered in J1, it returns the same four Open records in the example. Keep J1 outside both the source and the spill area.

The formulas above require Excel 2021 or Microsoft 365 because they use the dynamic-array FILTER function. Excel for the web also supports FILTER. Older desktop versions that do not support it may show a #NAME? error; use the transpose workaround below, or open the workbook in a supported version. If your Excel installation uses semicolons as formula separators because of regional settings, replace the argument-separating commas when entering a formula.

Common Mistakes

  • Expecting the regular Filter dropdown to filter columns. On the Data tab, Filter creates dropdowns for filtering rows based on column values. It does not hide or show columns according to the values across a row. For a horizontal layout, use FILTER or temporarily transpose the data.
  • Using condition and data ranges with different widths. In a horizontal FILTER formula, the include test must line up with the columns in the array. For example, B1:G4 and B3:G3 both cover columns B through G. If one range starts in C or ends in F, Excel may return an error or results that do not correspond to the intended records.
  • Leaving the output area occupied. FILTER needs room to spill. Text, formulas, merged cells, or other content in the destination range can prevent the results from appearing. Clear the blocking cells or move the formula to a genuinely empty area.
  • Forgetting that a horizontal layout is not an Excel Table’s usual structure. Excel Tables are designed with field names in columns and records in rows. A horizontal source can be harder to sort, chart, and analyze with other features. Avoid converting it to a Table and expecting its dropdowns to filter across columns.
  • Using a criterion that does not match the source. A typo or a different status label can produce no matches. Check the source values directly and use a cell reference for the criterion when you want to change it often.

Tips

  • Keep a label row in the returned range. Including IDs or names makes it easier to tell which records survived the filter and helps preserve context when you copy or share the output.
  • Keep the source and results separate. Put the formula on another worksheet or well away from the original range. This protects the source and gives the dynamic result a predictable place to expand.
  • Use a criterion cell when filtering repeatedly. A cell reference lets you change Open to Closed without editing the formula itself. You can also add a clear label beside the criterion cell so another reader knows what it controls.
  • Consider reorganizing data that will be filtered often. A conventional layout—with one record per row, one field per column, and a single header row—works naturally with Data > Filter, sorting, PivotTables, and Tables. If you can change the source structure, this is usually easier to maintain than a horizontal arrangement.
  • Use transpose when you specifically need dropdown filters. Copy the horizontal range, select an empty destination, then choose Home > Paste > Transpose. The transposed copy has records down rows, so you can select the range and choose Data > Filter. This creates a separate copy; changes to the original do not automatically update a normal pasted copy.

Troubleshooting

The formula shows #SPILL!: Excel cannot place the full result in the cells it needs. Select the warning icon or inspect the destination area, clear any blocking content, and check for merged cells. If the result is close to the edge of a worksheet, move the formula so there is room in the direction it will spill.

The formula returns “No matches” even though a match appears to exist: Confirm that the criterion is in the row referenced by the formula and that the source and condition ranges cover exactly the same columns. Check for trailing spaces, inconsistent text, or a status stored differently from what you expect. If using a criterion cell, make sure it contains the value rather than a similar-looking label.

Excel shows #NAME?: Your Excel version may not include FILTER, or the function name or argument separators may not match your installation’s language and regional settings. Use Microsoft 365, Excel 2021, or Excel for the web for FILTER, or transpose a copy and apply the standard Filter command.

The result is missing fields or includes the wrong records: Expand the first argument so it includes all the rows that should travel with each record. Then make sure the condition test refers to the correct field row. When adding multiple conditions, verify each test refers to the intended row and that the conditions are combined with multiplication for “both must match.”

You cannot find the Transpose command: Copy the range first, then select a blank destination. On Windows, use Home > Paste > Transpose from the ribbon or right-click the destination and select the Transpose paste option. On Mac, use Home > Paste > Transpose or the equivalent option in the paste menu. In Excel for the web, use the paste options when available; if the command is missing in your current interface, open the workbook in the desktop app. Transpose into a blank area because it does not filter the original data in place.

Frequently Asked Questions

Can I apply Excel’s normal Filter dropdown horizontally?
No. The standard Data > Filter command filters rows based on values in columns. To select columns based on values across a row, use FILTER with a horizontal condition range, or transpose the data and then apply the normal dropdown filters.

Does FILTER work when the condition is in a row?
Yes. The include argument can be a horizontal range when the data array has the same number of columns. In the example, B3:G3 tests the six record columns in B1:G4 and the matching records spill into the output area.

Can I filter for either of two values?
Yes. Combine two TRUE/FALSE tests with addition when you want either condition to match. Be sure both tests cover the same horizontal columns and refer to the appropriate rows. For “both conditions must match,” use multiplication instead.

Will the results update if the source changes?
Yes, the FILTER result recalculates when values in its source or condition range change. A regular transposed paste is a snapshot and does not stay linked to the original. If you need a live transposed copy, use a formula-based approach or refresh a data transformation rather than relying on Paste Transpose.

Can I sort the filtered columns by sales?
FILTER preserves the source order; it does not sort the result. You can reorder the source columns, or use a separate sorting approach compatible with your Excel version. For data that needs frequent filtering and sorting, consider storing records in rows instead.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.