Excel® 2007 Dashboards & Reports For Dummies®

Chapter 12. Using External Data for Your Dashboards and Reports

Wouldn't it be wonderful if every dataset you came across were neatly packed in one easy-to-use Excel table? Unfortunately, there are people (and you know who they are) who insist on using platforms other than Excel. Imagine the gall.

Of course, I'm being cheeky. The reality is that you will undoubtedly encounter situations when 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.

Throughout this book, I advocate the separation of data and presentation. When dealing with small datasets that are developed and maintained in Excel, you have to make a conscious effort to make that separation. However, in complex models where large volumes of data come from Access or SQL, the effort on your part is eliminated. That data is already separated, baby. The worry in these situations is how to efficiently move that data from over there to over here. This chapter explores the most efficient ways to get external data into Excel.

Before jumping in, however, there are a couple of disclaimers your humble author would like to throw out there. First, the focus of this chapter is on getting data from Access and SQL Server databases; mainly because the data for a typical Excel user resides in Access or SQL ...