Filter Excel Horizontally: Methods That Work

Yes, you can filter Excel horizontally, but the standard Filter dropdown is designed to filter records in rows, not to hide or show columns according to values across a row. To return only matching columns, use the FILTER function in Microsoft 365 or Excel 2021 and later; for an interactive, refreshable workflow, transpose the data in Power Query and filter it as rows.

What horizontal filtering means in Excel

Excel’s usual data layout has one record per row and one field per column: for example, each sales transaction is a row, with columns for date, region, product, and amount. In that arrangement, filtering a column hides the rows that do not meet a condition. This is the task the familiar dropdown arrows in Excel’s Filter command perform.

Horizontal filtering usually means selecting columns based on values arranged across a row. A worksheet might have months across the top, departments in separate columns, or a row of status labels such as Keep and Drop. The desired result is to show or return only the columns that meet a condition. Excel does not provide a standard AutoFilter dropdown that hides columns this way.

That distinction matters because a range can look like a table without being organized as records. If each column represents an item and the rows below it describe that item, the data is effectively arranged sideways relative to Excel’s normal filtering model. You can still get the result, but you need a formula, a transformed layout, or a manual workaround.

Use FILTER to return matching columns

For a formula-driven result, FILTER can return columns when its include argument is a horizontal array with one condition for each column. In the example below, B1:G4 contains six departments as columns. Row 1 has the department names, row 2 contains Keep or Drop, row 3 contains sales, and row 4 contains order counts. The status row is B2:G2. Enter the formula in an empty area outside the source range:

=FILTER(B1:G4,B2:G2="Keep","No matching columns")

The formula returns four columns: North, East, West, and Online, because those are the columns whose row 2 status is Keep. It carries the corresponding sales and order-count values along with each department. South and Central are excluded. The final argument displays No matching columns if none of the status cells says Keep.

FILTER spills its result into neighboring cells, so leave enough empty space to the right and below the formula. It does not hide the original columns or add dropdown arrows; it creates a separate result array. If a value in B2:G2 changes from Drop to Keep, the result updates automatically. This is useful for dashboards, summaries, and reports that should respond to changing criteria.

FILTER is available in Microsoft 365 and Excel 2021 or later. It is also available in supported versions of Excel for Mac and Excel for the web. Older Excel versions without dynamic arrays cannot use this formula as written; use Power Query, a transposed copy, or a manual approach instead. Do not type the formula inside an Excel Table if you expect the spill range to expand there: dynamic array formulas spill into ordinary worksheet cells, not within table cells.

Make the formula match your condition

The condition does not have to be a text label. For example, if a row contains monthly totals, you could select the columns whose total exceeds a threshold by using a horizontal comparison as the include argument. The key is that the array being filtered must contain the columns you want returned, and the include array must have one True or False result per column. If the dimensions do not line up, Excel reports an error.

For more than one criterion, combine horizontal tests so a column is included only when both are true. Conceptually, a status test can be combined with a numeric threshold test: columns marked Keep and having a value above 100 pass, while other columns do not. Make sure both tests refer to the same number of columns. If criteria are based on different rows, verify that each row covers exactly the columns in the array you are filtering.

Also check whether headers should appear in the output. Including the header row in the array, as in the example, returns the header for every matching column. If you filter only the data rows, the result will not include those headers unless you add them separately. Keeping headers with the result is usually clearer, particularly when the output is used by someone who did not build the formula.

Use Excel’s regular Filter for row-based data

If your data has one record per row, the built-in command is simpler and usually the better choice. On Windows, select a cell in the data range and choose Data > Filter. Excel adds dropdown arrows to the header row. Open the arrow for the field you want, then select values or use the available text, number, date, or other filter options. The remaining rows are hidden from view, not deleted.

For example, with a list of orders in rows and a Region column, open the Region dropdown and select West to display orders from that region. You can apply another column filter at the same time, such as choosing orders above a particular amount. Each additional filter narrows the visible records, and clearing the filters restores the rows.

The Windows shortcut Ctrl+Shift+L toggles the Filter command for a selected range. On a Mac, shortcut behavior can depend on the keyboard and Excel version; use Data > Filter if the shortcut is not recognized. The command still filters rows on Mac and in Excel for the web. It does not become a horizontal filter just because the worksheet has been transposed visually.

Transpose the data when columns represent records

When every column represents an item and its details are listed down the rows, transposing the data can make standard filtering practical. Suppose column A contains field names such as Department, Status, Sales, and Orders, while columns B:G contain one department each. After a transpose, each department becomes a row and its attributes become fields in columns. Excel’s regular filter can then filter the Status field normally.

For a one-time rearrangement, copy the range, choose an empty destination cell, then use Home > Paste > Transpose. This creates a rotated copy; it does not keep the copy linked to the original data. If the source later changes, repeat the operation or use a formula-based approach. Avoid pasting over the source range, because the operation can overwrite data you still need.

In the transposed result, check that the first row contains usable column headings and that each record occupies one row. Excel filtering works best when the range has a single header row, no completely blank rows or columns inside the data, and consistent field types. Once the layout is suitable, select a cell in the range and choose Data > Filter.

Use Power Query for repeatable transformations

Power Query is a useful alternative when the source is horizontal and the transformation needs to be repeated after data changes. In Excel for Microsoft 365 on Windows, format the source range as a table or select a cell in a properly structured range, then choose Data > From Table/Range. In Power Query Editor, use Transform > Transpose to rotate the data. If the first transformed row contains the field names, choose Home > Use First Row as Headers.

Once each original column is a row, filter the relevant field using its dropdown in Power Query Editor. For example, filter the Status column to Keep. Then choose Home > Close & Load to return the result to Excel. To incorporate later changes in the source, refresh the query. Power Query maintains the transformation steps, so you do not have to transpose and filter manually each time.

Power Query is a transformation workflow, not a live column-hiding control. It produces a query result in a new worksheet or destination, and refreshing updates that output from its source. The exact available commands and data connectors can differ by Excel edition and platform. If the Power Query commands are not available in your Excel version, use the FILTER formula if supported or create a transposed copy.

Choose the method that fits the job

Use the approach that matches the result you need. A formula is often best for a compact, automatically updating display. Power Query is a strong fit for recurring imports or a repeatable reshape-and-filter process. Regular AutoFilter is best when the data already has one record per row. A manual transpose is quick for a one-off task but needs repeating when the source changes.

Need a dynamic set of matching columns on the same worksheet? Use FILTER and place its result in a clear area.
Need dropdown filters for data arranged across columns? Transpose it first, or reshape it with Power Query, so each item becomes a row.
Need to hide whole worksheet columns manually? Select the column letters, right-click, and choose Hide. This is not condition-based filtering and will not update automatically when values change.
Need to sort columns from left to right? Data > Sort > Options includes a Sort left to right option in supported desktop Excel versions. Sorting changes order; it does not filter out columns.

Keep the original data intact when experimenting with a transformation. A separate formula output or Power Query result makes it easier to compare the filtered view with the source and reduces the chance of accidentally losing information.

Common problems and misconceptions

A common surprise is that the Filter dropdowns appear on the header row but still hide rows, not columns. That is expected: AutoFilter treats rows as records and header cells as field names. Selecting a row of month names or department names does not change how the feature works. To select columns based on a row, use a horizontal FILTER include array or reshape the records.

If a FILTER formula returns an error, first compare the width of the array and the include condition. A six-column array needs six include results. Also check that the spill area is empty; an occupied cell in the output area can cause a #SPILL! error. If the output does not change as expected, confirm that the formula points to the actual condition row and that spaces or inconsistent text are not making labels differ.

Another misconception is that filtering deletes excluded data. AutoFilter hides rows temporarily, whereas FILTER returns a separate array containing only the selected values. A transposed paste creates a separate copy, while Power Query creates a refreshable output. These behaviors are different, so choose based on whether you need to preserve the source view, produce a calculated result, or build a repeatable transformation.

Frequently Asked Questions

Can Excel’s Filter button filter columns instead of rows?
No. Excel’s standard Filter command filters records in rows according to values in columns. To return matching columns, use FILTER with a horizontal condition, or transpose the data and filter the resulting rows.

How do I filter columns based on a row of values?
In Microsoft 365 or Excel 2021 and later, use FILTER with the full range as its array and the relevant row test as its include argument. The include row must have one result for each column in the range, and the output spills into empty worksheet cells.

Can I filter months arranged across the top of a sheet?
Yes, but the normal dropdown will filter rows, not individual month columns. A FILTER formula can return months whose values meet a condition. For repeated interactive filtering, reshape the data so months are records in a Month field and use regular AutoFilter or Power Query.

Does hiding columns count as horizontal filtering?
It can make a worksheet look filtered, but manual Hide is not a value-based filter. Hidden columns stay hidden until you unhide them, and their visibility does not automatically respond to changes in a status row.

Why does my horizontal FILTER formula show #SPILL!?
The formula needs an unobstructed output area. Clear any cells blocking the expected result, and check that merged cells are not in the spill range. Also confirm that the array and condition cover the same number of columns.

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.