Management information specialist and Excel detective
General
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.
Finding the day of a week in Excel
Oct 13th
I read an article this morning on Lifehacker which outlined some ways to work out what day of the week a given date was. Clever as these methods are, if you’re by a computer you can do this through Excel as long as it’s for a date after 1st January 1900:
- Open a new spreadsheet or find a blank cell and enter the date eg: 1/11/2003.
- Making sure that the cell is selected open the format cells dialog (CTRL + 1)
- Select custom number format.
- Overwrite the entry in the type box with DDDD and you can see in the sample field that what the day of the week was.
An Excel problem – the case of the frozen worksheet
Jul 26th
I had a call last week from someone who was struggling with a slightly unusual problem with a spreadsheet and needed a bit of Excel Detective work.
In this case the data wasn’t moving; nothing that he did would change the view he was seeing on the main worksheet. He had data in front of him when the file opened but as he moved the cursors around the screen the data remained still and didn’t scroll over to other rows or columns. He could use the cursors, or the go to command to move to different cells and could see data change in the name box and formula bar but on the main worksheet it was as if the screen was frozen.
Whilst on the call discussing possible causes he mentioned that the text seemed quite big and we worked out that the spreadsheet was displayed at zoomed up by 130%
From there it was quite straightforward to realise what had happened: the screen had been frozen so that the first few rows and first few columns remained visible. This is a simple, but very useful trick if you have large worksheets you wish to manoeuvre around. For example take a spreadsheet where the columns have heading labels in them, you may wish to freeze those so that as you scroll down the rows you can still which column contains what value.
In order to activate the freeze command then (using Excel 2010) click the View tab on the ribbon then click the freeze panes option:
You just then choose the one that you require – whether to freeze just the top row (you would likely use this if row 1 included your headers) or just the first column (or column A, for example if this was a list of names that you wanted to remain in view whilst you scrolled to the right)
If you choose the first option – freeze panes, this will freeze around whichever cell you have selected. For example if you’ve cell C3 selected it will keep frozen rows 1, 2 and 3 and columns A, B and C. All other cells you’ll be able to scroll through using your mouse, the cursor keys or whichever method you’d prefer.
If you’ve not used freeze panes it’s a really useful option, have a go on some of your own data.
Back to the ‘phone call. In this instance the freeze panes option had been used, afterwards someone had increased the scale of the zoom so rather than looking at the screen at 100% it was around 130% larger. I’m not sure if this was accidental or to make the text larger however the effect was that the frozen rows and columns were the only thing now visible on the screen, and the unfrozen rows and columns became hidden.
Excel was still working correctly – the problem was that there were no visible cells on the worksheet which weren’t frozen.
Excel Detective solution: return the zoom to 100% and / or unfreeze the panes,
A slightly unusual problem but a fun one to solve, have you come across anything similar? Use the comments below to let me know, I’d be interested to hear.
Are you out qualified by a 13 year old?
Jul 14th
Yesterday I met with Chris Barnard, the director of future technologies at Abraham Darby Academy here in Telford. This is a school which is truly embracing all kinds of technologies – from having their own Twitter account through to using QR codes. As part of this they encourage all students up to year 9 (aged around 13) to take their Microsoft Office Specialist (MOS) exams and in fact a number of them have gone on to become Microsoft Office Masters.
Seems like a great idea to me, giving these students real life skills and a recognised qualification and it led me to thinking how many employers and parents there are out there with these qualifications themselves. Are you being outqualifed by these teenagers? The attitude in many places still seems to be that having a qualification in Office is a frivolous luxury – “oh anyone can use Word / Excel etc”. This seems odd to me for two reasons. Firstly many companies put priority into training for their technical IT staff for fear of some error on (for example) the servers jeopardising business safety. Yet your humble spreadsheet has just as much power to cause a significant problem, as discussed in a recent blog post.
Secondly it’s back to the efficiency thing. Yes I know it’s a drum I keep banging but by having the training to understand how to work with the tools most effectively it keeps productivity high and morale too. [Edit – I hadn’t seen Karen’s blog piece on certification earlier this week before I wrote this, but it’s pertinent]
So are you being trounced by these students? Microsoft Office Specialist exams aren’t easy – I’m in the process of taking them myself – but I’d thoroughly recommend investigating them if you’re reliant on Office for your day to day work. Despite being an expert user of Office for a number of years, I’ve found studying for MOS exams forces you to think slightly differently about different applications of some of the tools. A very worthwhile investment, an internationally recognised qualification and – if these 13 year olds are anything to go by – the future.
Efficiency – saving time is saving money
Jul 11th
By working with you to review your business processes, improve your spreadsheets and provide a more streamlined workflow I can help make you more efficient. Don’t switch off, this really is important stuff – there’s never been a more important time to be working as efficiently and as effectively as you can. Being more efficient might not seem on the face of it to be as important as bringing in the money but time is money said Benjamin Franklin.
Let’s put it another way, if I can save you just a minute on a task that you do twice a day, then over the course of the year that will have saved you a whole working day. All those wasted seconds moving between spreadsheets, retyping in data or copying and pasting may not seem like much but they add up and eat into your core business activity, whatever that might be.
Recently I’ve done some Excel detective work with a client, helping them to improve a process that typically took around 3 hours a month. By making some changes to the spreadsheet and ensuring that they understood a few particular excel commands we’ve dropped that to just 15 minutes. Apart from the fact that they were now no longer dreading the task the time saving is around 4 ½ days a year.
If time is money, what’s your hourly rate? Can you afford for your staff to be spending time unnecessarily on tasks which could be done quicker and more effectively? I’m happy to come in and take a look at your processes, spreadsheets that you use and how they interact with other Microsoft products and your other software and take a look to see if I can help save you money.
A good friend (Leighton, from Maison Jaune) pointed me in the direction of a recent blog post from Seth Godin, a well known American entrepreneur. It’s a short blog post and is on exactly this topic. You can read it here – and then, back to work. Time is money don’t forget…