Once upon a time I had a master worksheet with data in predefined columns that was used for uploading data into a legacy system. Users would submit data in worksheets they developed, but oftentimes their columns didn’t always match that of the master worksheet.
So I used a little known option in the Sort feature of Excel to quickly rearrange their columns of data to match the master worksheet. This made it a snap to copy data from one worksheet to the other.
The column headers, from left to right are: Delta, Bravo, Alpha, Charlie. We want them to be in alphabetical order: Alpha, Bravo, Charlie, Delta.
Select a cell in the data range and choose Data > Sort. In the Sort dialog box, un-check the box for My data has headers.
Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK.
In the Sort dialog box, click the Sort by drop-down arrow and select Row 1. Make sure the Sort On selection is Values and Order selection is A to Z, then click OK.
The data is sorted left to right by using the first column.
Change the Example
Now let’s assume that we need the column order: Bravo, Delta, Charlie, Alpha. What to do? Well this is more like a real-life example that only takes a couple more steps to complete.
We need a blank row above the data, so right-click row 1 and select Insert from the pop-up menu. Enter 1 above Bravo, 2 above Delta, 3 above Charlie, and 4 above Alpha.
Now go through the same steps we did before, except the Row 1 sort Order will be Smallest to Largest.
Once sorted you can delete the row above the header and we’re done. Excel has done all the heavy lifting.
Now you can use this Sort feature to make your life easier.