Importing data in Microsoft Excel

Many of the features and enhancements added to the latest versions of Excel have been designed to extend Excel’s ability to turn raw data into valuable business intelligence. A key aspect of this process is getting at the data in the first place.

Although we can import or copy the data into Excel, linking to the data offers many advantages. Not only does this reduce the chance of introducing errors as the data is transferred or while it sits in Excel, but it also means that it can easily be refreshed to reflect additions or changes. Correctly designed reports will then adjust automatically to incorporate the changes.

Excel can link directly to data stored in a database format. This doesn’t have to be a database file from a dedicated database application: many systems will store their data in a format that Excel can make use of.

The Excel DATA ribbon tab includes a Get External Data group which contains a set of tools for linking Excel to different types of data source. When you link to an external data source the data will be displayed as an Excel Table that remains linked to the external data source, so that it can be refreshed to bring in any new or changed information without having to run through the Get External Data routine again.

The From Access, From Web and From Text commands will allow you to search for files of the appropriate type and link to entire tables or queries from a Microsoft Access database, tables of data on a website or the contents of a text file.

From Other Sources allows you to choose from some additional types of data source. It also includes the From Microsoft Query option. This allows you to choose the type of data to connect to and also choose individual fields from the data source and even to join separate tables of data. In the example below, we are using the From Microsoft Query option to link to some expenses data held in an Access database.

When we choose From Microsoft Query we see the Choose Data Source dialog. This lets us choose an existing data source or create a <New Data Source>:

The options you see will depend on the version of Excel that you are using, your original set-up choices, and any data sources that you, or software installations, have installed. In this particular case, we want to connect to an Access database, so we choose the Access option. Note that if we had already created a query to extract the information we required, we could access this by clicking the Queries tab. Clicking the OK button launches the Query Wizard itself. This can be quite an advanced feature, so we’ll look at it closely in a future post.

MS Excel Online Training
Filtered is an online course that only teaches the features of Excel that are useful to you, saving you precious time and providing a truly tailored learning experience.
Find out more about the Filter teaching methodology and take a look at the Excel course syllabus.

Identifying duplicates, how do I do that again? This step by step guide will give you a refresher course on how to present any duplicates your Excel spreadsheet may contain.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.