XLTest: Spreadsheet testing and auditing add-in

XLTest helps you to check the integrity of your spreadsheets far more quickly than with tedious cell-by-cell inspection. Its colour maps of the worksheets give you a visual overview that enables you to very quickly see inconsistencies. Its detailed listings are ideal for audit record purposes. The ability to compare versions and run test cases facilitate compliance with spreadsheet controls.

XLTest Ribbon menu 

Buy XLTest 1.55 for Excel 2007 / 2010 / 2013 now from Paypal.

XLTest Free Edition: download the free version (1.8MB Zip file) to evaluate the formula colorizing feature.

To try a FREE time limited evaluation copy, or to see the full manual, please contact us

Buy now using PayPal which accepts most payment cards. XLTest will be sent by email in a zip file with documentation and sample workbooks.
If your email system blocks zip files, please contact us to agree an alternative method of delivery.

Volume discounts are available, currently:
All prices are in euro, plus VAT currently 23% in Ireland
Single user: 299
Five users: 922
Ten users: 1499
Twenty users: 2434
Forty users: 3955
Training as a separate option is available at 700 euro/day.

The XLTest menus

XLTest Start menu   XLTest Compare Menu   XLTest Test Cases menu   XLTest Database menu 

XLTest Visualize menu   XLTest Utilities Menu   XLTest Shortcuts menu

 XLTest also provides 14 extra functions that you can use in your spreadsheets.


Sample screenshot
Start new test session: Open workbook for testing, start new log, $APP sheet Excel app settings
Create $DOC sheet for Workbook documentation

Settings, Table of Contents, Properties, Internal and External links, Onactions, VBA statistics, Defined names, Styles, Number Formats, Cell comments, Flagged notes, Scenarios.
Reset workbook settings, hidden rows, columns, sheets Revealed#
Create $INF sheets of detailed cell inspections

This creates a sheet $INF  that summarises the warnings on the sheet. The addresses are hyperlinked to a named range referring to the cells. The sheet lists the #Error values, the error and warnings checks, some formula statistics, a list of functions used, any circular reference chain, and the number formats in use.

Example info on Errors sheet
List / Colour cells by distinct formula (R1C1)
Shows inconsistent formula blocks
Distinct Formulas Budget08
Budget08 coloured by formulas
List / Colour Conditional Format Formulas  
Shows inconsistent conditional format rules
Conditional Formats
Colour by Conditional Format Data sheet
List / Colour Data Validation Formulas  
Shows inconsistent validation rules
List of Data Validation formulas
List, colour, Data Validation Budget sample
Colours cells by data type and input/output usage

Colour by Data TYpe and Usage
Colour by Precedents location

This sheet, this book, External. Key to Precedent / Dependent colouring
Colour by Precedents Sample
Colour by Dependents location or count
   Key to Precedent or Dependent counts
Colour by Dependents Count sample
Colour by Number Format

Shows inconsistent formatting
Number Format colour map
Watch: Keeps an info window open on current selection
Shows details of cell content, format, validation, range name, etc
Watch dialog
Flag active cell  for a table of contents List flagged cells
Add supporting sheets:  $Readme, Palette, Table of Contents sheets Table of Contents sheet
Unprotect sheet
Bypasses worksheet contents protection password
Compare two workbooks (same named sheets)

It also compares VBA code.

Compare two worksheets
Workbook Somparison
Save Copy As  (saves a copy of the current workbook)

Options for what to search for when documenting XLTest options
Help on colours and keyboard shortcuts  Help screen
Run Test Cases and scenarios

XLTest can execute a set of test cases on a target workbook and stores the result of each test. You can use this for regression testing to verify that a new version performs the same as before, except of course for what you expect to have changed.

Reset Excel's Text to Columns parsing

Delete Custom Styles

Delete Unused Number Formats

Utilities to navigate sheets and handle data.

Copy Formula; Copy Text;  Copy/Move selection;  Go To Reference

Flag active cell; Select from Active Cell;  Select Formula Region

Jump to Bottom Right of worksheet

Move/Copy multiple selection
Batch autotest process
Runs all the above tests in a batch on all the worksheets of a workbook. It saves copies of the workbook with each colouring scheme.

Export / Import VB Components
Send VB source folders to Diff/WinMerge for comparison
New in version 1.5:
Scan folders for Excel files
Perform summary analysis
Store and compare audit results in database

User defined Functions

XLTest provides these functions for use in your spreadsheets.




ColorName( Colorindex )

Return color name from index


USTDate( date )

Convert text mm/dd/yyyy or mm-dd-yyyy to a date


EUTDate( date )

Convert text dd/mm/yyyy or dd-mm-yyyy to a date


TextToDate( text, format )

Convert text to date specifying order and delimiter


IsLike( text, pattern )

True if Text matches Pattern regular expression


Alphas( text )

Returns alphabetic characters in Text


Numerics( text )

Returns numeric characters in Text


TextToValue( text, [minus], [decimal] )

Converts text to number, optional negative and decimal symbols


GetFormula( cell )

Returns formula in a cell


JoinValues( range, delimiter )

Concatenates range to a string list


SumN( range )

Sums non-formula numeric cells


FileSize (filename )

Returns size of a file given its name


DateModified( filename )

Returns modified date of a file given its name


DirFile( fileno, dirname, [attrib] )

Returns the file name in the given position a directory



Contact us for more information

Back to Top

Book: Spreadsheet Check and Control: 47 best practices to detect and prevent errors