Annielytics.com

I make data sexy

You are here: Home / Excel / How to Dynamically Generate URLs on the Fly in Excel or Google Sheets

How to Dynamically Generate URLs on the Fly in Excel or Google Sheets

a group of people in a factory

Why Include Dynamic Links in Dashboards

When I create dashboards, I like to add as many resources as possible for stakeholders who want more information. And it’s important to make your dashboards as self-sustaining as possible. If you have to be there to walk viewers through your dashboard, you’re going to run into issues because once you loosen your grip on it and send it out into the wild, there’s no telling whose desk it’s going to show up on — or in what for s/he will view it.

For internal dashboards, a line item may warrant additional research. To wit, someone might see a report from a tool like SEMRush (one of my favorite marketing tools) or Google Search Console and want to research it further. To facilitate this research (or even discussion), you can create dynamic links to individual reports.

Here are just a few ways you can use these links:

  • Link to a specific Google Analytics report — e.g., landing page, referral, channel, PPC keyword, etc.
  • Link to the Queries report in Google Search Console** (GSC) to get data on a particular keyword — under Search Traffic > Search Analytics
  • Link to the Pages report in GSC — also in the Search Analytics report — to see the keywords generating impressions and clicks for each URL in the report
  • Link to the Internal Links report in GSC to investigate internal linking patterns
  • Link to Google’s Mobile-Friendly Test for a report with a list of pages from your site to spot check pages to see if they’re mobile friendly
  • Link to SEMRush reports — e.g., keywords, landing pages domains — to get competitive data
  • Link to Buzzsumo reports to research popular articles for particular topics, pages on a domain, etc. (Tip: Use data validation to create a drop-down menu that allows dashboard users to choose recency (e.g., past 24 hours, week, month, six months, year) to save yourself a step when you click through to Buzzsumo.)
  • Link to Newswhip to see trending topics

You get the idea. Many marketing tools use unique URLs for their individual reports, and any that do can be included in your dashboards to provide easy links to learn more.

** FKA Google Webmaster Tools

Examples from the Video Tutorial

SEMRush

I start the video off with a simpler example using keyword reports in SEMRush.

keyword report in SEMRush

Using a helper column, you’ll learn how to dynamically create a link to the keyword report for each keyword listed that will ultimately look like this: http://www.semrush.com/info/funky+shoes.

Google Analytics

Things get a little more intense with this example because, unlike the SEMRush example that only has one variable element (the keyword), the Google Analytics reports we will dynamically create links to will have three variable elements:

  • Landing page
  • Start date
  • End date

Also, Google Sheets has a handy feature for creating date pickers that makes this process a breeze for your dashboard users. I show you how to use the TEXT function to convert these dates to the format used in the Google Analytics report URLs.

Date picker in Google Sheets
Click for larger image

You can get a date picker in Excel, but it’s much more complicated (thanks, Obama). In Excel 2013 for PC users you can use a form control (learn more), or for 2011 Mac users you can use an add-in (learn more).

Brace yourselves. This example requires multiple steps. But I walk you through it step by step.

Dynamic URLs in Excel and Google Sheets
Click for larger image

Functions Used

By the time you get through this video you’ll have an understanding of how to rock the following functions to create dynamic links:

  • SUBSTITUTE
  • IF
  • HYPERLINK
  • TEXT
  • RIGHT
  • LEN
  • SEARCH

Practice File

If you want to follow along, you can use the practice file I use in the video. If you want to use it for yourself, create a copy by logging in to Google and going to File > Make a Copy.

Video Tutorial

You can view the video below. I’ll warn you: This video is longer than most of mine because I wanted to demonstrate a more complicated example. But if you understand these two examples, you should be able to dynamically generate links to any marketing tool that uses a custom URL to generate the report.

Learn More

For more beastly data visualization tips, check out out my Annielytics Dashboard Course offerings.

Image Credit: Arno Senoner on Unsplash

Comments

  1. Wonderful post, and video tutorial. I am working on creating a spreadsheet to track multiple pieces of content across multiple platforms. I want to include these wonderful dynamic links, and variations there of.

    Is it possible to do all of the work on a workbook, with all the helper columns etc and then have the final pretty See Report appear on another workbook? Is there any way to automate that or does it come down to just a copy/paste of the final value?

    Reply

  2. Interesting! Thanks for sharing the video and giving us more insights about it. I believe this really works not just to give more information to clients but to your own advantage as well. It one way to organize urls or your own links so you won’t be having a hard time looking for resources if you might need one.

    Reply

  9. Dear Annie,

    Thank you for this wonderful video. I can easily call it a life saver. Thank you again and looking forward to watch more tutorials from you.

    Best regards,

    Alan

    Reply

  10. Literally exactly what I was looking for tonight to generate specific urls based off of a few bits of data for my accounts. Thank you!

    Reply

  11. Hi Annie,

    Thank you so much for such ever simple, clearer and beautiful instructions to make our requirements come true.
    Really enjoyed this post, i’ll be connected to learn such beautiful things.

    Thanks again 🙂
    Cheers!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Privacy Policy