Management information specialist and Excel detective
Excel Hints
Removing all hyperlinks from a spreadsheet
Sep 30th
The other day I retweeted a handy tip from Tom Urtis:
It can be frustrating if you have a large number of hyperlinks in an Excel spreadsheet which you want to remove as usually you’d have to go through each one individually to delete them all. This solution is a simple piece of VBA to go do them in bulk and is a great example of a quick piece of code as well as giving a few examples of handy shortcuts.
Make sure that you’ve got the spreadsheet open, with the active sheet being the one with the links on, then follow the steps above in Tom’s tweet. Broken down the shortcuts do the following:
_____________________________________________________________________________________
ALT + F11 Opens the VBA (Visual Basic for Applications) Editor.
CTRL + G Displays the Immediate Window, a place used often for debugging macros, but in this case a handy way of executing a single code immediately.
Type: ActiveSheet.Hyperlinks.Delete Does exactly what it says – checks the active sheet for hyperlinks and then deletes them
Enter Executes the code
Alt+Q Closes the VBA editor
_____________________________________________________________________________________
You should be left with a sheet with no hyperlinks, just their values – be warned if you want to do this on more than one worksheet in the same Excel workbook you’ll need to repeat it and also that you can’t undo this using the standard CTRL + Z. Perhaps a good opportunity to use Save As?
The power of Save As and your Excel spreadsheet
Sep 27th
I often tell people that F12 is one of the most useful keyboard shortcuts for any of the Microsoft products, including Excel, and certainly it is one of the ones that has been most useful to me. But why is Save As so important?
It’s simply a way of saving your spreadsheet or document in another way. This could either be with another name, or possibly into a different format (either a previous version of the software, or perhaps in an entirely different form such as pdf or .txt)
For me it’s using it to save a spreadsheet as another name which has been so useful, this gives the opportunity to have an “Original” version of a spreadsheet to return to whilst you try out some new functions and take your analysis in different ways with no fear of harming your source data. You can always return to the first version if needed.
Just a word of cation though, firstly make sure your new file names are meaningful and secondly that you don’t get click happy using F12 too often, ending up with multiple copies with helpful titles as “ORIGINAL Version 1 FINAL” or “Wednesday version 3 FINAL original use this one”.
Do you Save As and find it useful? What’s your favourite shortcut? Do leave me a comment below
Microsoft Excel Shortcut Tweet Roundup w/c 19th September
Sep 23rd
Autumn is here and in between turning produce from the allotment and hedges of shropshire into soups, chutneys and other harvest delights I’m going to start back tweeting Excel shortcuts. Hopefully these shortcuts, along with other hints and tips I’ll blog / tweet about will give you some ideas as to how you can streamline your work and make Microsoft work for you. A roundup of tweets I’ve previously made is here, but this week I covered:
_____________________________________________________________________________________
Selects the entire active row SHIFT + SPACEBAR
Selects the entire active column CTRL + SPACEBAR
Undo! CTRL + Z (NB: Very handy and works across microsoft office programmes)
Redo CTRL + Y
Opens the format cells dialog box CTRL + 1
_____________________________________________________________________________________
Any suggestions for things that you would like to see then please do get in touch leaving a comment below.
Microsoft Excel Shortcut Tweet Roundup w/c 1st August
Aug 5th
This week’s shortcut tweets were
_____________________________________________________________________________________
Enter current date CTRL + ;
Open pivot table wizard ALT + D then P
Sort data ALT + D then S
Move between tabs in a workbook CTRL + PageUp or CTRL + PageDown
Open Spellcheck F7 NB: this works in other Microsoft Office products too, such as Word, Outlook and Powerpoint
_____________________________________________________________________________________
There will be more next week, but in the meantime what are your favourite shortcuts?
Microsoft Excel shortcut round up
Jul 29th
Periodically I post on twitter some handy keyboard shortcuts for Microsoft Excel. I am going to try and make this a more regular occurance with weekly roundups, so in case you’ve missed those I’ve tweeted so far, here they are:
_____________________________________________________________________________
Add a border around selected cells CTRL + SHIFT + &
Remove a border from selected cells CTRL + SHIFT + _
Add a comment to a cell SHIFT + F2
Hide a row CTRL + 9
Unhide a Row CTRL + SHIFT + (
Hide a column CTRL + 0
Unhide a Column CTRL + SHIFT + ) NB: if you’re using Windows Vista or Windows 7 this probably won’t work. See here for why and a way to get around this
Convert selected cell to a currency format CTRL+SHIFT+$ NB: this coverts to the default regional setting, so if you’ve UK settings will be £ despite the $ used in the shortcut!
_____________________________________________________________________________
I hope you find these useful, I’ll continue to post them regularly but what’s your most used keyboard shortcut? Let me know in the comments below.