In this week’s roundup, add custom chart labels, get started with VBA, compare worksheets, get info from hyperlinks, and many more Excel 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:
- Use the INDIRECT function to compare values on different worksheets. In Excel 2013 and later, combine it with FORMULATEXT, to compare worksheet formulas.
- 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. Get Info From Hyperlinks
On the Moz blog, Marie Haynes use Excel functions to extract the domain names from hyperlinks, so she can analyze them.
3. File and Folder Pickers
Download a copy of Andrew Egwirda’s file and folder picker tools, which allow users to pick a file/folder, but prevents them from adding new folders, or do other things that could cause problems
4. Excel Tip Sheets
Anne Walsh and Bill Jelen (Mr. Excel) have published 3 tip sheets ($1 each), to help you with data cleaning, conditional formatting and data validation. Keep them near your computer, to remind you of all the great things that Excel can do. Or give them to your co-worker who constantly bothers you with Excel questions.
5. Custom Labels for Charts
Jon Peltier shows 5 ways you can add custom labels to your Excel charts. The methods range from quick and easy (type over the existing labels) to using programming.
And if you want to color those data labels, Jon’s charting utility makes that easy (and that’s just one of the amazing things that it does).
6. Functions Wish List
Chandoo asked what functions should be added to Excel, and you can read the comments, to see some great suggestions. What would you add to the list?
7. Close a File
Are you ever locked out of an important file because someone else opened it, and then wandered off? Richard Harker has a solution — VBA code that saves and closes the file after a period of inactivity. (You could change the code so it doesn’t save.)
8. Mouse and Keyboard Tricks
Here are 5 handy mouse and keyboard tricks from Sumit Bansal. However, Tip #1 has a misleading title — it moves the top item down, but doesn’t switch the items, as promised.
9. Intro to VBA
If you’re just getting started with Excel programming, read Jon Acampora’s introduction to VBA, which uses a kitchen scenario to help explain macros.
10. Power Query Samples
If you’re using Power Query in the Excel 2016 preview, Chris Webb found some sample VBA that you can use to create and manage your queries. He also extracted the code to a text file, if you’d like to look at it, and don’t have the preview.