using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published February 28, 2023

How to Convert a Table to a Range in Excel

What is Excel?

Microsoft Excel is a spreadsheet application software used to store, organize, and format data in a tabular form. Google Sheets is also available online to keep all the data in one place for sharing.

Microsoft Excel stores data range to a table. A table here represents the rows and columns of the Excel spreadsheet and the intersection of a row and column is referenced as a cell. The data is organized in such a way that it can be easily viewed and edited.

What is an Excel Table?

An Excel table is a rectangular range of data in the form of rows and columns, that has been defined and named specifically. An Excel table facilitates the management of its contents independently from the rest of the worksheet data.

An Excel tables provide a list of features which 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:

Normal Range

Table Format

To convert an Excel table to a range, we need to have table functionality first.

How to Create 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:

On the menu Insert tab, in the Tables section group, click Table tab option. A default style table will be inserted.
Insert a New Table in Excel with Default Styles
On the Home menu tab, in the Styles section group, click Format as Table option, and select one of the predefined table styles. You can choose any banded rows style.
Apply a predefined format to an existing Excel spreadsheet table
Another option and the fastest way to create an Excel table is 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.

Excel automatically selects the data range for new tables based on the context. Verify that the selection is correct and press OK to apply the desired table formatting styles.

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 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 lets have a look 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:

Go to the Table Design tab under Table Tools> Tools group, and click Convert to Range.
Converting a Styled Table in to a Normal Range using the Menu Bar options
Or, right-click anywhere within the table, and click Table > Click Convert to Range.
Converting a Styled Table in to a Normal Range using the Context Menu

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

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 in with accuracy and fast performance. It supports all .NET components, along with platforms such as Android, Xamarin, Linux, Azure, and macOS.

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;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' Set Formulas
Private worksheet("A1").Formula = "Sum(B8:C12)"
Private worksheet("B8").Formula = "=C9/C11"
Private worksheet("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()

' Get Formulas
' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet("G30").Value

'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()

VB C#

IronXL helps developers to rely on simple yet high performance with accuracy and efficient lines of code, 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 from this link.