Sorting data in Excel is messing up my rows!

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, Office 2010 or Office 2013, 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.

Find out more about our Excel training London, UK, or our Excel training Sydney, Australia.



About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Office 2007 and 2010 and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s