I had a call from a client to say that several people were using the same worksheet, and somehow when the records were sorted, the records were no longer altogether with rows going astray. If you just click in one cell in a data area to be sorted, and then sort, the whole data area will be sorted. If you select a whole column and then try and sort you will get a message coming up asking whether you want to expand the selection or just sort on that column. Similarly, if you select a few cells in one column, the same message will appear – usually you do want to expand the selection and sort the whole data list.
The problem that was occurring was that people were selecting a subset of the data and then asking it to sort. The sort was then being performed just on the cells selected, with no warning message at all!
How do you get round this? Well you could send your staff on Excel training – no doubt they would pick up a lot of other hints and tips along the way. But in the short term, if you are using Office 2007 or Office 2010, change the data set into a table – select the data area, then from the Home tab, then in the Styles group, click Format as Table. Choose a table design of your choice. The data will be formatted as a table. Now if anyone tries to sort, it will sort the whole data table, using the leftmost column of the selected cells as the sort column.
To find out more about our Excel training, visit http://www.jmdtraining.co.uk/microsoft-office-training/microsoft-excel-training.