External data is defined as data that exists outside of the Excel workbook, in some other place.  Other places could be almost anywhere; and Excel supports pulling external data from a wide variety of sources.  Examples include data stored on web pages, in text files, or in other programs.

Excel 2007 is been used in below examples.

We get external data in excel by using different ways:-

1.  From Access
2.  From Web
3. From Text
4. From Other Sources

•         From SQL Server
•         From XML Data Import

 

I) Import Access Data

In Excel, when you import data, you make a permanent connection that can be refreshed.

1. On the Data tab, in the Get External Data group, click From Access.
2. Select the Access file.
3. Click Open.
4. Select a table and click OK. 
5. Select how you want to view this data, where you want to put it, and click OK. 

Result. Your database records in Excel.

6. When your Access data changes, you can easily refresh the data in Excel. First, select a cell inside the table. Next, on the Design tab, in the External Table Data group, click Refresh.

II) Import Web Data

1. On the Data tab, in the Get External Data group, click From Web.

 

2. When you select From Web Excel opens a dialog box displaying your browser homepage with the URL of the page highlighted. Paste the copied web address into the Address box. A quick way to paste is to Hold down the Ctrl Key as you tap one time on the V key( Ctrl + V ). You may have to scroll to find the data table. When you find it, click one time on the arrow next to the Data table and then click on the Import button.

3. You have a choice to make. Excel is ready to import the data, but needs to know if you want it put into the existing workbook, or a new workbook.

III) Import Text Data

1. On the ribbon, click the data tab and then click the “From Text” button on the “Get External data” group.

The “From Text” button, located on the Data tab of the ribbon.

2. Select your file from the next dialog:

And Click Import.

3. Now it is time to define what settings we need for the import.Click the Open button. The text import wizard opens up:

Step 1 of the Text Import Wizard, define file type.

In this example I have selected to import a delimited file and set the file origin to Windows (ANSI). Click Next when you’re happy with the settings.

3. Step 2 of the wizard allows us to define the delimiters. I selected Comma:

Step 2 of the Text Import Wizard: define delimiters.

3. Click Next again to go into the third step, where you can select a format for each column of your file. I changed the date format of the first column to dmy order. Click a column to set up its formatting.

Step 3 of the wizard, Define column formats.

You can click the advanced button to set up details like the decimal and thousands separators (note I switched them here):

The Advanced text Import Settings dialog.

Note that any changes made in this dialog apply to all columns.

4. After you finished defining all columns, click the Finish button. Excel opens the Import Data dialog, asking where to put the results. Select the proper location.

The Import Data dialog.

5. Import your data

Finally, click OK to have your data imported. My sheet looks like this:

Results after importing the text file.

6. Refresh your data

On the Data tab, find the group called “Connections”. Click the dropdown “Refresh All” and select “Refresh”:

The Refresh All button on the ribbon.

IV) Import Data from Other Sources

• Import SQL Server Data 

On the Data tab, in the Get External Data group, click From Other Sources.

1. Click From SQL Server.

2. In the Data Connection Wizard, enter the server name and logon credentials, and click Next.

3. Choose the database and tables you want to work with, and click Next.

4. You can click Finish, or click Next to change details for the connection.

5. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, Pivot Table report, or Pivot Chart. 
6. Import your data

Finally, click OK to have your data imported. My sheet looks like this:

 

• Import XML Data Files

On the Data tab, in the Get External Data group, click From Other Sources.

1. From Other Sources > From XML Data Import. 
2. Go to the drive, folder, or Internet location that has the XML data file (.xml) you want to import. Select the file and click Open. 
3. In the Import Datadialog box, do one of the following:

1. • XML table in existing worksheet: The contents of the file are imported into a new XML table in a new worksheet. If the XML data file doesn’t refer to a schema, Excel infers the schema from the XML data file. 

1. • Existing worksheet: The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn’t infer a schema, and you can’t use an XML Map. 

1. • New worksheet: Excel adds a new worksheet to your workbook and automatically puts the XML data in the upper-left corner of the new worksheet. If the XML data file doesn’t refer to a schema, Excel infers the schema from the XML data file.

4. Excel sheet will look like this:

 

References

• https://www.gcflearnfree.org/excel2007
• https://support.office.com/en-US/Excel

 

About Suman Maity:

Suman Maity is a B.Tech(Electrical Engineering) . Currently he is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore. He has prior worked for around 1+ year with T&M Services Consulting Pvt Ltd and HR Chamber Outsourcing Pvt Ltd.