Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #312, your Monday morning espresso, in spreadsheet form!

My sons go back to school this week and I'm looking forward to getting back to a regular routine. The summer was amazing but loooong. After 11 weeks, we're all ready for school to begin.

In this newsletter, we're looking at how to use hyperlinks in Google Sheets. And, if you're an advanced user, I've got a spicy formula for you at the end of the newsletter.

Finally, I'm always in need of Google Sheets problems to address in these newsletters! ;)

So, if you've got a burning Google Sheets question or a killer tip that you'd love to share, let me know. I can't promise that I'll publish it. But your input is super valuable to keep the newsletter relevant.

Brought to you by: SheetsFinance Access premium financial data directly from Google Sheets SheetsFinance connects your Google Sheets to real-time and historical market data through a suite of powerful functions, free templates and in-built tools, allowing you to build dynamic dashboards, watchlists, portfolio trackers and valuations in a matter of minutes. With 30+ years of detailed data for over 80,000 global stocks, crypto and more – join 1000s of investors benefitting from data-driven decision making. Try free for 15 days

➜ News

I.
​Look out for a new "Convert to table" prompt in Google Sheets, rolling out over the next few weeks.

It's an automated way to transform your datasets into the new Tables format, with all the inherent benefits.

​Read more here >>​

II.
​In last week's newsletter we looked at the tricky GETPIVOTDATA function.

I've taken that newsletter and expanded it into a deep-dive post about the GETPIVOTDATA function.

​Read it here >>​

➜ Sheets Tip #312: Links 🔗

In this tip, let's talk about adding links to our Sheets.

We can add external links to other sites (even other Google Sheets!) from our current Google Sheet.

Or we can add internal links to help with navigation within our current Google Sheet.

And for you advanced users, I've got a spicy hyperlink formula to share with you at the end of this post...

Adding Links

Creating links in Google Sheets is easy-peasy.

Here's how... are you ready for this... drumroll please...

• Copy the URL and paste it into a cell of your Sheet.
• Press Enter twice to activate the link.

💥 Boom! That's it!

You can modify what the link looks like in the Sheet. Click on the link cell and then click on the pen symbol to edit the link. Replace the long URL with a short, friendly name.

Here's what this looks like in action:

You can also add a link by right clicking on a cell. Then select "Insert link" from the menu and add the URL and name.

Adding Links with Hyperlink Function

You can also use the HYPERLINK function to add links, which looks like this:

=HYPERLINK( "<URL>" , "<FRIENDLY NAME>" )

Here's an example:

=HYPERLINK("https://www.benlcollins.com/spreadsheets/getpivotdata/","GETPIVOTDATA tutorial")

Adding Multiple Links in Single Cell

One last thing, you can have multiple links inside a single cell!

To do this, add the URLs or names to your Sheet, then highlight the separate sections and select the link symbol in the toolbar. Add the URL for each link separately.

This image shows the process:

Adding Internal Links

Internal links are links that take you to other parts of your Google Sheet.

A classic use case is to build an index page if you have a lot of Sheets in your Google Sheet file. This lets users navigate easily to other parts of the Google Sheet.

To add an internal link to a cell:

• Right click to bring up the menu
• Select "Insert link"
• Click on "Sheets and named ranges →"
• Select a cell or range to link

Alternatively, go to the destination cell first (i.e. where you are trying to link to) and right click. Then select:

• "View more cell actions" > "Get link to this cell"

That spicy HYPERLINK formula I promised 😎

This formula uses creates a link to jump us to the next blank row of a data table!

It might be useful with big data tables that require frequent data entry.

Here's how it works:

When you click on "Add Data" it takes you to the bottom of the dataset, to the next blank row.

If you add new data, then the next time you click on "Add Data" it takes you to the new bottom of the dataset.

Nice!

And this is the formula:

=HYPERLINK( "#gid=0&range=A" & ArrayFormula( MAX( IF( ISBLANK(A2:A), 0, ROW(A2:A))))+1 , "Add Data")

To make it work, you might need to change the "gid" number (0 in this example) to match the "gid" shown in the URL of your Google Sheet (e.g. might be something like "1001194938").

It works by finding the row number of the last non-blank cell, then adding that to "A" to create a cell reference, e.g. "A44".

This is fed into the HYPERLINK function.

Spicy stuff! 🌶️

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. The real reason it's called the world wide web 🕸