The Excel Auditor - Documentation

Microsoft make Excel. The Excel Auditor makes Excel excellent.

1)       Introduction

a)   Welcome to The Excel Auditor, a series of add-ins designed by Excel specialists for accountants, auditors and other Excel professionals.

b)   These add-ins are designed to help you analyse, assess and understand Excel workbooks by giving you essential background information that could, otherwise, take days or weeks to gather. The information will help you to make informed decisions about audit processes that you may wish to use.

2)       Installing The Excel Auditor

a)       Load the disc into your floppy drive then run the .exe file

b)       Then, in Excel, on the Tools menu, click Add-Ins

c)       If The Excel Auditor is not listed in the Add-Ins available box, click Browse, and then locate the add-in.

d)       In the Add-Ins available box, select the check box next to The Excel Auditor.

e)       Click OK

3)       Managing your memory

You can load any tool by selecting it from the "Audit" menu.

The Management Centre gives you additional control over utilities. During an Excel session it tells you which utilities are in memory, and lets you decide which:

• are automatically loaded when you launch Excel,

• you want to keep in memory,

• you want to remove from memory.

a)       Screen shot of the Management Centre dialog

b)       Description of choices

Left Check Boxes

• At the side of each tool name there are two check boxes. By checking any of the left boxes, those utilities will load automatically when you launch Excel.

Right Check Boxes

• By checking or clearing the right boxes, you can load and unload utilities during an Excel session.

• By removing the check from the "Auto Open" box, no utilities will automatically load when Excel is launched.

 4)       Sample Audit Session

a)       DocIt

Brings together a lot of information about your work into one convenient place.

It gives you data over the following categories:

• System data: Excel version, paths in use, current Excel settings.

• User information: who you are, and your current directory.

• Workbook data: Its name, where it is stored and its size.

• Workbook information: Title, subject, author, keywords and comments.

• Sheets: Lists of worksheets and the area covered, charts, dialog sheets and modules.

• Ranges: names and addresses.

• Links to other workbooks.

• VBA: Cross reference of subs and functions.

i)         Screen shot of dialog

ii)       Description of choices

• Background data
  x

• Sheets
  x

• Ranges
  x

• Links
  x

• Module Analysis
  x

• Set up for printing
  x

iii)      Sample of the output

b)       Audit Map

Ideal if you need documentation for your work, or find your way around someone else's spreadsheets.

Gives a visual overview of all the cells in use, what's in them (formulas, numbers, errors, text, etc.).

Highlights the areas covered by range names.

While the Audit Map is preparing the data, you can see its current position in the status bar. It lets you know:

• which cell it's dealing with, and

• in which row it's currently working.

At the end it displays a statistical summary, a copy of which also appears on the output. When you print the map you also get a text listing of all the range names showing which cell they begin in and the areas they cover.

Get best results by using the output of this tool with Cell Analyzer.

i)         Screen shot of dialog

ii)       Description of choices

• “Set up for printing” adds headers and footers for final output.

iii)      Sample of the output

2)       Detailed Information

a)       Search

Search for data according to your own predefined list.

It searches your work for strings you specify, such as formula names and cell formats. It then presents the results formatted and ready for you to print.

Worksheets must be unlocked for "Search" to function properly.

i)         Screen shot of dialog

ii)       Description of operation and choices

• Open the workbook “DataList.Xls” containing the list that is the basis for your search. You can add or amend lists according to your requirements The workbook is kept in The Excel Auditor’s directory.

• " DataList.Xls" is special to the way you tell "Search" what to look for. "Search" uses the information in the "A" column of its active worksheet as search strings. Column "A" cannot have blank rows between the search strings.

• Use the Prev and Next buttons in the Sheets frame to select the sheet containing your search list.

• Here is a sample sheet from “DataList.Xls” containing Excel’s date and time formulas.

• Use the Prev and Next buttons in the Workbooks frame to return to the workbook you are searching.

• Select any formats for which you wish to search. If you only want to search for cell formats you do not need to open “DataList.Xls”.

• When you have set up your search, click on OK.

iii)      Sample of the output

iv)     Description of the output

• Worksheet           The name of the worksheet the analysis is from.

• Address                 The cell address of the data.

• Search                   The string we were searching for.

• Formula                 The cell's underlying contents.

• Value                     What Excel shows you.

• Format                   The cell's format string

• Data Type              The type of data in the cell

If "Search" is prevented from auditing a cell, it reports an "** Error **" in the "Search" column

b)       Worksheet Cell Analyzer

Listing of all cells on a worksheet that contain formulas. Where the source for the formula comes from a different worksheet or workbook, those sources are also listed.

i)         Screen shot of dialog

ii)       Description of choices

• Cell analysis will breakdown the contents of each cell into its constituent parts.

• Notes attached to formula cells will include cell notes in the results.

iii)      Sample of the output

iv)     Description of the output

• The output goes into a new workbook.

• It lists each cell that contains a formula and optionally the constituent parts of the formula.

• Source workbooks and sheets are also listed where these are not the same as the source sheet.

c)       Print VBA Modules

i)         Screen shot of dialog

d)       Cell Roots

i)         Screen shot of dialog

ii)       Description of choices

               Select a "Precedent" or Dependent" listing

iii)      Sample of the output

iv)     Description of the output

The output goes into a new workbook. It lists the contents of the cell for which you requested the information together with all cells, and their contents, that have an effect on the cell.

e)       Circular References

Circular references can be notoriously difficult to trace. This tool will help you in the process. It analyses each sheet in the workbook you are currently in and reports each circular reference it finds, together with a list of the cells affected. It does not analyse protected sheets.

i)         Screen shot of dialog

ii)       Description of choices

IMPORTANT: TO FIND CIRCULAR REFERENCES THIS UTILITY NEEDS TO CHANGE YOUR DATA.

Only use this utility on a copy of the original workbook.

iii)      Sample of the output

iv)     Description of the output

The output goes into a new workbook. It lists each circular reference found and the cells it affects

3)       Audit Utilities

a)       Bars and Buttons

This tool has three windows. All the toolbars available to you within Excel, including those you have developed, are listed in the upper window. When you select any of the toolbars shown in this window, the tools attached to it are listed in the lower window.

It is especially useful when you need to:

·         change tooltips

·         edit button pictures

·         delete buttons.

i)         Screen shot of dialog

ii)       Description of choices

• Hide or display a toolbar by double-clicking on its name, or by clicking on the "Hide/Show" button.

• Delete non-Excel toolbars by clicking on the "Delete Bar" button. This button is not available if the toolbar currently highlighted belongs to Excel.

• Remove user added buttons from Excel's toolbars by clicking on "Reset".

• Buttons on non-Excel toolbars can be deleted by clicking the "Delete Button" button.

• Click on a tool shown in the lower window and its button face is displayed to the window's right.

• Edit the button's face by clicking on "Edit Button", which opens Excel's button editor.

• Change tooltips in the bottom window, then click on the "Update ToolTip" button. Tooltips attached to certain buttons cannot be changed, in which case "--- NA ---" is displayed.

b)       Lister

• Sort worksheets

• Hide or expose worksheets

i)         Screen shot of dialog

ii)       Description of choices

• At first the display lists information about the workbook in which you started Lister. If you move to another workbook, while you are in Lister, you can collect its data by pressing Refresh.

• You can paste in the current sheet, another sheet or another workbook. If you do not have a spare sheet available then create one by pressing "Insert Worksheet". If you do want the data listed in new workbook, create a new one with "Add" in the "Workbooks" section.

• You select the items you want in the list by selecting the appropriate option button.

• Sorting

You can sort the lists into ascending or descending alphabetical order before pasting the list. Additionally with the workbook sheets, you can shift them into the new alphabetical positions by pressing "Shift".

By using the arrow keys you can move sheets individually to new positions.

• Visibility

Keep prying eyes away from your work by making sensitive sheets "Very Hidden" so they can only be opened with a macro or Lister.

The button, above the "Very Hidden" button, changes its text according to the current sheet: it can say Visible, Hidden or Very Hidden. Pressing this button will change a sheet from  Visible to Hidden, and vice versa. Press this button to change a sheet from Very Hidden to Visible.

• Workbooks Section.

Create a new workbook while you work

Move between open workbooks using the "< Previous" and "Next >" buttons,

Return to the workbook that holds the data currently in "Lister" by pressing Source,

Change the data in Lister to the current workbook's by pressing "Refresh"

Change the data source to the current workbook by pressing "Refresh". This is also useful if you want to update range-name information after you have pressed "Remove REF Names"

• Other

Remove all "REF" names from your range-names in one go.

“Go to" the currently highlighted sheet.

"DrawingObjects - Data" - lists information about drawing objects on the current sheet - you do not need to press "Refresh" for this option.

c)       Objects

See the names and locations of objects in your work, such as buttons and notelets.

Adjust their size and positions and watch how the changes affect your work.

Apply some or all of the size and position features of one object to another.

i)         Screen shot of dialog

ii)       Description of choices

These options only become active after you have put an object into memory.

• The button "M+" puts an object's position and size into memory.

• If an object is in memory its name is shown above the M buttons.

• The display shows you if a macro is attached to the object. and its name.

• The display shows you what the accelerator key is, if one has been assigned.

• You remove an object from memory by clicking on the "M-" button.

• While an object is in memory, its position and size can be applied to other objects using the "p" series buttons, "pT", "pS", "pW", and "pH" (where "p" stands for paste, and the others are top, side, width and height).

• These buttons are not available if you have not put an object into memory.

To apply some or all of the size and position features of one object to another:

• Select an object and press "M+" to put its size and position in memory.

• Select another object and give it the features of the object held in memory by:

• pressing "pW" to give it the width,

• pressing "pH" to give it the height,

• pressing "pT" to position it in relation to the top of the sheet,

• pressing "pS" to position it in relation to the side of the sheet.

This is especially useful when you are trying to align objects for presentations, such as multiple graphs on one sheet.

Important: Change your mind - put them back to their original settings by pressing Reset. This feature is limited in that most text formatting will be lost.

Where is it? Not sure where an object is, then press "Go to" and the tool will place you in a convenient cell nearby.

Don't like it? Then delete it. (Important: You can't restore a deleted object.)

Hide, Show and Print. Hide or show a button by clicking on the "Hide" and "Show" button. By default, this button shows the current state of the object. Similarly you can make an object printable or non-printable by clicking on the "Print" and "Non-Print" button.

Edit Text. Change the text in an object, especially if you do not like the defaults Excel has given you.

Change Objects Name. Change an object's name in the name edit box, then press "Amend Object Name".

d)       Options

i)         Screen shot of dialog

ii)       Description of choices

This is especially useful when you are designing Excel applications for clients. You can alter your screen design without having to go back through Excel’s menu system each time that you want to make a change.

e)       Interactive Zoom

i)         Screen shot of dialog

ii)       Description of choices

See your zoom settings take effect when you select them.

The option buttons let you select specific settings.

The scrollbar inside the frame lets you select your own zoom percentage.

The two scrollbars outside the frame let you move the screen position of the worksheet so you get on screen what you want.

If you then want to go back to your original settings, click “Original”.