Are there errors in your Excel file?
Dec 1, 2015
Catch missing data, prevent worksheet errors, get an Excel Advent calendar, and much more, in this week's Excel news.
- Debra - email@example.com
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!
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.
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. One of his tips is to make backup files, with version numbers, as you work. That's easy if you have a copy of my Excel Tools add-in -- just click the Make Backup button. (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.
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!
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: https://www.contextures.com/newsletter/excelnews2015/20151201ctx.html
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2018
All rights reserved.