Sometimes you may want to
bring information from another application into an Excel spreadsheet.
One of the easiest ways to import data is to copy a table from a Word document or a database
query and then paste the results into your spreadsheet. To do this, you simply need to highlight the area of the
table you want to copy and press
C together to copy it.
Now click in the cell at the top left of where you want to paste the data
and press CTRL and
V together to paste.
Excel will work out how many cells are needed to paste the data and the
contents of each cell of the copied table will be placed in the corresponding
cell in your spreadsheet.
For larger amounts of data
it is easier to use comma-separated or tab-separated values in a data file.
Such a file might look like this:
If using a comma-separated
file you need to ensure that any text data containing commas (e.g. 14, High
Street) is placed within inverted commas 14, High Street within the file
so that the comma within the data item is not confused with the commas
separating the data. The inverted
commas are called Text qualifiers and it is important to let Excel know if
you are using qualifiers.
you have your data file to import (normally as a .txt, .csv or .dat text file)
you can click on Excels Data drop-down menu and choose Get External
Data and then Import Text File.
A window appears as below
where you can select the text file you want to import, we are going to
import the comma-separated file called contacts.txt.
on the name of the file and then click the Import button.
The Text Import Wizard window will appear as below:
Check that the correct type of
file is selected at the top of the window, if you are using comma-separated or
tab-separated values then you need to select Delimited, if you have data
that is padded out with spaces or zeroes so that a fixed width of characters
holds a fields data then you need to choose Fixed width.
If you dont want to import the whole of the data you can set the Start
import at row box to start importing further down your list.
Click Next when you have checked these settings and you will be
taken to the second screen of the Text Import Wizard.
Enter a tick in the appropriate Delimiters
box to show what character is used to separate your data. If you have used your own custom character, e.g. |, you can
tick the Other box and enter this character in the box to the right to
show which character you are using. Check
that a Text qualifier is set if you need one from the drop-down box on
the right. A preview of your
imported data is shown in the bottom of this window shown above so you can see whether it looks as you would expect.
When you have set your delimiters, click the Next button and you
will be able to set the format for your imported data.
To set the format, click on each of the
columns in the preview section shown above and set the data format from the list at the top right of the window.
For our data we can set all columns to Text but if you are importing
dates it is a good idea to format them at this point.
If you click on the Advanced button you can choose whether you
want different separators for decimals and thousands in any numeric data.
When you have set the data type for each of
your columns, click the Finish button and you will be asked where your
data should be placed.
If you want the data to be placed in
the current worksheet you should not have to do anything.
If you want the imported data to be placed further down the worksheet
then you can click on the right hand end of the box underneath the words Existing
Worksheet shown above and select the top left cell of the area where you
want the imported data to be placed. If
you want your data to be placed in a new worksheet, click the bullet beside New
By clicking on the Properties
button in the window shown above you can set your worksheet to update itself
from the text file or other data source.
By clicking on the Refresh
every box and setting a time in the minutes box you can make Excel
refer back to your text file and update the spreadsheet if the data has changed. This facility is normally more useful when you are importing
data from web sites or databases.
Click here to
return to the Excel index.