Excel Dashboards and Reports For Dummies, 2nd Edition

Chapter 13

Using External Data for Your Dashboards and Reports

In This Chapter

Importing from Microsoft Access

Importing from SQL Server

Wouldn’t it be wonderful if all the data you come across could be neatly packed into one easy-to-use Excel table? The reality is that sometimes the data you need comes from external data sources. External data is exactly what it sounds like: data that isn’t located in the Excel workbook in which you’re operating. Some examples of external data sources are text files, Access tables, SQL Server tables, and even other Excel workbooks.

This chapter explores some efficient ways to get external data into your Excel data models. Before jumping in, however, your humble author wants to throw out one disclaimer: There are numerous ways to get data into Excel. In fact, between the functionality found in the UI and the VBA/code techniques, there are too many techniques to focus on in one chapter. Instead, then, in this chapter I focus on a handful of techniques that can be implemented in most situations and don’t come with a lot of pitfalls and gotchas.

Importing Data from Microsoft Access

Microsoft Access is used in many organizations to manage a series of tables that interact with each other, such as a Customers table, an Orders table, and an Invoices ...