Keep Formatting When Sorting? Excel & Sheets Fix

Sorting data in Excel or Google Sheets usually keeps the cell formatting (colors, borders, number formats) attached to the cells, but it often breaks row-specific formatting like alternating band colors or conditional formatting rules that were set for a fixed range. This article explains exactly what happens to formatting during a sort, why it breaks, and how to keep your formatting intact.

Formula

The calculator above works out how many color bands you need and where the last one starts when you apply alternating row shading to a table. The formula is:

Number of bands = CEILING(rows / band_size)

Last band start row = start_row + (number_of_bands – 1) × band_size

Variables:

• rows – total number of data rows in your table (e.g., 10).
• band_size – how many rows share the same color before switching (e.g., 2 for alternating pairs).
• start_row – the first row of your data (usually 1 if your headers are in row 1, or 2 if row 1 is a title).

Worked Example

Using the defaults: 10 data rows, band size 2, starting at row 1.

1. Number of bands = CEILING(10 / 2) = 5
2. Last band starts at row = 1 + (5 – 1) × 2 = 9

So you get 5 bands: rows 1–2, 3–4, 5–6, 7–8, and 9–10. The calculator shows 5 bands and the last band starts at row 9.

What Actually Happens to Formatting When You Sort

When you sort a range in Excel or Google Sheets, the values move but the formatting usually stays with the cells, not with the values. This means:

• Cell fill colors, borders, and fonts stay on the same cell positions. If you had row 3 highlighted yellow, after sorting, row 3 is still yellow, but it now contains different data.
• Number formats (currency, date, percent) also stay with the cell, so a date column sorted numerically will still show dates correctly as long as the whole column was formatted.
• Conditional formatting rules behave differently. Rules that apply to a fixed range (like $A$2:$Z$100) will re-evaluate based on the new values, so the formatting moves with the data logically, not physically.

The most common problem is when you use alternating row colors (banding) to make a table readable. After sorting, the bands no longer alternate because the colors are stuck to the rows, not the data.

Why Sorting Breaks Alternating Row Colors

Alternating row colors are often applied manually by selecting rows and filling them with a color. When you sort, the rows move as a block, so the colors move with the rows. If you had rows 1, 3, 5, 7… shaded and rows 2, 4, 6, 8… unshaded, sorting by a column will reorder those rows, so you might end up with two shaded rows next to each other and two unshaded rows next to each other.

The same happens if you use a table style in Excel (Insert > Table) or a banded row option in Google Sheets (Format > Alternating colors). These are dynamic banding methods that recalculate based on the row position, so they actually survive sorting perfectly. The issue is only with manually applied fills.

How to Keep Formatting When Sorting (Step-by-Step)

Here are the reliable methods to keep your formatting looking right after a sort:

Method 1: Use a Real Table (Excel) or Alternating Colors (Google Sheets)

1. In Excel, select your data range and press Ctrl+T (or Cmd+T on Mac) to turn it into a table. The table style automatically applies banded rows that move with the data when you sort.
2. In Google Sheets, select your data, go to Format > Alternating colors, and choose a style. This applies a conditional formatting rule that follows the row position, so sorting keeps the bands correct.

Method 2: Use Conditional Formatting with a Formula

Instead of manually coloring rows, use a formula-based conditional formatting rule:

1. Select the entire data range (e.g., A2:Z100).
2. In Excel: Home > Conditional Formatting > New Rule > Use a formula. Enter =MOD(ROW(),2)=0 for even rows, or =MOD(ROW(),2)=1 for odd rows.
3. In Google Sheets: Format > Conditional formatting > Custom formula. Enter the same formula.
4. Set the fill color and apply.

This rule checks the row number, not the data, so after sorting, the bands still alternate correctly because the rule recalculates for each row’s new position.

Method 3: Sort with the Formatting (Not Recommended)

If you absolutely must keep the manual colors attached to the specific data rows, you can add a hidden helper column with a unique ID for each row, sort by that ID to restore the original order, and then delete the helper column. But this is cumbersome and usually not what you want.

Common Mistakes and Misconceptions

Mistake: Selecting only one column to sort. If you sort a single column without expanding the selection, only that column moves, and the rest of the row stays in place – this completely destroys your data alignment and formatting. Always select the entire range or use the “Expand the selection� option.

Mistake: Thinking conditional formatting is lost. Conditional formatting is not lost; it just re-evaluates. If you had a rule that highlights cells greater than 100, after sorting, the rule still works on the new values. The formatting moves with the values, which is usually what you want.

Misconception: Sorting always breaks formatting. In modern Excel and Google Sheets, sorting a full table with a table style or alternating colors does not break the banding. It only breaks if you manually painted cells.

How to Choose the Right Approach

Consider these factors:

• Do you need the bands to stay visually consistent? Use a table style or conditional formatting.
• Do you want the formatting to move with specific data? For example, if a row is highlighted because it’s a “totalâ€� row, you want that highlight to stay with that row when sorting. In that case, manual formatting works, but you must include the formatting in the sort range (which is automatic if you select the whole row).
• Are you sorting only part of the data? Avoid sorting a subset; always sort the entire table.

Comparison: Excel vs. Google Sheets

Both Excel and Google Sheets handle sorting and formatting similarly, but there are slight differences:

• Excel Tables (Ctrl+T) automatically expand and contract as you add/remove rows, and the banding follows the table. Sorting within a table keeps the bands correct.
• Google Sheets Alternating Colors is a conditional formatting rule that applies to the selected range. It also survives sorting, but if you add rows beyond the range, you need to update the range manually.
• Filtering (not sorting) hides rows, and both tools keep the visible rows’ formatting intact. Filtering does not change the order, so banding remains correct.

Frequently Asked Questions

Why did my alternating colors get messed up after sorting?

Because you probably applied the colors manually to specific rows. When you sort, the rows move, so the colors stay on the same row numbers, not on the same data. Use a table style or conditional formatting with =MOD(ROW(),2)=0 to make the bands recalculate automatically.

Does sorting in Excel remove conditional formatting?

No. Conditional formatting rules are not removed by sorting. They are recalculated based on the new cell values. If a rule was set to highlight cells above a threshold, the highlighted cells will change to match the new values after sorting.

How do I keep the header row formatting when sorting?

If your header row is part of the sort range, it will move with the data. To keep the header in place, freeze the top row (View > Freeze > 1 row) and make sure you exclude the header from the sort range (or check “My data has headers� in the sort dialog).

Can I sort without affecting formatting at all?

No, sorting always moves the formatting with the cells. The only way to keep formatting truly static is to not sort, or to use conditional formatting that recalculates based on row position. If you need to preserve the original order, use a helper column with a serial number and sort by that to restore.

Why does my number format change after sorting?

Number formats are attached to cells, so they should not change when sorting. If you see a change, it’s likely because the column had mixed formats (some cells text, some numbers) and sorting grouped them differently. Ensure all cells in a column have the same number format before sorting.

Related

• Weird Products That Run on Power Tool Batteries
• Dewalt 20V Tools Second Power Level Explained
• Dewalt Adapter as Power Bank: Does It Work?

in rows | Number of data rows | - | 10 | 1 | 1000
in band | Band size (rows per color) | - | 2 | 1 | 100
in start | Starting row number | - | 1 | 1 | 100000
out bands | Number of bands | - | ceil(rows / band) | 0
out last_band | Last band starts at row | - | start + (bands - 1) * band | 0