How to use Excel's Formula Audit -tools

Sometimes it is hard to understand the logic of complex Excel formulas - Especially when there are lots of references to different cells or ranges. Excel has (of course :-) ) great helper tools for this. There is set of Audit Formulas -tools in Ribbon bar's Formulas tab.

• Trace Precedents: Shows with arrows from where the selected formula takes it's parameters
• Trace Dependents: Shows with arrows the formulas that contains reference to the selected cell
• Check for Errors: Tool for analysing possible errors in formula
• Remove Arrows: Clears arrows from the screen

Following video demonstrates this:

Tip: If you have difficulties to see the video, try to maximise it from lower right corner:

How to highlight dates that are older than 70 days using Excel's Conditional Formatting

Following video demonstrates how to use Excel's conditional formatting to highlight dates that are older than 70 days. The formula takes into account that empty cells won't be highlighted. Following formula in D1's Conditional Formatting does the trick:

=AND(D1<>"";DAYS360(D1;TODAY())>=70)

Copy the formula to all cells in column D.

Here is a video demonstrating this:

How to make text starting with =, - or + character in Excel

Sometimes you may want to write cell text that begins with =, - or + character. Excel, however interprets text beginning with those characters as formula and that causes an error. Trick is following:

• Type ' -character as a first character. This forces Excel to interpret the cell content as text.
  e.g. typing '--> displays as --> and causes no error

This video demonstrates this: