Blog Archives

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , ,

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

One way to verify that a bona fide date is being entered into a cell is to use Data Validation.

In the pictured example, dates are being entered into a list in column E.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Here’s an example of using Data Validation to NOT allow a particular data item entry.

Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Data Validation is an excellent way to control data entry to meet a certain condition.

Suppose you want to insure that numbers entered in the yellow cells are OK for decimals,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

I previously posted this example of selecting only constants or formulas.

You can do the same with cells that contain comments or data validation.

Select the range of interest.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Data Validation is an excellent way to control data entry to meet a certain condition.

In this example, suppose you want to insure that a specific character is included in a text entry for a range of cells.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Data Validation is an excellent way to control the entry of data in your worksheet to meet a certain condition. In this example, suppose you want to insure that numbers entered in the yellow cells are divisble by 5,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Tom’s Tutorials For Excel: Using a Data Validation List From a Named Range

Here is how you can apply a Data Validation list for cells in one sheet, with that source list existing on another sheet. The process involves creating a named range for the source list,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Commenting a Cell With Data Validation

Tom’s Tutorials For Excel: Commenting a Cell With Data Validation

As a practical alternative to using a comment for supplying information about a cell, consider using Data Validation instead. The advantage is, the user sees the popup message for directions or tips when they select the cell as they enter or edit data.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Forcing entries in UPPER case

Tom’s Tutorials For Excel: Forcing Entries in UPPER Case

Here’s how you can force users to enter all alpha (letter) characters in UPPER case, using Data Validation and no programming code.

To make this happen, press Alt+D+L on your keyboard to show the Data Validation dialog box.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Prevent Duplicate Entries

Tom’s Tutorials for Excel: Prevent duplicate entries

There are times you will be using Excel that duplicate entries could be disastrous such as lists of employee’s Social Security numbers, patient’s confidential medical records or student’s contact information information to name a few. Here Tom Urtis explains how to ensure that never happens:

If you are entering a list of values that are meant to be unique,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Data Validation from Custom List

Tom’s Tutorials for Excel: Data Validation from Custom List
A special solution that Tom has in his ‘Most Wanted’ file.

Data Validation does not directly access Custom Lists, but with VBA you can establish Data Validation in a cell, with the list of drop-down items being that of one of your Custom Lists.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,