Make Your Own Excel Rules
Sept 5, 2017
Make your own rules for data entry, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
With Excel's data validation feature, you can make rules for what is entered in a worksheet cell. To allow specific items only, create a drop down list that appears in the cell. (My Data Entry Pop Up kit makes them easier to use.)
In addition to lists, there are other built-in rule options, such as allowing numbers in a set range, or text entries that are a specific length.
You can create custom rules too, if you can't find a built-in rule that does what you need. For example, use a formula to prevent duplicate entries in a range named EmpIDs.
=COUNTIF(EmpIDs,A2) <= 1
See more custom rule examples on my Contextures website. Block weekend dates, limit the total amount, and more rules. Get the sample workbook on that page, and see how the rules work.
Things can go horribly wrong, if Excel ends up in Manual Calculation mode, and you don't notice. That can happen if someone sends you a file that was saved in Manual mode, and it's the first workbook that you open.
To quickly check your settings, add the Automatic Calculation command to your Quick Access Toolbar -- see my example here. Or, go to the Formulas tab on the Excel Ribbon, and click Calculation Options.
If you need to fix Calculation settings frequently, there's a free Application Settings add-in, from Andrew Engwirda. Quickly see and change the settings -- the non-default settings are in red font. There's also an option to let the add-in fix the application settings when you open or save files.
NOTE: The download link is at the very end of Andrew's article.
Here are a couple of recent Excel articles that you might find useful or interesting.
Excel Tips - Ben Kusmin explains what attorneys should do to uncover hidden content in Excel files that might be used as evidence. Even if you're not an attorney, these tips can help you troubleshoot a file that you inherited from someone else. (Level - Int/Adv)
Excel Versions - Do you remember the days when Excel only allowed one font per sheet, and had a 1MB file size limit? I found old MacUser magazines with a review of Excel Version 2.2 (8 colours!), and Excel tips from Version 1.0, that are still useful -- see the highlights on my Contextures blog. (Level - All)
The first versions of Excel were made for the Macintosh, and we still have our original Mac in the basement. Here's a photo of the boxy-looking mouse that came with it. The mouse has a roller ball on the bottom -- do you remember cleaning those? There is only one button on the mouse, and no scroll wheel.
To help us navigate our worksheets faster, the scroll wheel was created by an Excel Program Manager at Microsoft, in the early 1990s.
That's it for this week! If you have any comments or questions, send me an email.
NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20170905ctx.html
ddalgleish @ contextures.com
Last updated: August 31, 2017 3:30 PM