0
0
In Excel, sorting by multiple columns isn’t as difficult as you might think. Although the function LOOKUP doesn’t work in the same way as it does in other spreadsheet programs, there are still ways to sort your data by multiple columns using simple VLOOKUP functions and other shortcuts in Excel. By the end of this article, you’ll have mastered the art of sorting data by multiple columns in Excel, and you’ll be able to use your newfound knowledge to sort faster than ever before!
Sorting Numbers
To sort your data by multiple columns in Excel, first select the cells you want to sort. Then, click the Data tab on the ribbon and choose Sort. In the Sort By drop-down menu, select the first column you want to sort by. Next, click the Add Level button and select the second column you want to use for sorting. Continue adding levels until all of your desired sorting criteria are in place.
Finally, click OK to apply your sorts. You can also rearrange how your data is sorted by dragging and dropping it up or down within the grid. Just highlight the row you want to move and then drag it over another row. The rows will swap places in your spreadsheet. If a row is highlighted, only that row will be moved; if more than one row is highlighted, all of them will be moved together.
If you want to reverse the order of your sort, just hold down CTRL while clicking Sort on the Ribbon. You can also undo any unwanted changes by selecting Edit > Undo (or Ctrl+Z). If you don’t want to undo the last thing you did, but want to redo it instead, press SHIFT+CTRL+Z.
Sorting Dates
If you’re working with dates in Excel, you may want to sort them by more than one column. For example, you might want to sort first by year, and then within each year, sort by month.
Here’s how to do it:
- Select the data you want to sort.
- On the Data tab, click the Sort button.
- In the Sort dialog box, under Column, in the Sort by list, click the first column that you want to sort.
- Under Order, click either Ascending or Descending.
- Repeat steps 3 and 4 for each additional column that you want to sort on.
- When you’re done, click OK.
- The data will be sorted according to your specifications. You can also use this technique to sort rows instead of columns, but note that you must change Column to Row in step 3. For example, if you have a table that lists vendors who sold products and their respective sales figures for 2011-2012, you could select the entire table (hold down Ctrl while clicking on any cell) and then click Sort Row. In the Sort dialog box, select Jan as the sort key. You could choose another column if you like – just make sure to place Jan before the other columns in order of precedence. Click OK and voila! Your data is now sorted by Jan, 2012, Feb 2012, etc. Happy sorting!
Sorting Text
To sort text in Excel, you can use the SORT function. This function takes two arguments: the range of cells to sort and the sort order. The sort order is a number that specifies how the data should be sorted: 1 for ascending, 2 for descending, 3 for by color, and so on.
For example, if I want to alphabetize names with the first letter as the key column and then have them appear in ascending order, I would enter =SORT(A1:A11,1) into cell C2. You can also create your own custom sort order by choosing Custom from the drop-down menu next to Sort Order. Next, click Add Level and specify which columns will determine the order (in this case, A1 will be the first column and B1 will be the second).
Select Ascending or Descending depending on what you want. Then click OK. Finally, change the Sort Level field in Cell C2 to 3 (the third row) for sorting by both columns A and B in ascending order. Your spreadsheet should now look like this:
Another way to sort text is by using the LARGE function, which counts the frequency of each word and sorts accordingly. For example, let’s say we want to see all words in our list sorted according to their frequency – more frequent words are displayed before less frequent ones. We could do this by entering =LARGE(A1:B11,B1) into cell C2. Let’s take a look at an example spreadsheet that shows how LARGE works .
In this spreadsheet, there are three tabs: one showing the original list of numbers without any counting done, one tab with a formula using LARGE in order to find out which numbers appear most often and lastly another tab where I entered =LARGE(A1:B11,C2) in cell C2 to show which values corresponded to those seen when looking at the frequencies.
If you’re interested in trying it out yourself, go ahead and copy the data from the original tab (without any calculations) onto your spreadsheet. After pasting it into column A1-C3, select cells B4-E4 and insert a formula for LARGE. Be sure to update C2 with the appropriate values.
Now you’ll notice that your spreadsheet has been updated with new data. Now, delete everything but columns A and B (select these two columns and press Delete) because these are the only information needed to calculate frequencies. You’ll need these later on in order to complete the last step of this process! When everything has been deleted, hit Enter again. That’s it.
