# Excel dynamic sort

Sorting data in-place in MS Excel is easy. This technique however will sort a table without changing the original data. This means you can have several referring tables that will automatically update when the original data is modified. Without macros.

P.S. Use my Online Excel Translator if you want the functions in another language.

This is our initial table:

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | Name | Score | ||||||

2 | Mallory | 50 | ||||||

3 | Bob | 120 | ||||||

4 | Eve | 10 | ||||||

5 | Alice | 100 |

Let's sort by score. Fist we need to add a column to the left of the data, with the "RANK" of each row. We have to add +COUNTIF(C$2:C4;C5) if the sorted variable (score) is not necessarily uniqe.

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | Name | Score | ||||||

2 | 3 | Mallory | 50 | |||||

3 | 1 | Bob | 120 | |||||

4 | 4 | Eve | 10 | |||||

5 | =RANK(C5;C$2:C$5) | Alice | 100 |

Then we add a column with the desired order of data, usually 1,2,3,.. or perhaps a descending order.

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | Name | Score | Desired order | |||||

2 | 3 | Mallory | 50 | 1 | Bob | 120 | ||

3 | 1 | Bob | 120 | 2 | Alice | 100 | ||

4 | 4 | Eve | 10 | 3 | Mallory | 50 | ||

5 | 2 | Alice | 100 | 4 | =VLOOKUP(E5;A$2:C$5;2;FALSE) | =VLOOKUP(E5;A$2:C$5;3;FALSE) |

That's it!

Now, if score is updated in column C, the sorted list to the right will automatically update. Especially convenient if you want to sort the same data in different ways.

Read more and discuss this page here.

Created on Tue, 07 Apr 2009 00:00