How do you save a sort in Excel?

Hello,

I have several groups of data and I have to sort these data by the same sort criteria. However when I go from group to group (they are seperated by several rows for subtotals, etc.) I have to reset the sort request.

Do you know how I might be able to save the sort and not have to reset it each time I move down to another group of data? I have looked around at Microsoft and asked some peers and they don't know.

There has to be a way..........surely.

Thank you in advance for your assistance.

DDenton / Pappy1961

I'm not really sure what you mean.

In Excel 2007 you may have two options that help.

1. A Custom Sort order. This can be arranged via Office button>Excel options>Popular>Edit Custom Lists... Once this is done you can select custom list... under the 'order' drop down in custom sort
2. Set up all the different 'areas' of your data as individual tables. This way they operate as stand alone 'units' on the sheet with their own filtering, sorting, etc.

I know exactly what Pappy 1961 means.

I have a table with 8 or so columns - nothing too complicated and I used to be able to select the sort option and the three columns I wanted to sort by were already there from a previous 'sort'.

I upgraded my excel to the latest edition and now I have to re-input the sort data every time I sort the list which is very regularly and a real pain. 

Bizarrely though if I open the list on another computer-another excel (same edition) and sort the list then save it, the sort criteria stays. It remains even when I reopen it on mine, but disappears as soon as I save on my excel.

I can't find any preferences which seem to control this and I can't find any info on it anywhere. Although several other people have posted the question but no answers seem to address the problem.

Rachel, I'm noticing odd behaviour in 2007 as well. Sometimes, I'm able to make the sort "stick" by making sure a cell within the data is selected. Or was it a cell within the heading. I seem to have to try both before it sticks.

I think your question is a bit different than the original post, though. There, the data is split with empty rows between the rows. I'm not sure if there is a fix for that other than a bit of VBA to select the data group that the cursor is in, then sort by the desired columns. Since I never put sub-totals in the middle of the data, I'm not familiar with the best way, though.

Thank you for this.

I did find having a cell selected worked - again I can't remember which one, but not even that seems to work now. It seems quite unstable.

Is there an excel support contact does anyone know?

I've tried it again and you are absolutely right. If a cell in the header row is selected the 'sort by' criteria is saved.

Brilliant - Big THANK YOU Omar!!!

Rachel

I tried this in Excel 2010 but it didn't work.

With Excel 2003 I could set up sort criteria in a table (a small league table in fact with sort criteria (all in descending order) of points, then goal difference then goals for.  I could copy the sort cell range elsewhere in a spreadsheet, even to different worksheets and the sort criteria would follow.  I could even copy the table within a larger cell range and the sort criteria would be retained. 

With 2010 it loses all sort criteria even when selecting a cell on the header line before sorting. 

Are there any contacts at Microsoft we can get in touch with to find out who has removed this 'basic' functionality without having to fork out a fortune for an online 'expert'.

Have any of you tried using Custom Views to get what you need?  You can set up the sheet with the filters and sort options that you want, save a Custom View, and then move on to the next custom sort/filter, and generate the next Custom View, and so on.

For Ribbon users, go to the View tab and click on Custom Views (after you've set it up to look the way you want it).  Click 'Add', then give it a good name (i.e "All Employees" or "Employees named Mike sorted alphabetically by last name").  It'll offer a couple of options for saving print data and hidden/filter data.  I keep them checked.   Hit 'Ok' when finished.  Close out of the Custom View menu, adjust your sort settings and filters, and then go back into it again.  Rinse, repeat. 

Once your views are set up, you can open the menu select one of the views you've saved, and presto, everything's sorted the way you want it.

Hope this helps!

I can't make it work. Excel 2010 keeps dumping the sort criteria randomly, customised view or not. Excel 2000 always remembered the last sort criteria. This is a major nuisance when for example in an account file I nearly always need sorting at two date columns and I have to keep entering the same sort criteria all the time.

Got it!  Or close enough..by accident.  I highlighted the rows to sort by column and hit the "Sort A to Z" button (left of the sort box), then hit Ctrl Z (to clear any action the previous might have had), then hit the Sort button again and Voila!  My previously entered sort criteria was there.  A couple extra keystrokes...but better than retyping in multiple column sorts.

Hi

When you try to perform the sort a second time, Excel sometimes 'forgets' that you have a header row, so in turn it 'forgets' your sort details.

If you make sure that your header row doesn't have any blank cells, then Excel automatically assumes it is a header row and therefore remembers your sort prefs from last time.

Works for me anyway!