How to Get Google Trends Data Into Excel

Want to find the latest trending items on Google? You could go to trends.google.com, or you could import that data into your Excel file. In this post, I’ll show you how.

To get the latest trending topics on Google, you can go to https://trends.google.com/trends/trendingsearches/daily?geo=US. You can adjust the string at the end if you want to see trends from a different country, but the link I’m using here is for the U.S.

Use Power Query to import the data

One of the most effective ways of getting data into Excel from an external site is by using Power Query. The key is finding the right link. On the Trending Searches page in Google, there’s an RSS button that you’ll want to click on:

Then, you should see the following URL:

https://trends.google.com/trends/trendingsearches/daily/rss?geo=US

That is the URL you’ll need to use in Power Query. Back in Excel, go to the Data tab and select the From Web button. Put that link in the following box:

When the Power Query window loads up, you might be tricked into thinking there is nothing there:

Expanding the data

The data is there, it’s just contained within a table. Click on Transform Data at the bottom of the Power Query window. Then, click on the icon in the channel field that shows that there are items within there that you can expand:

Click on the OK button in the following dialog box where you can specify all the columns you can add. There are many embedded tables here and if you want to see what’s contained within them, simply click on one of them that says Table. For example, if I click on the Table shown under the channel.item field, I see all the values contained within that table:

Within here, I can see that there are still multiple tables that I can expand, and there’s also a title column that looks to contain the actual trending item name. You can continue expanding depending on the fields you want to see. You can expand everything, and then at the end select which columns you want to keep. If you do expand everything, you’ll end up with some unnecessary columns such as channel.link which contains the same repeating value. To remove a column, simply right-click it and click on Remove:

Repeat this step for all columns you want to eliminate. Alternatively, you can select the columns you want to keep, and then select the option to Remove Other Columns. Either approach will work the same. The columns that I’ve chosen to keep include the title, the number of searches, the two description columns, as well as the time of publication:

Cleaning up the table

There are a few items I can clean up to make this import nicer into Excel. The first is to make those numbers actually read as numbers. Because of the + sign at the end, I can’t convert them into whole numbers. What I will do is select the entire column, and then click on the Repace Values button under the Transform section in Power Query. Then I am effectively doing a find and replace, to swap the + signs with nothing:

Now, the format of that column can be changed to a whole number. I can do that by clicking on the ABC letters (indicating it is a text field) and selecting the option for Whole Number:

Upon doing so, you’ll notice the values align to the right, meaning they are now being read as numbers. Now they can be sorted in descending order so that the most popular items are at the top.

Another change I will make is to replace some more values in the two description fields:

• Replace ' with an apostrophe (‘)
• Replace   with a space (‘ ‘)
• Replace " with quotation marks (“)

There are also some duplicate items in the title field that I’ll remove. To do that, right-click on the field name and select Remove Duplicates. The last thing I’ll do is re-name the headers.

This is what my Power Query table looks like after all these changes:

Now I can click on Close & Load to get the data into Excel:

Now, all you need to do to load the latest trending data into Excel is just to refresh the query.

If you liked this post on How to Get Google Trends Data Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.