Let's learn how to get data into MS Excel from a database like MS
access. Of course, you can import data into Excel from many other
databe types like MySQL.
If you wish to import or rather dump data into Excel from a database
you can use the following procedure:
Click on the data tab
From the command group 'Get External Data' click on
A new window called 'Select Data Source' opens
Select the databse file. We have slected the sample
database file 'Northwind 2007.accdb'. You can select your own database
A new window called Select Table opens
Click on the item Employees
A new window called 'Import Data' opens
In the window a cell $A$1 is highlihgted under 'Where
do you want to put the data?" and the option button 'Existing
worksheet' is already checked
At the top under 'Select how you want to
view the data in your workbook?' the option button Table is also
Since we have a header in cell A1 we click inside
cell A4 which is displayed in the Import Data window and we click OK
All the dta of the employees is dumped into sheet1
starting with cell A4
We may not want the complete data but we may want specific data like
first name, last name and email. How can we implement this procedure of
importing specific data into Excel?
We select sheet2
Click on Data and then select 'From Other Sources'
above the command group 'Get External data'
From the drop down menu we'll select 'From Microsoft
A new window opens with the title Choose Data Source
We'll click on MS Access Database. You'll notice that
the checkbox right at the bottom of the window displaying 'Use the
query wizard to create/edit queries' is selected
The Select Database window opens
We'll select 'Northwind 2007.accdb' in the My
The 'Query Wizard - Choose Columns' opens
Under 'Available Tables and Columns:' we'll select
customers and click on the + sign next to the customers
Well select the items 'Last Name', 'First Name' and
Email one by one and click on > evertime to move the items under
'Columns in your query'
We can rearrange the items to display: First Name,
Last Name and Email in that order by clicking on the First Name and
then clicking on the Move Up arrow to the right
Click on 'Options...' and check all the options
Tables, View, System Tables and Synonyms, especially the System Tables
We can click on the item First Name and then click
Preview Now and see the data under 'Preview of data in selected column'
Click on Next to come to the window Query Wizard -
Filter Data. here well do nothing and click on Next to proceed to next
In the new window Query Wizard - Sort Order we'll
select First Name and sort in Ascending order by selecting the option
Ascending on the right
In the window Query Wizard - Finish we'll accept the
selected option: 'Return Data to Microsoft Office Excel'. Other option
available is 'View data or edit query in Microsoft Query'
which can display the data in a grid. We can also select: 'Save
Query...' to save our query to run it without going through each of the
In the import data window click OK
We now observe that the selected columns First Name, Last Name and
Email have been imported into our Excel Worksheet 2. Now we can request
Data Administrators to give us specific data like this and not to dump
the complete data tables so that we can concentrate on the job at hand.