Search Contextures Sites
Mouse Tricks and Macro Intro
June 16, 2015
Add custom chart labels, get started with macros, compare worksheets, get info from hyperlinks, and many more tips, in this week's Excel news.
This week, I've grouped the articles by their Excel skills level, to make it easier to find what you need.
- Debra - firstname.lastname@example.org
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:
Quick and Easy
1. 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. There is a keyboard shortcuts tip sheet on my website too, that you can download.
2. 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.
3. 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.
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.