Visual Basic Skills Assessment Try our free on-line Assessment

XL - Excellence



Overview

XL - Excellence is a spreadsheet Add-In designed to aid in developing high-quality spreadsheets. The Add-In generates reports on nominated workbooks which give insights on.

  • Formulae that are too complex
  • Formulee that are inconsistent
  • Formulae that return errors
  • Areas that have poor separation between formulae and constants

Downloads

Download the Add-In and its documentation by clicking on a link below

Version 1.0.4 of the Add-In Spreadsheet guide
Version 1.0.4 of the documentation Spreadsheet guide

Installation

After downloadning start Excel. Choose File | Options | Add-Ins. A form will appear. Press the Go... button on the lower part of the form. Press the Browse... button on the dialog that appears. Navigate to where the Add-In is located and press Ok and then Ok again.

Location of menu items

Once installed the Add-In is usable from either of two menus. The first menu is in the ADD-INS section of Excel’s main ribbon. [The main ribbon is the one that contains the FILE, HOME and other ribbons.] Choose ADD-INS and then make your selection from the xl-excellence menu that appears.

ADD-INS menu

The second, alternate, menu is available from the pop-up menu that appears when you right-click on a spreadsheet.

Pop-up menu

Function of menu items

Make report

This is the main menu item and will generate a report on the currently active workbook. The original workbook is not altered and the report appears in a new, separate workbook. The report shows the following information.

  • A list of cells that return errors
  • A list of named ranges that are in error
  • A list of spreadsheet tabs that are on a nominated required list but which are missing
  • A list of external workbooks that are referred to
  • A list of hyperlinks to each tab in the original workbook. Those hyperlinks give more information on the structure and quality of the individual tabs.
Sample report

Heatmap - formula length

Before selecting this menu item click on a hyperlink in the report.

Hyperlink

That will take you to a map showing which cells contain formulas and which contain constants.

formula vs constant

Now click on the Heatmap - formula length menu item. The colours of the map will change.

heatmap formula length

Colours will be green, orange or red depending on the lengths of the corresponding formulae in the spreadsheet being reported on. Formuals less than 100 characters in length are shown in green, more than 200 in red and between 100 and 200 in orange. [The "100" and "200" thresholds can be changed by using the options menu item.]

Heatmap - formula consistency

The colours of the map will change to reflect the consistency (or otherwise) of the formulas in the spreadsheet being reported on.

heatmap formula consistency

Large blocks of the same colour will indicate formulas are consistent. If there are large numbers of small blocks with different colours that indicates formulas have little consistency.

Jump to Source

Use this menu item to jump to the corresponding cell in the original workbook. This lets you inspect formulae that might be reported as being too long or inconsistent.

Options

Use this menu item to set options. Options that can be set are listed next.

Required tabs

When a report is run a check is made to see whether required tabs are found in the workbook being reported on. Missing tabs are listed on the report. The required tabs option lets you set the names of tabs that should appear in all workbooks that will be reported on.

options required tabs

Formula length

This option lets you set thresholds for colors that are reported by the Heatmap - formula length menu choice.

options formula length

About

Use this menu item to shows the version number and installation location. Click on the link in the form for online help.