Contextures News 20190219

Excel Quick Tip and Errors

February 19, 2019

Excel error values, hyperlink quick tip, 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 helps support the free tutorials on my site.

Hyperlink Quick Tip

When you create a hyperlink in an Excel sheet, the pointer changes to a hand shape when you point to the link. But if the column is wider than the hyperlink, the right side of the cell isn't clickable.

If you want the entire cell to be clickable, when it contains a hyperlink, change the cell setting to Wrap Text -- that command is on the Home tab of the Excel Ribbon.

hyperlink with wrap text

Get more Hyperlink tips, and Hyperlink function examples, on my Contextures site.

Error Values

Things can go wrong with Excel formulas, and occasionally you'll see an error value in a worksheet cell, instead of the number or text that you expected.

I've added a new page with a simple example for each of Excel's hash error values, and links to help with troubleshooting.

For example, can you see why this VLOOKUP formula is returning an #N/A error value?

Excel #N/A error

Answer: Pant needs an "s" at the end, or Excel can't find it in the lookup range.

The new page also shows how to create your own error values. This formula shows "#OVER!", if the total amount is over the budget limit.

=IF(SUM(B2:B7)>D2, "#OVER!", SUM(B2:B7))

But remember, use your Excel powers for good, and not evil! Add notes, or a cell comment, to explain your fancy error values.

IF function with custom error value

Excel Articles

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

Nerds - Ten years ago, there was a "You might be an Excel nerd if..." contest on my blog, and you can read all of the entries here. Recently, Trevor Fox posted his list of 30 ways to tell if you love spreadsheets too much. (Just ignore the sales pitch.) Do you fit the descriptions in either of those lists? (Level - All)

Macros - Excel's Macro Recorder is a great way to get started with VBA programming. Kevin Lehrbass shows how he recorded a few steps, and then edited the code, to create a fun macro with hearts and a robot. You don't have to do serious work with Excel all the time! (Level - Intermediate)

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

Snow Days

Were you in the path of the big winter storm last week? This was the view from my office, with freezing rain piling up on the window -- it's nice to work from home. If you're in the southern hemisphere, and suffering in the summer heat, I hope this photo helps you feel a little cooler! I also hope that Wiarton Willie was correct on Groundhog Day, and spring will arrive in a few weeks.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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

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.



Get weekly Excel tips from Debra


pivot power free






Last updated: February 19, 2019 11:38 AM