Posts tagged productivity

Using Autofilters in Microsoft Excel
Dec 2nd
Following a hefty chunk of data analysis the last few days, CTRL + SHIFT + L has probably been my most used shortcut this week. This activates data filters on your Excel spreadsheet – alternatively you can switch them on and off by going to Data then Filter on the ribbon (or toolbar pre Microsoft Office 2007). THe shortcut however really comes into its own on toggling the filters on and off, saving time clearing them each time.
If you’re not already familiar with filters then I thoroughly recommend you check them out, data filters (or autofilters) are a simple way of helping drill down through your data. Take for example the following spreadsheet which is based on the Nobel Peace Prize winners (taken from the Guardian’s DataBlog)
Let’s assume that to start with we wanted to just drill down to find out who were the UK winners. Activate the filters by either using the shortcut or by going to Data on the ribbon and choosing the filter option on versions 2007 and 2010.
By using the data filter we can click on the little arrow on the country column and select just the UK. Suddenly all other data becomes hidden and only those winners from the UK are shown.
You can see that the filter has been activated because there is a small filter symbol rather than an arrow (to indicate which column has been filtered). In addition the row numbers are also highlighted in blue (see the pic above). If you want to you can also drill down further by using other filters, for example to see which UK institutions have got the award just use the filter on column F, “institution or individual”.
Sometimes though you can get carried away with layering multiple filters on different columns and as I said above, this is where the shortcut really comes into its own. Rather than having to go through all the filters to uncheck them if you wish to go back to display all data in the original list just use CTRL+SHIFT+L to toggle the filters on and off.
Don’t forget to have a play with some of the other options on the filters – in Excel 2007 and later versions you can use multiple options to drill down, selecting for example particular years by checking and unchecking boxes:
You can also use the autofilters to pull out data such as the top 10 largest values, or values between certain criteria – choose the Number filters option and then select which options you’re after.
Just a few suggestions here but using the data filters are a brilliant way to help you pull out particular strands of data or groups on your spreadsheet, do give it a try: CTRL + SHIFT +L
Microsoft Excel Shortcut Tweet Roundup w/c 10th October
Oct 14th
In an almost unprecedented frenzy I blogged twice this week: once on wildcard searching in Excel, and also on finding out the day from a given date in a spreadsheet. As usual I tweeted a number of shortcuts which are as below, and was delighted to bump into someone this week who thanked me for tweeting the shortcuts, CTRL + K for hyperlink in particular. It’s good to know they’re being used! Here’s this week’s roundup:
_________________________________________________________________________________________
Toggle Office Ribbon CTRL + F1
Find CTRL + F
Replace CTRL + H
Open Text to Columns Dialog ALT + A then E
Select All CTRL + A
_______________________________________________________________________________________________
That’s it for me, I’m off to London to revisit some old haunts frequented before our move to Shropshire. Have a lovely weekend!
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.

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