Let’s just say we wanted to create a spreadsheet that could calculate the stress due to thermal expansion in a beam that is fixed on both ends. In addition to knowing the dimensions of the beam and the temperatures involved, we would also need to have the linear coefficient of thermal expansion of the materials involved.
In order to make the spreadsheet really powerful, and minimize the amount of rework required in the future, it would be really nice to have a table of thermal expansion coefficients for as many materials as possible.
You can find the data in textbooks and other written sources, but it requires a lot of work to manually enter the data into a spreadsheet.
Fortunately, the data also can be found on numerous pages of the internet AND Excel also has a great tool for importing this data and putting it into a spreadsheet – it’s called a WEB QUERY.
Browse for the Data You Want to Use
First, find the page you want to pull the data from in your web browser. For this problem, I found a nice table of data on The Engineering Toolbox. Highlight the URL of the webpage and copy it to the clipboard.
Tell Excel Where to Find the Data
In Excel, open the query tool (Data>New Query>From Other Sources>From Web). The particular tool we want to use here is in the “Get and Transform” section of the Ribbon. It is a much more advanced and flexible tool than the standard “From Web” located in the “Get External Data” section.
Copy and paste the URL into the dialog box that appears. Then click OK.
Excel automatically recognizes any tables that appear in the document and lists them on the left side of the window. By clicking on the table, we can preview it on the right side of the window and verify that it does indeed contain the data we need.
Clean Up the Data
One thing that is slightly problematic with this table is that it does not contain entries for all of the materials in US units.
It does however included data for all materials in SI units.
If we try to pull the US data from the table using a lookup function later on, we could end up with an error.
Besides, if we have the data in SI units, we can also do the conversion to US units anyway.
To prevent any future problems, we will discard the column of data in US units.
Click “Edit” in the window above to open up the Query Editor.
Next, right-click on the heading of the column containing data in US units and select “Remove”.
The column of data is removed from the query AND an entry is added in the “Applied Steps” on the right side of the window.
Excel is remembering the steps that have been performed on this data. This will come in handy if we ever want to update this data from the web page if, for instance, a new row of data was added.
Load the Data into a Spreadsheet
Once it has been verified that we have the correct data, click “Close and Load” in the upper left corner of the Query Editor. Excel creates a new sheet and puts the data there, starting in cell A1. It also does some formatting automatically to create a nice looking table.
View the workbook queries by clicking “Show Queries” in the Data tab. You’ll see a list of queries. Hovering over the query with the cursor displays it’s properties in a pop-over window. We can see the source URL for the data as well as when it was last refreshed.
If for some reason the data changes in the future (e.g. a new material is added), it is incredibly easy to update this table. Just click on the refresh icon to quickly update it.
