July 2013


Many people (especially in Finance) use Excel and Access almost interchangeably, feeding data between both applications. But if you’ve never done it, then how do you? There are several ways to import data from Access to Excel, and you don’t even need to have Access open. You can import Tables, as well as most queries (Crosstab queries aren’t supported), so it’s up to you to determine which you want to import. In general, since you use Access to store large amounts of data in tables, then use its fantastic querying ability to pare that data down into manageable chunks, you’ll find yourself importing query results most of the time.

In this article we’ll discuss the easiest way to import Access data into Excel whether from Tables or Queries. To get started go to Excel’s Data menuàGet External DataàFrom Access (this was a new option with Excel 2007 and the Ribbon Interface), although if you’re familiar with it, the old MSQuery option is still there, which we’ll discuss in another article.

From there, select the database that you want to use as your import source. Excel will then display a list of the available Tables and Queries that can be imported:

In this case we’ll import the Customers table. Once you’ve selected the Table/Query that you want to import, Excel will ask you how you want to import it. For this example we’re just going to import the Access data as a table, although you can see that Excel gives you several options for importing the data. You should note here that while you can’t currently import Crosstab queries from Access, you can import the data that supports the Crosstab as a Pivot Table report (after all, a Pivot Table is essentially Excel’s version of an Access Crosstab query).

Sidenote: If you’re using Excel 2013 you’ll see a check box at the bottom to “Add this data to the Data Model”. This is an exciting new feature that the Excel team developed that allows you to create relationships between tables (a lot like you’d do in Access when building queries). If you’re interested, then make sure to check out this article from Diego Oppenheimer, Program Manager for the Excel development team at Microsoft: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx.

Properties – While you can certainly get detailed with Import Properties, for this article I just wanted to point out a feature that’s handy if you have a data set that refreshes at regular intervals: you can tell Excel to automatically refresh your Access data whenever you want.

Skipping past the Data Model and Properties, if you hit OK your table data will be immediately returned to Excel:

If you’re at all familiar with Excel’s Tables, then you’ll recognize this format, which you can modify in the Table Tools Design tab, which will become active when you select any range in the table.

If you haven’t taken advantage of the Refresh options in the Properties dialog, you can refresh on demand from the Table Tools Design tab or the Data tab.

Note: The database examples used in this article were from Microsoft’s Northwind database for Access 2010, which you can find under FileàNewàSamples.