Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel spreadsheet with two cells in each row that I'd like to merge. Highlighting all the cells of interest and just merging from the cell formatter seems to just merge all the cells together, when instead I just want one merged cell per row. Any ideas how to do this?

I should note that one of the columns contains values (all are either true/false) and the other column is just a null cell.

share|improve this question

6 Answers 6

  1. Merge the two cells in the first row by selecting Home > Alignment > Merge Cells command.
  2. Select the merged cell and select Home > Clipboard > Format Painter.
  3. Click and hold the first cell on the next row, and select all the range by dragging your mouse. (For example, if you merged cells A1 and B1 in the previous step, now select range A2:B99.)
  4. Excel applies the merge command individually to each row and you're done.
share|improve this answer
    
This is certainally the better method of the two. +1 –  Joe Taylor Nov 10 '10 at 14:35
    
Unfortunately, not working, as it destroys the content of the second cell... Am I missing something? –  Kobus Myburgh Jun 22 at 16:05
    
I was going to suggest selecting the cells to be merged row by row and then doing the merge. When selecting that way, Excel will merge the cells row by row. Mehper's answer is much better, though. (Much less fiddling around and much less prone to mistakes when selecting.) –  RobH Jun 22 at 16:37
    
@Kubus Merging only keeps the value of the leftmost/topmost cell that is being merged. In fact, in Excel 2010 (and later, I presume), if you try to merge multiple cells that contain data, a warning will appear that will tell you just that. –  RobH Jun 22 at 16:40

Excel has (had?) a Merge Across function. It's not on any of the toolbars by default, but is available as a customization. I usually add it to my Formatting toolbar, after the Merge Cells and Unmerge Cells buttons. (Right-click the toolbar, choose Customize, go to the Commands tab, select Format in the Categories list on the left, scroll down to find Merge Across, drag it to wherever you'd like.) Once it's on your toolbar, you can merge across however many cells with a single click. Important: Note that merging keeps the leftmost value only!

Unfortunately, Excel 2007/2010 has totally broken/gotten rid of the whole customization feature, so if you're using one of the newer versions, you're stuck doing the Format Painter thing.

share|improve this answer
1  
"Merge Across" is still there, it's just hidden inside the "Merge & Center" button/dropdown. –  AviD Jan 1 '12 at 13:47
    
It is there, yes, but does not keep both columns of data, unfortunately, unless I am missing something? –  Kobus Myburgh Jun 22 at 16:04
    
@KobusMyburgh: merging has only ever kept the top left data. (I.e. if you're merging columns, you keep the data in the leftmost column, and if you're merging rows, you keep the data in the top row.) This has not changed between different versions of Excel. –  Martha Jun 22 at 19:30
    
I figured - hence I had to do a work-around. –  Kobus Myburgh Jun 23 at 11:55

as long as you do not have anything in the cells you want to merge then this method should work. merge the first two, then copy the merged cell. now select all the cells that you want to merge in the same fashion and paste.

share|improve this answer
    
Unfortunately, I do have something in one of the columns. All the values are either true or false, so will this work if I sort by true/false and then do your method? –  jlv Nov 10 '10 at 13:47
    
It worked by just sorting out by values first, and then applying your method. –  jlv Nov 10 '10 at 14:07

Simply merge a couple of cells the way they should be, then copy the resulting cells and then Paste- Paste formatting on the other rows/columns. (Excel 2010)

share|improve this answer

Would've loved a more elegant solution on this one.

Unfortunately, all merging actions throw the rest of the data out (keeps upper left, top most data only).

Not sure if this will help you (or if you still need a solution) but to circumvent this, I saved the file as a CSV, added a place-holder column with ____ as the text, and saved the file, then opened it in a text editor, and replaced ____, with an empty value.

Thus, the result was data,data first becoming data,____,data in the CSV file, then becoming datadata after replacing in the text editor.

Hope this helps someone, as you are unlikely to still need this after almost 5 years.

share|improve this answer

Highlight the range of cell by -

Clicking on the drop down arrow next to "merge & center".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.