Excel’s merge function for cells can be useful for centering text across multiple rows or columns; however, merged cells are known to cause problems in spreadsheets. These problems include difficulty sorting, copying, pasting, or moving data.
Merging cells can actually create more problems than it solves. Here are a few reasons why you should avoid merging cells in Excel:
1. Merging cells can make it difficult to edit your data.
2. Merging cells can make it difficult to apply formatting changes to your spreadsheet.
3. Merging cells can cause errors in formulas or other data that relies on cell references.
So next time you’re working in Excel, remember to avoid merging cells!
Alternatively, we have a feature, the Center Across Selection provides the same look as merged cells without merging the cells and has the feasibility to sort, copy, paste and move.
You could access this feature in 2 easy ways.
- You could access this feature in 2 easy ways. On the Home tab, Click the small pointing down arrow in the bottom-right corner of the Alignment group. This opens up a pop-up with Alignment highlighted. Click the Horizontal drop-down to select the Center Across Selection.
- The other way is to select the cells that you would like to merge and hit Ctrl+1 and then follow the same steps as pop-up open and proceed with the above steps in #1
Let’s say if we have some data to sort and here is the error message that shows up when I try to sort.
So, here’s the solution for this.
First, unmerge the cell
Then press Ctrl+1
Go to Horizontal drop down and select Center Across Selection and click OK
Now if you go and try to sort the data it will allow you to sort single columns.
Note: N/Aappears to be in the center of both Column B and Column C, but the inputN/Awill be in the cell B7
Hope you learnt something new in excel today! Now, it’s your turn to tart using the Center Across Selection instead of Merging Cells!
