In Excel, you can set up drop down lists that are dependent on the selection made in another cell. In this example, you select a region in column B, and only the customers in that region are in the drop down list in column C.

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

The Data Validation formula being used, with cell E3 as the active cell in that selection, is =AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D")

Periodically, you may encounter numbers in Excel that you can't sum or use arithmetically. A common cause for this is numbers formatted as text. Often, reports exported from other programs, such as an accounting package, will be formatted as text or they might contain embedded spaces.

In this article, I'll describe three ways you can convert numbers that appear trapped under glass into a usable format:

• Use the =VALUE function.
• Paste Special, Multiply by 1.
• Text to Columns feature.

I don't know if these are really "best practices" or if I'm just making them up. I know I heard them somewhere, but that doesn't make them universal by any means. Also, I generally agree with all of these best practices, although I have no intention of changing my ways:

• Never use lowercase 'i' or lowercase 'l' as variables.
• Don't reuse variables.
• Align data types in Dim statements.
• Declare each variable on its own line.
• Comment, comment, comment.
• Avoid Exit For and Exit Do in loops.
• Use named parameters.

There are no fancy Excel stunts here. These are 6 simple pointers that escape the Excel tutorials. Like anything that's done well, there are personal habits that quietly develop to keep you focused or avoid traps:

• Use color coded tabs for guidance.
• Always know where you're going and what the finish line looks like.
• When you receive a new dataset throw it into a pivot table.
• Build complex formulas in small steps.
• Don't hide row 1 or column A.
• Think "data management" because Excel is only a tool.

Are your spreadsheets always as small as they could be? This article considers common methods of reducing file size:

• Locate CTRL + END cell on each worksheet.
• Clear blank cells.
• Reducing size using *.xlsb as an intermediary.
• Reducing size for workbooks specifically containing charts and / or formatting.
• Reducing size for workbooks specifically containing text only.
• Zipping the file as an intermediate step.

Before you can create a pivot table, you must have your data laid out in the right structure. This is the most critical step, and also the most common mistake when learning how to create pivot tables. Once you have your data organized correctly, you will become much more proficient at creating reports, analyzing data, and finding trends.

This article will explain:

• The correct vs. incorrect structure for pivot table source data.
• Why it is important to understand this.
• How to convert your reports into the right structure using formulas.

When we give to our colleagues/customers/partners/friends etc. workbooks that contain custom VBA functions it is always useful to include some description about what the functions do, or what their input parameters are. Of course we could either write some comments in the beginning of the VBA code, or write a description anywhere in the workbook (i.e. just before the functions are used).

Although these methods might be convenient for normal or advanced users, new Excel users – in practice – might have some troubles. For example, a new Excel user might not know how to switch to the VBA editor (ALT + F11) in order to see the function description. So, what will happen if the developer has forgotten to add the function description in the workbook? The answer is that the new user will probably NOT use the VBA function, unless he/she finds some information from the developer.

In order to avoid this unpleasant situation a VBA developer can use the "MacroOptions" method so as to add a description to the custom VBA function, as well as to its arguments.