Data Validation is a very useful Excel tool. It controls what can be input into a cell, to ensure its accuracy and consistency.

In this blog post we will explore 11 useful examples of what Data Validation can do:

• Allow uppercase entries only.
• Prevent future dates.
• Creating drop down lists.
• Dependent drop down lists.
• Prevent duplicate values.
• Allow only numeric or text entries.
• Validate an entry based on another cell.
• Allow the entry of weekdays only.
• Restrict the text length.
• Entries contain specific text.
• Create meaningful error messages.

Data Validation is a useful way to provide help for users when they're filling in a data entry form.

But the Data Validation popup message covers the remaining input cells and is very distracting, especially if the form contains many cells to fill. And it cannot be dismissed.

This article describes a technique for adding help using an information icon 🛈 with hyperlink and Data Validation message.

A common problem around web forums and blogs is how to plot blank cells in Excel charts.

There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart.

A new feature in Excel 2016, Show #N/A as an empty cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Do you want to know how to make a header in Excel? Or are you wondering how to add the footer "page 1" to the current worksheet?

This tutorial will teach you how to quickly insert one of the predefined headers and footers and how to create a custom one with your own text and graphics.

Topics include:

• How to insert header in Excel.
• How to add footer in Excel.
• Insert a preset header or footer.
• Create a custom header or footer.
• How to change header and footer in Excel.
• How to close header and footer.
• How to remove header and footer in Excel.
• Excel header & footer tips and tricks.

Excel can be used to conduct a forensic audit, gathering evidence of possible fraud.

We cannot eliminate mistakes or "fudging" in financial data, however we can positively try to minimize it.

Here are five techniques that can be applied using Excel for tracing such issues:

• Identifying duplicate transactions using highlight values.
• Analyzing round numbered transactions.
• Above average payments to vendors or checking the ratio between a maximum and minimum.
• Gap detection.
• Checking ratio of the highest to the second highest number.

Excel projects of any significance are very often complicated. Documenting such projects is crucial for auditing and maintainability.

Fortunately, Microsoft provides several options for documenting Excel projects:

• Workbook name and path.
• Workbook Properties.
• Model structure.
• Titles.
• Value labels.
• Names and Structured References.
• Cell Comments.
• Text boxes.
• Data Validation.
• Hyperlinks.
• Documentation worksheets.
• External documentation.

[Note: The article also suggests using the N() function to include documentation in a formula. This is a risky practice that may result in errors. iⁿ advises not to use the N() function for documentation.]

Excel's data validation feature is underused because many users don't realize how versatile it is, especially where dates are concerned.

Dates seem to complicate things, but only in your head! This feature handles dates fine.

Here are four ways to express dates using data validation:

• Literal values. Just enter the first and last acceptable dates.
• Input values. Refer to input cells instead of entering literal date values.
• A dynamic list. Create the date list as a Table, so the validation updates as you modify the list.
• Formulas. Create a formula to validate the dates. This approach is dynamic and very flexible.

With the hype of deep learning neural nets, and machine learning algorithms, it's easy to forget that most of the work in data science involves accessing and preparing data for analysis. Indeed, not all data is Kaggle-ready. The reality is: data is often far from perfect.

Do your consultant (and budget) a favor and follow these rules-of-thumb when using spreadsheets to collect and organize your data:

• Do not rely on spreadsheet formatting to indicate associations in your data.
• Never merge spreadsheet cells.
• Always use Data Validation tools for data entry.
• Never (ever!) delete rows of data if you want the data excluded from the analysis.
• Create a key that explains each column of data in a table.
• Preserve the integrity of the data by separating the data from the analysis.
• Use a fixed spreadsheet template and collect data in a series of spreadsheet files (rather than a series of tabs in a file).