Contextures News 20150616

Mouse Tricks and Macro Intro

June 16, 2015

Add custom chart labels, get started with macros, compare worksheets, get info from hyperlinks, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Quick and Easy

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.

Excel Humour -- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

Beyond the Basics

1. Compare Worksheet Values -- On my Contextures blog, I show how to use the INDIRECT function to compare values on different worksheets. In Excel 2013 and later, combine it with FORMULATEXT, to compare worksheet formulas.

2. 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).

3. 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?

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

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

Advanced and VBA

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

2. 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.)

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

Photo of the Week

I've got a few window boxes hanging on the railing of the back deck, and the plants are doing well this year. I always plant marigolds, because I love the bright colours, and they're easy to grow. There are a few varieties of lettuce too, and some herbs, so it's like having a lovely salad bar, right outside the back door! If it's growing season in your neighbourhood, I hope you're enjoying some fresh produce too.

weekly photo

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy the link below, and paste it into your browser:

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.


Last updated: October 29, 2021 7:04 PM