Help for PATools Data Analyser
Tip: Use your browser's 'Find' (often Ctrl+F) to search within this page.
Setting the range to Work on (top section of screen)
1a. All cells will run the tool on every cell from A1 to the last cell used on a worksheet, including cells in hidden rows or columns, and including each cell in a merged set of cells.
1b. Range allows you to specify the range of cells on which the tool is to run, either by typing in the range or by selecting it on the worksheet. A named range can be used.
2a. Copy sheet will create an exact replica of the current worksheet in front of it and run the tool on that replica sheet only. Thus your original worksheet is left untouched, and if you wish you can manually delete the replica sheet once you have finished reviewing the results of the tool.
2b. Current sheet will run the tool on the current worksheet. In this case you will not be able to 'undo' any changes made, and you may alter the appearance of the data (eg alignment, font, numeric formats, colours etc) so that the worksheet no longer appears as you would wish. In this case it is
recommended that you backup your workbook before running the tool.
2c. All sheets will run the tool on all worksheets in the workbook. In this case you will not be able to 'undo' any changes made, and you may alter the appearance of the data (eg alignment, font, numeric formats, colours etc) so that the worksheets no longer appear as you would wish. In this case it is
recommended that you backup your workbook before running the tool.
Activity: Analysing or clearing formats (middle left section of screen)
1. Analyse is the option to use to show you how a worksheet is constructed. Depending on the options you select it will reformat the appearance of the data so you can see at a glance what a cell contains, irrespective of the value shown eg is the cell a formula, a number, text, does it evaluate to an error, etc.
To the right you can specify whether to analyse using the fill colour (cell background colour) only, the font colour only, or all formats (ie font, fill, alignment, numeric format and borders). To view or change the settings use the Settings button (see 'Changing the settings for the analysis' below).
2. Clear formatting is the option that resets formats to their defaults. Note: These are the worksheet defaults, not the previous settings that you may have specified - in other words, this is NOT an 'undo' button to return your worksheet to its appearance prior to running the analyse tool.
To the right you can specify whether to reset the fill colour (cell background colour) only, the font colour only, or all formats (ie font, fill, alignment, numeric format and borders).
Changing the Settings for the analysis (middle section of screen)
The Data Analyser already has default settings for each type of cell (ie formula, number, text and error) in the analysis. However you are free to change these settings either temporarily or permanently. The factory defaults can always be reset for all types or any one type.
On pressing the Settings button the 'Change format settings' window opens. This allows the settings to be changed for how a cell containing a formula, number, text or error will be displayed when the analyser is run. In each case you can set up the font, fill (colour and patterns), alignment, numeric formatting and borders. At the end of all changes you can select whether to retain the settings permanently or not.
Note: If you enter the dialog box for any format type (eg font) then the Analyser determines whether you have made a change based on whether you exit the dialog box with the OK key (settings logged as altered) or the Cancel key (settings logged as not altered), irrespective of whether you actually end up changing the setting.
You can also reset the factory defaults for each type individually, and you will be prompted for retaining these settings either temporarily or permanently.
The Reset Defaults button allows you to reset the factory settings for all four cell analysis types, and you will be prompted for retaining these settings either temporarily or permanently.
The Show me button displays the current settings in a temporary workbook which is immediately closed again (and not saved).
Analysis options (middle right section of screen)
If you find you usually require any option then by right-clicking it you can choose whether it is permanently checked or not.
1. Treat empty cells as text is an option to help clarify the visual appearance of an analysis.
The default setting is that empty cells are analysed as if they contained text because it is often clearer to the eye (eg where a long text cell overruns into an adjacent empty cell's space) if empty cells are treated as text. In the final summary of the analysis they will still be counted as blanks, but they will have been formatted as if they were text cells.
2. Do not separate error cells allows you to analyse cells resulting in an error depending on their content (ie formula, text or a number).
Use of this software has shown it to be useful that by default cells that result in an error are shown separately, irrespective of whether it is a formula, text or number that generates the error.
3. Treat arithmetic formulae as numbers is an option that means that any cell that contains a formula which is purely arithmetic (eg =3+4, or =25*5) will be treated as if it were a number.
Good spreadsheet practice and the need for audit-trails often dictates that where a cell may contain a value (eg 300) and then require an adjustment (eg add on another 100) this should be shown (in our example the cell contains =300+100, which displays the result as 400), rather than just the new value (400). In this case Excel understands this cell as a formula. When analysing a worksheet it may be more helpful if these 'adjusted-value formulae' are shown as values rather than formulae.
top
Screen off will give even more speed by deactivating the screen while it runs so you will not see the alterations being made. This is best used for large worksheets, and you will need to be patient - it is working even if you cannot see it!!!
Note: If you right-click on the checkbox you have the option to change the default setting.
top
1. This software will not alter the contents of the cells in your workbook in any way. The only changes that will be made are to formatting (ie appearance).
2. Hide temporarily hides the Analyser to allow you to work on the worksheet, and when reactivated will instantly reopen it in the same screen position with the same selections as when it was hidden. In the meantime you can open and close files, and use all the Excel facilities.
Note: No harm will come if you close down Excel whilst the Analyser is hidden.
3. Although the software is well tested and written, we would recommend as good practice that any data and template workbooks be saved and backed-up before running this software, as it is impossible to protect against every possibility of system crashes, printer problems, etc causing runtime problems that may damage your data.
4. For this, as with any software, the best way to evaluate it and make it work for you is to play with it using dummy data until you become comfortable and are ready to use it for real.
|
