Excel: Remove Table Formatting Without Losing Data

Yes. In Excel for Microsoft 365 on Windows, choose Table Design > Table Styles > More > Clear to remove a table’s style while keeping the data and the table itself. If you want to remove the table structure too, choose Table Design > Convert to Range, confirm, then use Home > Clear > Clear Formats to remove the leftover cell formatting without deleting contents.

Choose whether to keep the table or just its data

“Remove table formatting” can mean two different things in Excel: remove the colors and borders, or turn the table into an ordinary cell range. Those are separate actions, and choosing the right one prevents surprises. A formatted Excel table is more than a visual style: it can add filter buttons, automatically extend when you enter data beside it, and use special references in formulas.

Keep the table, remove its style: Clear the table style in the Table Design tab. The table remains a table, with features such as filter buttons and automatic expansion.
Keep the data, remove the table structure: Convert the table to a range. The contents remain, but table-specific behavior ends. Then clear the remaining cell formatting if you want plain-looking cells.
Keep the table and its style, but change its appearance: Choose another table style or adjust options such as banded rows in Table Design.

If your goal is simply a cleaner look, start by clearing the style rather than converting the table. You can always convert it later if you decide you no longer need table features.

Remove the table style while keeping table features

Use this option when you want to keep sorting, filtering, structured references, and the table’s ability to expand, but do not want its preset colors or borders. Click a cell inside the table so Excel displays the contextual Table Design tab. Open the Table Styles gallery using its More button, then choose Clear.

The table remains a table. Its header and filter controls remain available, and formulas that refer to table columns can continue using structured references. The Clear option removes the selected table style; it does not remove the data or convert the table to a normal range. If you still see color or borders, check whether they come from direct formatting, conditional formatting, or another setting rather than the table style.

Table Design also includes options such as Header Row, Total Row, and Banded Rows. These options control table features or how the table is displayed; they are not a substitute for converting a table to a range. For example, turning off Banded Rows can stop alternating row shading while leaving the table intact.

Convert a table to a normal range

To keep the contents but stop using Excel’s table structure, click inside the table and select Table Design > Convert to Range. When Excel asks whether you want to convert the table to a normal range, choose Yes. The cells, values, and formulas stay on the worksheet, but the range no longer has table-specific behavior.

Converting does not necessarily make the cells look plain. Excel generally retains the table’s existing visual formatting as ordinary cell formatting, so the former banded rows, fills, or borders may still be visible. To remove that appearance, select the former table range after converting it and choose Home > Clear > Clear Formats. Clear Formats removes formatting, not cell contents.

After conversion, check formulas that used structured references, such as references to a named table column. Excel changes table references in formulas to regular cell references when the table is converted. The formulas can still calculate, but they no longer use the table’s column names and may not automatically expand to include new rows. Review important formulas, especially if they were designed around a table that grows over time.

What stays and what changes

These actions are designed to preserve the data, but they do not preserve every table behavior. Understanding the difference helps you avoid mistaking a changed display or removed feature for lost information.

Cell contents: Values and formulas remain when you convert a table to a range or clear formats. Clear Formats does not clear the contents of the selected cells.
Appearance: Converting to a range can leave the table’s formatting behind. Clear Formats is the additional step for removing ordinary font, fill, border, and number-format choices.
Table controls: Converting removes table-specific filter controls and automatic table expansion. If you want those, clear the style instead of converting.
Formula references: Formulas remain, but structured references change to ordinary cell references during conversion. Check them if the workbook relies on table names or automatic expansion.
Displayed dates and numbers: Clearing number formats changes how values appear, not the underlying values. A date may display as a serial number, and a value previously shown with two decimal places may display with more digits.

For example, a table containing product names, prices, and a calculated tax column will keep those entries and calculations after conversion. However, if the tax formula used the table’s column names, Excel will convert its references to cell addresses. If you then clear formats, a price previously shown as currency may appear as a plain number; the stored price itself has not been changed.

Clear only the formatting you actually want removed

Home > Clear > Clear Formats is broad: it removes the selected cells’ formatting rather than just the table’s colors. That can include number formats, fonts, fills, borders, and other formatting choices. If you want to keep currency or date display, avoid clearing all formats across the entire range. Instead, remove the table style, or select only the cells whose formatting you want to reset.

Excel’s Clear menu offers other commands, but they do different jobs. Clear Contents removes cell contents while leaving formatting; it is not the right choice when you want to keep data. Clear All removes contents as well as formatting and other cell information, so avoid it for this task. When the goal is to preserve data, verify that you have selected Clear Formats rather than Clear Contents or Clear All.

Conditional formatting can also make cells appear colored even after you remove a table style. If the appearance remains, check Home > Conditional Formatting > Manage Rules to see whether rules apply to the range. You can remove applicable rules through Home > Conditional Formatting > Clear Rules, choosing the appropriate scope. Do this only if you want to discard those rules; they may be intentionally highlighting values such as overdue dates or low inventory.

Common problems and safe checks

A frequent surprise is that converting to a range does not remove the table’s colors. That is expected: conversion removes table behavior, while the formatting may remain on the cells. Clear Formats is a separate step if you want to remove that appearance.

Another common issue is selecting the wrong range. Before clearing formats, click the former table and confirm the selected area includes the intended cells, but not unrelated headings, notes, or nearby data. If you need to select the whole table before converting, click inside it and use the table controls rather than guessing the range. Afterward, inspect the first and last rows and columns to make sure the intended area changed.

For a cautious workflow, save a copy of the workbook first, particularly if it contains complex formulas or carefully designed number formats. Then:

Decide whether table features such as filters and automatic expansion are still useful.
If they are, clear the table style and leave the table in place.
If they are not, convert the table to a range and confirm the change.
Clear formats only if the remaining appearance should be removed.
Check formulas, dates, currency values, conditional formatting, and any connected workbook processes.

If the result is not what you wanted, use Undo immediately. As with other Excel changes, it is safer to review the outcome before making further edits that could make the original state harder to restore.

Windows, Mac, and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, click inside the table and look for the contextual Table Design tab; the table-style gallery and conversion commands are available there, though the ribbon layout can differ slightly by release. To clear ordinary cell formatting, use the Home tab’s Clear menu and choose Clear Formats.

Excel for the web also supports working with tables, but its ribbon layout and available commands can differ from the Windows desktop version. If a specific command is not visible, check the contextual table tab after selecting a table cell, or open the workbook in the desktop app for the full set of ribbon controls. The key distinction remains the same: clearing a style preserves the table, while converting to a range removes table behavior.

Frequently Asked Questions

Will removing table formatting delete my data?
No. Clearing a table style, converting a table to a range, and choosing Clear Formats are intended to leave cell contents in place. Do not choose Clear Contents or Clear All if you need to keep the data.

Why does the color remain after I convert the table to a range?
Conversion removes the table structure, not necessarily the cell formatting. Select the converted range and choose Home > Clear > Clear Formats if you want to remove the remaining formatting too.

Can I remove the stripes but keep the filters?
Yes. Keep the table, then turn off Banded Rows in Table Design. You can also choose a different table style or clear the style while retaining the table and its filter controls.

Will my formulas still work after I convert the table?
Formulas generally remain, and Excel converts structured table references to ordinary cell references. Review formulas that depend on table names or on new rows being included automatically, because those table-specific behaviors no longer apply.

How do I make the cells look plain without changing their values?
Convert the table to a range if you also want to remove table behavior, then select the former table and choose Home > Clear > Clear Formats. Be aware that this also removes number formats, so dates and currency values may display differently.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.