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.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Did you know that there's a hidden number in Excel's worksheet error messages? To see an example, try this:
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.
With a worksheet check box, you can change formula results with a single click. In this example:
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).
You can get the detailed instructions and download the sample file on the Loan Payment Check Box page of my website.
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
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.
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.
Last updated: March 16, 2020 10:27 AM