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
-
On the worksheet, select a range of cells that you want to format by applying a predefined table style.
-
On the Home tab, in the Styles group, click Format as Table.
-
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.
-
-
Click anywhere in the table.
-
Go to Table Tools > Design on the Ribbon. On a Mac go to the Table tab.
-
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 or get support in Communities.
See Also
Video: Create and format an Excel table
Total the data in 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
