Top N automation

[Neil_Armstrng]

Hi Everyone,

I have an excel sheet based on which I create Pivot to find out top 3 values every month.

My Problem is, as the data us getting huge I am not able to refresh pivot and it gets crashed.

So I want to design a formula which will calculate top 3 values based on filters selected at the report level of Pivot.

I have attached excel file here for reference.

Kindly assist.

[alansidman]

Here is a tutorial that uses Power Query that will do what you desire and will be quite quick.

https://www.youtube.com/watch?v=u0kg0xIbJuw

[Neil_Armstrng]

Hi Alan,

Thanks for your reply. But Power query doesn't work on my system. That's why I am looking for formula based solution.

If you can help me, the that would be great.

Thanks.

[alansidman]

For your version of Excel, you need to go to Microsoft Web Site and download the add-in for Power Query. It is native to excel beginning with 2016. Power Query and Power Pivot can handle large files with out issue. A formula will not necessarily prevent a crash as files get bigger.

https://www.microsoft.com/en-us/down....aspx?id=39379