Management information specialist and Excel detective
Jennifer Deacon
Jennifer is the Management Information Specialist, Excel Detective and Microsoft Trainer at Deaconsulting. Find out more by visiting www.deaconsulting.co.uk
Homepage: http://www.deaconsulting.co.uk
Posts by Jennifer Deacon
Microsoft Excel Shortcut Tweet Roundup w/c 3rd October
Oct 7th
So this week’s Excel shortcut tweets turned out to be a celebration of F11:
______________________________________________________________________________________________
Insert a new worksheet SHIFT + F11
Delete the current sheet ALT + E then L
Activate Chart tool / wizard F11
Open VBA Editor ALT + F11
_______________________________________________________________________________________________
You may wonder what CTRL + F11 does. This inserts a new macro worksheet which is a relic back of the Excel4 days. Yes almost 20 years since Excel 4, when macros were written in a particular worksheet rather than the VBA editor, this shortcut still exists. As far as I know the new worksheets (titled Macro1 etc) don’t have any real use apart from for compatibility with the older version, unless you know to the contrary? Leave me a comment below as I’m intrigued if they’ve other uses.
Microsoft Excel Shortcut Tweet Roundup w/c 26th September
Sep 30th
Another Friday, another round up of my Excel and other Microsoft Office shortcut related tweets for the week in case you missed them:
_____________________________________________________________________________________
Add in a hyperlink CTRL + K (works across the Office platform)
Save As F12 (see my blog post here on why I think it’s one of the most useful shortcuts)
Enter the Current Date CTRL + ;
Enter the Current Time CTRL + SHIFT + ;
_____________________________________________________________________________________
I also retweeted a nice solution from Tom Urtis on how to remove mutiple hyperlinks in a spreadsheet in a few keystrokes and then blogged about how the VBA works here.
Any suggestions for Excel (or indeed other) tasks that you’d like to know a shortcut for then just leave me a comment below.
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.