Part V: Analyzing Data with Excel
678
Accessing external database files from Excel is useful in the following situations:l You need to work with a subset of a very large database.l (^) The subset of data changes over time. It’s very easy to refresh a query and replace the old
data with updated information.
l (^) The database is in a format that Excel can’t import, or the database may be too large to import.
l The database contains multiple tables with relationships between those tables.
If you need to work with data in an external database, you may prefer to use Excel rather than the
tools available in database programs. The advantage? After you bring the data into Excel, you can
manipulate and format it by using familiar tools such as formulas, charts, and pivot tables.
Of course, real database programs, such as Access, have advantages, too. For example, creating
a complex database report in Access may be easier than creating it in Excel.
People who spend their days working with databases seem to have their own special language. The
following terms can help you hold your own among a group of database experts:
l External database: A collection of data stored in one or more files (not Excel files). A database
contains one or more tables, and tables are composed of records and fields.
l Field: A component of a database table, it corresponds to a column in Excel.
l ODBC: Open DataBase Connectivity is a standard developed by Microsoft that uses drivers
to access database files in different formats. Microsoft Query comes with drivers for Access,
dBASE, FoxPro, Paradox, SQL Server, Excel workbooks, and ASCII text files. ODBC drivers for
other databases are available from Microsoft and from third-party providers.
l OLAP Cube: A multidimensional aggregate data source, often created from various other
sources. OLAP is an acronym for OnLine Analytical Processing.
l Query: Search a database for records that meet specific criteria. This term is also used as a
noun; you can write a query, for example.
l Record: In a database table, a single element that corresponds to a row.
l Refresh: Rerun a query to get the latest data. It’s applicable when the database contains infor-
mation that is subject to change, as in a multiuser environment.
l Relational database: A database stored in more than one table or file. At least one common
field (sometimes called the key field) connects the tables.
l Result set: The data returned by a query, usually a subset of the original database. Query
returns the result set to your Excel workbook or to a pivot table. (I discuss pivot tables in
Chapter 34.)
l SQL: An acronym for Structured Query Language (pronounced see-quel). Microsoft Query
(discussed in this chapter) uses SQL to search data stored in ODBC databases.
l Table: A record- and field-oriented collection of data. A database consists of one or more tables.
Understanding Some Database Terminology