Ever received or generated a spreadsheet of data that would be a lot more useful if it was sorted how you needed it? With previous versions of Excel or if you choose the wrong options in your configuration, it is easy to sort a single column but break the correlation between the data in that column and the rest of the entries in each row. Here’s how to sort the spreadsheet by a single column and get all of your data to move with it.
As an example, let’s say here’s your data. For the teachers, the spreadsheet was prepared and sorted by last name. However, for us, that’s not as useful. We think it would be better to be sorted by building. Right now, that data in this small excerpt of the data is a mess.
Select all of your data. You can do this by highlighting all of the data or I think it’s better to select all of the columns by clicking and dragging from the first column to the last. Once the data is selected, switch to the Data tab and hit the Sort button.
The Sort button will bring up a window where you can configure things with more details. Since my data has the headers of Building, Room Number, and Teacher, I’m going to leave the ‘My data has headers’ box checked. If you don’t have headers and just have data, uncheck the box.
Next, go down to the lower level and choose what column you want to sort by. For us, that is ‘Building’. The next to columns allow us to choose what to ‘Sort On’. Since we care about the values in the fields, we’re going to stick with the default ‘Values’. We want it to be alphabetical, so we’ll leave it A to Z. You can then hit the Ok button and the selected data will be sorted as you specified.
Now your resulting data is sorted by the column you specified and the rest of the columns remained intact with each row.
You can use that Sort window for more complicated sorting by clicking the ‘Add Level’ button. This will allow you to sort by additional columns in case there is a tie in the first specified column.