Data Tables

Hi Jason,

Thank you for using the forum to ask a question.

Importing external data into Excel can be achieved by heading to the “Data” tab on the Ribbon then using the “Get External Data” subgroup. It does depend on how the 3rd party supplies you with data, but there are options for connecting to Access, Web, Text, and Other (Including XML & SQL)

Once you have selected the data source and completed the Wizard, it will ask you “Where do you want to put the Data?”, you can select a new sheet or specify a cell reference beneath a current heading. This will import the data into Excel.


There is a way to import and map data using XML. You need to display the “Developer” tab in the ribbon which is off by default. File/Options/Customise the Ribbon and then tick the check box next to “Developer”.

Under the “Developer” tab, select the “Import” options from the XML subgroup.

Select the data in XML format. This may include a schema of the headings, if not Excel will create one at this stage.

I would suggest choosing the option Import to a new Worksheet, to avoid any damage to the existing table.

The data will import, and the schema data will be displayed in the “Source” pane (Developer) on the right of the screen.
You can right click on each element in the pane, “remove element”.
You can then click and drag the element to the correct heading on the existing table you have.
When you import data again, the schema will now map that element to the heading in your existing table.

Mapping the elements to your headings does assume that your 3rd party data is in the XML format. If you would like more help regarding this matter, please get in touch so we can discuss providing you with a customised solution.

Kind regards

Richard Bailey
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global