Contextures News 20180313

Dynamic Ranges

March 13, 2018

Create a dynamic range, 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 supports the free info on my site!

Hyperlink Quick Tips

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:

• Select the cell(s)
• On the Home tab of the Excel Ribbon, click the Wrap Text command.

Later, if you need to select a hyperlinked cell, without following the link:

• Point to the cell, and press the left mouse button for a couple of seconds.
• The pointer will change to a big white plus sign, to show that the cell was selected.

See more hyperlink tips on my website.

Dynamic Ranges

If you're referring to a range of cells, you can use a static or dynamic reference:

• A static range has an address that doesn't change, such as a list of months in $A$1:$A$12
• A dynamic range adjusts automatically if rows/columns are added or removed. For example, a product list can grow or shrink

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.

• =AVERAGE(OFFSET(B4,0,0,1,$B$1))
• =AVERAGE(B4:INDEX(B4:G4,1,$B$1))
• =AVERAGE(B4:CHOOSE($B$1,B4,C4,D4,E4,F4,G4))

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.

Seating Chart Part 2

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.

Excel Articles

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!

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Proud Canadians

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.

Debra Dalgleish
ddalgleish @ contextures.com

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.