Excel Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro

By clicking on this link you are helping to support this blog – thanks!

In this video I demonstrate how to automatically sort data when it is changed or when new data is added. The video covers the following methods:

1) Automatically sort numeric data using the RANK, INDEX and MATCH. Deal with ties using IF and COUNTIF.

2) Automatically sort text data using COUNTIF, INDEX and MATCH. Deal with ties using IF and COUNTIF.

3) Automatically sort any data in Excel 365 using the SORT function.

4) Automatically sort any data in situ using a VBA macro.
------------------------

[SecretVoodoo]

Looked really hard for something similar to auto sort for one of my projects and at last, i stumbled upon your video. Really great tutorial, my query is solved now!

[wayneschell]

Hey! This is great!

Is there anyway you could show how to auto sort a table of data based on a date?

[zackwells]

What do you do when you have more than 2 with the same criteria? This method appears to only work for a max of 2 at a time. For instance I want to sort by Date and I have >2 with the same date.

[LaniConcepcion]

This is great! Tried this and it worked on my file. Thanks a lot.

[UncleBubba]

Really handy video, thanks.
Quick tip using the final VBA method: add another variable, _DataCol_
Like this:
_Set DataCol = Range("MySheet[ColumnX]")_
_Set SortCol = Range("MySheet[ColumnY]")_
Then change _If Not Intersect(Target, SortCol) Is Nothing_ *to* _If Not Intersect(Target, _ *DatatCol*_) Is Nothing_
This allows you to sort by Column Y as data is entered into Column X

Why? It allowed me to sort a sheet by date of data entry for certain fields. Just thought this might be useful for others.

[PNWDan]

Love the webpage. Might be good to add an example of a two-column sort, which I needed.

[t]

Thank you for this video. One issue I couldn't have solved it when the table is provided with a function that generates on it constantly an update. The table doesn't sort automatically in that case.

[mehran]

Thanks. Great solutions. One question though. Would it possible to have the vba for sorting based on two columns for example first column B and then column a. Thanks.

[sagarvsoni]

How to sort rows of different colors in excel? Suppose I want to keep red rows at top, yellow rows at middle and white at last?

Everytime I change the color of row, I have to manually sort it. Is there any way to sort it automatically?

Thankyou

[IvanCortinas_ES]

Excellent tutorial. I think than in method 2 it is better to use the "<" sign, because this way we get a descending order. Thank you very much! Very useful.

[mehdihaghi]

Thank you so much for the information. My excel sheets are working perfectly! The only issue is when I have more than 2 with the same criteria, then the 3rd one would be blank. Do you have any solution for it?

[StuartLee-pw]

THis is brilliant thankyou. But when i am ranking by date i have multiple dates which are the same can i assign a unique rank number as i have multiple repeated.

[ZeljkoDejanovic-pege]

Hi Chester, would the first solution work if the data in the table is live data and it changes in real time? Would the sorting still be in real time in the second table?

[alid-Alanazi]

Thank you, there are more then 2 in Rank, what should we do in TIE, =IF(COUNTIF)>1

[jameswd]

I know nothing about Excel and this just blew my mind...

[simonaust]

Awesome tutorial, when I get a tie I seem to loose a name in sales person column?

[parthomodi]

Thank you for the information. Worked very well for my query.

[pomodoroforwork]

This is incredible! Thank you so much. I can't tell you how big a help this is :)

[cascadeanalog]

This is like like Flying to Italy to buy an Olive oil than simply walking to your nearest Local market and purchasing one.

[ABellaLuna]

Is there a way to do this when its sorting more information?
I have a list of projects where the due dates change as do the priority (High, Medium, Low). Is there a way to sort it first by Priority then by Due Date?