First, let's start with an article featuring many great Excel functions, entitled 11 Advanced Excel Tricks That Will Help You Get An Instant Raise At Work. It describes the following Excel functions:
Excel map created with PowerMap
The second article that we selected discusses several Excel tricks:
Click here to read the article. Note that the Excel VLookup function can be extremely slow.
Finally, we found a great article about advanced Excel features. It discusses the following topics:
Also, check out our section 6 of our data science cheat sheet. We plan to add many additional examples of Excel spreadsheets, some with advanced computations / modeling, and some for beginners.
DSC Resources
Additional Reading
Follow us on Twitter: @DataScienceCtrl | @AnalyticBridge
Comment
In my experience, sluggishness from a VLOOKUP function is not a major problem; INDEX/MATCH will cause sluggishness far sooner.
Waohh, like a city!!! Excel can do it now... amazing !!!
I'm surprised to see that SUMPRODUCT function didn't make this list (calculates the inner product of two vectors/arrays). It's great for calculating weighted mean, scoring OLS models, etc.
I always use index-match. It's faster and less brittle. Specifically, if you change column order, index-match does not break like Vlookup does.
Thanks for the nice tips. I followed on your MMult option which I was unaware of. As it turns out, Excel also provides:
1. MDeterm - computes the determinant
2. Transpose - computes the trasnsposed matrix (also available in the Copy->Paste Special->Transpose option
3. MInverse - computes the inverse function
All very handy in modeling too.
A reader wrote:
In terms of speed, INDEX (MATCH) beats VLOOKUP for >1 column
© 2018 Data Science Central ® Powered by
Badges | Report an Issue | Privacy Policy | Terms of Service
You need to be a member of Data Science Central to add comments!
Join Data Science Central