Excel Quick Tip and Errors
February 19, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
Get more Hyperlink tips, and Hyperlink function examples, on my Contextures site.
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?
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.
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)
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.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190219ctx.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.
Last updated: February 19, 2019 11:38 AM