Excel Roundup 20150615

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!

Weekly Excel Roundup http://blog.contextures.com/

1. Contextures Posts

In case you missed them, here are the articles that I posted recently:

  • 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.


Weekly Excel Roundup http://blog.contextures.com/