How to Merge Cells in Excel Without Losing Data
This article demonstrates how to merge multiple cells into one using Microsoft Excel and IronXL programmatically.
Merge Multiple Cells into a Single Cell Using Microsoft Excel
The built-in Merge and Center option in Excel is the quickest and simplest approach to integrate two or more cells. There are only two fast steps in the entire process:
- Choose the adjacent Excel cells that you want to combine into one cell.
- Click the Merge & Center button in the Home tab's Alignment group to merge two cells or multiple columns in Excel.
In this example, the upper left cell A1 contains a list of fruits, and data in two adjacent empty cells (B1 and C1) is combined to create a single, sizable cell that can hold the complete list.
Merging Cells
The text is centered, and the selected cells are combined into one large cell once you click Merge & Center, as shown in the above screenshot.
Excel's Options to Combine Multiple Cells
Select the choice you want from the drop-down menu by clicking the tiny drop-down arrow next to the Merge & Center button, which will give you access to a few more merging options supplied by Excel, such as Merge Across, Unmerge Cells.
Combine Cells
Using the Merge Across command, each row's individually chosen cells are combined.
Using the Merge Cells command, you can combine the selected cells into one without centering the text or losing data.
Simply pick the merged cell and click the desired alignment in the Alignment group on the Home page to change the text alignment of only the data after merging all the cells.
There are a few things to remember when using Excel's built-in functions to combine adjacent cells:
- Only the content of the upper-left cell will remain after merging, as all data in the other cells will be removed. Therefore, make sure that all the data you want to include in a merged cell is entered in the leftmost cell of the chosen range.
- The selected cells are likely in Edit mode if the Merge and Center button is grayed out. Try merging cells after exiting Edit mode by pressing the Enter key.
IronXL Library Features
You can quickly read and change Microsoft Excel documents with the IronXL library in C#. Without installing Microsoft Excel or relying on Microsoft Office Interop Excel, IronXL is a standalone .NET software library that can read other spreadsheet formats.
One of the best Excel spreadsheet libraries for C# is IronXL, which works with both .NET Framework and .NET Core. It supports many versions of .NET Frameworks, including Console Applications, Windows Forms, and Web Applications. IronXL makes it simple and quick to read Excel files with or without merged cells. Numerous Excel file types are supported, including XLSX, XLS, CSV, TSV, XLST, XLSM, and others. Numerous procedures, including import, edit, export of data tables, export of datasets, and others, are available. With IronXL, you can export and save files in a variety of extensions, including XLS, CSV, TSV, JSON, and more.
You can easily read, alter, and create Excel spreadsheet files in the .NET environment with IronXL's user-friendly C# API. It offers full support for Azure, .NET Core, .NET Framework, Xamarin, Mobile, Linux, and macOS.
IronXL supports various Excel column data formats, including text, integers, formulas, dates, currencies, and percentages, and it is capable of carrying out calculations like Excel.
Creating a New Project in Visual Studio
Open Visual Studio, select "New project" and choose "Console App" from the File menu. A C# Console Application is used for simplicity.
New Project
In the relevant text box, type the project name and file path. Next, select the necessary .NET Framework by clicking the Create button. The project will now create the program.cs
file's structure and open, allowing you to enter the program's code and build or run it.
Project Configuration
The solution's necessary IronXL library must then be downloaded. By entering the following command in the package manager, you can download the package:
Install-Package IronXL.Excel
IronXL
The "IronXL" package can also be found and downloaded using the NuGet Package Manager. Dependency management in your project is made simple with the NuGet Package Manager.
NuGet Package Manager
Merge Cells Using IronXL
IronXL can merge multiple columns/cells in existing Excel sheets. Below is the sample code to merge multiple cells.
using IronXL;
class Program
{
static void Main()
{
// Load the existing Excel workbook
var excelDoc = WorkBook.Load("demo.xlsx");
// Get the default worksheet
WorkSheet workSheet = excelDoc.DefaultWorkSheet;
// Define the range of cells to merge
var range = workSheet["A1:C1"];
// Merge the specified range of cells
workSheet.Merge(range.RangeAddressAsString);
// Save the changes made to the workbook
excelDoc.Save();
}
}
using IronXL;
class Program
{
static void Main()
{
// Load the existing Excel workbook
var excelDoc = WorkBook.Load("demo.xlsx");
// Get the default worksheet
WorkSheet workSheet = excelDoc.DefaultWorkSheet;
// Define the range of cells to merge
var range = workSheet["A1:C1"];
// Merge the specified range of cells
workSheet.Merge(range.RangeAddressAsString);
// Save the changes made to the workbook
excelDoc.Save();
}
}
Imports IronXL
Friend Class Program
Shared Sub Main()
' Load the existing Excel workbook
Dim excelDoc = WorkBook.Load("demo.xlsx")
' Get the default worksheet
Dim workSheet As WorkSheet = excelDoc.DefaultWorkSheet
' Define the range of cells to merge
Dim range = workSheet("A1:C1")
' Merge the specified range of cells
workSheet.Merge(range.RangeAddressAsString)
' Save the changes made to the workbook
excelDoc.Save()
End Sub
End Class
In the example code above:
- The existing Excel workbook is loaded into the IronXL workbook object.
- The default worksheet is selected.
- The range of cells (
A1:C1
) that you want to merge is specified. - The
Merge
method is then called to combine the specified range of cells. - Finally, the
Save
method is called to save the changes to the workbook.
This Merge
function merges a group of cells, retaining only the value of the first cell in the range without removing the values from other cells. However, the values from those merged cells are still accessible in IronXL.
Conclusion
A highly popular Excel add-in, IronXL, doesn't rely on external libraries. It is a standalone solution that doesn't require Microsoft Excel to be installed. It works with a variety of platforms.
With IronXL, you may programmatically perform a vast array of functions on Microsoft Excel documents. You can sort strings or numbers, trim and add data in empty cells, look up and replace values in blank cells, merge and unmerge cells, save files, concatenate functions, and perform other operations. You can specify cell data types and evaluate spreadsheet data with it as well. IronXL also has CSV file reading and writing capabilities.
When IronXL is released, it will cost $749 to acquire. Additionally, customers have the option to pay an annual membership fee for product upgrades and support. IronXL offers unrestricted redistribution rights for an extra fee. You can click on the licensing page here to go to the appropriate source and get more specific pricing details.
Frequently Asked Questions
How can I merge cells in Excel without losing data?
You can use IronXL to merge cells in Excel without losing data. IronXL provides a programmatic solution that retains the data from all merged cells, unlike the native Excel function which only keeps the content of the upper-left cell.
What is the programmatic method to merge cells in Excel using C#?
With IronXL, you can merge cells in Excel using C# by loading the workbook, selecting the worksheet and range, applying the Merge
method, and then saving the workbook. This process ensures all data is retained.
How does IronXL handle data when merging cells compared to Excel's built-in feature?
IronXL retains the data of all cells being merged, ensuring no information is lost. In contrast, Excel's built-in 'Merge & Center' only retains the data from the upper-left cell.
Can I merge cells in Excel across different platforms using IronXL?
Yes, IronXL supports multiple platforms, including .NET Framework and .NET Core, allowing you to merge cells in Excel files across various environments like Console Applications, Windows Forms, and Web Applications.
Is it necessary to have Microsoft Excel installed to use IronXL for Excel operations?
No, IronXL is a standalone .NET library that does not require Microsoft Excel to be installed on your system. It allows you to perform Excel operations independently.
What are the steps to merge cells in Excel using IronXL?
To merge cells using IronXL, first load the Excel file into your application. Select the worksheet and the range of cells to merge. Use IronXL’s Merge
method on the selected range, and then save your changes to the file.
How do I install IronXL in a Visual Studio project for Excel manipulation?
You can install IronXL in a Visual Studio project via the NuGet Package Manager. Search for 'IronXL' and install it, or use the command Install-Package IronXL
in the Package Manager Console.
Can IronXL be used to perform other Excel operations besides merging cells?
Yes, IronXL supports a range of Excel operations beyond merging cells, including reading, editing, and creating Excel files, data import/export, and performing calculations similar to Excel.
What licensing options are available for using IronXL?
IronXL offers licensing options that include an annual membership with product upgrades and support. Detailed pricing information is available on their licensing page.
What types of Excel formats does IronXL support?
IronXL supports multiple Excel formats such as XLSX, XLS, and CSV, allowing for extensive document manipulation and compatibility across different Excel file types.