How to import data to Excel

Besides Word, Excel is probably one of the best and most widely used Office programs. The sheer complexity of the program shows how useful it is: it features calculations, graphing tools, pivot tables, even Visual Basic.

Working with Excel means working with a lot of data, and sometimes that data comes from a different source. A text or word file, webpage, or any other source. Importing that data into Excel can be a real pain in the neck, unless you’re doing it properly.

Simple copying and pasting data won’t help much, as the data will most likely end up in a single cell and you’ve achieved nothing.

In this tutorial, I will explain how to import data from a text file:

  • Open the Excel file you need, and select the cell where you wish the data to be imported
  • In the horizontal menu bar find Data and press it
  • The first banner on the left is called “Get External Data”, where you can choose from five different options: From Access, From Web, From Text, From Other Sources, and Existing Connections. For the purpose of this tutorial, we will choose From Text.
  • Browse around and find the file you wish to import, and click Import.excel
  • The Text Import Wizard will guide you through the process:
  • If the text file has data separated with commas, tabs or spaces, you can choose the Delimited option, to have Excel split the data that way. Otherwise select Fixed Width if there are a certain number of spaces between each field.
  • Click Next
  • Select the type of character to separate the fields.
  • Choose a format for each column: Select the column heading in the Data preview and then select a data type from the Column data format options.excel2
  • Click Finish
  • Select where you would like the imported data to be in the workbook and click OK.

Importing from the Web

Importing from the Web is essentially the same, and the main difference is that Excel allows you to choose which data you will import. As websites will offer a lot of useless text accompanying the data you need, it’s a useful add-on.

Here’s what you’ll want to do:

  • Open Excel
  • Go to Data
  • Choose “Get External Data”
  • Pick “From Web”
  • A mini browser will open up. There you can navigate to the website you want to extract the data from. For example, latest US stock price can be found at http://www.money.cnn.com/data/markets.
  • Once the site loads, you will see a lot of yellow arrows everywhere. Those represent tables you can import. Click the yellow arrow at the table you wish to import.
  • Press import. Magic!