Category: Application

Posted on

Using Hyperlinks in an Excel worksheet

I like to have easy access to web pages that link to the data I’m working on.  I can copy and paste the web address into a column, which is fine, but takes up lots of space.

hyperlink-microsoft-excel-2007-intermediate
See, the web address takes up loads of space. Not very helpful.

A colleague showed me a good way to include my hyperlinks, where they are easily accessible within the document, but assigned to a word.

I type “go” and then use the hyperlink function to assign the web address to the word.  I click into the cell containing “go” and then right click to bring up the menu options.  I click on Hyperlink and another small screen appears where I can copy and paste the web address from my browser, or type it in manually.  Then click ok.

hyperlink-apply-microsoft-excel-2007-intermediate
I right click on the cell with “go” and select the Hyperlink option right at the bottom….

When I want to access the website quickly from within the document, I click the word “go” and it takes me to the page I want.  See how little space this needs, in comparison to putting in the whole address.

tidy-hyperlink-microsoft-excel-2007-intermediate
See how little space this now takes up.

When you put your mouse on the hyperlink text, Excel will show you the address as a quick way to preview the information.

hyperlink-preview-microsoft-excel-2007-intermediate
Preview of the web address, if I click on the hyperlinked text.

If you want to change or remove the hyperlink, just right click on the word you’ve assigned and make the changes or remove the link.

Hyperlinks aren’t just for web addresses, I can assign them to another worksheet location, or intranet page, or file.

So for more information on Microsoft Excel 2007 training, why not “go” to https://www.stl-training.co.uk/syl/28/excel-for-power-users.html and boost your Excel skills.

Posted on

The difference between Autofilter and Sort in Excel

What is the difference between Autofilter and Sort in Excel?  Why use one and not the other?

I found out the difference when I was trying to sort through an enormous worksheet and a colleague asked me why I wasn’t using the Autofilter.  I didn’t really want to admit that I didn’t know what it was…so I resorted to shrugging my shoulders and hoping that he would show me how it worked…which he did.  And so I share this little tip as it has saved me oodles of time.

When I use Sort to sort data in Excel, it sorts the entire table – which makes sense but might not be the best way forward.  Sort is great for arranging a client list alphabetically for example, or sorting from lowest value to the highest value – but it may not be the best method as data can still be buried among the other rows and columns of detail.

Autofilter gives me the option to view the bits of data I actually want, and filter out the ones I don’t.  So I can spot the trends, or analyse costs, or spot duplicate entries etc.  Now, I really like this.  It means I can easily find the data  I need (surrounded by white space which helps me read it all more clearly).

To filter data, I select the field name for the data I want to filter.  Go to the Home tab on the Ribbon and select the Sort and Filter button, and select Filter.  Excel will prompt the next stage by asking what you want to use to filter your data, and tick the ones you want or add a new filter (such as a text filter).  Excel then filters the data for you.  Easy peasy.

Sort-and-filter-microsoft-excel-training-courses
Sort and Filter, hidden on the Home tab…but so useful

This can be really helpful if you want to find records for a department’s expenditure (and filter out the information on other departments).  It’s really quick, so you can answer a colleague’s question on a key piece of data and then return to your normal worksheet view.  When you want to return to the view of all your records, click on the Sort and Filter button again and then choose Clear.

It may appear a simple difference, but so worthwhile to know, because you can use Sort or Autofilter to meet your needs. It may not be a question on QI, but it is undeniably useful.  They are just another little set of tools that you can use in Excel for  time-saving at work.  Check out what more you can learn to boost your skills with Microsoft Excel training courses https://www.stl-training.co.uk/microsoft/excel-training-london.php