Sometimes, understanding how a figure is calculated is difficult because the formula seems quite complicated; comprising of several intermediate calculations and logical tests. Luckily Microsoft Excel has a powerful auditing tool, Evaluate Formula, which will help you to unravel the different parts of a formula, in the order that it is calculated, to understand how the end result is constructed.

Excel is a flexible and deceptively easy tool to use, which is also one of its dangers. The following is a checklist to assist you avoiding some of the most common problems:

• Scope the job.
• Is excel the right tool for the job?
• Use the formula builder.
• Design it in stages and check as you go.
• If there is an error.
• Use formulas to help you.
• Use formula tools.
• Use conditional formatting.
• Make the input stage simple to use.
• Review.
• Lock it down.

If you import data to Excel from another program chances are the dates will come in formatted as text, which means they're not much use to you in formulas or PivotTables.

There are many ways to fix the dates and the method you choose will depend partly on the format they're in and partly based on your preference for a formula or non-formula solution:

• VALUE function.
• DATEVALUE function.
• Find & Replace.
• Text to columns.
• VALUE and SUBSTITUTE functions.
• Error checking.

Knowing how to use Pivots is one of the MUST KNOW features of Excel.

This blog shows you, in a step-by-step manner, how to produce a summary report/analysis using a Pivot table.

This short article shows you how to sort a range of (unique) data using formulas.

The technique uses the COUNTIF function in a helper column to extract the order range in numeric values. It then uses a VLOOKUP function to create the dynamically sorted list.

In the old days, we could use a combination of the IF and ISERROR functions to clean up reports. This method was good, and worked for many years... but starting with Excel 2007 there is an easier alternative.

This post explores the IFERROR function.

In Excel 2013, amongst all the other amazing features that have been added since 2010 is a new add-in that can analyse your workbook for errors or inconsistencies.

The Inquire add-in is an auditing tool that enables you to compare workbooks, analyze a workbook for problems or inconsistencies, and create a diagrammatic view of relationships between cells, worksheets, and external data sources.

The VLOOKUP function is the most popular lookup and reference function in Excel. It is also one of the trickiest and the dreaded #N/A error message can be a common sight.

This article will look at the 6 most common reasons why your VLOOKUP is not working:

• You need an exact match.
• Lock the table reference.
• A column has been inserted.
• The table has got bigger.
• VLOOKUP cannot look to its left.
• Your table contains duplicates.