Transposing data in Excel (i.e., swapping rows and columns) is a common requirement in data processing, reporting, and ETL workflows. Within desktop Microsoft Excel, you can copy a range and use the Transpose paste option. For .NET‑based applications, you may need to perform this transpose task programmatically without opening Microsoft Excel.

This tutorial walks you through two practical ways to transpose rows and columns in C# using Spire.XLS for .NET , from the built-in copy with transpose option to the native TRANSPOSE array formula.

• Method 1: Transpose Data via Static Copy
• Method 2: Transpose via the Dynamic TRANSPOSE Array Formula
• Excel Transpose Data Method Comparison
• Conclusion: Choosing the Right Method
• Frequently Asked Questions

Prerequisites

1. A .NET project (.NET Framework / .NET Core / .NET 5+)
2. Install Spire.XLS for .NET via NuGet Package Manager

Install-Package Spire.XLS

Note: Spire.XLS works on Windows, macOS, and Linux environments for cross-platform Excel processing.

Method 1: Transpose Data via Static Copy

Starting with Spire.XLS 16.1.4, the library introduced native support for transposing rows and columns when copying a cell range. It creates a disconnected snapshot of transposed data, preserving cell formatting.

using Spire.Xls;
class Program
{
    static void Main()
    {
        // 1. Create a new workbook
        Workbook workbook = new Workbook();

        // 2. Load an existing Excel file
        workbook.LoadFromFile(@"F:\SalesData.xlsx");

        // 3. Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];

        // 4. Define the source range (e.g., A1:D5)
        CellRange sourceRange = sheet.Range["A1:D5"];

        // 5. Define the destination cell (top-left corner)
        CellRange destCell = sheet.Range["F7"];

        // 6. Set transposition options
        CopyRangeOptions options = CopyRangeOptions.Transpose | CopyRangeOptions.All;

        // 7. Copy the range with transposition
        sourceRange.Copy(destCell, options);

        // 8. Save the result
        workbook.SaveToFile(@"Transposed.xlsx", ExcelVersion.Version2016);
        workbook.Dispose();
    }
}

How It Works

• CopyRangeOptions.Transpose: Inverts rows and columns for the copied cell range.
• CopyRangeOptions.All: Copies cell values, formulas, fill styles, borders and font settings. You can combine multiple flags with the bitwise OR (|) operator.
• You only specify the top‑left destination cell, and Spire.XLS will automatically calculate the full output range dimensions.

Result Preview:

The source range A1:D5 is transposed to F7:J10. The original rows become columns and the original columns become rows.

Copying Without Styles

If you only need the raw data and want to avoid copying cell formatting, omit CopyRangeOptions.All:

CopyRangeOptions options = CopyRangeOptions.Transpose;

Copying to a Different Worksheet

You can also transpose data into another worksheet:

Worksheet sourceSheet = workbook.Worksheets[0];
Worksheet destSheet = workbook.Worksheets.Add("TransposedData");

CellRange sourceRange = sourceSheet.Range["A1:D5"];
CellRange destCell = destSheet.Range["A1"];

CopyRangeOptions options = CopyRangeOptions.Transpose | CopyRangeOptions.All;
sourceRange.Copy(destCell, options);

Method 2: Transpose via the Dynamic TRANSPOSE Array Formula

Spire.XLS 16.9.2 added support for the Excel TRANSPOSE function. This method is useful when you want the transposed output to remain dynamically linked to the source range—if the source data changes, the transposed data updates automatically (depending on Excel's calculation settings).

using Spire.Xls;
class Program
{
    static void Main()
    {
        // Load an existing Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(@"F:\SalesData.xlsx");

        // Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];

        // Source range: A1:D5 (5 rows × 4 columns)
        // Transposed output: F7:J10 (4 rows × 5 columns)
        sheet.Range["F7:J10"].FormulaArray = "=TRANSPOSE(A1:D5)";

        // Save the result
        workbook.SaveToFile("TransposedFormula.xlsx", ExcelVersion.Version2016);
        workbook.Dispose();
    }
}

Important Notes

• The output range must be sized correctly: if the source is m rows × n columns, the destination must be n rows × m columns. In the example above, A1:D5 is 5×4, so F7:J10 is 4×5.
• The formula is entered as an array formula using FormulaArray, not the regular Formula property.
• This approach preserves the dynamic link only if the recipient opens the file in Excel with automatic calculation enabled.
• Source‑sheet cell formatting is not copied to formula‑driven output cells.

Result Preview:

Excel Transpose Data Method Comparison

Conclusion: Choosing the Right Method

Converting rows to columns or columns to rows in C# is a common requirement that Spire.XLS for .NET handles elegantly. The best method depends on whether you need a static snapshot or a dynamic, formula-driven result.

Pick Method 1 if:

• You need a snapshot of transposed data
• You want to retain cell styles, borders, fills, and formatting
• End users only view the result and won’t edit the source
• You don’t want formulas in the final Excel file

Pick Method 2 if:

• You’re building editable Excel templates for users
• The transposed block needs to refresh when source data changes
• You prefer minimal code and don’t need to copy formatting

Always validate exported Excel outputs against real‑world test data before production deployment. These reusable patterns help streamline row‑and‑column transformation within your .NET applications.

Frequently Asked Questions

Q: Can I transpose a single row or column?

A: Yes. Set the source range to a single row, such as A1:J1, or a single column, such as A1:A10. The output becomes a column or row, respectively.

Q: Does CopyRangeOptions.Transpose work with merged cells?

A: Merged cells can behave unpredictably when transposed. The CopyRangeOptions.Transpose method may preserve them in some cases, but it is safer to unmerge cells before transposing or test the result. 

Q: After static transpose, can I still modify the transposed cells independently?

A: Yes. The static transpose output creates independent cell copies. These cells hold plain values and formatting and have no link to the original source cells. Any edits to the transposed region will not affect the source data, and vice versa.

Q: Can I transpose multiple ranges at once?

A: Not in a single built-in call. You can transpose each range separately and place the outputs in different destination areas, or combine the data into one range before transposing.

When working with Excel in C#, copying rows, columns, and cells can feel like a simple task, but it often comes with a catch—how to keep the formatting intact. Whether you’re organizing data for a report, creating a presentation, or just trying to keep your spreadsheet looking sharp, maintaining the original look is crucial. In this article, we will demonstrate the methods to copy rows, columns, and cells in Excel while preserving the original formatting in C# using Spire.XLS for .NET.

• Copy Rows in Excel with Formatting in C#
• Copy Columns in Excel with Formatting in C#
• Copy Cells in Excel with Formatting in C#

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Copy Rows in Excel with Formatting in C#

Copying rows in Excel while preserving their formatting can be efficiently achieved using the Worksheet.CopyRow(CellRange sourceRow, Worksheet destSheet, int destRowIndex, CopyRangeOptions copyOptions) method. This method enables you to duplicate rows either within the same worksheet or across different worksheets with precision. Additionally, you can control the copying behavior, such as copying all formatting, conditional formatting, data validations, styles, or even just the formula values, through the CopyRangeOptions parameter.

The following steps explain how to copy rows across different worksheets with formatting using Spire.XLS for .NET.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the desired row that you want to copy using the Worksheet.Rows[index] property.
• Copy the row and its formatting from the source worksheet to the destination worksheet using the Worksheet.CopyRow(CellRange sourceRow, Worksheet destSheet, int destRowIndex, CopyRangeOptions copyOptions) method.
• Copy the column widths of cells in the source row to the corresponding cells in the destination row.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyRows
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired row that you want to copy
            CellRange row = sheet1.Rows[0];

            // Copy the row from the source worksheet to the first row of the destination worksheet
            sheet1.CopyRow(row, sheet2, 1, CopyRangeOptions.All);

            int columns = sheet1.Columns.Length;

            // Copy the column widths of the cells in the source row to the corresponding cells in the destination row
            for (int i = 0; i < columns; i++)
            {
                double columnWidth = row.Columns[i].ColumnWidth;
                sheet2.Rows[0].Columns[i].ColumnWidth = columnWidth;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyRow.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Copy Columns in Excel with Formatting in C#

Similarly, copying columns in Excel with formatting can be accomplished using the Worksheet.CopyColumn(CellRange sourceColumn, Worksheet destSheet, int destColIndex, CopyRangeOptions copyOptions) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the desired column that you want to copy using the Worksheet.Columns[index] property.
• Copy the column and its formatting from the source worksheet to the destination worksheet using the Worksheet.CopyColumn(CellRange sourceColumn, Worksheet destSheet, int destColIndex, CopyRangeOptions copyOptions) method.
• Copy the row heights of cells in the source column to the corresponding cells in the destination column.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyColumns
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired column that you want to copy
            CellRange column = sheet1.Columns[0];

            // Copy the column from the source worksheet to the first column of the destination worksheet
            sheet1.CopyColumn(column, sheet2, 1, CopyRangeOptions.All);

            int rows = column.Rows.Length;

            // Copy the row heights of cells in the source column to the corresponding cells in the destination column
            for (int i = 0; i < rows; i++)
            {
                double rowHeight = column.Rows[i].RowHeight;
                sheet2.Columns[0].Rows[i].RowHeight = rowHeight;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyColumn.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Copy Cells in Excel with Formatting in C#

In addition to copying rows and columns in Excel with formatting, Spire.XLS for .NET also allows copying cell ranges with formatting using the CellRange.Copy(CellRange destRange, CopyRangeOptions copyOptions) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the source cell range and the destination cell range using the Worksheet.Range[] property.
• Copy the source cell range and its formatting from the source worksheet to the destination cell range in the destination worksheet using the CellRange.Copy(CellRange destRange, CopyRangeOptions copyOptions) method.
• Copy the row heights and column widths of the source cell range to the destination cell range.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyCells
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];
            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the source cell range
            CellRange range1 = sheet1.Range["A1:E7"];
            // Get the destination cell range
            CellRange range2 = sheet2.Range["A1:E7"];

            // Copy the source cell range from the source worksheet to the destination cell range in the destination worksheet
            range1.Copy(range2, CopyRangeOptions.All);

            // Copy the row heights and column widths of the source cell range to the destination cell range
            for (int i = 0; i < range1.Rows.Length; i++)
            {
                CellRange row = range1.Rows[i];
                for (int j = 0; j < row.Columns.Length; j++)
                {
                    CellRange column = row.Columns[j];
                    range2.Rows[i].Columns[j].ColumnWidth = column.ColumnWidth;
                    range2.Rows[i].RowHeight = row.RowHeight;
                }
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyCells.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.