Formatting Issues

How to Keep Alternating Row Colors After Filtering in Excel

Adam Davis Sep 28, 2026 873 views

Question details

The user wants to maintain an alternating row shading pattern (banding) dynamically when filtering data in a spreadsheet.

How to Keep Alternating Row Colors After Filtering in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering data in a spreadsheet that uses alternating row colors for better readability.
Observed behavior: Manual row shading gets misaligned or hidden when data is filtered, causing the alternating color pattern to break.

Before you start

Before proceeding, ensure that any manual row shading (fill colors) applied to your data range is removed, as it will override dynamic table formatting.

Solution 1Recommended

Convert Data to a Table with Banded Rows

Converting your dataset into a formatted table ensures that alternating row colors dynamically adjust when you filter or sort the data.

Using Excel's built-in Table feature is the most reliable way to maintain row banding. When you apply manual fill colors, they are tied to specific rows. If those rows are hidden by a filter, the pattern breaks. A formatted table automatically recalculates the visible rows and applies the banding accordingly.

Clear existing manual formatting

Select your entire data range. Navigate to the Home tab, click the Fill Color dropdown icon (paint bucket), and choose 'No Fill' to remove any manual shading.

Convert the range to a table

Click any cell within your data range, go to the Insert tab on the top ribbon, and click 'Table'. Alternatively, you can use the keyboard shortcut Ctrl + T.

Confirm table details

In the Create Table dialog box that appears, verify the data range is correct, check the box for 'My table has headers', and click OK.

Enable Banded Rows

Select any cell in your newly created table. Go to the Table Design (or Table Tools) tab that appears on the ribbon, and ensure the 'Banded Rows' checkbox is selected in the Table Style Options group.

Convert Data to a Table with Banded Rows

Dynamic Adjustments: Once formatted as a table, your alternating row colors will automatically adapt and remain perfectly alternating even if you sort, filter, or insert new rows.

Easily Manage Alternating Row Colors with WPS Spreadsheets

WPS Spreadsheets provides intuitive table formatting features that make managing banded rows and data filtering simple, all while remaining fully compatible with your existing Excel files.

1. Open your file in WPS Spreadsheets: Launch WPS Office and open your spreadsheet document.
2. Create a Table: Select your dataset and press Ctrl + T, or navigate to the Insert tab and select Table.
3. Apply Banded Rows: Navigate to the Table Tools tab and ensure 'Banded Rows' is checked to apply the dynamic alternating colors.
4. Filter with ease: Use the filter dropdowns in the header row; the row shading will adapt automatically to your filtered data.

One-click table formatting for dynamic alternating row colors.Fully compatible with Microsoft Excel (.xlsx) file formats.Lightweight, fast, and free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use conditional formatting to keep alternating row colors when filtering?

Yes, you can use conditional formatting with a formula like =MOD(SUBTOTAL(3, $A$2:$A2), 2)=0. However, using the built-in Table feature with Banded Rows is much simpler and less prone to calculation errors.

Why is my table banding not showing up after converting to a table?

This usually happens if you have manual fill colors previously applied to the cells. Select the affected rows, go to the Home tab, and choose 'No Fill'. The dynamic table banding will then show through.

How do I change the color of the banded rows?

Click anywhere inside your formatted table, navigate to the Table Design or Table Tools tab, and select a different Table Style from the gallery to instantly change the alternating color scheme.

Document & File Issues