The TRUNC function in Excel is used to reduce the number of decimal places on a number without rounding.

Excel provides a few different rounding functions such as ROUND, ROUNDDOWN and CEILING to cater for all your rounding needs. However the beauty of the TRUNC function is that you can set the number of decimal points for a number without rounding.

Truncating numbers in this way will affect the number and therefore impact on calculations that rely on this number. Using the Excel formatting tools to reduce the number of decimal places is another alternative that does not affect the number involved.

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.