Fix "You cannot convert an alternating color range without a header format into a table"

By Gerard Fernandez · Updated · 3 min read

You select some data in Google Sheets, choose Format → Convert to table (or press Ctrl+Alt+T), and instead of a table you get this message:

There was a problem. You cannot convert an alternating color range without a header format into a table. Please specify one and try again.

Google Sheets dialog: There was a problem. You cannot convert an alternating color range without a header format into a table. Please specify one and try again. OK
Real test: the dialog after Format → Convert to table on a range with alternating colors and no header. With UK English it says "colour".

We reproduced it twice in a test sheet, on two different ranges, and fixed it both ways described below.

Why it happens

Your range already has alternating colors applied, and that alternating color style has the Header option switched off. A table always has a header row, so Google Sheets doesn't know which row should become the table's header and stops.

The "header format" in the message just means the Header tick box in the Alternating colors panel. In our test, the panel ticked Header by default; the error only appeared after we unticked it, which is easy to do by accident when you want every row striped.

Fix 1: turn on the header (keeps your colors)

  1. Click any cell in the range.
  2. Go to Format → Alternating colors.
  3. In the panel, tick Header and click Done.
  4. Make sure the first row of the range really is your header row (column names).
  5. Choose Format → Convert to table again.
Alternating colors panel in Google Sheets for range A1:B5 with the Header option ticked and a Header row in the custom style
Real test: Header ticked for A1:B5. A Header row appears in the style preview.

This time the conversion worked straight away:

Expense and Amount data converted into a Google Sheets table called Table1, with a header row and column menus
Real test: the range is now Table1, with a header row and column menus. Sheets also froze the header row and said so in a message at the bottom.

Fix 2: remove the alternating colors first

Tables come with their own striped formatting, so you usually don't need the old alternating colors at all:

  1. Click any cell in the range and open Format → Alternating colors.
  2. Click Remove alternating colors at the bottom of the panel.
  3. Choose Format → Convert to table.

We tested this on a second range (D1:E5) that gave the same error: after Remove alternating colors, Convert to table created Table2 with no message.

After converting, you can change the table's colors from the table name menu (the arrow next to Table1).

Still getting the error?

  • Several alternating color ranges: a sheet can have more than one. Click a cell in each part of your data and check the panel. The Apply to range box shows which cells each style covers.
  • The selection is bigger than the data: if you selected whole columns, the conversion may include another range. Select only your data, header included, and try again.
  • Merged cells: tables don't accept merged cells. Unmerge them first. For this and the other Convert to table messages, see Convert to table errors.

Frequently asked questions

Do I lose my data when I convert to a table?

No. Converting only changes the formatting and adds the table features (column types, filters, a name). You can undo it with Ctrl+Z.

Can I have alternating colors without a table?

Yes. Alternating colors work on any range, with or without a header. You only need the header when you want to convert the range into a table.

What's the difference between a table and alternating colors?

Alternating colors are just formatting. A table is a structured range: it has a name, a frozen header, filter menus on each column and column types such as dates or drop-downs. Use alternating colors for a quick visual touch and a table when you want to manage the data. To color cells based on their values instead, use conditional formatting.