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 26, 2022

How to Merge Cells in Excel: Step by Step Tutorial

Excel is a powerful spreadsheet application that allows users to input and organize data in rows and columns. Large data tables can be challenging to read or edit, especially when there is a high number of cells. One option is to merge cells in an Excel sheet in order to make tables easier to read and edit.

Merging cells in an Excel sheet is a commonly-performed task. It is often necessary to combine two or more cells into one cell. There are in fact many reasons why we need to merge cells in an Excel sheet. For example, if you have a large cell and want to center it, you need to join the adjacent cells on either side. In some cases, you may want to merge cells with no adjacent cells because when there are no adjacent cells, Excel will think that the merged cell is two separate cells, and will therefore not center it.

Many people find it challenging to understand how to merge cells in Excel. At first, this may seem like a daunting task, but we've got your back! This blog will walk you through how to quickly merge cells in Excel.

IronXL: The C# Excel Library

IronXL is a C# Excel library with many advanced features for Excel operations. One main benefit of the IronXL C# library is that you don't need to install Excel software locally on your system. It processes the Excel document without using Excel software.

You can create a new Excel file and format data according to your needs using the IronXL C# library. It is a complete go-to package for all Excel operations. If you are building any software and have to create or edit an Excel file in your software, you can quickly perform such tasks with the help of the IronXL library. To use this library in your software build, you must download its DLL file or install it with NuGet Package Manager. You can use the free trial version to test all the features. The trial version is free, and it is very straightforward to use the license key with your project. You can also use this tutorial to better understand it. Once you are satisfied with IronXL, you can buy it at a very reasonable price.

Next, we will talk about how to merge cells in Excel using the IronXL C# library. IronXL provides elementary and easy-to-use functions to read and merge cells in an Excel file. Let's examine how to join cells in C# language using the IronXL library:

Create a C# .NET or VB .NET project in Visual Studio.
Download the DLL file of the IronXL library or install it via NuGet Packet Manager in Visual Studio.
Apply your License key using this tutorial.
Use IronXL in your project.

a. using IronXL;
Load an Excel book using WorkBook.Load() function.

a. WorkBook wb = WorkBook.Load("fileaddress.xlsx");
When the Excel file loads, select the sheet where we merge cells using the GetWorkSheet() function.

a. WorkSheet ws = wb.GetWorkSheet("SheetName");
We have selected the sheet where we have to merge cells. Let's choose the cell address to merge using the Merge() function.

a. ws.Merge("From Cell : To Cell");
Selected cells are merged, and now it's time to save the edited file using the SaveAs() function.

a. wb.SaveAs("Filename.xlsx");
You have now seen how easy it is to merge cells in Excel using the IronXL library, given a working knowledge of C#. You can also use the tutorials on Excel operations in the IronXL official documentation.

You can download a file project from this link.

Merging Cells in Microsoft Excel

Introduction to Microsoft Excel

Microsoft Excel is a spreadsheet application that helps users organize, analyze, and share data. It is one of the most popular office software programs to date. The goal of Excel is to simplify analyzing and managing data in order for people to be more productive at work. This particular tutorial will discuss how to merge cells in Excel. Before proceeding to examine how to join cells in Excel, you need to have a basic working knowledge of the following terms: cells and ranges.

What are Cells and Ranges in Excel

Cells:

A cell is the smallest unit of data in a spreadsheet. This is where you enter data, formulas, and other information. Every spreadsheet has a grid made up of rows and columns, and every cell in the grid is assigned an address that uniquely identifies it on the grid. You can identify cells by their location in the grid, such as A1 or B3.

Ranges:

A cell range is a group of cells with different addresses. For example, a cell range with two numbers must be formatted as A1:A5. In Excel, the default reference style is "Cells," which must be changed if you want to use a different one. There are two kinds of ranges: horizontal and vertical. Each type has its purpose and uses.

Methods of Merging Cells in Excel

Merging cells is essential because it saves time when working with large tables with many rows and columns. When merging cells, the software will automatically center on the merged cell so that there's no need to adjust it manually. When we merge cells, most upper-left cell values are retained. This will also make your table look nicer and easier to read. Let's look at how we can merge multiple cells in Excel.

1) Using the Merge & Center Button

The Merge & Center button is the simplest method to merge cells in Excel. Let's examine how to merge cells using this method:

Open your Excel file in Microsoft Excel.

Select the cells you want to merge.

Now click on the Merge & Center button to merge selected cells.

The cells will be merged. You will get this output:

This method automatically merges cells and makes content align as centered. It is important to note that the software always preserves the furthest upper-left cell value. Other values will be discarded.

2) Merge cells using the "Format Cells" dialogue box

You can merge cells using the "Format Cells" dialogue box. This gives you multiple options. Let's examine how to merge cells using the "Format Cells" dialogue box:

Open your file in the Excel Software.

Select the cells you want to merge.

Open the "Format Cells" dialogue box. You can open it in various ways.

Go to the Home tab and click on the arrow icon on the Format button in the Cells section, and from the dropdown, select the Format Cells option.

You can open the Format Cells dialogue box using the keyboard shortcut Ctrl + 1.

The third way to open this dialogue box is to right-click on selected cells and select the Format Cells menu.

The Format Cells dialogue box will open. Go to the alignment tab, select merge cells, and click the OK button.

Selected cells will be merged. You can identify merged cells by checking the borderlines of the cells.

Notice that data in cells is not centered using this technique. This needs to be done manually from the home tab using the formatting section.

3) Alt Hot Key

You can perform this task quickly if you don't want to use your mouse, and instead use the keyboard to merge cells. You must select the cells you want to join and press Alt + H + M + C simultaneously. Your selected cells will be merged.

If you want to use the merge tool frequently, you can add this option to your quick access toolbar. Let's look at how we can add the Merge and Center option to the Quick Access toolbar.

Go to the title bar and click on the little arrow icon. It will show a dropdown. From the dropdown, select the More Commands option.

A new pop-up box will open. Scroll down and select the Merge and Center option.

After selecting Merge and Center, click the Add button, and this will add the command to the Quick Access toolbar options. Next, press OK to apply this change.

Now, you can use the Merge and Center option using the Quick Access toolbar.

You can use this button using Alt with another key. You can see which key is related to the Quick Access toolbar option by clicking the Alt key.

You can use the Alt + 5 key combination to merge cells quickly.

5) Concatenate Function

If you wish to combine data from two or more columns, then the Merge and Center option will not work because it retains only the first cell's data and discards that from other cells. If you try to merge two or more cells that contain data, it will give you a warning as shown below:

To combine the data from cells, you have to use an Excel function called the "Concatenate" function. Let's see how it works.

Let's say we want to combine a first name and a second name.

We can't do it using the Merge and Center option. We will do it using the Concatenate function. Now move to the cell after the second name, where the data will be combined.

Type =Concatenate() and in parameters, give the address of those cells whose data you want to merge, like this:

Press Enter, and you will see that the data from the two cells has been merged.

Next, select the cell where the data is combined and dragged from the user to apply a function on other cells in the column.

You will see that the data from the two cells have been combined. You can now delete the columns if you don't want them.

Unmerge Cells

We can unmerge cells with a straightforward method:

Select the merged cells in Excel.
Go to the home tab and click on the Merge and Center button. It will unmerge cells.

This is how you can unmerge cells very quickly. If you wish to unmerge all merged cells in Excel, you can do it easily as follows:

Press Ctrl+A to select the whole sheet.
Go to the Home tab and select the Merge and Center button. It will unmerge all merged cells in an Excel sheet with just one click.