How to check for duplicate row labels

Inconsistent labelling impedes model clarity. In a big model, it can be easy to lose track of what we have called things for duplicates to creep in.


This is especially true when multiple people are working on the same model. It's helpful to occasionally check the complete list of labels in your model.

This is impossible to do manually.

The Productivity Pack includes a macro that will list all the unique labels in your model. This will allow you to check for inconsistencies and duplicates. This is not something that you will need to do often. However, it is helpful when reviewing the model and preparing it for release.


Download the sample model used for this section:


Run the label checking macro

Ctrl+Shift+6, then hit L to produce the list of labels

The macro will create a new workbook, containing a sheet which every row label used in the model. A sample COUNTIF function shows the number of times the row label occurs, and the sheet is automatically filtered to show labels that occur two or more times:

From this list, we can see that there are duplicates occurring frequently on the Annual and Quarterly financial statements. I'd consider this an acceptable exception to the "no duplicates" rule.

We'd want therefore to pay attention to the duplicates on the other sheets.

In particular, I can see that there is a duplicate of "Cash received from invoices" on the Ops sheet and "Net cashflow to/from equity" on the Metrics sheet. These look like prime examples of two calculations where some time passed between their creation, and the modeller (me in this case!) just "forgot" that the label was already being used. This is very common and easy to do; which is why we need a software check to pick up these occurrences.  

The links in Column C of the row label report can be used to navigate directly to the location of the label so that we can investigate the label in situ, and decide how to fix the row duplication.

For example with the equity cash flow, I have changed one of the labels to make it clear that this is the cashflow for the incoming investor:

In the case of the duplicate "Cash received from invoices" label, this is due to me including two different versions of the accounts receivable calculation.

I have therefore changed the label on one to make it clearer that this is not being used in the model:

Once you have tidied up the labels in your model, delete the report. You can run it again anytime.

Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.
jamie@example.com
Subscribe