Tip o’ the Week 485 – Excel and the Web

clip_image002For decades, it’s been possible to import data into spreadsheets from elsewhere. Excel supports many data sources, from basic stuff like CSV, ODBC and OLE DB, to more specific and advanced knowledge of particular data sources and types.

A recent tweet from @msexcel showed a simple video  on how to grab data from a website – highlighting a capability that’s been in Excel for years but has been refreshed and made a lot easier to use.
Try this as an example:

  • clip_image004In a new Excel workbook, go to the Data tab and choose From Web
  • Try a web site that has data tables – eg
    https://en.wikipedia.org/wiki/World_population
  • clip_image006After confirming the authentication method to connect, Excel will retrieve the page and try to make sense of its layout – and let you choose the table you want to retrieve data from.
    This is part of a new “Get & Transform Data” set of functionality that’s available to Office 365 subscribers, that uses Power Query to retrieve data from a variety of sources, and can include some advanced transformation and editing of the query too. See here for a more detailed tutorial.

clip_image008If you want to revert to using the old data import methods, you can either enable the Legacy import wizards in Options (File > Options > Data > Show legacy data import wizards), or just type Legacy into the Search / Tell Me box in Excel, and see the available actions from there.
You can jump to the Search box quickly by pressing ALT+Q and jump straight to any Excel function, or get help on how to do many tasks.

clip_image010Another cool Office 365 data feature is to use Data clip_image012Types. Enter your data in a column, select it and choose the appropriate Data Type – let’s use Stocks as an example.

Once you’ve tagged the source data, clicking on the icon to the left of the data point will show a pop up with the background detail, or you can reference the fields within formulae to display or manipulate the data values.

clip_image014Each data source presents a number of fields that can be discovered and selected through autocomplete in a formula, and the values can be refreshed easily.

See more detail on using the Stock quotes functionality, here.

Leave a Reply

Your email address will not be published.