Contextures

Contextures News 20200714

Excel Hide or Show Errors

July 14, 2020

Open a group of Excel files, hide or show errors, 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.

You'll get my next newsletter on July 28th -- we're on the summer schedule now, with a newsletter every 2 weeks.

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

Where's Workspace?

Did you ever use Excel's Workspace feature, that disappeared in Excel 2013? You could save a set of workbooks, maybe for a project, or your morning tasks. Later, you'd open that Workspace, and quickly get back to work.

If you miss that feature, my new sample file, UF0052 - List and Close All Workbooks, might help you.

  • Click a button, and a macro lists all the open files, and closes each file.
  • To get back to work, open the macro file, and click a button to open all the listed files.

Hide or Show Errors

Even if you don't need to practise your times tables, you'll get a few Excel tricks from my multiplication tables workbook. There aren't any macros, just formulas, data validation, and conditional formatting.

There are 2 sheets where you fill in your answers. When you're finished, choose Yes from a drop down list, to highlight any errors. Cell J9 is orange, because of this conditional formatting rule -- 9 x 4 is not 38!

  • =AND(B9<>"", $M$4="Yes", B9<>$E4*B$8)

highlight errors

If you enter a range of numbers, formulas create a grid with that sequence. Conditional formatting puts borders on those cells only, so the sheet doesn't look cluttered.

create a number sequence

If you'd like details on how the file works, go to the Excel Multiplication Table Practice Workbook post on my Contextures Blog.

To download the file, go my Sample Files page, and look for FN0065 - Excel Multiplication Tables, in the Functions section.

Excel Articles

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

Data Viz: Sara DeLong explains why you need a data vizualization style guide. At the end, she links to a list of Data Visualization Style Guides -- you'll find lots of inspiration there! (Level - All)

Charts: On the Policy Viz blog, Jon Schwabish shows how to make a fireworks chart, using a bar chart and scatterplot, with a picture added. (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

Early Harvest

Last month, I showed you a picture of our Tumbling Tom tomato plant. It had lots of flowers and tiny green tomatoes, and it's done well since then. On Friday, I picked all of the ripest tomatoes, and there are about 10 times as many still on the plant. We might have to set up a fruit stand at the end of the driveway, to sell the surplus crop!

weekly photo

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

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: October 7, 2020 4:12 PM