FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
March 24, 2016  
Greetings from The Excel Addict
Hi fellow Excel Addict,

Sorry for the delay in getting out this newsletter. Something came up and I couldn't get it out as scheduled for last Thursday. If you celebrate Easter, I wish you a happy and joyous Easter season.


In case you missed Tuesday's 'Excel in Seconds' newsletter, I showed you a keyboard shortcut that allows you to 'Scroll Sideways' by a full screen width in your worksheets. You can read it here.

In today's tip I'm going to share with you one of my favourite everyday tools that I have been using in Excel as long as I can remember. Today,
I'm going to show you how to create your very own personal hyperlinks page in Excel. I call it my 'MyLinks Page'. This page can significantly reduce the time you spend searching for and browsing for your most frequently-used files and other resources.

Having an easily-accessible workbook to store and organize 'one-click' links to your projects and data sources will change the way you work with Excel.

I originally published this back in 2013, so it's about time for an encore.

This is a lengthy tip, so you may want to print it out to follow along. But don't let the length of this tip be an excuse to skip it. I believe that this could be one of the best time-saving techniques you have ever learned from my newsletter.

If you find it useful, please feel free to share it with anyone else you think could benefit from it.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)





If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

“You have brains in your head. You have feet in your shoes.
You can steer yourself any direction you choose.
You’re on your own. And you know what you know.
And YOU are the one who’ll decide where to go…”

-- Dr. Seuss 'Oh, the Places You'll Go'


 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Create Your Own Personal MyLinks Page

March 28, 2016 Jessica C.
"Hi Francis, this is a tip that I read about the first time you sent it out (2103)& implemented right away.  Little else is more annoying at work than scrolling through the same set of folders multiple times a day.  It's been really useful over the years; I don't know how much time it's saved, but the frustration savings are off the chart!"

A personal hyperlinks page gives you easy, one-click access to the files and resources you use every day...and maybe even some you use less frequently and struggle to remember their locations.

Your Own Personal Excel Hyperlinks Page in Microsoft Excel 2007 2010 2013 2016 365
If you think about all the time you spend each day browsing to and searching for files, folders, websites, etc... it is probably significant.

Now imagine that you have an Excel spreadsheet containing links to all of these resources right under your nose, allowing you to access them in an instant. Think about how much time and frustration that would save you.

Some of the benefits of an Excel hyperlinks page are...

  • No need to remember all of the locations where your files are stored.
  • No more time-consuming browsing through the folders in the File/Open dialog every time you need to open a file or folder.
  • Easily organize into logical groups, links to related files and resources regardless of where they are stored.
  • One-click access to workbooks or any other type of document, whether they are on your computer, on a network drive or on the Internet.
Where to store your MyLinks Page and why
If you work with Excel all day long, I recommend that you store your MyLinks Page in your XLSTART folder.

The XLSTART folder is a special Excel folder on your computer where you can store files that you want to open every time Excel starts. This will ensure that your MyLinks Page is always available when you have Excel open.

Most Excel users work with multiple workbooks open at one time. Just leave your MyLinks Page open all day. If you are working in a different workbook and need to access a link, you simply click the MyLinks button in the Taskbar then click the link.

How to create your own personal MyLinks page
Basically a MyLinks Page is just a regular workbook with a bunch of hyperlinks added to it.
You can start yours from a blank workbook or you can download my sample workbook below.

1) Open a blank workbook (or download my sample workbook here);

2) Click File, Save As;

3) Click Browse to open the Save As dialog;

4) Browse to your XLSTART folder...


Quickly Locate Your XLSTART Folder

The XLSTART folder is often difficult to find. It may be in a different location depending on your computer's operating system and it is often in a hidden directory. Here is a quick and easy way to locate the XLSTART folder on most Windows computers.

1) In the Save As dialog, type the following path into the 'File name' field or you can highlight and copy (CTRL+C) it from here and paste (CTRL+V) it into the 'File name' field...

%APPDATA%\Microsoft\Excel\XLSTART

2) Press Enter and the XLSTART folder will open in the Save As dialog.
Path To XLSTART Folder in Microsoft Excel 2007 2010 2013 2016 365


4) At this point, you can change the name of the workbook if you want, then click OK to save it.

If later you decide that you don't want your Hyperlinks workbook to open every time you start Excel, you can move it from your XLSTART folder to your My Documents (or some other) folder.

To quickly locate your XLSTART folder, Paste %APPDATA
%\Microsoft\Excel\XLSTART into your Windows File Explorer address bar.

How to Create Hyperlinks for your MyLinks Page

Hyperlink to a Web Page

1) Think of a website that you often need to access when you are working with Excel (e.g. banking, stock info, financial stats, etc...) and open that web page in your Internet browser;

2) Highlight the address from your browser's Address Bar, right-click and select Copy (or CTRL+C) to copy the web page address to the Clipboard;

3) Go to your MyLinks Page and select the cell where you want to place the hyperlink;

4) Press CTRL+V to paste the website address directly into the worksheet cell. Sometimes the hyperlink will be created automatically, other times it may not. If the text in the cell changes to an underlined, blue font you have a live hyperlink. You can then change the text displayed in the cell without affecting the hyperlink.

To select a cell without activating the hyperlink, click the cell and hold down the mouse button for a second or two.

If the hyperlink isn't automatically created, goto Plan B.

Plan B: The web page address is still stored on your Clipboard. With the cell selected where you want the link, press CTRL+K (keyboard shortcut for Insert Hyperlink) and the Insert Hyperlink dialog opens. Click in the Address field at the bottom of the dialog and right-click, Paste (or CTRL+V) to paste the website address. Edit the 'Text to Display' field at the top of the dialog to display whatever you want to see in your hyperlink cell. Then click OK.

Create a Spreadsheet Hyperlink to a Website in Microsoft Excel 2007 2010 2013 2016 365

Hyperlink to a place (i.e. cell or defined name) in the current workbook

You can use hyperlinks to create a simple navigation system for getting around a workbook. For example, you could create a table of contents on Sheet1 with links that go to each sheet in the workbook, and on each sheet, create a link back to the TOC.

To create a hyperlink to a place in the current workbook...

1) Select the cell where you want to place the hyperlink;

2) Right-click and select Hyperlink (or press CTRL+K) to open the Insert Hyperlink dialog;

3) Make sure that 'Existing File or Web Page' option is selected in the 'Links to' area on the left of the dialog;

4) Click the Bookmark... button on the right side of the dialog;

5) Select a sheet name or defined name. If you want to hyperlink to go to a cell other than A1, type the cell reference into the field at the top of the dialog and click OK;

6) On the Insert Hyperlink dialog, you can change the text in the 'Text to Display' field if you want, then click OK.

Hyperlink to a folder

Think of how many times you open folders in Windows File Explorer as you are working in Excel. Creating hyperlinks to your frequently-used folders allows you to go to them immediately, right from Excel.

To create a hyperlink to a folder...

1) Select a cell on your MyLinks Page where you want to place the hyperlink;

2) Press CTRL+K to open the Insert Hyperlink dialog;

3) Select the 'Existing File or Web Page' option in the 'Links to' area on the left of the dialog;

4) Browse to and select the folder you want to link to. You will see the folder's path in the Address field at the bottom of the dialog and also in the 'Text to display' field at the top;

5) In the 'Text to Display' field, type any text you want to display in the hyperlink cell;

6) Click OK.

Hyperlink To Folder in Microsoft Excel 2007 2010 2013 2016 365

Hyperlink to another workbook

Rather than waste time navigating through a bunch of folders every time you need to open a workbook that you use frequently, why not have a hyperlink on your My Links Page so you can open it in one click?

To create a hyperlink to a workbook...

1) On your My Links Page, select the cell where you want to place the hyperlink;

2) Press CTRL+K to open the Insert Hyperlink dialog;

3) Select the 'Existing File or Web Page' option in the 'Links to' area on the left of the dialog;

4) Browse to and select the workbook you want to link to. You will see the file path in the Address field at the bottom of the dialog and also in the 'Text to display' field at the top;

5) In the 'Text to Display' field, type the text you want to display in the hyperlinked cell rather than the full path and filename;

6) Click OK.

Create Hyperlinks to Other Document Types

You can also create hyperlinks to other document types on your MyLinks page as well using the previous steps. When you click the link on your MyLinks page, the document will open in its own application.

Paste as Hyperlink

In earlier versions of Excel there was a Paste as Hyperlink option that enabled you to easily create hyperlinks. That disappeared in Excel 2007. Now, if you want that option, you need to add it to your Quick Access Toolbar.

To add the 'Paste as Hyperlink' option to your Quick Access Toolbar (QAT)...

1) From the File menu click Options;

2) Click Quick Access Toolbar and from the 'Choose commands from' dropdown list, select All Commands;

3) Scroll down and select the Paste as Hyperlink command and click the Add>> button to add it to your QAT.

Add Paste As Hyperlink Option in Microsoft Excel 2007 2010 2013 2016 365


To Paste a Hyperlink into a worksheet (using the Paste as Hyperlink command on your QAT)...

1) Open the workbook you want to link to, then select a cell in the sheet tab that you want the hyperlink to jump to;

2) Press CTRL+C to Copy;

3) Switch to your MyLinks Page and select the cell where you want to place the hyperlink;

4) Right-click the cell and click the Paste as Link command on your Quick Access Toolbar;

5) If there was text in the cell that you copied, that text will be used in the hyperlink cell. If the cell you copied was empty or contained numeric data, the entire workbook path and cell reference will be pasted into the hyperlink cell. You can simply type over that text in the cell to replace it.


 Change Hyperlink Text Displayed in Microsoft Excel 2007 2010 2013 2016 365

Moving your Hyperlink Cells

After you have created a hyperlink, you may want to move it.

1) Select the cell containing the hyperlink;

2) Hold down the SHIFT key and point to the edge of the cell until the mouse pointer changes to a four-headed arrow;

3) Click and hold your left mouse button and drag the cell to a new location;

4) Release the mouse button, then release the SHIFT key.


Using Images and Shapes for your Hyperlinks

You are not limited to storing your hyperlinks in worksheet cells. You can also attach a hyperlink to an image, shape or some other object.

1) Copy an image into your worksheet or create a shape (Insert, Shapes);

2) Right-click the image or shape and select Hyperlink (or press CTRL+K);

3) Follow the instructions above for whichever type of hyperlink you need (i.e. current workbook, another workbook, folder, website, etc...)

Use Images And Shapes For Your Hyperlinks in Microsoft Excel 2007 2010 2013 2016 365
Changing the formatting of your hyperlinks

Hyperlinks created in Excel, by default, have a blue, underlined font.
When you click a hyperlink, the font color of the 'followed' hyperlink changes to purple. If you want to change the way your hyperlinks are formatted in your My Links workbook, you need to modify the Hyperlink and/or the Followed Hyperlink cell styles.

Note that the Hyperlink cell style is available only when the workbook contains at least one hyperlink. The Followed Hyperlink cell style is available only when the workbook contains a hyperlink that has been clicked.

To modify the formatting of your Hyperlinks in the current workbook...

1) On the Home tab, in the Styles group, click Cell Styles;

2) Right click on the Hyperlink* style and click Modify...;

3) In the Style dialog click Format... button;

4) In the Format Cell styles dialog, click the Font tab, make the changes you want, then click OK and OK.

* Repeat the steps above to change the Followed Hyperlink style.


Modify Hyperlink And Followed Hyperlink Cell Styles in Microsoft Excel 2007 2010 2013 2016 365

Saving Changes to Your MyLinks Page

With your MyLinks Page 'always open', when you close Excel you may be prompted to save changes even though you may not have made any. My rule for this is to always click No to the 'Save Changes?' prompt for my MyLinks page when I am closing Excel. The reason I click NO is because I don't want to save any changes I may have inadvertently made to the page throughout the day. I only click Yes when I know I've made changes that I want to keep.

So, always save changes to your MyLinks workbook as you make them.

Backup Your MyLinks Page Workbook

If you're like me, your hyperlinks page will likely become a very important part of your Excel life. If your Hyperlinks Page is stored in your XLSTART folder, it is important that you occasionally save a backup copy of the workbook to a different folder, such as My Documents or a flash drive, in case your hard drive crashes or you get a new computer. Most IT people aren't aware of Excel's XLSTART folder and may not save and restore the files from there.

You don't want to lose all the work that you have put into your MyLinks pages over many months or even years.

The 'Not So' Final Product

Now that you have some hyperlinks added to your MyLinks page, you can do some formatting to 'pretty it up'. The only limitation is your imagination and creativity.

Remember, this is not just a one-time setup process. You will be continually adding and deleting hyperlinks to your MyLinks page. Over the years, my personal MyLinks page has evolved into a multi-sheet workbook, with hundreds of links, with each sheet containing links for a certain topic, and on each sheet links are further broken into related groups.

Deleting your MyLinks Page from the XLSTART Folder

If you ever need to delete your MyLinks page, here's how...

Type or 'Copy and Paste' %APPDATA%\Microsoft\Excel\XLSTART into the Address Bar of Windows File Explorer or in the File Name field in Excel's Open or Save As dialog boxes. Then press Enter and you'll be taken to the XLSTART folder where you can simply right click and delete it.

Deleting a file from your XLSTART folder in Microsoft Excel 2007 2010 2013 2016 365

Creating a Hyperlink to Your XLSTART Folder

If you'd like to create a hyperlink to you XLSTART folder you cannot use the
APPDATA%\Microsoft\Excel\XLSTART path. You will need to first locate the actual XLSTART path and then copy that for your hyperlink address.

Final Thoughts
I know this has been a really long tutorial but believe me, setting up your basic MyLinks Page isn't that difficult but it is definitely going to be worth it. First, get the template set up so that it opens whenever you open Excel. After that, you can modify it whenever you have time.

When you get your MyLinks Page set up, please send me a screenshot. I'd love to see what you've done.



Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630