Other Links
This is one of the 33 lessons from the
Tutorial on Excel
Lesson 1-4: Data/Import External Data
In this lesson we are looking at the "Import Data" functionality.
When you go to the menu bar "Data/Import External Data/Import Data" Excel offers you the following dialog window:
Importing Data from other Excel Files
Forget about these sources if you haven't started creating your own queries.They are web queries developed by Microsoft. Try them for the fun of it but study lesson 1.5 to become an expert at queriing the WWW.
In "Files of Type" at the bottom of the window select "All File (*.*)" and select the directory where the file that you want to open resides then click open. If you are opening an Excel file you will then see this new dialog window in which you will select the sheet containing the data.
You uncheck or check the "First row of data contains column headers, you select the sheet and click "OK". A final dialog window comes to the screen:
Here you simply click on "OK" unless you want the data in a cell other than A1 or unless you are an advanced user who wants to create a pivot table or edit the properties or the query.
The data is imported, the query is created and from now on you will just have to right click anywhere in the dataset and select "Refresh Data" to update the data.
You can use the same approach to import data from Microsoft Access. The "Select Table" window above will show all the tables and queries of the database from which you will make your selection.
Importing Data from Files other than Excel (The Import Wizard)
When you try to open or import data from files other than Excel (or CSV) the Import Wizard kicks in. This tool allows you to tell Excel how the data is organized in the file that you are trying to open. The first dialog window of the Import Wizard looks like this:
In this first window you will tell Excel if there are clear delimiters that can be used as column separator (comma, space, semi-colon, etc.) and in most case there are. So you select "Delimited". We will see the "Fixed Width" later. You then click "Next".
In this window you tell Excel what is the delimiter. Use the "Other" to key in other types of delimiters like hyphens, slashes, backslashes, etc. When you see that vertical lines separate the pieces of date as you need you can click on "Finish".
The file is then opened or the data imported. There is a third steps that you will rarely need to use that allow you to specify the format of the different columns(number, text, date, etc.).
If you are trying to open a file that doesn't have delimited fields or if you want to merge certain columns you will choose "Fixed width" in step 1. The "Step 2 of 3" looks like this:
Move the column lines as you wish then click "Finish" unless you need to go to step 3 to format certain columns (numbers, text, data, etc.).
Automating the Query
By doing the process above you have created a query and you can automate it. To do so you right click anywhere within the dataset, select "Data Range Properties" and the following dialog window appears:
The name at the top will not be "excel-tutorial-sumproduct" but the name of the workbook from which you are extracting data. Advanced users may modify many of the items in this window but as a beginner limit yourself to the topic "Refresh Control".
Never uncheck "Enable background refresh" but you can check "Refresh on Open" and the data will be refreshed each time you open the workbook. Or "Refresh every" and set a time in minutes and while the workbook is open data will be refreshed automatically based on the interval that you have set. This last setting is interesting for people who query financial data from the Internet.
Whatever automation you choose from then on when you open the workbook an alert will be shown:
You click on "Enable automatic refresh" and you tell the other users of this workbook to do the same. If you are working on a report in this workbook you might want to disable the automatic refresh functionality. To reactivate it close and save the workbook and reopen it. For advanced users I have to say that there is no way to prevent this dialog window to open even with VBA
This is one of the 33 lessons from the
Tutorial on Excel
