<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Mappit! - a free Excel model auditing addin

Published on
34,262 Points
17,462 Views
18 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick

Introduction

Good Excel modelling practice

Most organisations have corporate guidelines for modelling, especially for financial models used for important business decisions.  A standard set of guidelines would include most, if not all of the list below:
 
use introduction sheets

formulas to be kept as simple as possible

clear ownership structure for the model

a documented system for archiving model versions, controlling model access, model updates

a consistent layout across sheets (same years in the same column etc)

colour coding system to distinguish between inputs, calculations and algorithm changes

full referencing of the source and date for key assumptions

log of model - and if applicable value - changes

use of data validation and conditional formatting on data inputs to control and/or warn users

error checks

row totals

graphing of key input assumption and output metrics

avoid using dynamic ranges (i.e., the OFFSET function) that can't be easily audited

avoid array formulas

keep VBA to a minimum

build shadow models to test the main model

peer review of model inputs
 

Common model errors


Even models built by skilled practitioners adhering to good design principles, can and frequently do contain errors. Two common sources of model errors are:
1) Design mistakes in the initial model build, for example
 
not copying formulae completely over rows,

absolute/relative formulae inconsistencies
2) User error or "enhancements", for example

paste special values over a block of formulae,

adding an "adjustment factor" to a cell than forgetting about it,

Mistakenly inserting links to external workbooks
 

Using Tools to assist the detection of errors

There are a number of professional products available that offer a suite of audit utilities to identify potential model errors such as these. See here for a list of these tools.

Having used several of these programs over the years I found the mapping utility to be the one key application that I used repeatedly.  But I thought that there was considerable room for improvement on the scope, speed and presentation of the mapped output, especially given some of these tools need to make changes to the source files to work properly. This was the driver for me to create my own - and free - mapping addin, Mappit!.
 

The Mappit interface

pic71) Format Formulas

All formulas in the selected sheets are mapped to a corresponding new workbook as per the diagram below. Mappit! uses variant arrays to compare formulae as quickly as possible.pic4
Use conditional formatting on output map
This option applies conditional formatting to the map workbook to make any changes in the formula patterns easier to identify.Pic11
Output sample
pic22) Additional Cell Formatting
Five options are available under Additional Cell Formatting:

Formulae that contains constants

Refers to other Workbooks

Refers to other Worksheets

Logicals (Formulae & Constants)

Errors (Formulae & Constants)
The first three options will simply change the colour - but not the content - of the mapped cells. Please note these three options work in descending priority, if all three are selected then the Formulae that contains contants takes precedence, then Refers to other Workbooks, lastly Refers to other Worksheets
The last two options will change the mapped cell text to "ERR" or "T/F" respectively. Note that the colours of these mapped cells will also be updated by the first 3 formatting options if applicable.
pic8While I have avoided time consuming looping through ranges on these options, the processing here is more time consuming, especially the Refers to other Worksheets option. So users may wish to turn these options off if they are non-critical.

The "Formulae that contains constants" uses regular expressions (see Patrick Matthews excellent regexp in VBA article) to parse formula cells that contain both cell references, and a numeric constant (either as number, or as a number with a "%").  Unlike some other mapping tools Mappit! will not flag "false" formulas which contain numeric constants if they do not have a valid mathematical operator.
For example
Mappit! will not flag
=IF(A1>10,1,100)
Mappit! will flag
=IF(A1>10,1,100)+6.81

Output sample pic13) Format Constants
These options will retain the contents of the numeric and text cells in the mapped output and apply user selected colour formats.
pic5
4)  Detailed inconsistent cell report
This option produces a one sheet summary listing all the unique formulas, by location and formula value.
pic10pic3

Mappit! output sheets

Two summary sheets are produced by default
The first is a hyperlinked sheet summary listing the key outputs from the mapped worksheets
This sheet may prove handy in flagging potential issues such as:
Inconsistencies between supposedly identical sheets in terms of links to external workbooks

The presence of hidden cells

The number of "unique" cells detected during the Mappit! run
pic6
The second sheet provides a legend map, plus the selected users settings - a list of the options that were run and the formatting colours.
pic9  
 

Mappit! floating navigator bar

I borrowed this idea from John Walkenbach's excellent PUP addin
The floating userform provides an easy to user interface to switch between the source workbook and the mapped output. It can be closed manually, and it will also be closed automatically if the user closes the map file and has macros enabled.
 

Settings

Save Settings will store the current option and colour settings in the registry
Restore Settings will delete any user saved options
 

Updates

I am certainly open to suggestions for improvements, the inevitable bug fixes and updates.
 

Credits

A huge thank you to Jerry Paladino (aka ProdOps) for his in depth testing and suggestions, especially in making the product more user friendly.

Download the Mappit! addin here          
Mappit-1.11a.zip
 

Associated Article

Readers may find my article on Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell conten a useful adjunct to this article.

 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found the article helpful please click on the "YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.

If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.

If you liked this article and want to see more from this author, please click here.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 

Free addins that I recommend for model auditing:


FindLink by Bill Manville

Name Manager by JKP and Charles Williams

ASAP Utilities by Bastien Mensink
You can download both FindLink and Name Manager from The Excel MVP Page
ASAP utilities is available at http://www.asap-utilities.com/
18
Comment
Author:Dave
  • 17
  • 2
  • 2
  • +6
27 Comments
LVL 50

Author Comment

by:Dave
Updated 17 March

- Glitch with Legend On/Off reporting fixed
- removed the "=" sign from the test for cells with both formula references and hardcoded numbers, ie
=IF(A1=10,1,100)
will no longer be flagged
- ran Ron Bovey's excellent Code Cleaner utility over Mappit! to compress the addin size

0
LVL 50

Author Comment

by:Dave
Updated 24 March to V1.02

- fix the error caused when populating the hyperlinked summary sheet index that occurs the sheets have numeric names
- Some cells that Excel sees as single cell standalone ranges (even though they do have neighbouring cells) were incorrectly being formatted as "*"  rather than "<", "^" or "+""

thanks Doug for the feedback

Regards

Dave


<File removed. Please see main Article>
0
LVL 55

Expert Comment

by:Mark Wills
Just updated the current version.

Looks good.

Anytime you want a "victim" to help test, wouldnt mind seeing my name added to the list of credits *laughing*.


Cheers,
Mark Wills

0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

LVL 50

Author Comment

by:Dave
I've made two updates to the V1.03 of Mappit!

I have added functionality to map hidden cells using my code from Adding a Subtract Range method alongside Union & Intersect. Note that formatting hidden cells  takes the lowest priority of the four colouring options

Following feedback from Shikha I have added a checkbox option (see picture) that will parse the numeric constant from
=IF(A1=10,1,100).


Regards

Dave Changes

<File removed. Please see main Article>
0
LVL 50

Author Comment

by:Dave
V1.04

Looks to paste cells as values in the rport  if the automatic de-linking is an issue on large sheets
Fixed an issue where the addin looks to close more than once when there are no open Workbooks to analyse

Cheers

Dave

<File removed. Please see main Article>
0
LVL 16

Expert Comment

by:Jerry Paladino
Dave,

I ran a workbook through Mappit! and noticed "ERR" cells that are in Hidden Rows or Columns do not display with the red "ERR" in the cell but contain the text equivalent of the error value like #DIV/0.  Since the column widths are small they just display as "#" and not an indication of the error condition.  Maybe something for the next release.  ;-)

 Mappit ERR Cells
Hope you are doing well…
Jerry
0
LVL 50

Author Comment

by:Dave
Jerry,

Good pick-up

In V1.03 the new functionality for hidden cells inadvertently took out the earlier formatting (the ERR was entered, then overwritten).

I have made several updates in V1.05 (re-attached in the original article). V1.05 is the last version that I will release publicly with any reporting enhancements, although I will still support any bugs or cosmetic changes.

List of updates:

Fixed the hidden cells issues raised by Jerry
Occasionally a lone cell to the left of a contiguous range is processed after the larger bank of formulae. This was at times leading to two indentical formulae both being formatted as unique, I have now added a check for this
Added a report that breaks down the internal worksheet links from a total in V1.04, to a worksheet by worksheet report in V1.05. I added this functionality having seen it in SpreadSheet Advantage, as Mappit! had this data readily available. So it's only fair that in return I plug SpreadSheet Advantage as an auditing addin worth considering :)

Regards
Dave
0
LVL 50

Author Comment

by:Dave
And V1.06 re-fixes the error  reintroduced in v1.05 where Mappit! looks to close more than once when there are no open Workbooks to analyse

Cheers

Dave
0
LVL 16

Expert Comment

by:Jerry Paladino
Dave,

Thank you for the Mappit! update...   Wish I could vote YES again for all the extra work you have put into this.

Jerry
0
 

Administrative Comment

by:Kevin Cross
Dave:

The community definitely appreciates your effort in not only writing this, but keeping it up-to-date with latest iterations of code. Thanks again and on behalf of the page editors, congratulations!

You Article has achieved Editor's Choice.

Best regards,

mwvisa1
EE Page Editor
0
LVL 50

Author Comment

by:Dave
v1.07

now handles a the check for a non-existent unique cell cell when working with the right-most column
some cells were being skipped by the routine that formats links to other internal sheets, addressed (thx Rory)

Regards

Dave
0
LVL 50

Author Comment

by:Dave
After feedback from Mike I have updated Mappit! to V1.08


The hardcoded cell count on the summary sheet was not being reported
The unique formula list - if selected - was being added to the same output sheet containing the sheet mapping
Added a version number to the Mappit! menu

Regards

Dave
0
LVL 50

Author Comment

by:Dave
Mike noted that the internal worksheet link summary in Mappit! v1.08 had not properly handled a sheet name of
"X&Y"
when looking to map the internal links, these links had not been reported

Excel adds quotes to this sheet name as it contains a non numerical/alphabetic character, however Mappit! 1.08 was testing only for the presence of spaces for a decision as to whether to serach for sheet names with or without quotes

I have now rectified this using a basic regular expression as below
[^w]
pattern matches any non numberic/alphabetic character

so
X&Y
will now be looked for as
'X&Y'

Please note that Excel also converts
'
from sheet names into
''
for formulae

which is why the VBA below contains a replace

Regards

Dave





Set RegExC = CreateObject("vbscript.regexp")
        RegExC.Pattern = "[^/w]"
            For Each wsSource In gWbOriginal.Worksheets
                sName = IIf(RegExC.Test(wsSource.Name), "'" & Replace(wsSource.Name, "'", "''") & "'!", Replace(wsSource.Name, "'", "''") & "!")

Open in new window

0
LVL 50

Author Comment

by:Dave
.... the regex pattern was mis-typed

it should of course have been as below

Dave
RegExC.Pattern = "[^\w]"

Open in new window

0
LVL 50

Author Comment

by:Dave
Addin reloaded as the prior 1.09 version was actually 1.08

Cheers

Dave
0
LVL 10

Expert Comment

by:broro183
hi Dave,

As recently mentioned on www.TheCodeCage.com...
I don't think I have found any bugs (although I seem to have a different interpretation of "unique formulae"*). However, I do have a few questions/development suggestions based on a test run using a simple example file (+ my past use of different mapping addins such as John Mote's "Formula Mapper")...

- The text which appears at the base of the Addins dialogbox currently states "EE version, audit, mapping tool", can the version number be included in the text?
This would help as part of a nice easy check before loading the addin, esp for people like me who don't have our addins loaded all the time. (I just found out yesterday that my copy of Name Manager was 9 "builds" out of date!)

- What is the difference between "initial formula" & "inconsistent formula"?
(see cells H4:I4 of the attached file)

- In terms of readability, I think it would be useful to make the column widths for each of the Mapped! output sheets the same as that of the original sheets. For example, something like this air code...
    InputWs.RealUsedRng.Copy
    OutputWs.Range("a1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
'I did try to use a variant array on the column widths but I couldn't get it to work for multiple columns at a time & since I was too lazy to loop through each column so I went for the above easy (& maybe slower?) option!

Open in new window


- Could there be an option to check the "activewindow.freezepanes" status & apply matching settings ([ctrl + home] cells) on the Mappit! output sheets?
I realise it may/would slow the performance down, since Freezepanes is a member of the Windows collection - unless there are some tricks to avoid activating each sheet when setting the property?

- *Could the addin give the option to identify unique formulae using either the R1C1 reference style or using the A1 style?
This could lead to a big reduction in the number of entries on the "Mappit! - Unique Cell Sheet" sheet. The test file I've uploaded states 4 "unique formulae" (I assume, & it is correct, based on R1C1 style) in cell C4 of the Summary sheet but lists 11 strings (A1 style) on the Mappit! - Unique Cell Sheet.

- Can the floating navigation bar give the option of retaining focus or immediately passing focus back to the activated sheet after being clicked?

- The floating navigation bar currently seems to synchronise the cell selection when changing from the Mappit! Output file to the original workbook. Could there be an option to allow synchronisation when going from the original to the output file?

Rob
use-of-Mappit.xlsm
0
LVL 50

Author Comment

by:Dave
Rob,

Thanks very much for your detailed feedback

>The text which appears at the base of the Addins dialogbox currently states "EE version, audit, mapping tool", can the version number be included in the text?
Yep, will do in the next update
Currently I put this in the addin menu as disabled item

>- What is the difference between "initial formula" & "inconsistent formula"?
An initial formula is at the top left of a formula range, so by definition it has no cells to the left/top that it may be inconsistent with (H4 in you example). Whereas an inconsistent cell has a different formula than the cell to the left/top

>Could the addin give the option to identify unique formulae using either the R1C1 reference style or using the A1 style?
The addin uses the R1C1 values internally for the detection of unique formulae.

>lists 11 strings (A1 style) on the Mappit! - Unique Cell Shee
I will revisit your sample, you are correct that the "unique formula" output does appear inconsistent with the summary sheet (which looks right)

>In terms of readability, I think it would be useful to make the column widths for each of the Mapped! output sheets the same as that of the original sheet
It is an interesting trade-off. I went with the narrow format to get more cells in the viewable screen, this was based on my personal preference, plus I liked the way that Oak (from operis) - which I think has the best look of the commercial products - produces it narrow ouput

>- Can the floating navigation bar give the option of retaining focus or immediately passing focus back to the activated sheet after being clicked?
Yes, will do in next version

>The floating navigation bar currently seems to synchronise the cell selection when changing from the Mappit! Output file to the original workbook
I'm not sure what you mean by this, can you please elaborate?

Thanks again

Dave

 
0
LVL 10

Expert Comment

by:broro183
hi Dave,


>>The floating navigation bar currently seems to synchronise the cell selection when changing from the Mappit! Output file to the original workbook. Could there be an option to allow synchronisation when going from the original to the output file?

Here's an attempt at clarifying my question...

If the Output file is active with cell Q10 of Sheet1 selected, then we use [alt + tab] to change back to the original workbook (ignoring the floating navigation bar), we can see that cell H5 of Sheet1 is selected. Now, if we use [alt + tab] to go back to the Output file, cell Q10 of Sheet1 is still selected. Press [Go to the original workbook button] while the Output file is active.
The screen will change to the original workbook and the activecell will become cell Q10 of sheet1.

However, if we flip the above on its head...

If the Output file is active with cell Q10 of Sheet1 selected, then we use [alt + tab] to change back to the original workbook (ignoring the floating navigation bar), we can see that cell H5 of Sheet1 is selected. This time, stay in the original workbook & press [Return to map].
The screen will change to the Output workbook and the selected cell will stay as cell Q10 of sheet1 ie it won't change/synchronise to cell H5.
My question is, can the code be changed so that, if we press [Return to map] while the original workbook is active, the activecell of the Output file becomes cell H5?
(not necessarily all the time, but perhaps via an optional setting)

btw, I just spotted that the tooltip (?) for the "Look for any constants in formulae" checkbox has three typo's ;-)

Rob
0
LVL 50

Author Comment

by:Dave
Mappit! updated V10 taking into account Rob's comments

Updates to V1.09

-    Inconsistent cell list is now the inconsistent cells only not all the individual uniques (the summary report shows total uniques)
 -   Added version control to addin description
 -   Implemented your idea above to set the map cell equivalent to the actual file address when using the "return" button
 -   Fixed those fat fingered typos on the "Look for any constants in formulae :)

Cheers
0
LVL 50

Author Comment

by:Dave
Version 1.11 added

This version
- colour codes sheets that are hidden (yellow) or very hidden (red) in the sheet relationship summary
- lists whether the sheet is hidden or very hidden in the Mappit! Summary SHeet

 Mappit-1.11.zip
 Pic2 Pic2
Cheers

Dave
0
LVL 50

Author Comment

by:Dave
0
LVL 50

Author Comment

by:Dave
V1.11a uploaded

Minor tweak to ensure that hidden and/or very hidden sheets will still be flagged even if these sheets are empty (V1.11 ignored empty sheets)

Cheers

Dave
0

Expert Comment

by:ramridge2
Hi Dave -
Just came across your add-in and have downloaded it. I am in the process of evaluating a few commercial products for purchase. On top of my list are Spreadsheet Professional, Spreadsheet Detective. I have evaluated Space and discounted it. Too many bugs and ended up by corrupting my excel file!
On first glance and reading the comments above I am impressed. But I will give it a spin in the next few weeks and let you have feedback.
The background is that I want one or two tools in my armory to audit models developed by actuaries in insurance companies.
Ram
0
LVL 50

Author Comment

by:Dave
Thanks Ram

I will be interested in what you think

On the commercial front I like SpreadSheet Advantage, and I thought Oak's demo version looked very good

Regards

Dave
0
LVL 1

Expert Comment

by:Stephen Byrom
Hi,
I downloaded your latest Mappit, but when I try to run it, I get "Object variable not set (Error 91)"
I did unzip it and browsed to the add-in to install it, but no luck.
I'm using MS Excel 2010.
Thanks for your work
0

Expert Comment

by:Pascal Fabiani
Hi,

I downloaded your latest file Mappit! I always get the error run-time error '5'. What can I do to fix this.

Thanks,

Pascal
0

Expert Comment

by:James Movundlela
Hi

I am currently using the Microsoft 365 and i have enabled the plug in but my biggest problem is that i cannot seem to Locate in on my Excel

Kind Regards
1

Featured Post

JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Join & Write a Comment

This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month