When I was a kid, every Thursday night I’d be glued to Top of the Pops. If you’ve not heard of it, it was a weekly music TV show in the UK broadcast by the BBC. During the show they would run through that week’s Top 20.

BTW, if you’re wondering, the picture above isn’t me. It’s AI-generated!

Of course these days if you want to see what’s in The Charts, you need look no further than the plethora of websites such as official charts.com but it was the memories of those Thursday nights that inspired me to create the Top 10 Singles Tracker that is the subject of this week’s YouTube video.

If you’ve read this far (they do say a good story is a way to hook people in) you’re probably wondering what has this got to do with pulling non-tabular data into Excel from web pages. Read on…

To create the Top 10 Singles Tracker, I needed a data source. As I said there are many websites that you can pull the information from and I chose to use official charts.com.

When getting data from a webpage (using Data > From Web) Excel only recognises data stored in traditional HTML Tables i.e. Tables generated with the HTML <TABLE> tag.

Unfortunately data on web pages is often not stored in HTML Tables. The Tables may have been generated on the fly using a scripting language, which means that extracting data from such pages can be difficult, if not impossible.

There is no way to pre-check how the data is stored so it’s a case of clicking Data > From Web and typing or pasting the URL. Excel will display any HTML Tables it finds on the page. Clicking on the name of the Table displays a preview of the Table’s contents. By comparing the preview with the data on the web page you can see if the data it has detected is the data that you want to load into Excel.

So what do you do if Excel doesn’t recognise the data source? Not much you can do. Until now.

The Power Query Web Connector which powers Data > From Web, has recently undergone some improvements. In addition to detecting HTML-based Tables, it now presents you with “Suggested Tables” which Microsoft defines as Tables from the web page that aren’t necessarily created with the <TABLE> tag.

There’s also a new AI-powered feature called Add Table Using Examples, which displays a dialog box where you enter sample values of the data you want to extract and Excel tries to recognise a pattern in the data and automatically complete the rest of the rows.

If you can’t envisage how this would work, check out the video.

Finally, before you get too excited, I think these new features are currently only available to users on the MS Office Beta Channel. If you are on the Beta Channel you may still not have access to them as Microsoft offer new features to random users on the Beta Channel.