Check usage of external sources
Creating a simple overview of all external sources is oddly challenging in Excel, but sometimes it’s necessary, especially when you need to share those sources or when an update isn’t possible. PerfectXL provides a visualization of all external sources, along with a list of formulas, defined names, and rules that reference them.
- Download the sample file to follow the steps.
- Already working with PerfectXL? Then jump to the solution with PerfectXL.
Step-by-step without PerfectXL
Reasons to track external sources…
Compiling a list of external sources turns out to be a particularly time-consuming task. Especially when you’re about to send a model to a client and you know they don’t have access to the same resources as you, or when your accountant asks for a list of all source files, including the locations where these sources are referenced, and you don’t exactly have the time…
The most common reason to start searching for external sources, however, is this very annoying pop-up:
Step 1: Workbook Links
You start searching for external sources by going to “Data” -> “Workbook Links”. An overview of all the external sources used in your file will appear in a sidebar on the right of your screen. In our sample file, two sources are found: Invoices.xlsx and Operations.xlsx, but it doesn’t show us where…
Step 2a: Queries & Connections
Next, we will have a look at “Queries & Connections” (under “Data” in the menu bar). In our example, on the right side of the screen, under “Queries”, we find “Table1” and “Document”.
Step 2b: Details of a query
Hover your mouse over one of the queries to bring up a pop-up with details. Click on the query to jump to the exact location in your model.
Step 2c: Open PowerQuery
Click the “Edit” button at the bottom of the pop-up to open PowerQuery.
Step 2d: Find external sources
Click on “Source” in the “Applied steps” menu on the right side of the PowerQuery window to retrieve the source. The formula bar will then reveal both the source and its location: C:\Users\mateo\Documents\Expenses.xlsx
To identify all sources, we need to repeat this step for each query in our document.
Conclusion
Our example model consists of only 6 tabs and a few sources, but you can imagine that the process described above is extremely time-consuming in the case of a complex model.
Step-by-step with PerfectXL
Step 1: Open the model in PerfectXL Explore
Open PerfectXL Explore and drag the respective model into the Explore box, or open it via your File Explorer.
Step 2: Examine the visualization
The analysis is performed immediately, so the next thing you will see is a visualization of the entire model.
Step 3a: Overview of external sources
Click on the “External Sources” tab in the menu on the left. Here you will find all the links and connections that were found in your model.
Step 3b: Details about the reference to the external source
You can click on the arrow in the “References” column on the right to learn more about the location of the link in your model. The orange icon next to it will take you to the relevant location with just one click.
Step 4: Create export
Documenting all external sources has now become a piece of cake. Click on the “Export” button at the top right of the screen to download a comprehensive report with all characteristics of the model in Excel. On the “External Sources” tab, you will find the overview of external sources and direct links to their locations in your model. Click here to download the export of our sample file report.
Conclusion
As you can see, PerfectXL Explore creates a clear and transferable overview of all external sources in your model within a few clicks!