All language subtitles for 4. Adding Navigation Buttons

Something that I didn't actually include in my golden rules for spreadsheet design and probably should

have, if I'm honest, is making your spreadsheets easy to navigate by adding things like navigation

buttons and links.

We really want to make our spreadsheets easy to navigate, as well as easy to understand.

And if you have a particularly large workbook that contains lots and lots of different worksheets,

you want to make sure that users have a quick way of jumping to the different worksheets and navigating

efficiently between the different tabs.

So let me show you a couple of examples, and then you can have a think about how you might implement

these.

So I've got this workbook here.

We have quite a few different worksheets running across the bottom.

And it might be if I go to the sales data worksheets, maybe I want to have a button on here which says

something like click here to see analysis.

And when I effectively want this button to do is when the user clicks on it, it's going to jump them

to the analysis worksheet so they can see the pivot table and the pivot chart that's been created based

off of this data.

Now I can implement this in a few different ways, and this is really a stylistic choice which you're

going to make yourself.

I could just add a piece of text, so if I click just here and I think in this example, this is going

to be a bit easier if I turn grid lines back on temporarily, I could just add a text based link.

So maybe I want to type in, please click here to see analysis.

Something like that doesn't look particularly interesting, but it does work.

I can select this text and I can create a hyperlink to the analysis worksheet.

Now we can create hyperlinks in a couple of different ways.

I could go to the Insert tab and all the way over in the links group.

We have a link button just here.

Keyboard Shortcut Control Plus.

OK, now this is going to open up the Insert HYPERLINK dialog box and there are tons of different things

you can link to.

From here, I could link to an external website, I could think to an email, or I can link to a place

in this document.

And that's exactly what I want to do.

So I'm going to select place in this document.

Now, notice it brings up on the right hand side all of the different worksheets that I have in this

workbook.

So it's very simple for me to just select the worksheet that I need, which is analysis in this case,

and I can even choose where I want their cursor to land on this particular worksheet.

So I'm going to leave this on a one when I could even do up here is I could add a screen tip so that

when the user hovers over this piece of text, it's going to show a little popup box that tells them

something like where this link is going to take them.

So maybe I just want to jump to the analysis worksheet, something like that and click on OK, so I've

selected my worksheet, I've selected the cell.

I've added a screen to it.

Let's click on.

OK, and now this is a hyperlink notice.

As I hover my mouse over it, my cursor changes to a little hand icon and there is that screen tip popping

up.

So if I click this link, it jumps me to the analysis worksheet and noticed that my cursor is in Cell

A1 by default.

So that is the first method I could use.

It works, but it's not particularly interesting.

So how can we make this a bit more interesting?

Well, we could create something that looks more like a clickable button, and we would do this simply

using shapes.

So up on the insert ribbon, let's go to shapes and I can choose any one of these.

Now, I think I'm going to use this one here, the rounded rectangle.

Let's grab that tool.

My cursor has changed to a small cross, and I can simply draw something which kind of resembles a button.

Now, of course, we have all of our usual formatting options on the shape format, contextual ribbon.

So if I want to change the color of that, I can.

Maybe I want to change it to something which matches the spreadsheet colors a little bit better.

I can change the shape outline and let's give it a dark green outline, and then I can add some text

now with shapes.

If you've got them selected, you can simply start typing to add some text, so let's say please click

here to see analysis.

I don't particularly like the font that's in use in this particular template, so let's change that.

Let's go to the home ribbon.

I'm going to change this to let's go with Ariel, and I need to make this a bit bigger.

And I also might want to move that text into the middle.

And of course, you can play around with this as much as you like.

You might want to make the font slightly bigger.

You might even want to add some shape effects to this to make it look even more like a button.

So if I was to add something like that, that kind of gives it more of a button look.

So now that I have my button created, I can simply click on the shape.

I could go up to insert and choose Link from there, or I could right click my mouse and go to link.

It's going to pop up the same dialog box we want to place in document, we want it to link to the analysis

workbook, but this time let's choose a different cell to link to.

So I'm going to say B 10.

I could add a screen tip if I wanted to.

I'm not going to in this case and click on.

OK, so now we have a clickable button.

When I click on this again, it's going to jump me to the workbook.

Take a look at where my cursor is Cell B 10.

And personally, for me, that looks a lot more effective than just the text.

Let's take a look at one more example, and this time I'm going to use something which was a reasonably

recent addition into Excel.

I say reasonably recent.

They've been around for the past few years, and that is using icons.

You can set up links on pictures, icons, 3D models, text shapes, pretty much anything you want.

So if we click on icons on the insert ribbon, maybe instead of a shape, I want to use one of the icons

from the Icon Library.

So I'm actually going to search for the word analysis.

And let's just use this icon and insert it.

I can put it somewhere down here, I might want to add some text to it to make it super obvious as to

what it's going to do.

But I think you get the idea.

Let's just set up handling this time.

I'm going to use the keyboard shortcut of control.

Click to open that dialog box.

We're linking to the analysis workbook.

And let's choose.

Let's choose A5.

This time I will add a screen to it for this one because it is simply an icon.

It's not so obvious.

Jump to analysis.

Click on OK, OK again.

And now this icon, when we click on it, is going to jump us to the relevant worksheet.

Now, as I said, you could insert a picture and set this up in exactly the same way you could insert

a 3D model, a different type of shape, lots of different ways that you can create hyperlinks.

One thing I find that sometimes quite nice to do, particularly if I have a workbook structure like

this, is sometimes I like to have just a little icon in the corner, which will enable users to jump

back to the how to use page.

So again, I might choose to use an icon and it's just search for a home icon just here.

Let's use this one.

Of course, I can change the color of this icon so that it matches my worksheet.

I want to make that a bit smaller.

I could place that in the corner or somewhere else on my worksheet, and I could copy this across to

all of my worksheets simply by doing Control C and Control V, and I want to place this in the corner

of all of them.

And then I'm going to set up a link control, OK, which is going to jump me to the how to use page.

Let's add a screen tab that says Jump to how to use click on OK, click on Okay again.

When we click it, it's going to jump us to that page.

So a few different methods there for creating clickable buttons to help you navigate around your worksheets

and workbook.