Contextures News 2020-09-08

Get Started With Excel TEXTJOIN Function

September 8, 2020

Navigation trick, TEXTJOIN examples, 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.

Thank you for reading the news, and you'll get the next email in two weeks, on September 22nd.

• Compact Hyperlink Buttons
• TEXTJOIN Examples
• Excel Articles
• Weekly Photo

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

Compact Hyperlink Buttons

Worksheet buttons with hyperlinks are great for workbook navigation, but they can get pretty big, if you use a font size that's readable.

To save space, use compact buttons, with captions that overflow to the right. The captions are part of the button, so they're clickable too -- thanks to AlexJ for this tip!

The quick steps are shown below, and you'll find more details and a video here: Compact Buttons & Captions.

Here are the steps for creating a compact button and caption:

• Add a small shape to the worksheet, and type a caption
• Click the shape's border, and on the Ribbon's Home tab, format the font as black, bold, 14pt
• Right-click on the shape, and click Size and Properties
• On the Shape Options tab, click the Text Box triangle, to expand the section
• Add a check mark to Allow text to overflow shape
• Remove the check mark for Wrap text in shape
• On the shape, add a few spaces at the start of the text, so it moves to the right of the shape
• Right-click the shape, click Link, and add your hyperlink

To download the sample file, go to this link: ET0001 – Compact Buttons With Captions.

TEXTJOIN Function

In Excel 365 and Excel 2019, you can use the new TEXTJOIN function to combine text from multiple cells, quickly and easily. It's much easier than using CONCATENATE or the ampersand (&).

To see a couple of easy TEXTJOIN examples, watch my new "Get Started" video.

In the video, the first example joins the values from 5 cells that have weekday names in them, and separates them with a comma and space character:

• =TEXTJOIN(", ",TRUE,A2:A8)

The second example combines a list of dates, and formats them as d-mmm:

• =TEXTJOIN(", ",TRUE,TEXT(A2:A6,"d-mmm"))

See more tips on TEXTJOIN, and other ways to combine text, on my Contextures site.

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Checklist: On the Make Use Of site, there are instructions for making a checklist in Excel. It even warns you if you haven't completed the list. The cell references for the totals are a bit mixed up, but I'm sure you can sort that out! (Level - All)

Navigation: Here's some fancy Excel navigation - Robert Mundigl uses a chart carousel that shows the contents of main sheets. (Level - Adv)
(Robert's carousel inspired me to build a simple version, using comment pictures.)

Also see: My Excel Products || Previous Issues

Fall Lilacs?

Just when you think that 2020 couldn't get any weirder, another strange thing happens! Our lilac shrub usually blooms in early June, but there weren't any flowers this year. Until last week! Yes, instead of spring blossoms, we have (almost) fall lilacs. I'm afraid to see what might happen next.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200908ctx.html
I'll also post any article updates or corrections there.

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

Debra Dalgleish
[email protected] 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.