Skip to footer content
USING IRONXL

How to Convert a Table to a Range in Excel

An Excel table provides a list of features that are used to effectively analyze and manage data, such as auto-filter and sort options, total row, calculated columns, table style, automatic expansion, etc.

The screenshot below shows a difference between a normal range and a table style formatting:

How to Convert Table to Range in Excel - Figure 1: Normal Range
How to Convert Table to Range in Excel - Figure 2: Table Format

This tutorial will demonstrate how to convert an Excel table to a range.

How to Create a Table?

There are many ways to create or convert a range to a table range in Excel. To insert a table in Excel, click any single cell within your data set, and do any of the following:

  1. On the Insert menu tab, in the Tables section group, click the Table option. A default style table will be inserted.

    How to Convert a Table to a Range in Excel, Figure 3: Insert a New Table in Excel with Default Styles Insert a New Table in Excel with Default Styles

  2. On the Home menu tab, in the Styles section group, click Format as Table, and select one of the predefined table styles. You can choose any banded rows style.

    How to Convert a Table to a Range in Excel, Figure 4: Apply a predefined format to an existing Excel spreadsheet table Apply a predefined format to an existing Excel spreadsheet table

  3. Another option and the fastest way to create an Excel table is by pressing the keyboard shortcut: Ctrl + T.

Whichever method you choose, the Create Table dialog box appears. Excel automatically selects the data range. Verify the selected range, then check/uncheck the "My table has headers" option, and click OK.

How to Convert a Table to a Range in Excel, Figure 5: Format the Table with Excel preselected style Format the Table with Excel preselected style

As a result, a nicely formatted table is created in the worksheet.

The filter arrows allow us to filter and sort the data in the table based on your criteria. While scrolling, the column headers box is visible. New data can be included using automatic table expansion, Quick totals, Calculating table data with ease are some prominent table features.

Now we know how to convert a regular range of cells to a table style. At some point, we might need to remove table formatting and convert the table to a normal range without losing any data and formulas. So let's have a look at how to convert an Excel table to a range.

How to Convert Table Style to a Normal Range?

There are two ways to convert a table to a cell range in Excel.

If you want to remove a table without losing the table data, table formatting, or structured references, you can follow the below steps:

  1. Go to the Table Design tab under Table Tools > Tools group, and click Convert to Range.

    How to Convert a Table to a Range in Excel, Figure 6: Converting a Styled Table into a Normal Range using the Menu Bar options Converting a Styled Table into a Normal Range using the Menu Bar options

  2. Alternatively, right-click anywhere within the table, and click Table > Convert to Range.

    How to Convert a Table to a Range in Excel, Figure 7: Converting a Styled Table into a Normal Range using the Context Menu Converting a Styled Table into a Normal Range using the Context Menu

This is how you can convert the data of a table to a range. Notice that the arrows are removed from the range but the data and formatting are preserved.

How to Convert a Table to a Range in Excel, Figure 8: The appearance of the spreadsheet data, after being converted back to a Normal range The appearance of the spreadsheet data, after being converted back to a Normal range

The IronXL C# Library

IronXL is a .NET library that facilitates reading and editing Microsoft Excel documents with C# without Microsoft Excel or Interop. IronXL can read and manipulate Excel files with accuracy and fast performance. It supports all .NET components, along with platforms such as Android, Xamarin, Linux, Azure, AWS, macOS, Docker, and MAUI.

With a few lines of code, developers can perform all Excel-related tasks without any complication. These tasks include adding two cells, adding rows and columns of data to Excel tables, and applying functions across rows and columns, among others.

IronXL Feature Set

  • Load, read, and edit data — from XLS/XLSX/CSV/TSV
  • Saving and exporting — to XLS/XLSX/CSV/TSV/JSON
  • System.Data Objects — work with Excel Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
  • Formulas — works with Excel formulas. Formulas get recalculated whenever the sheet is edited.
  • Ranges — easy to use ["A1:B10"] syntax.
  • Sorting — it can sort rows, columns, and ranges within a table or normal range.
  • Styling — font, font size, cell style, alignments, background color, and many more styling options are available.

Below are some code examples of C# code to manipulate Excel files:

using IronXL;

public class ExcelExample
{
    public static void Main()
    {
        // Load an existing Excel workbook
        WorkBook workbook = WorkBook.Load("test.xlsx");

        // Choose the default worksheet in the workbook
        WorkSheet worksheet = workbook.DefaultWorkSheet;

        // Set formulas for various cells
        worksheet["A1"].Formula = "SUM(B8:C12)"; // Sum cells B8 to C12
        worksheet["B8"].Formula = "=C9/C11"; // Divide value in C9 by value in C11
        worksheet["G30"].Formula = "MAX(C3:C7)"; // Find the maximum value from cells C3 to C7

        // Force recalculating all formulas in all sheets
        workbook.EvaluateAll();

        // Retrieve calculated value from a cell
        string formulaValue = worksheet["G30"].Value.ToString(); // e.g., 52

        // Retrieve the formula as a string from a cell
        string formulaString = worksheet["G30"].Formula; // e.g., "MAX(C3:C7)"

        // Save changes with updated formulas and calculated values
        workbook.Save();
    }
}
using IronXL;

public class ExcelExample
{
    public static void Main()
    {
        // Load an existing Excel workbook
        WorkBook workbook = WorkBook.Load("test.xlsx");

        // Choose the default worksheet in the workbook
        WorkSheet worksheet = workbook.DefaultWorkSheet;

        // Set formulas for various cells
        worksheet["A1"].Formula = "SUM(B8:C12)"; // Sum cells B8 to C12
        worksheet["B8"].Formula = "=C9/C11"; // Divide value in C9 by value in C11
        worksheet["G30"].Formula = "MAX(C3:C7)"; // Find the maximum value from cells C3 to C7

        // Force recalculating all formulas in all sheets
        workbook.EvaluateAll();

        // Retrieve calculated value from a cell
        string formulaValue = worksheet["G30"].Value.ToString(); // e.g., 52

        // Retrieve the formula as a string from a cell
        string formulaString = worksheet["G30"].Formula; // e.g., "MAX(C3:C7)"

        // Save changes with updated formulas and calculated values
        workbook.Save();
    }
}
Imports IronXL

Public Class ExcelExample
	Public Shared Sub Main()
		' Load an existing Excel workbook
		Dim workbook As WorkBook = WorkBook.Load("test.xlsx")

		' Choose the default worksheet in the workbook
		Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

		' Set formulas for various cells
		worksheet("A1").Formula = "SUM(B8:C12)" ' Sum cells B8 to C12
		worksheet("B8").Formula = "=C9/C11" ' Divide value in C9 by value in C11
		worksheet("G30").Formula = "MAX(C3:C7)" ' Find the maximum value from cells C3 to C7

		' Force recalculating all formulas in all sheets
		workbook.EvaluateAll()

		' Retrieve calculated value from a cell
		Dim formulaValue As String = worksheet("G30").Value.ToString() ' e.g., 52

		' Retrieve the formula as a string from a cell
		Dim formulaString As String = worksheet("G30").Formula ' e.g., "MAX(C3:C7)"

		' Save changes with updated formulas and calculated values
		workbook.Save()
	End Sub
End Class
$vbLabelText   $csharpLabel

IronXL helps developers to rely on simple yet high-performance code with accuracy and efficiency, which helps reduce the risk of error and makes it easier to manipulate Excel files programmatically.

You can download the IronXL software product and try it for free.

Frequently Asked Questions

What is the purpose of converting a table to a range in Excel?

Converting a table to a range in Excel can be useful if you want to remove table features like auto-filter, sorting, and formatting while preserving the data and underlying formulas. This can simplify data management and improve performance in larger datasets.

How do you create a table in Excel?

To create a table in Excel, click on a single cell within your data set, go to the Insert menu tab, and select the Table option. Alternatively, you can use the Home menu tab, click Format as Table, and choose a predefined style. You can also press Ctrl + T as a keyboard shortcut.

What are the key features of an Excel table?

Excel tables offer features like auto-filter and sort options, total row, calculated columns, table styles, automatic expansion, and more, which help in effectively analyzing and managing data.

How can you convert a table to a normal range in Excel?

To convert a table to a normal range, go to the Table Design tab under Table Tools, and click Convert to Range. Alternatively, right-click within the table, select Table, and then Convert to Range. This will remove table formatting but keep the data intact.

What happens to the data and formatting when converting a table to a range?

When converting a table to a range, the structured references and table formatting are removed, but the data and any custom formatting are preserved.

How can you efficiently work with Excel files programmatically?

Using IronXL, a .NET library, developers can efficiently read, edit, and manipulate Microsoft Excel documents within C# without needing Microsoft Excel or Interop. It supports various platforms and provides functionalities like adding rows, applying functions, and more.

Can IronXL handle different file formats?

Yes, IronXL can load, read, and edit data from XLS, XLSX, CSV, and TSV files, and it also allows saving and exporting to these formats as well as JSON.

What are some of the styling options available when manipulating Excel files?

IronXL offers various styling options including font type and size, cell style, alignments, background color, and more to customize the appearance of Excel spreadsheets.

Is it possible to manipulate Excel files in cloud environments?

Yes, using IronXL, Excel files can be manipulated in cloud platforms such as Azure and AWS. It also supports other environments like Android, Xamarin, Linux, macOS, Docker, and MAUI.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
Talk to an Expert Five Star Trust Score Rating

Ready to Get Started?

Nuget Passed