Contextures News 20151201

Excel Worksheet Errors

December 01, 2015

Catch missing data, prevent worksheet 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.

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

Catch Missing Data

As data entry worksheets get larger, it's harder to notice when a piece of data is missing. I've seen worksheets that have conditional formatting in every row, to highlight empty cells. That can help, but thousands of cells with conditional formatting formulas can slow down a big workbook.

In some of my files, I use a Data Check cell at the top of the worksheet, to check for missing entries in specific columns. For example, in one worksheet, I'm supposed to enter an ID number, and sometimes I forget!

In that Data Check cell, a formula counts the items in the Date column (I always remember to enter that), and subtracts the count of items in the ID column.


If the result is greater than zero, that number shows up in the cell, and conditional formatting turns the cell red (Highlight Cell Rules - Greater Than 0). That bright colour catches my eye, andreminds me to fill in those ID numbers.

You can see more conditional formatting examples on my website. It's a great tool when used in moderation!

highlight missing data

Excel Advent Calendar

For a bit of holiday fun, I've uploaded a new version of my Excel Advent Calendar. Instead of numbers on the little doors, there are simple formulas. Find the formula that is equal to today's day number, and click on it.

The workbook has macros that prevent you from opening the doors before the correct day, but you can override that by choosing "Yes" in the Test Mode cell on the Lists sheet. There is also a Reset Calendar button, so you can close all the windows again, after testing.

Instead of using my formulas, you can create your own, or use numbers 1 to 24, if you'd prefer something simpler.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Prevent Errors -- Charley Kyd shares seven ways to avoid errors in Excel reports, and these can tips can save you lots of trouble. (Level - Intermediate)

Pivot Table Values -- Eric Svensen shared a great tip for quickly removing all the value fields in a pivot table. Just drag the Values button out of the Pivot Table Field List. (Level - Basic)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

It's Getting Berry Cold

November was surprisingly mild, so now we'll see what December brings, weather-wise. On one of my lunchtime walks last week., I snapped this picture of a few hardy berries that are adding a bright touch of colour to the dready landscape. And if you tilt your head sideways, that fence looks like the grid on an Excel worksheet!

weekly photo

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

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.


Last updated: May 24, 2021 7:29 PM