How to Connect Data Sources to Excel

Why Analyze Data in Excel?

Sometimes you or your team simply has a thought or question that needs answered. That and you don’t really want it to be “a whole thing” or initiate the process of building a new way of looking at data. Now that sort of situation is a great use case for tapping into the power of Excel.

Look, we don’t necessarily advocate using Excel as a primary way to track any one metric over time. That sort of thing is really best suited for something like PowerBI or a dashboard in Dynamics 365 Sales.

TL;DR Copilot Summary

This is a guide on how to connect external data sources to Excel, enabling dynamic, auto-updating reports. It highlights Excel’s strengths for quick analysis while recommending PowerBI and Dynamics 365 for larger data sets and regular monitoring. With step-by-step instructions for connecting and refreshing data, tips for collaboration using OneDrive or SharePoint, and insights into security considerations, this page empowers users to enhance their data analysis capabilities, streamline workflows, and collaborate effectively.

---

When Not to Do This

As mentioned above, if you’re regularly monitoring data, it’s really best to take advantage of the reporting ability of PowerBI & Dynamics. The idea is if it’s something that’s important, we may as well have something in place that’s unshakable and scalable.

Now, if you need to review a LOT of data. What exactly does that mean? There’s not an exact number I can give, but Excel will let you know. Meaning your device will crash and/or freeze due to the sheer amount of data you’re trying to jam into it. If you see that happen, it’s worth considering the other platforms listed above.

With regards to PowerBI & Dynamics, PowerBI tends to be more flexible (hence the Business Intelligence angle), when it comes to large amounts of data from diverse origins and potentially complicated relationships. Dynamics’ Dashboards tend to rock your socks off when you just need high-level performance tracking (think sales, activity, and other KPIs like that). Cool thing is, all three are great and are in your toolkit ready to rock.

Another consideration, of course, is security concerns. Excel is a wonderful tool but anything you can use to copy and paste data is something to consider. So if sensitive information is tracked (social security #’s, bank information, etc.), it is NOT recommended to utilize Excel in this fashion. Aside from this, you can strictly control access to any file stored on OneDrive and SharePoint, so there’s at least that, but y’know, better safe than sorry.

---

How to Connect to the Data

Step one on this wild journey is simply taking a look at what options you have available to you. The way Excel lists the data sources for you is almost sorted by how bananas you want to get with this thing. Do you want to keep things simple and connect to another Excel sheet or do you wanna get weird and combine queries to maximize your data query efficiency? Dealer’s choice. To help you get started, when I resort to dig into data in a way that needs to bend, not break, I typically connect to something from the Dataverse (Microsoft Dynamics “database”), a SharePoint List, API connection, a random website with regularly-updated tables, and/or something from another Excel file. Or any variation of these.

• Click on the “Data” tab
• On the left, click on “Get Data”
• From the list, select where you want to grab data from
• There are numerous options to choose from and with the advent of ChatGPT and Copilot (built-in or browser-based), so if you need these broken down further, a quick prompt will provide you with the nitty-gritty details. Keep in mind that Excel holds your hand pretty well that you shouldn’t need to, but in the event you need further help, Google and/or AI buddies are always an option.
• Once you have the table in place, you can tap into what you have in place.
• If you need to simply make a chart, you’re pretty much set. Just click on the new table and navigate to the “Insert” tab to enter one based on that data. If you want to get fancy, Pivot Tables are also a wonderful option. Those let you go a bit more crazy with regards to complexity. This helps to nail down weird, nitty gritty details or specific trends you need to identify.
• The next thing to consider is whether or not you need to have this data be updated regularly or not.

---

Refreshing the Data

When you connect to a cloud-based data source, you have the option to refresh the data to ensure that what you’re looking at is up-to-date. One of my favorite things to enable is auto-refresh. The less clicks I have to make, the better. So in this case, first thing to consider is frequency. If it’s a “whenever you feel like it” sort of situation, you can right click on the table and click on “refresh.” That’ll automatically fetch the data from its source and repopulate the table with the most up-to-date information. Alternatively, if you wish it to be a bit more automatic, first, click on the table then:

• Pop on over to the “Query” tab
• Click on “Properties” and a “Query Properties” window will pop up.
• Here, you can really specify how frequently you want this data to be updated.
• I’ve found that simply setting the data to refresh when opening the file to be sufficient in most cases.
• A caveat here is that if you’re pulling a lot of data from multiple sources, the second you open this Excel file, it’ll hammer your CPU and network trying to quickly retrieve the data.
• If this is an issue, you can likely adjust the query to remove the importing of columns in your data you don’t need.
• If this continues to be problematic, it may be time to explore the likes of PowerBI & Dynamics.
• Do keep in mind that data connections need to be opened in the Excel for Desktop app. You can still view the document in your browser, but updating the data isn’t possible without using the Desktop app.

Collaborating on the Same Excel Sheet

Once you have everything in place, you may need to collaborate with your team. To avoid passing this thing around like a hot potato, you can take advantage of something like OneDrive or SharePoint within the Microsoft 365 suite. This would essentially hold the document and you’d open it within the Excel for Desktop app. While doing so, if you have multiple people in the file, you’ll see where each user is by multi-colored highlights on whichever cell they’re in. As they make changes, they’ll be updated immediately over the cloud. The benefit of this is that you’ll be able to all work from the same document and can avoid juggling emails back and forth. Since you’ll be working within this file that is stored on SharePoint (or OneDrive), it will auto save your work.

Conclusion

Connecting data sources to Excel can be a game-changer for your data analysis adventures! Imagine creating dynamic, auto-updating reports that keep you in the loop without breaking a sweat. While Excel is your trusty sidekick for quick, ad-hoc analysis, remember that even superheroes have their limits. For those epic battles with large data sets and regular monitoring, PowerBI and Dynamics 365 are your go-to allies.

By tapping into Excel’s data connection superpowers, you can streamline your workflow and collaborate like a pro with your team. Just don’t forget to keep your data safe and explore advanced features like Power Query and auto-refresh to level up your efficiency.

In the end, integrating Excel with external data sources empowers you to make smarter decisions and keep your data fresh with minimal effort. Whether you’re tackling a one-time mission or need a long-term strategy, mastering these techniques will make you the hero of your data story. 🚀📊