How to Convert Table to Range in Excel

What Are the Three Methods to Create Tables?

There are several ways to create or convert a range to a table in Excel. Understanding these methods is essential when creating Excel files programmatically or managing worksheet data. To insert a table in Excel, click any single cell within your data set and do any of the following:

1. On the Insert tab, in the Tables group, click Table. A default style table will be inserted. This method is similar to how you'd create a new Excel file with structured data.

   Insert a New Table in Excel with Default Styles

2. On the Home tab, in the Styles group, click Format as Table and select one of the predefined table styles. You can choose any banded rows style. This approach is particularly useful when styling cells and borders for better visual presentation.

   Apply a predefined format to an existing Excel spreadsheet table

3. The fastest way to create an Excel table is by pressing Ctrl + T. This quick method is especially helpful when working with Excel in C# applications where you need to simulate user actions.

How Do I Configure Table Settings During Creation?

Whichever method you choose, the Create Table dialog box appears. Excel automatically selects the data range, similar to how IronXL's range selection works in C# code. Verify the selected range, check or uncheck the "My table has headers" option, and click OK.

Format the Table with Excel preselected style

When loading spreadsheets programmatically, understanding these table structures helps in data manipulation. The selected range formula ($A$1:$E$6) uses absolute references, which are important when working with Excel formulas in C#.

Why Should I Use Table Formatting?

As a result, a nicely formatted table is created in the worksheet. Tables offer several advantages over regular ranges, especially when you need to sort cells or apply conditional formatting.

The filter arrows allow you to filter and sort data based on your criteria. While scrolling, column headers remain visible. New data can be included using automatic table expansion. Quick totals and calculating table data with ease are some prominent table features. These features are particularly useful when converting Excel to SQL databases or working with DataTables.

Now we know how to convert a regular range of cells to a table. At some point, we might need to remove table formatting and convert the table to a normal range without losing data or formulas. This is common when exporting Excel spreadsheets to systems that don't support table objects. Let's look at how to convert an Excel table to a range.

What Are the Two Conversion Methods?

There are two ways to convert a table to a cell range in Excel. Understanding both methods is important when automating Excel tasks with libraries like IronXL.

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

1. Go to the Table Design tab under Table Tools > Tools group, and click Convert to Range. This method preserves all your cell formatting and background colors.

   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. This context menu approach is useful when editing Excel files programmatically.

   Converting a Styled Table into a Normal Range using the Context Menu

Both methods prompt you with a confirmation dialog. Click Yes to proceed with the conversion. This safety feature prevents accidental conversion, similar to how password protection prevents unauthorized changes.

What Happens After Converting to Range?

This is how you convert table data to a range. Notice that the arrows are removed from the range but the data and formatting are preserved. The data maintains its structure, making it ideal for importing into C# applications or converting to CSV format.

The appearance of the spreadsheet data, after being converted back to a Normal range

After conversion, you lose table-specific features like:

• Automatic expansion when adding new data
• Structured reference formulas
• Built-in filtering and sorting controls
• Table styles that update automatically
• The ability to use named tables features

However, you retain all your data, formulas, and basic formatting. This makes it suitable for reading Excel files in legacy systems or converting spreadsheet file types.

How Does IronXL Help Automate Excel Tasks?

IronXL is a .NET library that facilitates reading and editing Microsoft Excel documents with C# without Microsoft Excel or Interop. IronXL reads and manipulates 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.

For startup founders looking to build MVPs quickly, IronXL offers a cost-effective solution for Excel automation without the overhead of Microsoft Office installations. You can work with VB.NET Excel files or integrate with Blazor applications seamlessly.

Why Should Startup Developers Use IronXL?

With a few lines of code, developers can perform all Excel-related tasks without any complication. These tasks include adding comments to cells, adding rows and columns of data to Excel tables, and applying functions across rows and columns. The library also supports advanced features like creating charts and managing images.

Which File Formats Does IronXL Support?

• Load, read, and edit data from XLS/XLSX/CSV/TSV
• Save and export to XLS/XLSX/CSV/TSV/JSON
• System.Data Objects — work with Excel as DataSet and DataTable objects
• Formulas — work with Excel formulas that recalculate when edited
• Ranges — easy-to-use ["A1:B10"] syntax
• Sorting — sort rows, columns, and ranges within tables or ranges
• Styling — font, size, cell style, alignment, background color, and more

Additional features include freeze panes, hyperlink support, autosize rows and columns, workbook metadata editing, and merging cells.

How Do I Implement Excel Formulas with IronXL?

Below are code examples of C# code to manipulate Excel files using IronXL's powerful formula capabilities and math functions:

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();
    }
}

You can also create Excel spreadsheets from scratch, open Excel worksheets, and read XLSX files with similar ease.

Why Is IronXL Ideal for Startup MVPs?

IronXL helps developers rely on simple yet high-performance code with accuracy and efficiency, reducing error risk and making it easier to manipulate Excel files programmatically. The library supports ASP.NET MVC applications, converting DataTables to CSV, and writing CSV files efficiently.

For startups concerned about costs, IronXL offers flexible licensing options with clear pricing and the ability to apply license keys easily. The library has undergone significant performance improvements and enhancements that make it suitable for production use.

You can download the IronXL software product and try it for free. Check the API Reference for detailed documentation and explore the changelog for the latest updates.