Contextures News 20200317

Excel Error Message Trick

March 17, 2020

Easy trick for error messages, formula check box, 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: You'll get the next newsletter in 2 weeks -- March 31st.

• Error Message Trick
• Formula Check Box
• 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.

Error Message Trick

Did you know that there's a hidden number in Excel's worksheet error messages? To see an example, try this:

• On a blank worksheet, press Ctrl + 8 -- the shortcut to show/hide outline symbols
• An error message will appear, because there's no outline on the worksheet:
  • "Cannot show outline symbols because no outline exists on the active worksheet. Create an outline?"
• To see the hidden error number, press Ctrl + Shift + I
• The number appears at the bottom right corner of the error message

You could use that error number to search for more information about the problem.

For more shortcuts, you can go to the Excel Keyboard Shortcuts page on my Contextures site.

Formula Check Box

With a worksheet check box, you can change formula results with a single click. In this example:

• Add a check mark, to see the total amount that you'll have to pay back.
• Remove the check mark, to see the monthly payment amounts.

This saves space in your workbook, and people can quickly switch between monthly and total amounts. You could use this technique for other Excel projects too -- have a check box for discounts or sales tax.

The check box is linked to cell C1, which will contain TRUE (1) if the box is checked, or FALSE (0) if the box is not checked.

This formula, in cell C12, calculates the loan payment, and then multiplies to get the total amount paid, if the box is checked (C1 is TRUE).

• =-PMT($B12/12,12*$A12,C$11)*IF($C$1,$A12*12,1)

You can get the detailed instructions and download the sample file on the Loan Payment Check Box page of my website.

Excel Articles

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

Be Careful: On the Trustwave blog, Diana Lopera explains how the recent Excel 4.0 macro spam attempts work. As always, be careful with unknown Excel files -- just like VBA macros, these 4.0 macros won't work when macros are disabled. (Level-All)

Functions: There are a few problems in PCWorld's examples of Excel's most popular functions. 1: Why does TODAY need to be in cell A1? 2: Terrible SUM examples! 10: Don't use REPT, use formatting. Did you spot any other problems? (Level-Int)

Excel Humour: See what people said about Excel recently. Do people love to hear you talk about Excel?

Also see: My Excel Products || Previous Issues

No Hoarding

The local grocery stores were packed last week, with people stocking up in case we have to stay in our homes for a while. A few shelves were empty, but everyone waited patiently in the long, long checkout line. It reminded me of the old Canada Food Board posters, warning people not to hoard. Now I'm using Excel to plan our meals, so we don't waste anything, or have to go out shopping again, anytime soon. Stay well!

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200317ctx.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
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.