Contextures

Contextures News

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!

Data Entry Rules

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.

create a custom rule for no duplicates in a table column

Check Excel Settings

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.

application settings add-n

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

An Old Mouse

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.

weekly photo

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: http://www.contextures.com/newsletter/excelnews2017/20170905ctx.html

Debra Dalgleish
ddalgleish @ 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.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: August 31, 2017 3:30 PM