The other day, when I was showing the basics of creating a worksheet in Excel, I was asked a really good question - one that made me think “I’m sure you must be able to!” and “Why have I not heard (or thought of) this question before?”
The question was about sorting, not sorting the rows, but sorting columns (for example into alphabetic order). I've written this blog to feature a workaround to achieve this and thought it might be useful to include some other aspects of sorting in Excel.
First, a pitfall to avoid: don’t sort when you have only a part of your table selected (unless you only want to sort those cells and keep the others where they are) as the data in your table will probably become meaningless. If you only have a single cell selected, Excel normally gets it right and sorts the whole table around that column (but sometimes I still check to make sure – you can always click on “Undo” if it gets it wrong).Selecting the whole table first is my normal belt & braces approach.
Just click within the column you want to sort/group by, click on “Sort & Filter” on the ‘HOME’ tab and select ‘Sort A to Z’ or ‘Sort Z to A’ if the column contains text, ‘Sort Smallest to Largest’ or ‘Sort Largest to Smallest’ if the column contains numbers or ‘Sort Oldest to Newest’ or ‘Sort Newest to Oldest’ if the column contains dates.
I often use ‘Custom Sort’ to group/sort by more than one field. Click on “Sort & Filter”, ‘Custom Sort’ to see the ‘Sort’ window. Click on the drop-down arrow by ‘Sort by’ and select the first field you want to group/sort by (if it doesn’t show the headings, place a tick by ‘My data has headers’) and the order (e.g. 'A to Z' or 'Z to A'). Then click on “Add Level” and repeat the process for the next field you want to group/sort by – repeat this for as many extra sorts/groups as you need. Click on “OK” to see everything sorted into your new sequence.
I have included a video of the next couple of items.
Sorting by day or month order:
As well as 'A to Z' and 'Z to A', you can also sort by a custom list. This is most useful if I want to sort records by month (as January, February, March, April, etc. not April, August, December, February, etc.). Just use the drop-down arrow under ‘Order’ in the custom sort window, select ‘Custom List’ and select the list you want. Anything not matching an item in the list will go to the bottom of the sort.
Finally, how would I sort column headings into (for example) alphabetic order? There are 2 ways: I could use the Transpose option within Paste Special or change the options in a custom search:
Using Paste Special (Transpose): Select the whole table (all headings and data) and copy it (I use [Ctrl]&[C]). Locate a clear part of the workbook (or create a new page), select the top-left cell where you want to paste and paste special (I normally right-click on the cell and select ‘Paste Special’). In the ‘Paste Special’ window, place a tick by ‘Transpose’ and click on “OK”. Sort the new table as normal, then copy it, right click on the top-right cell of your original table (or a new page, if you’re creating a new copy), select ‘Paste Special’ again, transpose again and click on “OK” again. Job done!
Changing the options: Select the data you want to sort, including the headings along the top, but not the labels down the left (otherwise they might be shifted to a different column). Select 'Custom Sort' as before, then click on "Options" along the top and click by 'Sort left to right' (then click on "OK"). Set the row you want to sort by and order and click on "OK". You'll need to remember to click on "Options" and switch it back the next time you want to sort rows.
Tutor led courses
We run a tutor led courses in using Excel. Book onto Excel: Database techniques if you want to learn more about sorting your data. The next dates available for booking are 27 July and 7 September 2016.
Bye for now,