I want to sort rows of data but leave some rows untouched

Question

this is an unusually strange concept but i want to sort only certain rows AND my header but leave other cells unsorted.

example row 1 has my headers [usually numbers 1-36] rows 2,3,4,5,6 etc will have a series of numbers [data] in each cell

for my needs i would usually use SORTBY and place the sorted data further along in the spreadsheet

however what i want to do is for example sort row 5 into descending order, allowing my headers to sort with it BUT i want rows 2,3,4 and 6 to remain unsorted and not move with the sort. so that row 1 the header changes in line with the sorted row 5 BUT the other rows remain in their existing positions - so despite a sort c1, c2, c3, d1, d2, d3 etc will remain exactly as per the original table whilst A1/A2/A3 [headers] and E1, E2, E3 etc have changed due to the sort.

is this possible and how?

Answer 1

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

It's definitely possible to sort only certain rows in Excel. Here's how to do it:

Select the range of cells that you want to sort, including the header row. In your case, it would be cells A1 through to the last column in row 6.

Click on the "Sort & Filter" button in the "Editing" section of the ribbon, and select "Custom Sort" from the drop-down menu.

In the "Sort" dialog box that appears, under the "Sort by" section, select the column that you want to sort by. In your example, this would be the column containing the data in row 5.

Under the "Order" section, select "Descending" as the sort order.

Under the "My data range has" section, select "Header row" to include the header row in the sort.

Click the "Add Level" button to add a second level to the sort.

In the second level, under the "Sort by" section, select "No Sort" to indicate that you don't want this level to be sorted. In the "Order" section, select "None" to indicate that there is no sort order for this level.

Click OK to apply the sort.

This will sort the selected range of cells by the column containing the data in row 5, in descending order, while leaving the other rows unsorted. The header row will also sort along with the sorted row.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

Best Regards, Sneha

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi Pete. I am an Excel user like you. I believe that I understand what you are saying you want to do, but the explanation is a bit confusing. If I understand correctly, you are wanting to sort by rows rather than columns and to sort only a specific row along with the headers in row 1. Here is how you can do it.

You will need two helper columns that could be deleted after you are done or left to do another sort. In the first helper column number all of your rows so that the rows can be returned to their original order when the sort is done. In the second enter an indicator of which row(s) you want to sort.

Sort the entire section, A1:G6 by the Sort Key in column G so that the line with the sort key moves up to row 2.

Select your data Headers in Row 1 and the row to be sorted in row 2, A1:E2, open the Sort dialogue, and click on Options at the top of the dialogue box. In the Sort Options window that opens select "Sort left to right" and click OK.

In the sort window choose Sort by Row 2 and choose the order of Largest to Smallest to have the row sorted in descending order and click OK. Your headers and Row 2 will be sorted together in the descending order of Row 2.

Now select all of the data, A1:G6. Open the Sort dialogue and click on Options at the top again and make sure it is set to or change it to "Sort top to bottom", make sure that the box for Headers is checked, change the Order to Smallest to Largest, and then sort by Column F, your numbered row order, to put all of the rows back in their original order.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 3

thanks - will give this a go and see what happens

Answer 4

yes i can see where you are coming from and whilst this will work it may not satisfy my needs as the whole process needs to be instant and the sort rows will grow by 1 row every 40 seconds or so

sort 1 would be easy as it is a header and first row of data - this will allow my spreadsheet to carry out certain calculations

sort 2 would now be header and second row of data - this will allow the spreadsheet to update the data from sort 1

sort 3 would now be header and third row of data - again allowing the spreadsheet to update the calculations from the first and second sets - which must remain in tact

sort 4 would be header and forth row of data

the problem is that the 2 and 3 thirds rows need to stay static as it will change the extra calcs however i need to test it but it also gives me an idea that might allow me to mod your idea - so thanks