If you import dates into Excel from another system, or from the web, they may appear in Excel using a text format. This looks great but will not provide full functionality. Unless you can convert the text into a date format you will not be able to use that data in formulas, PivotTables and other Excel features.

Fortunately, Excel provides a function that can convert text into a date format, and it is very easy to use.

If working with dates in Excel you may need to find out how many months are between two dates. Fortunately, Excel provides some excellent Date and Time functions that allow us to calculate such differences.

A common problem when using Excel is handling duplicate entries. I am often asked about removing or highlighting duplicates and common entries.

But prevention is better than the cure, so let’s look at how to prevent duplicates in Excel.

We can use the Data Validation tool with a formula to stop duplicates from being entered by the user. In this example we want to prevent duplicates from being entered into column A.

Named ranges provide many benefits when using spreadsheets. They allow easy access to data on other sheets, make references absolute and provide meaningful names to data.

However, if the list is updated often with new records or columns of data, then the named range needs to frequently be updated also. This is an irritating and time consuming role, so this post aims to solve that problem by making the named range grow automatically when new rows and columns are added.

I was reminded of the brilliance of this feature just yesterday when I was asked by a company how they could get their formulas to include any new rows of data automatically. They had various formulas and PivotTables running on a large list of data to perform sales analysis. This data is frequently updated as more sales are made.

The Data Validation tool in Excel can be used to validate uppercase entries. This post demonstrates two examples of validation to ensure text is entered in uppercase.

The first ensures that the whole entry has been written in uppercase, and the second will only validate the first letter of an entry.

Validate Text Entries to be in Uppercase

This example will ensure that the whole entry is in uppercase.