The Web can be a wonderful source of information. But you can easily drag information from a web site table into Excel and squeeze even more meaning from it. I recently stumbled on a table of highest grossing movies of all time, and I was curious to know which of them had been released in 2010. Instead of scrolling through the list to search for them (quite a task since there were almost 500 titles in the list), I swooped the list into Excel so I could analyze from there. Here’s how.
First I copied the URL of the web site holding the data. Then I switched to Excel. On the Data tab of the Ribbon, I clicked the From Web icon in the Get External Data group. The New Web Query window opened providing an address field where I pasted the URL of my movie information source.
Importing Data From the Web
After I pressed Enter the web page displayed, but Excel still didn’t know which content on the page I wanted to import. The dialog box showed black and yellow arrow boxes as indicators for various elements on the page. I clicked the indicator next to my table and clicked Import at the bottom of the box. In the next dialog box I had to confirm cell A1 as my intended destination for the first cell of the table.
There was a slight pause for data retrieval, but in a few seconds the list of movies was sitting in my Excel spreadsheet ready for me to analyze. A favorite at our house from 2010, Toy Story 3, sits at No. 9 in the list!
