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:

	B	C
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
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	E	F	G
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.