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.
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.
8 Responses to “Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines”
This looks like it could be very useful for a project I'm putting together right now, thank you so much. Quick & silly question, how do I copy & paste the sparkline as a picture?
Question answered. For anyone else:
Select chart>Hold Shift key & select Edit/Copy Picture>Paste
[...] more information about PHD Sparkline Maker, please read this article and to learn more about Sparklines, read this article from Microsoft Excel 2010 blog. Also there [...]
Am I right in thinking that the y-axis is set automatically by excel?
That makes it possible to get the column chart not to start at zero.
Andy - yes, it is currently set to 'auto', which defaults to a zero base for positive values, but you can change that by left-clicking the chart, then choosing (in Excel 2007):
"Chart Tools/Layout/Axes/Primary Vertical Axis/More Primary Vertical Axis Options"
PUBLIC SERVICE ANNOUNCEMENT: When manually editing a chart's minimum/maximum axis values, PLEASE be sure there's a valid reason and that doing so won't skew the message shown by the data (e.g. by exaggerating differences). If in doubt, go back and read Tufte. (W.W.T.D.?)
[...] gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this [...]
thanks for the work creating the template!!!!
looks good