How to convert table to normal range in Excel and vice versa

This article explains how to quickly convert a cell range into an Excel table using a chosen table style, and how to revert a table back to a standard range.

Excel tables simplify data management, calculation, and updates. However, sometimes a table format isn't ideal. Fortunately, you can easily switch between a table and a range without data loss.

Excel Tables vs. Ranges: Key Differences

Many Excel users confuse "range" and "table." A range is simply a selected group of cells (e.g., A1:C6). An Excel table is a structured, pre-formatted, dynamic range with added features like calculated columns, banded rows, and built-in sorting/filtering. Its dynamic nature means it automatically expands as you add data. Tables are visually distinct, typically with a border, filter arrows, and frozen headers.

Converting a Range to an Excel Table: Advantages

Transforming a range into a table unlocks powerful features:

• Predefined table styles
• Structured references (easier formulas)
• Integrated sorting and filtering
• Automatic table expansion
• Quick totals
• Calculated columns

Converting an Excel Table to a Range

To convert a table back to a range, right-click any cell within the table and select "Table" > "Convert to Range." Alternatively, select a cell in the table, go to the "Table Design" tab, and click "Convert to Range." Confirm your choice when prompted. This removes table features but retains any applied table styling.

Note: This removes features like auto-expansion and structured references. To remove table styling as well, clear formatting before conversion.

Converting a Data Range to a Table

Several methods exist for creating tables from ranges:

Method 1 (Keyboard Shortcut): Select a cell in your data, press Ctrl T, verify the range and header presence, and click "OK."

Method 2 (Ribbon): Go to the "Insert" tab > "Tables" group and click "Table." Verify the range and header settings in the dialog.

Method 3 (Specific Table Style): On the "Home" tab > "Styles" group, click "Format as Table," choose a style, verify the range and header settings, and click "OK." To remove existing cell formatting before applying the table style, right-click the style and select "Apply and Clear Formatting."

The Quick Analysis Tables feature offers another quick way to create tables. This guide covers converting ranges to tables and reverting them back to ranges in Excel.

The above is the detailed content of How to convert table to normal range in Excel and vice versa. For more information, please follow other related articles on the PHP Chinese website!