How To Link An Excel Spreadsheet to A Microsoft Access Database

Microsoft Office - Making Connections between Excel and Access

Have you ever been asked to create a database at work and then been told all of the data is already held on an an Excel spreadsheet?

Or even 20 Excel spreadsheets?

Excel is usually the first place administrators will go to if they need to enter 'lists'. The lists are usually made up of things like names, addresses and zip codes. Maybe they are lists of clients. If you work in a school, maybe they are lists of parental information for each child in the school.

Excel does this kind of thing very well. it is a neat and tidy, column and row formatted spreadsheet but there its uses end as a piece of software for 'storing' data.

But Excel spreadsheets can be linked to other software packages in the Microsoft Office suite.

For example, you can copy and paste a graph from an Excel spreadsheet into a Microsoft Word document.

And you can also link an Excel spreadsheet to a Microsoft Access database - and that is when your static list on an Excel spreadsheet becomes something much more - it becomes part of a database.

And when it becomes part of a database, it is no longer static. It becomes an active table of data which you can manipulate at will.

Here is how to link an Excel spreadsheet to a Microsoft Access database.

Open Your Excel Spreadsheet - Tidy Up Before You Begin

Browse to your Excel spreadsheet and open it up (Figure 1)

Take a quick look at it and ensure any untidy elements are fixed.

Maybe some of the data is half-completed or untidy. Sort that out first.

Next, save it in the same place as you intend to save your Microsoft Access database.

This will make hunting down both files much easier.

Once you are happy that the data on the Excel spreadsheet looks ok, close it.

In this example, I have chosen a fairly straightforward Excel spreadsheet with just a few headings.

It is always worth ensuring that you have TITLES in your column headings so you know what is in each column, e.g. Surname, Forename etc.

Figure 1
Figure 1 | Source

Linking Your Excel Spreadsheet

Open Microsoft Access.

Select Blank Database from the top of the screen and then give it a meaningful name, e.g. 'Client Info.accdb'.

The next screen will be blank except for the first line of a potential table. Usually, you might be creating a database and then inserting the data into a table yourself.

In this instance, we are inserting the data from an Excel spreadsheet because it already exists. This is going to save us a huge amount of time in creating the database.

In the left hand pane, right click on Table1:Table

Select Import and then choose Excel from the drop down list. (Figure 2).

Figure 2
Figure 2 | Source

Linking the Excel Data

The 'Get External Data' screen will be shown which offers you three choices:-

  • Import the source data into a new table in the current database
  • Append a copy of the records to the table........
  • Link to the data source by creating a linked table

Let's look at these choices more closely.

  • Choice 1 offers you the opportunity to import and that will bring the data into Access where you will then append any additional client names into the database, actually rendering the spreadsheet obsolete.
  • Choice 2 offers you the opportunity to append a currently existent Microsoft Access table.
  • Choice 3 offers you the opportunity to link to an existent Excel spreadsheet and maintain that link so that any data updated in the spreadsheet automatically copies over into the database.

For this article, we are choosing Choice 3 - linking the Excel spreadsheet to the Microsoft Access database. (Figure 3)

Click on choice 3 and then click OK

Figure 3
Figure 3 | Source

Formatting the Excel Spreadsheet for Microsoft Access

When you see the next screen for Microsoft Access, it will ask you whether the first row of your spreadsheet contains the headings. Mine does so I click in the box to acknowledge it (Figure 4)

Click NEXT and then give the 'Linked Table Name' a meaningful name as requested.

Microsoft Access will then advise you that it is finished linking to the Excel spreadsheet, click OK

You will now see your Table containing your Excel data on the left hand pane of your computer screen.

And that is how you link an already existent Excel spreadsheet to a Microsoft Access database.

Figure 4
Figure 4 | Source

Linking Excel Spreadsheet to Microsoft Access Database

Linking from Excel to Microsoft Access Turning Static Data into Dynamic Data

You have basically re-used data which already existed in a different application and been able to use it to manipulate the data in a proper database.

The data was static but is now dynamic.

If you needed to, you could now run queries on the data.

  • e.g. Get Microsoft Access to do a query to show you all of the clients who did not give their address and chose email instead.
  • Get Microsoft Access to do a query to show you all of the clients called Smith.

You can also run reports in Microsoft Access based on the client list. Maybe you want to send out mailshots?

  • You can ask Microsoft Access to create a mailing list report to use in Microsoft Word.

You have just made your data more useful by linking from Excel to Access.

I hope you found this useful. Many thanks for reading.

More by this Author

How To Link An Excel Spreadsheet ot A Microsoft Access Database Comments 8 comments

Jools99 profile image

Jools99 4 years ago from North-East UK Author

Mike, Thanks for your comment. This is definitely one of the best things I ever learnt about Excel and Access, it has saved me hours and hours of data entry but mainly because other people find entering data onto Excel easier than onto Access (which to be fair is a lot more 'fiddly') and i just had to link it to move it over.

Mike Robbers profile image

Mike Robbers 4 years ago from London

This is a wonderful hub Julie and your technical writing is very clear.. bookmarked and I will try your intructions soon - it seems quite easy to follow them!

Many thanks .xls guru :)

Jools99 profile image

Jools99 4 years ago from North-East UK Author

Michelle, any time I can help :o) Thanks for your visit!

midget38 profile image

midget38 4 years ago from Singapore

Am keeping your hub for reference, Julie! This will be useful as I am not so excel friendly. Thanks for the tips!

Jools99 profile image

Jools99 4 years ago from North-East UK Author

Cyndi, thanks for looking in. I worked for someone who created this vast spreadsheet then asked me to have a look at it and 'sort it out for me'. She wanted a different numbered letter (between letter 1 and letter 7) to go toevery name on the spreadsheet based on one column's info. I thought, to pot with that, I'm turning it into a database and saving myself a day's work - and here is that experience as a hub some 8 years later :o)

cclitgirl profile image

cclitgirl 4 years ago from Western NC

Awesome hub! It sounds like you wrote this as a result of having this experience happen to you. Great information and you've got me thinking I need to use Excel more. :)

Jools99 profile image

Jools99 4 years ago from North-East UK Author

Janine, many thanks for being the first commenter - I have not had a lot of traffic but I hope they will be slow burners and maybe pick up traffic over time. They are certainly keeping me busy :o)

Janine Huldie profile image

Janine Huldie 4 years ago from New York, New York

This series is great Julie and will tell you this if I ever work in an office again in the future I am coming to re-read your articles for a bit of a refresher!!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.

    Click to Rate This Article