Web pages often contain information that is perfect for analysis in Microsoft Excel. For example, you can analyze stock quotes in Excel using information that comes directly from a Web page. Depending on your needs, you can retrieve data that is refreshable (that is, you can update the data in Excel with the latest data on the Web page) or you can get data from a Web page and keep it static on the worksheet.
Use a Web query to get data that's refreshable
With a Web query (Web query: A query that retrieves data stored on your intranet or the Internet.), you can retrieve data such as a single table, multiple tables, or all of the text on a Web page and analyze it by using the tools and features in Excel. With the click of a button, you can easily refresh the data with the latest information from the Web page. For example, you can retrieve and update stock quotes from a public Web page, or a table of sales information from a company Web page.
A Web query can retrieve the data on a Web page and return it to Excel for analysis.
Copy and paste static data or a refreshable query
You can use familiar copy and paste commands to bring the data from a Web page into an Excel worksheet. When you paste Web page data into Excel, you can keep the data static or make it refreshable by clicking Paste Options and clicking Create Refreshable Web Query.
Export data from Microsoft Office Web Components
Open an HTML, MHTML or XML file
You can open any HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) file, MTHML (Single File Web Page (MHTML): An HTML document saved in MHTML format, which integrates inline graphics, applets, linked documents, and other supporting items referenced in the document.) file, or well-formed XML (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) file in Excel by using the Open command on the File menu.
When you open an HTML or MHTML file, you get the entire Web page, but you might lose some formatting, scripts, .gif image files (HTML only), or lists of data in a single cell.
When you open an XML file, you have the option of applying one or more referenced stylesheets (XML stylesheet: Contains formatting rules which are applied to an XML file that references the stylesheet. The standard set of rules for XML stylesheets is the Extensible Style Language (XSL).).