Contextures

Contextures News 2021-02-23

Excel Shortcuts and Oddities

February 23, 2021

Hyperlinks oddity, line breaks shortcuts, 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 on March 9th.

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

Hyperlinks Oddity

There are macros on my Contextures site that let you work with Excel's hyperlinks. For example:

  • Create a table of contents that links to each sheet in your workbook,
  • Add hyperlinks to email addresses in a pivot table

Last week, I was working on a new macro, that was supposed to do 2 easy steps:

  1. Grab the value from the cell with the hyperlink (the hyperlink's "Text to Display")
  2. Put that value in the destination cell

The macro wasn't working though, and it took me a while to figure out the problem.

  • If the hyperlink's cell is a number, it's not in the "Text to Display" setting.

TIP: If you need to work with that number in a macro, change it to text, by putting an apostrophe at the start of it

There are more hyperlink tips, macros and examples on my Contextures site.

text to display not editable with number in cell

Line Breaks

If you want cell text to appear on separate lines, add a line break:

  • Select the cell, and click where you want the line break
  • Press Alt + Enter

If you've added line breaks to lots of cells, and you want to remove all of them, you can use Excel's Find and Replace feature.

However, you need to use a different shortcut in the Find and Replace boxes:

  • In the Find box, press Ctrl + J (you won't see anything appear in the Find box}
  • Leave the Replace box empty, or type the character you want to use instead of a line break
  • Then, click Replace All, or use Find Next to go through the cells one by one

To see the steps, watch Sarah's latest video, on the Data Entry tips page of my Contextures site.

line breaks in cell

Excel Articles

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

Charts: On his Junk Charts blog, Kaiser Fung shows how two charts can show a different story, with the same data and chart type. Click this link to learn more about the chart trifecta that he mentions.

Microsoft: Join Microsoft's Excel team for an Excel Reddit AMA (Ask Me Anything) on March 10 at 10 AM (Pacific Time). The whole Excel team will be there, answering your questions, so don't miss it! The AMA link will be posted soon, on this Microsoft page. (Level - All)

Challenge: Robert Gascon has posted another Excel formula challenge, asking you to calculate product inventory, using your array manipulation skills. Even if you don't want to submit an answer, take a look at some of the other impressive solutions! (Level - Int/Adv)

Also see: Previous Newsletter Issues

Bird Feeder

We got a bird feeder this month, and it took a couple of weeks before the birds came to visit. Now, it's a full-time job to keep it filled with seeds! Most of the visitors are chickadees, but cardinals sometimes stop by for lunch too. Oh, and pesky squirrels, of course. They haven't figured how to get into the feeder (yet), but they gobble up the seeds that are scattered below the feeder.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210223ctx.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
dsd @contextures.com
ctxdebra @gmail.com

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.

contextures newsletter info

 

Last updated: February 22, 2021 12:26 PM