How to Convert Excel Table to Ranges & Vice Versa

In Excel, tables offer powerful features like filtering, automatic formatting, and calculations. This guide will explain how to create a table from a range and make the most of these features.

Sometimes, however, you may need to convert an Excel table back to a regular range and remove its formatting. By following a few specific steps, you can do this efficiently without being left with unwanted table styles.

Converting an Excel Table to a Range

If you have a table in Excel but want to convert it into a regular range of cells—without the special formatting—follow these steps:

Steps to Convert a Table to a Range:

1. Select the Table
   Click anywhere inside the table to activate it.
2. Clear the Formatting
   It is very important to clear the formatting first before converting to a range; otherwise, you’ll need to manually remove the leftover formatting. On the “Table” tab, open the style options dropdown and select “Clear” to remove the visual formatting.
3. Convert to Range
   On the “Table” tab, click “Convert to Range.” 

Creating a Table from a Range of Data

When you have a range of data that you’d like to convert into a table to take advantage of Excel’s features, here’s how to do it:

Steps to Create a Table from a Range:

1. Select the Data Range
   Click anywhere within the range of data you want to convert into a table.
2. Insert a Table
   • Go to the “Insert” tab on the Ribbon.
   • Click the “Table” button.
     Excel will automatically detect the range, but if it’s incorrect, you can adjust the range manually. 
3. Choose a Table Style
   Once the table is created, Excel will apply a default style. You can change the appearance by selecting a different style under the “Table” tab.

4. Use Table Features
   Excel tables offer many helpful features:
   • Header Row: Show or hide the header row.
   • Total Row: Add a total row to the bottom of the table for automatic calculations.
   • Alternating Row Colors: Use banded rows to improve readability.
   • Column Emphasis: Highlight the first or last columns for better focus.
   • Automatic Filters: Tables automatically include filter options for each column.
5. Enable Auto Calculations
   If you add a total row, Excel provides an auto-sum dropdown that allows you to easily apply functions like SUM, AVERAGE, or COUNT. The total row will ignore rows hidden by filters.

Pro Tip: Using the total row feature allows you to quickly calculate values like sums or averages, and the results will dynamically update as you filter or adjust the data.

Summary

• To convert a table to a range:
  • First, clear any formatting, and then use the “Convert to Range” option to remove the table structure.
• To create a table from data:
  • Select your range, go to “Insert Table,” and apply formatting and features like filters, total rows, and alternating colors for easier data management.

With these steps, you can quickly switch between Excel tables and ranges based on your formatting and data needs.