Excel will give you a warning message telling that you will lose the data, when you try to merge cells using the Merge & Center feature. In this article, we’re going to show you how to merge cells in Excel without losing your data.
- Begin by adding a new column where you want the merged column to be placed.
- Select the first cell and create a formula CONCATENATE formula, adding a separator if necessary. In this example, we used a space (” “).=CONCATENATE(C2,” “,D2)
- Copy down the cell to apply to all cells in the column.
- When all cells in the new column are selected, copy the cells by pressing Ctrl + C. Alternatively, you can click the Copy item from the right-click menu.
- After copying, open the right-click menu again. This time click the Paste as Values button to replace formulas with static values.
- Since we got rid of the formulas, we do not need the old columns., and they can be safely removed. You can delete them by selecting the columns and clicking the Delete button from the right-click menu.
- You will end up with the merged column.
Why Merge Cells?
Merging cells in a spreadsheet is a process that allows you to join one or more adjacent cells (horizontally or vertically or both) into one larger cell that is then displayed across multiple columns or rows. One of the main reasons to do this is for reporting or presentation purposes – or simply put, to make a spreadsheet look nice.
If we take a quick look at the example below, you’ll see we have a simple Sales Results spreadsheet, with raw data from a number of different Departments and Divisions. A typical formatting decision for this type of information is to have the heading centred across the top of the table. To achieve this, you could merge all the heading cells to create one large cell and then centre the text. As the information in our example is raw data, we’d discourage you from doing any formatting to it at all.
The purpose of this article is not to teach you how to Merge Cells in Excel, but to provide some practical advice on some issues surrounding using merged cells unnecessarily throughout your spreadsheets.
Sample Sales Report Raw Data
- When cells are merged, the contents of only one cell (the upper-left cell) appear in the final merged cell block. The contents of all the other cells, that are being merged, are deleted.
- Excel treats the contents of the merged cell block as being contained in the top left cell of the merged block. For example, if you merged cells A1, A2, B1 and B2, Excel would contain the data in this merged block in cell A1. Important to know if you ever write a formula that involves a merged cell.
Now when it comes to creating a spreadsheet, we at ExcelSuperSite, use what we call the D.A.R.E Methodology.
This methodology breaks the design of a spreadsheet down into separate functions:
D – Data – separate your raw data and DO NOT modify its layoutA – Analysis – separate your calculations and analysisR – Report – create separate summary/presentation sheets in your workbooksE – Evaluate – does your information make sense
Following this methodology, merged cells won’t be used in any the Data areas nor Analysis areas of a spreadsheet, but may occasionally be used in the Reporting areas. It is generally not an issue to use merged cells in the Reporting areas of a spreadsheet as these areas are simply presenting the information obtained or analysed elsewhere and should not be used for calculation or analysis of raw data.
So What are the Issues with Merging Cells?
So let’s assume that you don’t follow our D.A.R.E. Methodology for creating your spreadsheets and that your spreadsheets are a mix of data and analysis and presentation/reporting tables. Then just for the fun of it, we also through some merged cells into the mix. Now, when trying to make changes to a spreadsheet that is constructed like this, we are faced with all sorts of complications. These include:
- Data containing merged cells can not be treated like a normal data table – meaning that we can’t use all of the tools that we might want to use for referring to a properly formatted data table, such as pivot tables, SUMIF, etc;
- Copying and pasting cell ranges is restricted to cells that are merged EXACTLY the same way as the cells being copied;
- Fill down doesn’t work if any of the cells in the range to be filled are merged;
- If you unmerge a range of cells, the merged cell contents will be placed in the top left cell of the unmerged cell range – which may not be where you want the contents to be. As a result column headings and data underneath it may now be misaligned;
- Excel won’t apply formats to a merged cell unless you select all the columns or rows that comprise the merged cell range;
- Columns with merged cells can’t be sorted;
- You can not select a single-column range if there is a merged cell in it;
- You cannot put a filter on a column with a merged cell in it;
- Formulas and Functions that refer to merged cells will not work;
The issues mentioned above can result in large amounts of additional effort when working with a spreadsheet that is not well constructed. Our advice is to only use merged cells in sheets that are purely for presentation/reporting purposes and NEVER EVER use merged cells in the Data or Analysis areas of your spreadsheets.Our advice – only use merged cells in sheets that are purely for presentation purposes and NEVER EVER use them in the Data or Analysis areas of your spreadsheets. Click To Tweet
How Do I Achieve the Same Effect Without Merging Cells?
So how then do you achieve a similar effect without merging the underlying cells? Use the Center Across Selection cell format option.
To apply the Center Across Selection format, select the cells you want to appear merged (cells A1 through to F1 in our example) and then launch the Alignment group dialogue and click the Alignment tab. Center Across Selection is in the Horizontal: drop-down box.
The Alignment Group Dialogue box can be launched in a number of ways
- Press CTRL+1; or
- Click the small arrow in the bottom right hand corner of the Alignment Group in the Ribbon Menu – see the image below; or
- Right click on your selected cells and select “Format Cells…”
Alignment Group Dialogue
Format Cells – Center Across Selection
Using Center Across Selection will achieve a similar formatting effect on your cell selection without having to Merge cells and without any of the complications mentioned above.