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, AND that only numeric increments of a quarter of a number are allowed. For example, the entry of 6.75 or 3.25 are allowed, but 4.35 or 1.62 are not allowed.

The following steps show how this can be done.

I've emphasized before that the key behind visual design is that every formatting decision you make should have a purpose behind it. In the following post, I plan to demonstrate more of the visual style I use in Excel:

• Dark background and white font.
• Soft gray lines.
• Dotted lines.
• White borders.
• Custom trim.
• Uniform column widths.
• Pastel highlighting.
• Excel data bars instead of color scales.

The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.

A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.

Over the next few articles we will explore the usage of Excel tables which are a powerful feature of Excel.

We will look at when to use them and when not to use them, the advantages over using plain tables - simple ranges - and a few of disadvantages of using them.

We will also go through a practical example of building a spreadsheet to manage your current account, which will highlight many key points of using Excel tables.

See:

• Part 1: Introduction.
• Part 2: Creating an Excel Table.
• Part 3: Structured references.
• Part 4: Totals.
• Part 5: Running totals.
• Part 6: Preserving entry order.

You may have imported or entered account codes into a Microsoft Excel spreadsheet and want to identify invalid account codes. Let's say the account codes are supposed to have five characters but because of data entry errors some accounts codes do not meet the required criteria; they either exceed or are less than five characters.

By using the Data Validation option to circle invalid data the invalid account codes will be circled and therefore easily identifiable. It must be noted that data validation will have to be applied before circling invalid data. Circling Invalid Data can only be applied with data that has already been captured, unlike with Data Validation which can also be applied prior to data being captured.