Well, I'm not sure how many people will be impressed, but I learned them by working. You have to make a conscious effort to be aware of them and use them.
I don't know about any site in specific, although there are many. What I can tell you is that you should master, at least, the following:
I could go on, but those are some groups of functionalities that most people do not understand, use or know about.
One thing I can recommend you is to be critic about your actions. If you're doing something that takes a lot of steps, repetitive or too complex, there's probably a simpler, easier way.
Finally, I think you will find those 2 add-ins very useful for your job:
I like ASAP Utilities (http://www.asap-utilities.com/) for one feature in particular: the ability to convert ranges of cells to absolute references, and back again. Comes in handy when you need to link to a range of data and then transpose it.
If you need to filter and separate by country, an option without VBA is to use a pivot table and then open details for any group required. That would put selected data in a separate sheet once at a time.
Another option is to install ASAP utilities. It is a great Excel add-in that already includes this functionality. Once installed, look it up in the ASAP Utilities Tab, under group Time saving tools / Range / Split the selected range into multiple worksheets. You don't even need to apply filters before.
Note: I have no affiliation with that page, but I've been a user of this tool for years and I cannot recommend it enough.
Keyboard shortcuts. If you ever work an internship in industry, they'll have you doing a lot more analysis of accounts than most public internships might. But I have learned time and again that you save more time by knowing what keyboard shortcuts there are instead of using certain functions like VLOOKUP to populate columns for you.
Here's a link. Pretty complex stuff, but you get the gist.
It's a really long list, but here's the list. Works better as a reference than a casual read, but whatever works best for you.
The better question is what do you use the mouse for currently? I'd recommend making a small list of things you use the mouse for (i.e. autofill, remove duplicates, increase font size, etc.) and then matching the shortcut that solves each one of those problems. Print out that list and keep it at your desk. Then, one at a time, work on implementing those shortcuts into your workflow. If you try to do too much at once (like reading that list) then you'll never retain any of it and just end up wasting your time. Tackle one new shortcut at a time and once you feel like you've mastered it, move on to the next one.
This might take a while, depending on how much you use Excel, but it's probably the best way to learn new shortcuts and tricks.
Try this third party add-in. It's free for Home/Student use but you need to buy it after 90 days for commercial use.
From what I found online, both of these errors are because you do not have Visual Basic for Office installed. You can find a fix here:
http://www.asap-utilities.com/faq-questions-answers-detail.php?m=145
Look at the "Quick solution" section.
Here is a link about this problem: link
That's a bummer. It will be awhile (read "probably never") before I can port anything like this over to another language.
I've held some similar positions (analyzing customer data sets for trends and making recommendations that roll up to CRM, marketing and sales groups). I like to use conditional formatting to make ad-hoc heat maps and look for trends to jump out. I also work with time-lagged series a lot and look at rolling correlations to get an idea of the overall robustness of a correlation.
ASAP Utilities has a lot of nice features for the power user. My supervisor swears by it.
Being able to run a simple monte carlo simulation in Excel might impress. There are a number of tutorials out there on that.
Good luck!
Oh I meant the excel hotkeys. I basically don't use the mouse when using Excel so the position of every key on the board staying the same is important, and changing the habit is really annoying since it's all muscle memory at this point.
This list at ASAP Tools is quite comprehensive:
http://www.asap-utilities.com/excel-tips-shortcuts.php
The only one that sounds like what you are looking for is F10.
On my tablet at the moment so am not able to confirm.
Conductor Searchlight, SearchMetrics and Brightedge - I've used all three in that order.
SEMRush, Moz and Majestic for link research.
Also what about Excel plugins? I like ASAP Utilities and Seotools for Excel.
For crawling large sites, I like Search Engine Optimization Toolkit for IIS. It requires some machine configuration but it won't run out of memory for a large site. (Also hasn't been updated in a while.)
Some tools of the trade to accelerate your work and make you robocop of analysts: 1) ASAP utilities (save a ginormous amount of time with ready-made scripts) - http://www.asap-utilities.com 2) PDF2XL by Cogniview (professional pdf to excel converter from structured and unstructured documents) - https://www.cogniview.com 3) Excel dashboards - http://exceldashboardschool.com
You should check out an Excel plugun called ASAP Utilities. There are tons of custom functions for doing stuff like this. It's free for 'home and educational' use.
I'm not a corporate shill, just a guy who uses this a lot. Just note that it doesn't work for 64-bit Excel. 64-bit Windows is fine though.
Unfortunately, I am not a coder and I'm not aware of any way to force a UDF to accept a multi-range array as part of an argument. Maybe someone with some VBA chops can chime in? I know ASAP Utilities has a feature that does exactly what you are describing, but that is not free for commercial use. I will mull it over, but currently I am unaware of a solution for you.