A reader imported some data from an external source and ended up with a table. They asked how they could get a copy of the data itself into the spreadsheet, without the table.

What is going on here?

When Excel 2007 imports data from an external source that can be updated back, such as the data connection, database (like SQL Server), query (via MS Query), and so on, it puts the data into a ‘Table’ object instead of just putting the contents right into your spreadsheet. Any change in the data is updated back to the data source if possible, and the data is updated when the source is changed.

This is useful behavior … apart from when you want a copy of the information, not a table! The user that asked the question wanted the data as a regular Excel worksheet data instead so it could be edited and manipulated without impacting the original data source.

Importing Data

If you want to import some data, first, we can look up our friend, Microsoft Query. Select the Data ribbon, click the From Other Sources button, and select your external data source.

After selecting the source, we need to specify where the data will be put. Note that there’s no option for just putting it as data, it has to be a ‘Table’, pivot table, or a pivot chart and report:

Now we’ve got the data imported as a table object into Excel, as seen here. Note that when a cell inside this table object is selected, the ribbon displays ‘Table Tools’:

Converting to a Range

Now for the actual solution we wanted, that is converting the data so it will become a part of the worksheet.

To do that, we’ll use the ‘Convert to Range’ button in the ribbon:

Pressing it, we’ll have to OK the change. Note that it’s not reversible, that is, you can’t undo it:

And now the data is a part of the worksheet instead of in the table object; note the ribbon:

Finishing Touches

One thing remains, the data still looks like a part of the table object and it does not match the rest of the sheet. To fix this, we’ll use the Format Painter. First select a cell outside of the (former) table area, so we can copy its format, then click on the Format Painter button:

Now, select the old table area:

And voila! Fully editable contents in your Excel worksheet.

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Twitter or Facebook.