Stop! Do Not Merge Cells in Excel - Heres why with fixes
Over the years I have seen many things in Excel that have made me go “wow, I won’t do that again in a hurry”. Thankfully as the years have progresses, so has Excel and many of these problem items have been resolved with updates. However, there are still a few things that you should avoid doing in Excel or stop doing altogether. One of these is the use of the popular Merge cells in Excel formatting option. I beg of you, do not merge cells in Excel
Merging cells in Excel cause problems with sorting and moving data. In this article we are going to look at the types of problems that you will encounter when you use merged cells and of course, a solution too 😊
Why you should not Merge cells in Excel
Pretty and all as you would like your spreadsheets to be, if you merge cells in Excel it can cause many different problems. First, if you are ensuring your workbook is accessible for people of all abilities, merging cells may skew layout to a screen reader and users may be given the information in the wrong context. Best practice on spreadsheet accessibility advises against the use of Merged Cells.
You might also find when you merge cells in Excel, some of your formula might not give you the value you expected. This is because merging cells loses the integrity of columns and rows.
Consider the following. We have two columns of data. We have totaled these columns using the SUM function. You can see from the image the SUM formula takes row 1 to row 6.
Let’s say we now merge the column A and B for row 1, Excel will see the value in the left most column. In this case, column A. The result is that column A now includes the 1990 which should be in column B.
Consider the following table of data. This table contains 3 columns merged for 1 row in the table.
If we were to try and sort this table by any of the Products, we would get an error as there are merged columns which breaks the traditional column structure.
Alternative Solution for Merged Cells in Excel.
Do not despair. We can easily apply a different formatting that will keep the appearance of merged cells, yet the cells won’t be merged. That way your workbook and spreadsheet remain pretty. After-all you don’t want to lose that look right! And we can continue to work with our data with out the error we discussed above.
To format cells so there appear as merged, select the cells. Right Click and select Format Cells.
This will open the format cells window. Select the Alignment tab.
From the Horizontal drop down, select Center Across Selection. The contents of the cell will then appear as if it was merged.
This only works from Left to Right and Excel will assume the value to be contained within the left most column.
Now if we try and sort the table, we do not face the same problem and the data sorts with ease.
Locating Merged Cells in Excel
Now you have read the article you are convinced merged cells are a big no in Excel. That was easy, Job done. But wait. The next problem is locating these cells so we can fix them. To do this, we are going to use Excels Find option.
The keyboard shortcut CTRL + F will open the Find and Replace window.
Once we open Find, we can select Options. Among other things this will allow us select between different formats. Select Format to open the Find Format window.
In the Find Format window, select the Alignment tab and select Merge cells. We do not need to select any other options as we are only searching for merged cells. Pressing OK will close the Find Format box and return us to the Find and Replace window.
By selecting Find All will list all the merged cells. Selecting any of these from the list will make it the active cell in the workbook. This allows you quickly navigate to merged cells.
Take a FREE course with us
Enroll in FREE course NOW
Updated* Now includes XLOOKUP
Explore The Ultimate Excel 365 Formulas course
We are the first Excel, PowerBI and DAX blog in the world where you can earn while you learn.
Sign up for our newsletter and get Excel Tips and Tricks straight to your inbox.
SIGN UP NOW
That is just so brilliant . This is all new to me. I did not know that merging cells in excel could cause such a headache and I didn't know that you could center text via formatting. Brilliant!
I'm glad to hear you liked it @angelak
Wow, great tip! I did not know that it can be done through a formatting option.
@tipu curate
Upvoted 👌 (Mana: 0/10)
Center across selection is one of my top tips too.
*******ignore that lol, I thought you were talking about how the images came accross from my blog to steemit lololololol.
Yes Centre across section is such a great tip, although many when I show them are really suprised excel can do that lol
@paulag sorry for the off topic comment.
Just a quick question.... can you find a quick way for parsing json like this
It has a multiple jsons in it ... the way I do it is first split it by delimiter and then parse it individually. If there is a better way to do it at once, I will be very happy :)
hi, yes it can be split in Excel using power query. however you might have to split it into a number of queries and then merge the queries. I would have to see how the raw data pulls into excel first. Are you on discord?
Yes ... discord dalz#3699
I cant seem to connect to you, may you can try connect to me paulag#0515
Thanks for the reply.
Here is what I got :)
Im glad we hooked up and got you sorted :-)
Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :
You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOPDo not miss the last post from @steemitboard:
Vote for @Steemitboard as a witness to get one more award and increased upvotes!