How is typing python commands better than using Excel's built in functions to do the same thing? (References, Lookups, Sorting, Filtering)
While Excel functions are relatively robust, doing more complex operations that impact more than one cell typically require macros. I was attempting to process 50,000 cells and I tried making a function, but it was a pain in the ass given how complex it was. Since, I didn't know VBA, but did know Python, I used Ironspread to use write a script to do the job. I was impressed, it's pretty smooth to use.
Having done my time in Excel and VBA, I can say as soon as you need to use VBA in Excel you're at the limit of what Excel can do.
It's a fantastic tool for visualising data, but as soon as you need to pass multiple data sets through an algo - you reach the limit of not only Excel, but the concept of visualising the entire working of an algorithm.
Basically, it can't iterate (well).