Check defined names

Step 1: Open the name manager in Excel

It’s not difficult to find out which names are present in your model. You click on ‘Name Manager’ under ‘Formulas’ in Excel’s main menu, and then a pop-up appears showing an overview of all names and the cells they refer to.

Step 2: Show Formulas

Now we know that our model has seven defined names, with the last one merely being the title of a chart on our ‘Summary’ tab. Since a title can be directly inserted, we can easily delete that one. However, we don’t know if and where the remaining six names are used, so we don’t know if we can delete them. That will require some investigation!

By choosing the ‘Show Formulas’ option under ‘Formulas’ in Excel’s main menu, we make formulas visible in cells that normally only display values. References to defined names become visible this way too.

In our example file, we only find the name “Exp_est” on the “Summary” tab and the name “Opt_est” on the “Expenses” tab, but with the naked eye, we can’t find any more.

Can we now conclude that the other four names are not used in this model? Let’s see what happens if we delete them…

Step 3: Delete unused defined names

Before deleting anything, you start, of course, by creating a backup copy of your file. If you are unsure, it’s not smart to work with the original.

Then go to the “Summary” tab in your backup copy and carefully observe the current results.

Now, we call up the Name Manager again (Formulas -> Name Manager) to delete all names that are suspected to be unused. Pay close attention to any changes in the results on the dashboard with each deletion.

After deleting the name “Opt_act,” the difference between income and expenses changes from -2,980 to 32,550. So, this name was used somewhere after all!

The difference seems to be caused by the value in cell C5: Actual Expenses.

The backup copy can be discarded because we threw away names that were apparently used. Create a new backup copy of the original to search for the hidden defined name.

Step 4: Search for hidden names

Upon selecting cell C5, a dropdown emerges, displaying only one list item: 35,530. This particular item is selected, and in the formula bar, we observe the reference =Operating!C24, so this selected list item points to a cell on the “Operating” tab.

When we go to the “Operating” tab and select the respective cell, we only find a SUM (of the data in the column). In the name box (top-left of the formula bar), however, the defined name we were looking for appears: “Opt_act”. How does this name affect cell C5 on the “Expenses” tab, where it only refers to the location of the SUM?

Step 5: Research and improve

Let’s return to the “Expenses” tab and select cell C5. Then, go to “Data Validation” under “Data” in the Excel menu. Here we discover that the drop-down list indeed contains the name “Opt_act”.

In case of this model, we can conclude that there is no use for the entire list in cell C5.

Click “Clear all” in the Data Validation pop-up, to remove the list from cell C5.

The value in cell C5 is now replaced by a fixed value:

That’s not our intention, so we manually replace the fixed value with a reference to cell Operating!C24.

Step 6: Check the results

If we now choose the “Show Formulas” option (under “Formulas” in the main menu), on the “Expenses” tab, we see that the names “Opt_est” and “Opt_act” are neatly next to each other in cells B5 and C5. The use of these names is now transparent.

Now we can trace three of the six remaining names in our model. We will give away, that when we remove the last three, there won’t be any consequences, because they are indeed not used anywhere. But you can imagine that this process is extremely time-consuming in more extensive models!