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.5 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 learn more, download the 17-page set of screenshots (2.6MB PDF) showing you what it does.

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, plus VAT in Ireland.

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.

Features  

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.
Sample
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
Unprotects
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

Tests
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.50:
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.

Function

Description

Example

ColorName( Colorindex )

Return color name from index

=ColorName(1)

USTDate( date )

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

=USTDate("12/1/2008")

EUTDate( date )

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

=EUTDate("12/1/2008")

TextToDate( text, format )

Convert text to date specifying order and delimiter

=TextToDate("08-02-09","mdy-")

IsLike( text, pattern )

True if Text matches Pattern regular expression

=IsLike("abc@def.ghi","*@*.???")

Alphas( text )

Returns alphabetic characters in Text

=Alphas(B5)

Numerics( text )

Returns numeric characters in Text

=Numerics(K5)

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

Converts text to number, optional negative and decimal symbols

=TexttoValue("12.3-")

GetFormula( cell )

Returns formula in a cell

=GetFormula(K9)

JoinValues( range, delimiter )

Concatenates range to a string list

=JoinValues(G2:G15,",")

SumN( range )

Sums non-formula numeric cells

=SumN(A1:K10)

FileSize (filename )

Returns size of a file given its name

=FileSize("test103.xls")

DateModified( filename )

Returns modified date of a file given its name

=DateModified("somefile.xls")

DirFile( fileno, dirname, [attrib] )

Returns the file name in the given position a directory

=DirFile(1,"xl*.xla")


_______________________________________________________

Contact us for more information
_______________________________________________________

Back to Top

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

http://www.sysmod.com/scc.htm