Apply a table style without inserting an excel table

You can quickly format your worksheet data by applying a predefined table style. However, when you apply a predefined table style, an Excel table is automatically created for the selected data. If you don't want to work with your data in a table, but keep the table style formatting, you can convert the table back to a regular range.

Note: When you convert a table back to a range, you lose table properties like row shading with Banded Rows, the ability to use different formulas in a Total Row, and adding data in rows or columns directly adjacent to your data will not automatically expand the table or its formatting. Any structured references (references that use table names) that were used in formulas turn into regular cell references.

Create a table, then convert it back into a Range

1. On the worksheet, select a range of cells that you want to format by applying a predefined table style.

2. On the Home tab, in the Styles group, click Format as Table.

   

3. Click the table style that you want to use.

   Tips: 

   • Auto Preview - Excel will automatically format your data range or table with a preview of any style you select, but will only apply that style if you press Enter or click with the mouse to confirm it. You can scroll through the table formats with the mouse or your keyboard's arrow keys.

   • Custom table styles are available under Custom after you create one or more of them. For information on how to create a custom table style, see Format an Excel table.

4. Click anywhere in the table.

5. Go to Table Tools > Design on the Ribbon. On a Mac go to the Table tab.

6. In the Tools group, click Convert to Range.

   

   Tip: You can also right-click the table, click Table, and then click Convert to Range.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Overview of Excel tables

Video: Create and format an Excel table

Total the data in an Excel table

Format an Excel table

Resize a table by adding or removing rows and columns

Filter data in a range or table

Using structured references with Excel tables

Excel table compatibility issues

Export an Excel table to SharePoint