Many of us face this problem.
We have some data in a few cells. Either for alignment or structure, we would like to merge the cell contents in to one big cell. But Excel wont help as it cannot merge values from all cells.
Aparna writes,
Hi Chandoo,
Can you tell me how to merge rows without losing the data in the format given below.
given data:
row 1: abcd
row 2: xyzrequired format :
row 1: abcd xyz
It would save a great amount of my time if i could get a solution to this !
That got me thinking, there should be a way to merge cells without losing data?!?
Of course there is. You can use simple features in Excel to do this with ease.
Select all the cells where your data is. (All the cells need to be in one area in one column).- Adjust the column width so that you can fit all contents in one cell. (basically make it wide enough)
- Select Home Ribbon > Fill > Justify
- Merge cells now.
Select all the cells where your data is. (All the cells need to be in one area in one column).That is all. The text from selected cells will be magically re-arranged in top-most cell. If you see the text spreading 2 rows, just make the column wider and repeat the process.
See this simple animation aside to understand how it works.
But wait, this technique has some limitations,
- It doesnt work if the selected cells have numbers or formulas
- It only works for cells in a single column, if the cells are spread across several columns, justify will not work.
- It requires a lot of steps.
Shouldn’t there be a better way to merge cells without losing data?!?
Of course there is, You can write a simple macro to do this job for you.
For eg. here is the JoinAndMerge() macro I have written that works for pretty much all types of data.
Sub JoinAndMerge()
‘joins all the content in selected cells
‘and puts the resulting text in top most cell
‘then merges all cells
Dim outputText As String
Const delim = ” “
On Error Resume Next
For Each cell In Selection
outputText = outputText & cell.Value & delim
Next cell
With Selection
.Clear
.Cells(1).Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub
Just copy this code in to a new module or add it your personal macros workbook. And you can use it to quickly merge cells without loosing data. Simple.
The code is easy to figure out if you know basic VBA. So I will not go in to detail.
What to do with all the saved time?
Simple, go read a quick tip.
PS: It is likely that you will save even more time by reading the quick tips, then what?
PPS: Simple, go play.
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.