In this week's roundup, make VLOOKUP faster, use keyboard shortcuts, run macros when Excel is closed, and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
1. Contextures Posts
In case you missed them, here are the articles that I posted recently:
- Upcoming free online events for Excel fans -- catch these before their deadlines!
- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
2. Excel Dashboard Course
Registration is open for Mynda Treacy's Excel Dashboard Course, and the deadline is this Thursday (April 30th).
The course is a great investment, and you can read my review for the highlights, and more information.
On the Daily Dose of Excel blog, Jeff Weir checks the speed of the double VLOOKUP trick, that he learned from Charles Williams. This only works on sorted data, so are the faster calculations worth the the extra effort of sorting? Read the comments too -- it's an interesting discussion.
Meanwhile, did Rob Collie actually admit that VLOOKUP can be more flexible than Pivot Power relationships?
4. Highlight Blank Cells
Even when he is sitting in airport lounges, Chandoo is still helping people with Excel problems. Read his tips for highlighting blank cells – he shares 3 ways to do that.
5. Schedule It
Have you ever wanted to run an Excel macro, even when Excel is closed? No problem! Zack Barresse shows how you can schedule it to run automatically, if your IT department allows this kind of activity.
6. Use the Menu Key
Do you ever use the Menu key, in the bottom row of your keyboard? MF Wong shows how to save time, by entering shortcuts with that key. In his example, he use our old friend, the Filter By Selection command.
7. Save Some Trees
Ben J. Kusmin shows how to shorten a long text document, by pasting it into Excel, and cleaning it up there, before printing. He uses a transcript as the example, but I've done similar cleanup on other types of documents.
8. Look at Disproportionality
The Science Goddess uses Excel to look at gender disproportionality in sports, and you can download her sample file.
She based the data entry form on my example, and you can learn more about how that is set up.
9. Learn from Microsoft MVPs
Earlier this month, Gašper Kamenšek attended the Amsterdam Summit, and has written a review of the event. Were his expectations too high? Which presentation had the greatest NTLpH (new things learned per hour) rate that he'd ever seen?
And, if you didn't make it to the Amsterdam Summit, you can still register for the free, first ever, Microsoft Americas MVP Virtual Conference. It will be held online, May 14-15, 2015, with sessions from 8 AM to 6 PM (Pacific Time), both days.
10. OpenVis Presentation Videos
The videos from the recent OpenVis conference are available online, and I love the Table of Contents that's used to access them. Click on a topic or word at the top, and see a marker, where that occurs in the video. Unfortunately, Excel isn't one of the available words, but I found a reference to it at the 2:00 mark in the video on Scrolling. Did you find Excel mentioned any any of the other videos?
I found this link in Nathan Yau's Flowing Data blog, where he talked about Danyel Fisher's presentation on cleaning up big data, that was stored in CSV files (Why Exploring Big Data Is Hard (and What We Can Do About It)).