How to Make a Fake Excel External Reference

Overview

As a best practice, we’re often taught not to use external references in Excel.  They can easily get out of sync, stale, and break altogether.  While there are times when they can be helpful, it’s generally best to keep everything self-contained within the workbook.  That includes using Power Query to connect to data in external files or sources (Excel and beyond).

But that doesn’t mean we can’t use external links to our advantage.  When you break external links, you’re left with the resulting values almost instantly, and there are times where that’s a major benefit.

In this article, we’ll look at how you can create fake external references, and some scenarios where you might want to use them so that you can quickly break them after they’ve been calculated.  Make sure to use the box below to download an example file that shows this approach in action!

Why use fake external links

You’ve probably built or used a file that had a lot of formulas linking between the sheets.  Those formulas might slow your file down or depend on a huge amount of source data to work.  The formulas could also be powered by a custom add-in that not everyone has access to, or that requires authentication to run the calculations.  While you probably want to keep the subtotals and other in-sheet formulas, those other formulas that refer to source data or other sheets might be okay to get rid of – at least in a copy of your file.

Here’s why that could be helpful:

• It can dramatically speed up the formula recalculation.
• It can make the fie significantly smaller by allowing you to remove source data.
• It can allow you to share the file with other people who don’t have access to the formula functions.
• It allows you to memorialize the values as of a point in time.

You might be thinking “why not just copy and paste values?”  While that’s an option, you probably don’t want to remove all the formulas – those subtotals, cross totals, and other in-sheet formulas.  It also might take a while to copy and paste several different ranges of cells.  Breaking external links is so much quicker!

Fake external link use cases

Here are two key use cases where fake external links have really helped me over the years (you may be able to think of others!):

Custom functions

I’ve used several financial software products that had a helpful Excel add-in.  Often they include specialized formulas to retrieve data points with arguments to specify accounts, locations, dates, etc.  While really helpful for building out reports, they can take a long time to calculate.  They also may not be installed on other users’ computers, resulting in the #NAME? error.  And lastly, they often require you to log in to see the results each time you open the file (like in this screenshot 🖼️).

Large source data volume

In other scenarios, I had a huge source data set in a separate sheet that formulas would reference.  We’re talking 200k+ rows of data.  It took a while to recalculate (60+ seconds) and the file was often too big to send to other users.

In both cases, being able to quickly convert those formulas – the custom ones and the ones referring to other sheets – into the resulting values proved super-helpful.  It sped the recalculation up dramatically and allowed me to delete the source data so I could share the files.

How to use fake external links

To make formulas easy to break, they need to reference an external file.  But that doesn’t mean the external file has to actually exist 😉!  When I first used this trick, I thought the file had to exist, so I made a file, entered a value of 1 in cell A1 of the first sheet, and referenced it, fully intending to break it later on.  That approach still works, but if you accidentally move or delete that file, the links can get out of whack, and if nothing else, create that annoying browse for file popup.

To avoid this altogether, wrap the external reference in IFERROR and return a 1 when it’s not found.  That way it doesn’t really matter what you name the file or where you save it.  It’s a good idea to make sure that file actually doesn’t exist in case it contains some value other than 1.

This is the exact fake external reference I usually added to my files to make this technique work (see it in a screenshot 🖼️):

*IFERROR([Book9999.xlsx]Sheet1!$A$1,1)

While much more rare, you may have a formula that results in a text string, not a number. If so, just change your reference a little bit. Make sure to use concatenation instead of multiplication and a blank string instead of a 1, like this formula suffix:

&IFERROR([Book9999.xlsx]Sheet1!$A$2,"")

Key things to remember from this reference:

• Make sure to multiply (ultimately you’re multiplying by 1 so the result remains the same as without it).
• Use IFERROR to handle the fake file reference.
• Make sure to use an absolute reference if the file will actually exist in case you copy the formula
• In the second IFERROR parameter, make sure to use a 1.

Important: when you break the links, make sure to save a COPY of your file so you don’t lose that hard work building out the formulas.  Chances are you’ll want to use them again!  See the macros section below for a helpful macro called Break Links Save PV that will save a “paste values” copy and break links automatically for you!

When you’re ready to break those links, go to the Data ribbon and click Workbook links. It will open up a pane on the right (or a popup in older Excel versions). Click the ellipsis button next to your fake file and click break links, then confirm at the prompt. See this screenshot 🖼️ of the items to click and this screenshot 🖼️ of the before/after with/without links.

Macros that help

Once I discovered this method, I went back and applied it to a few other files.  Because different formulas were spread throughout the file, I made a couple of macros to add that suffix instead of typing it out (or even copying and pasting it!).  Check these out in the support pages referenced below, or see them in action in the video below:

• Add Formula Suffix – this one prompts you for a suffix (like the fake reference above) and lets you set a default suffix so you don’t even need to type it out each time.
• Bulk Add Formula Suffixes – this one lets you apply a formula suffix across multiple ranges and even sheets.
• Break Links Save PV – this one will break all the external links in the active file and then save a copy of the file with “_PV” in the file name (for paste values).

In both cases, the warnings are suppressed so you don’t have to browse for a file that doesn’t exist, and the formulas are updated extremely quickly.

Video

Summary

As you can see, this is a clever approach that can come in handy in certain scenarios when working with a lot of source data, or bespoke functions that not everyone has access to.  It can also help you share just certain sheets within your file.

If you take this approach, make sure to save a copy of the linked file with formulas, so you don’t have to rebuild them and lose all that hard work.  It’s also worthwhile using a macro to quickly add those fake references.  To see this in action, make sure to download the example file using the box above.

What approaches have you found useful when working with a lot of source data or custom add-in functions? Let us know in the comments below!