Workbook statistics add-in

Sometimes it is helpful to be able to report statistics about the set of workbooks contained in folder. This add-in provides such a report and includes (see the screenshot below):

• A list of the sheets in a workbook and note of their status (active, hidden, etc.)
• A count of the number, text, logical and error values (manually entered) in cells on each sheet;
• A count of the number, text, logical and error formulas in each sheet;
• The number of names defined on each sheet and at the workbook level;
• The number of external references.

The report can be extended to display any statistic available within a workbook.

Signup to download and install the add-in you can access using the link at the top of the page. You will be prompted to sign up (or sign in if you already have an account on this site).

The add-in is designed to help professionals working with spreadsheets and although the add-in has no monetary cost, we do ask you to provide professional contact details. You will need to enter details that can be validated to ensure you have access to the add-in or other information on this site.  If you provide incomplete details it is likely the web site will not enable your account for this feature.

Copyright (c) 2008-2011 Lyquidity Solutions Limited

This add-in is provided under the Lesser or LGPL license, a derivation of the open source GPL license. Copies of the text of these licenses is included in the add-in.

The Lesser GPL license allows you to use the software for any purpose, including the options to use, copy, modify, merge, publish, distribute, sublicense, and/or sell, but requires that if you create a derived work (anything based on this original) this original source is made available with your derived work and that this license, and original copyright marks, are included with the derived work.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

• Download the zip file

  The download zip file (see the link at the top of this page) and unzip the file. The zip file contains two an Excel add-ins (XLStatistics 2003.xla and XLStatistics 2007.xlam) and these files should be used in the instructions that follow.

• Copy to the Microsoft add-ins folder

  Excel looks for add-ins in a special add-ins folder and the .xla and/or .xlam (Excel 2007) file from the zip should be saved into this folder. This folder can be found here:

  C:\Documents and Settings\<your account>\<my>\Application Data\Microsoft\AddIns

  where <my>should be changed to the name you login with. If you login to a domain, it will be <my username>.<domain>

• Enable the add-in

  Excel 2003: Select the "Add-ins..." menu option from the Tools menu. If the .xla file has been copied into the correct folder, it will shown in the list that the add-in manager presents.

  Excel 2007: Select the circular button at the top-left of the Excel window and then select 'Add-ins' from the options shown on the left-hand side of the window shown.  At the bottom of the page displayed is an option 'Excel addin...' and a button to the right.  Press the button and follow the instructions above.

  Click on the box to the left of the entry labelled "Monitor" so that there is a check mark in the box. Press the OK button to close the add-in manager window.

  A new option called Monitor Workbooks will now be available on the main menu.

Create a new, blank, workbook and save it into the folder containing the workbooks you want to report statistics about.

From the Monitor Workbooks menu select the Remove sheets option.

Select the "Run update" option and the add-in will begin to create a report.

• How long will it take

  The length of time it takes to generate the report will depend upon the number of sheets and cells within the work book. On a 1.6GHz Celeron processor you can expect it take 15 seconds to process a file containing 1 million cells.

• Passwords

  You will be prompted to enter the password when protected files are processed. If you don't know the password, press Cancel on the password prompt window and the add-in will proceed to the next workbook.

The report produces overview statistics for each sheet of every workbook in a selected folder hierarchy

The addin will include a section in the report for each sheet of every workbook in the folder hierarchy you choose. Statistics are generated for each sheet and aggregated to create workbook summary statistics.

If the workbooks being analyzed contain links then a diagram will be generated

The diagram is generated using a simplified Sugiyama-like layout algorithm and drawn using Excel shapes and connectors.

Green nodes indicate spreadsheets containing links within the set of spreadsheet being reviewed. Yellow nodes represent spreadsheets that are referenced but do not contain links. Red nodes are references to spreadsheets outside the range of documents covered by the report.

Though the default layout generated by the add-in should meet most requirements, because the dependency diagram is generated using Excel shapes, you can edit the diagram to create a clearer presentation based on your knowledge of the relatationship or just to explore the relatationships.