In my last Excel post, I talked about using Excel to bring together data from multiple sources. I demonstrated the concept with Microsoft SQL Server and Microsoft Access, but it works with many other platforms.
In this post, I'd like to talk a little bit about the various data sources that this method will work with, how to set up your connections, and setting them up to automatically refresh your data.
First, let's revisit the topic of linking vs. importing. Many people are familiar with the process of importing a CSV or TXT file into Excel. You can also link Excel to your data instead, providing you with a real-time data set that you can use to create reports or dashboards.
The beauty of this solution is that it allows you to bring together data from different databases.
What Data Can Be Linked to Excel?
Excel connects to data sources via something called "ODBC", which stands for Open DataBase Connectivity. Using this technology, you can link Excel to any data source that provides an appropriate ODBC driver. Good news: you'll find that many common data sources provide an ODBC driver.
So, how do you know if there's an ODBC driver available for your software package? The best place to start is by understanding which database technology sits behind the software. Ask the vendor or Google where database platform houses the data for the app. Some common answers are: Microsoft SQL Server, Oracle, MySQL, PostGres, to name just a few.
Once you find the answer to this question, your next questions is weather or not there is an ODBC driver available for that platform. In the case of the four I listed above, there is. In most cases, it's a free download.
Note: different drivers have different capabilities. Most allow you to link your data to Excel, but it's not a given. You'll have to check the capabilities of your driver to be sure.
Establishing the Connection
Now that you've discovered a driver for your database, you'll download and install that driver on your computer.
Note: often times the driver is automatically installed when you install your software package, so don't assume you need to do this. When in doubt, skip ahead to the part where you try to link the data. You'll quickly see if there is already a driver listed. If not, you'll have to get one.
The steps to download and install your driver are very similar to any other download/install you've performed. There's usually a wizard that walks you through the steps.
The weird thing about a driver is that once you've got it installed, there's no program to fire up, so that you can see if its working properly. These drivers are not executable. Instead, go into Excel and try setting up a link using the new driver. If that works, you're driver has installed correctly.
I've already covered a couple of variations on setting up the link in Excel, which you can find here:
I haven't laid out instructions for every data source, but if you read those two posts, you'll notice that the process is very similar for both. You'll find that to be true for other ODBC sources as well.
Refreshing the Data
Now that you've got links set up, you'll need to decide when the data in the Excel sheet refreshes. You've got three main choices (any other options, if you're willing to dive into VBA coding):
Refresh on demand
Refresh at regular intervals (ex: every 5 minutes)
Refresh every time the Excel file is opened.
The appropriate option depends on how you'll use the data.
Refresh on demand if the user is happy working with a static data set most of the time. Possibly they are always running reports on last month's data, so they don't need to include anything newer than that. This is a good option if you're working with a large dataset that takes a long time to refresh.
Refresh at regular intervals. This one should be used for Excel applications that are used frequently, and always need up-to-date information. I have a shipping dashboard, for example, that allows to quickly be aware when an order has been approved for shipping. Users keep it open all day long, and I've got it set up to refresh every 10 minutes.
Refresh when opening the file. This is a great option for running daily or weekly reports. In this scenario, the users opens the file, runs the report, prints or exports their results, and then closes the file. There's no need to refresh on interval, because it's closed most of the time. But when they do open it, it'll always be current.
Whichever option you choose, here's how you can set that up. Once you've established the link to your data, you'll need to view its properties. Do that by clicking your "Data" ribbon, and then the "Queries and Connections" button:
See the panel that popped open on the right? It shows all of the connections you've got in this Excel file. Right-click the appropriate connections, and click "Properties" to open the Properties pane:
As you can see, the refresh options are listed here.
The "Enable background refresh" option allows the user to use the Excel worksheet while a refresh is taking place. Be careful with this option. It's nice that the user can do things while the data is refreshing, but if they are too quick to run their report or look at their dashboard, they may be viewing out-of-date data. If you un-check this option, the user will be forced to wait until the refresh is complete before they can do anything in the file.
The next two options are self-explanatory: Refresh over XX minutes and Refresh data when opening the file.
Finally, you can specify if the connection is included when the user clicks the "Refresh All" button. If this is checked, the data will get refreshed whenever the user clicks "Refresh All", even if the data sheet with the data is not currently active. If you don't include it, the user will have to specifically go to the worksheet containing the data, and click the Refresh button.
Wrap Up
I hope this helps you understand the options you have when you've linked your data to an Excel file. In my next Excel post, I'll cover the other options on the Properties dialog ("Definition" and "Used In"). You'll see that these two tabs allow you to correct your connection if the source data moves. To be notified when that post goes live, subscribe using the form below.
In my experience, linking data to Excel is an underutilized feature that can help a business quickly understand and evaluate their data. Once you've established the links, you're free to utilize Excel's tools against that data, allowing you to dynamically adjust your dashboards and reports as business conditions change. Who doesn't need that?
Reach out if you'd like to discuss your Excel/Database project!
Kim
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.