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.
Full article: How to evaluate a nested formula one step at a time
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.
- Lock it down.
Full article: Top 11 tips to avoid spreadsheet errors
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:
- Find & Replace.
- Text to columns.
- Error checking.
Full article: 6 ways to fix dates formatted as text in Excel
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.
Full article: Sort data using formulas
In the old days, we could use a combination of the
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
Full article: Clean up reports with IFERROR
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.
Full article: Analysing your workbooks with the Inquire add-in
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.
VLOOKUPcannot look to its left.
- Your table contains duplicates.
Full article: 6 reasons why your VLOOKUP is not working