Excel’s data importing capabilities aren’t limited to data that is stored locally on your computer. It can also be used to pull data from sources on the internet.
To import data, first find a reference through any web browser. For this example, we’ll use ethylene glycol specific heat data found at:
www.engineeringtoolbox.com/ethylene-glycol-d_146.html
Excel can import the data with a refreshable link to the source.
First, copy the URL of the reference. In Excel, go to the Data tab, then choose New Query:
Choose From other sources followed by From web. In the window that opens, paste the copied URL, then click OK. Excel will look for tables of data in that page. The Navigator will appear, showing a list of the data tables that were found at left and a preview window at right:
You can click through the tables to find the one you need. In this case, Table 3 contains the specific heat data that we’ll use. Select this table, then click Load to add it to the worksheet.
The table contains extra information that needs to be removed – the entire first column is unnecessary, the two temperature columns are redundant, and there are footnote references throughout the table. You could manually edit the table, but it’s better to modify the query so that your changes will be applied if the table is ever refreshed.
To edit the query, select the Query tab (visible when the table is selected), then click Edit to open the Query Editor.
Select the unnecessary first column and choose the Remove Columns button in the ribbon. You can also right-click and select Remove from the menu; do this for the column containing the temperature in Celsius.
In the Applied Steps window, Excel will add a “Removed Columns” step:
Excel is keeping a record of the changes that have been made to the queried table, so that it can repeat them the next time the table is refreshed from the source data.
Next, we’ll remove all of the footnote references. Select all of the columns and click Replace Values. In the window that opens, type 1) as the Value To Find. Leave the Replace With box empty.
This will remove all the references to footnote 1. Repeat with footnote 2 by using 2) as the Value to Find. Again, leave the second box blank. Now, all of the footnote references are gone, and steps for “Replace Value” have been added to the Applied Steps.
To make the columns narrower, we can change the column headings to titles such as 25% EG, 30% EG, etc. Simply double-click each heading and type the new heading. Excel will add “Renamed Columns” to the Applied Steps.
Now, load the updated query into Excel by clicking Close and Load in the upper left corner. Excel will refresh the query and make the changes that were recorded in the Query Editor. It then loads the modified table into the worksheet.
