Dashboards limitations (made of pivots/charts + vba)

Hi all,

a more theoretical question to the pros here: at what point do you think its not a good idea to build dashboards with a few charts/tables/buttons with excel? In the last few years Ive learned to love excel and it got me a few career jumps already. As I'm getting more mature in presenting data, I'm at a point where I think that excels performance handling "big" data will hinder me from great results. Take the following dashboard for example (desensitized) 4i2B6Gc.png

With a few buttons, a couple pivottables (5 or so) made from a table with ~100.000 rows x 10 cols, about 20-30 formulas in total (some of them beeing array formulas) the performance on workplace i5 processors is already lacking. When a user clicks on a button (all countries for example), it takes about 1-2 seconds and quite a bit flickering(when updating pivots - application.screenudpating doesnt help) before excel/vba does all the transformations of data/recalculates pivots, hides/displays charts etc. This is already happening while the file is long from beeing ready - I want to add more functionality to the user, more data, more vba, more everything.

So heres the question: for creating such tools which allow the end user to explore the relationships between many datasets, do people stop using excel and go into tableau/powerBI? I know some python, but doing a dashboard like that from scatch with a intuitive and nice looking GUI will take waaay more time and will bring other shortcomings.

I would like to direct your attention to Power Pivot for more sophisticated pivot tables, Power Query for more sophisticated data acquisition, which work together as kind of a half-way step to powerBI.

Generally speaking though, you want to pull as much calculation out of VBA as possible and stick it in the built-in tools. That's usually the low-hanging fruit for speeding up a spreadsheet.