Management information specialist and Excel detective
Posts tagged excel tutorial
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.
Spreadsheet filtered using the Autofilter
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
Removing line breaks within a cell
Nov 18th
Did you know that you can put line breaks inside excel cells? It’s quite straight forward, within the cell just click ALT + Enter to make the line break. An example is if an address is entered in a single cell:
Personally it’s not something that I would use often as it makes the data harder to extract and analyse, but what happens if you have a spreadsheet has these incell line breaks, and need to break them out into columns? In the example here you may want to do an analysis on the geograhic spread or want to do a mail merge so want a list with columns for towns, counties etc.
If the lines were in separate cells you could use the paste special > transpose option to convert vertically listed data items into horizontal and vice versa however in this instance it won’t work as all the data is in a single cell. Instead what we need to do is remove the line breaks.
Using ALT + ENTER means that Excel inserts a line feed character, or ASCII CHAR(10) which shows as a new line. We need to remove these instances of CHAR(10) which appear invisible on screen and replace them with something else to act as a delimiter which we can then use to break the data into columns.
- To start with add a new helper column on your spreadsheet, in this instance it would be column B and type the formula =SUBSTITUTE(A1,CHAR(10),”£”)
- Fill this formula down against the rows that you have. This formula searches the cell indicated (eg cell A1) for all instances of CHAR(10) and substitutes them with £
- You can then use the Text to Columns function to break the data into columns using £ as the delimiter. You can of course use any character you like rather than the £ however make sure that it’s something you know doesn’t already appear in your data as otherwise the columns may break in the wrong places. And watch out for using the wildcard characters too (see my previous post on searching for wildcard characters)
The other alternative is to use find and replace to identify the line feed breaks which may be more appropriate if you have a large dataset with line breaks within the cells that need removing in a number of different columns. across the spreadsheet. However if you open the find and replace dialog box (CTRL + H) and type ALT + ENTER in the find box what happens? Erm, yes nothing – it just moves to the next box. Instead in the find box hold down ALT and type 010 then move the replace box and type in what you want the delimiter to be – eg £. Click Replace all and it should do the same as above but without the helper column.
What’s your Excel problem?
Jun 2nd
One of my goals on this blog is to have some posts on how I can help you take control of your spreadsheets and data, together with real excel hints, tips and guidance which can help you be more effective.
A huge amount of what I use on a daily basis has been gleaned from excel blogs and tutorials and I hope in a small way to add my voice to help people find easy solutions to their spreadsheet problems to enhance their data analysis and management information.
But what do you want to know? What really bugs you in Excel? What have you always wondered how it works? Have you had a spreadsheet problem to be fixed or a dashboard which isn’t doing its job? Any tips you’d like to share yourself? I would love to receive feedback on what you want and need to hear, let me know using the comments below and I’ll try to make sure I cover all suggestions over the coming months.