Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
NASA has a detailed web page with the timeline of the Apollo 11 mission from before liftoff to well after splashdown It’s an example of the two ways to put a web table into an Excel worksheet.
Newer Excel (365, 2019 and 2016) has PowerQuery with ‘Get and Transform’ which is much better.
The older method (Copy and Paste) has been in Excel for a long time. We’ll demonstrate that in a separate article for people with older Excels. As you’ll see, Copy/Paste needs a lot more work to format and normalize in Excel.
This is our excuse to show the neat feature copying web tables into Excel. It’s a practical example including some little tricks and extras. Also how to use Get Data to copy a ‘one-off’ table that doesn’t need later refreshing.
Try for yourself. It’s a simple and harmless way to step into Excel Get Data / Get & Transform.
Get Data from Web Table
Modern Excel has more powerful data import and conversion systems. They grab data from many external sources, transform in Excel data very well and drop into a worksheet ready to use.
Excel’s Get Data is setup to refresh the data from the source. That’s great in most situations but not always necessary. The Apollo 11 timeline is a static, unchanging table with no data refresh necessary.
We’ll show how to copy a web table with Get Data with no later updates.
Get Data | From Other Sources | From Web
On the Data tab go to Get Data | From Other Sources | From Web. The label ‘Get Data’ may be different depending on your version of Excel because Microsoft can’t seem to settle on a name. But the ‘From Web’ option will be there.
Paste in the web link
to the page with the web table. Basic is enough for a standard web page available to anyone Click Advanced if there are login or special requirements.
Wait while Excel grabs the web page and examines it.
The left of the Navigator shows the web page parts, click on the Table to see it in Table View on right. This is the data Excel will import.
Click Transform Data to go into PowerQuery. PowerQuery is where the incoming data table can be adapted to suit your needs. Data can be changed to different formats, filtering, sorting, calculated columns added and a lot more.
In this case there’s NOTHING to do. Get Data has converted all the NASA data into the correct Excel data types. GET is a time duration. GMT Time are time cells and GMT Date are all dates.
If that doesn’t seem like a big deal, see all the extra work needed in earlier versions of Excel with the Copy/Paste method. Get Data is much better at identifying incoming text and matching it to Excel data types.
Finally click Close and Load to insert the data into an Excel worksheet.
A lot of detailed work is done for you. The data is made into a table automatically with sort and filter pull-downs ready to go.
The data query from the web page is shown in the Queries & Connections pane.
Disconnect Connection to web page
Each time Excel refreshes data connections, it will go out to the NASA page, grab another copy of the table and update the worksheet.
Since the Apollo 11 timeline isn’t going to change, there’s no point in keeping the data connection.
Click in the table then go to the Query tab and click Delete. Simple as that.
Query | Delete only removes the data connection. The existing table remains unchanged.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.