Today my dad asked me a simple question: “How do you auto sort an excel table?”
At first I thought, “Don’t you just use the sort button on the selected table?”, but then I realized that it would be so cool if you could make a it AUTO sort. If you change some values in a table, so it will auto sort. With the help of your “friend” Google, i found out that you could do a Visual Basic script that auto sorts, but I couldn’t understand if it automatically sorts the table? Anyway, I found a simpler way to do it. But first, there are some thinks you have to consider:
1. Is your table vertical or horizontal?
Both are possible, but you have to use another excel function.
2. How complicated is your table?
If it’s a simple table, then you’re good. If you have a complicated table, you will have to change it so you can use this trick.
The table I will use is a simple monthly expenses report. I inserted snapshots with the values and also the formulas used (you can see ALL the formulas used in an excel using the ” Ctrl+` ” key combination). The tables looks like this:
OK! Now that we have the table, we are good to go. I will tell you the steps and at the end the snapshots with the final table and formulas. The steps are as follows:
1. As you can see I left a the firs row empty. That row will be used to set the rank (1,2,3,4..) for the “Total values”. So in the cell B1 we use the formula =RANK(B10;$B$10:$G$10;0). The last argument dictates the sorting order: 0 for descending, 1 for ascending.
2. We fill the cells C1 thru G1 with the same formula. We used $ in the B1 formula to make it not change when filling the cells to the right. Now we have the ranks of the Ratios.
3. Now we have to make the sort array. We can make it vertically or horizontally. I will use vertically, cause I thinks it’s more easier to read. You can make it horizontally, but you have to change the function we will later use (VLOOKUP instead of HLOOKUP). So we insert 1 thru 6, every value one in a cell, one under the other. I will use cells J2 – J7.
4. In K2 we insert the following formula =HLOOKUP(J2;$B$1:$G$10;2;FALSE). J2 is the value to search in the array B1:G10. 2 is the row in the array from where hlookup will return the corresponding value. False is used to find the exact match of the value used to search. We fill the cells K3 to K7.
5. In L2 we insert =HLOOKUP(J2;$B$1:$G$10;10;FALSE) and fill the cells L3 to L7.
Now we have another table in which there is the sorted expenses categories and the corresponding percentages. Finally our excel should look something like this: