March 13, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
If a wide cell contains a short hyperlink, the pointing hand will only appear when it's over the text, not in the empty space. To fix that:
Later, if you need to select a hyperlinked cell, without following the link:
See more hyperlink tips on my website.
If you're referring to a range of cells, you can use a static or dynamic reference:
You can create a dynamic range easily, with a named Excel table. But, in places where you don't want a table, use a formula instead.
To see an example, download my dynamic ranges sample file. It shows 3 ways to get the monthly Sales average for a specific number of months. I like using the OFFSET function, but it's volatile, and might slow down a large Excel file, The workbook also shows dynamic ranges created with INDEX and CHOOSE.
If you're struggling with slow workbooks, take a look at Charles Williams' FastExcel add-in. There are great tips on his website too, for optimizing your Excel files.
If you downloaded the Seating Plan workbook last week, there are details on the formulas in my latest blog post. You'll also see how to add more guests, tables, and chairs.
And if you don't need a seating plan, the formulas can be useful in other types of Excel files. For example, to create the list of names for the drop down, there's an IF within the MATCH function.
=INDEX(B:B, MATCH(H2, IF(H2<=MAX(D:D), D:D,F:F),0))
Numbers for previously used names are found in column F, and unused ones come from column D.
Here are a couple of Excel articles that you might find useful or interesting.
Excel Sites - Last Thursday was International Women's Day, and Kevin Lehrbass wrote about 3 of his favourite Excel sites that are run by women. For more Excel tips, go to the Newton Excel Bach site, run by Doug Jenkins, and see which of his Excel articles have been most popular. (Level - All)
Excel Ribbon - Doug Glancy has shared his free tool to create and edit the code for custom tabs in Excel Ribbons. Doug couldn't use the Custom UI Editor that I showed, so he built his own add-in that runs within Excel (2010 and later versions). (Level - Advanced)
Excel Humour - If you remember the early days of personal computing, you might find this story as funny as I did. Lots of executives suddenly had to do their own computer work, and it was a struggle!
The Microsoft MVP Summit was held in Seattle last week, and I didn't attend, but lots of my fellow Canadians were there. We're usually one of the largest groups, and the noisiest (but polite)!
At MVP events, we proudly wear our red Canadian gear -- hockey jerseys, hoodies or football jerseys. Here is this year's offical photo, courtesy of our CPM, Sim Choudry. The group always sings O Canada too - you can hear a bit of the 2013 version on YouTube (turn down your speakers first).
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180313ctx.html
I'll also post any article updates or corrections there.
ddalgleish @ contextures.com
Last updated: March 15, 2018 4:36 PM