Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

sorting

ResolvedVersion 2010

Michael has attended:
Excel Intermediate course

Sorting

Is it possible to sort columns where the cells are merged? We have a program that we use which exports data to excel. It always exports the data with merged cells. I cannot then sort the columns as the cells are merged - any ideas?

RE: Sorting

Hello Michael,

Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.

Thank you for your question regarding sorting when you have merged cells.

It is impossible to sort / filter data where there are merged cells within the data. I would select the columns of data where the merged cells are to be found and then click the Merge & Center button to remove the merged cells. This does leave you with some blank cells here and there so you have to proceed with this method very carefully... and then make whatever adjustments are necessary. Sometimes it is useful to put the data with merged cells into a separate sheet, make the adjustments and then move the corrected data back to the original sheet.

Try this out!

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

RE: Sorting

Hi Rodney

Unfortunately this doesn't resolve my query. The suggestion that you have made is what I currently do but I was hoping to find a quick solution to this. Sometimes the programme we export stuff from can export information with thousands of lines of merged data in columns.

I was hoping to find a way to un-merge these cells quickly without manually having to go through each one and do it one at a time.

If this can't be done then never mind!

Mike

RE: Sorting

Hello Michael,

In my explanation I did not say you must unmerge the cells one at a time. You can select the entire sheet if you want and then click the Merge and center button. My advice was to select the columns which contained the merged cells and then unmerge.

Give it a try!

I hope this resolves your question. If it has, please mark this question as resolved.

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

RE: Sorting

When saying you have to do it one at a time, I meant that once you have highlighted the section that needs to be unmerged (this may be the whole document but for me is usually just one column) and clicked the merge and centre button, you are left with a lot of blank cells. You then have to copy the cell above these blank cells into the blank cells below. This is the bit that needs to be done one at a time and can be very very laborious if you have thousands of rows to go through!

I understand there is no solution to this (which seems a little crazy considering the seeming lack of complexity involved) and I will plod on with unmerging and copying!

Thanks for your help and advice. I will now mark this resolved (insomuch that I am aware there is no solution!).

Regards

Mike

RE: Sorting

Hello Michael,

There is a way to copy the cell above a blank cell into the blank cell. This can be done all at once.

First, select the column containing the blank cells. Then press CTRL + G and click the Special button. On the left of the dialog box select Blanks and click OK. This action will select all the blank cells in the column. Then press = followed by the up-arrow and then hold the CTRL key down and press Enter. If you do this correctly, all the blank cells will be replaced by the cell above.

Hopefully this will work for you.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

RE: Sorting

That's absolutely brilliant, thank you - just what I was looking forward!

Mike

Excel tip:

Keyboard Shortcuts to Add Rows or Columns

Couple of other keyboard shortcuts. Shift+spacebar selects a row, Ctrl+spacebar selects a column. Select either row or column (or several) and use Ctrl and + to insert or Ctrl and - to delete rows or columns.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.