Quicktip-Grab some data off a webpage and put it in a spreadsheet

This skill works with just about any spreadsheet program, on the desktop or in the cloud, and with any of the major browsers, Chrome, Edge, Explorer, Firefox, Opera or Safari.

One of the simplest data tasks is to take some tabular data from the web and copy it into a spreadsheet program for some quick analysis. This works because the browser replaces the HTML table markup with tabs

For this demo we’ll go to the Canadian department of Fisheries and Oceans disclosure page for contracts at http://www.dfo-mpo.gc.ca/pd-cp/reports-eng.asp, and pick the most recent quarter of contracts posted (which is the third quarter of 2015-16 as we write this). If you click the link here, it will open in a new window.

Copying into a spreadsheet is as simple as highlighting the table of data by clicking and dragging your mouse from the top left corner to the bottom right corner of the data table. You can see part of it highlighted here.

Dataselected

Now, copy the content using copy under the edit menu of your browser or the keystroke CTRL > C on a PC or CMD > C on a Mac. Open your favourite spreadsheet, put your mouse in cell A1 of a new sheet, and paste the data. In some spreadsheets you may need to use Edit>Paste Special and paste the data as text.

It’ll end up looking something like this:

Datainsheet

Now, instead of a static HMTL table in your web browser, you have data in a spreadsheet. Now if you want to see which contract had the greatest value, you can quickly sort on the value column. We’ll show you how to do that in another quicktip.

There are plugins available for browsers such Firefox and Chrome that make copying tables even easier. Internet Explorer also allows you to copy HTML tables to Excel by right clicking on the table in Explorer and choosing “Export to Microsoft Excel.” In Google sheets you can use the IMPORTHTML function. We’ll cover that in another quicktip.