Management information specialist and Excel detective
Posts tagged detective
When the unhide column shortcut in Excel won’t work
Jul 27th
If you are a fan of using keyboard shortcuts in Microsoft Excel, or you’ve seen my tweets today and yesterday then you may already be aware of the handy combinations to hide / unhide rows and columns:
___________________________________________
Hide a Row CTRL + 9
Unhide a Row CTRL + SHIFT + 9
Hide a Column CTRL + 0
Unhide a column CTRL + SHIFT + 0
___________________________________________
But wait! Some of you may notice that the last shortcut doesn’t seem to work and that although you can use CTRL + 0 to hide a column, using CTRL + SHIFT + 0 does not unhide it (even though the similar keystrokes to unhide a row does work). Try it, does it work for you?
If it doesn’t work then it’s probably down to your operating system being either Vista or Windows 7, not down to which version of Office you have installed. This is because in these latest two versions the CTRL+SHIFT+0 combination is used as the shortcut to change the keyboard input languages and so does not work in Excel as you’d expect. Changing the keyboard layout can be useful if you’re going to regularly be typing in languages which have a different character set (eg French or Norwegian) however I suspect for many people reading this it isn’t a function that they would tend to use.
If you’d like to be able to use the unhide column shortcut in Excel all you need to do is make a slight change to the settings. It is fairly straightforward: on Windows 7 click the start button and search for “change keyboard input language”, then choose the “change keyboards” option from the “Keyboards and Languages” tab.
From the next menu select the “Advanced Key Settings” tab and then the “change key sequence” button towards the bottom
On the final dialog box make sure that you have clicked the “not assigned” radio button for “switch keyboard layout”.
Click ok three times and you should be done, have a go and see if this works for you. For windows vista it’s a very similar process and more details are on the Microsoft support centre here.
Hope that this helps, do let me know how you get on. If you try this and it still doesn’t work then leave me a comment below. Any other keyboard shortcuts that you’ve found no longer seem to work?
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.
Spreadsheet Problems and Errors
May 18th
There’s been an interesting discussion in one of my LinkedIn groups about this article in the Financial Times on errors in spreadsheets. Although some years old the article talks about research from the University of Hawaii showing that
“between 78 and 97 per cent of spreadsheets contain “serious material errors” with a potential to devastate the bottom line.”
I wonder, did they use a spread sheet to collate that figure?
In my view it’s the very aspects of Excel that make it so widely used that can lead to problems. It’s the flexibility, the speed in which fairly complex calculations can be done, its accessibility and widespread use. At its best it’s an incredibly powerful tool which shouldn’t be underestimated. I’d say that careless use of business critical spreadsheets is analogous to putting a young inexperienced driver in control of a new, more powerful car and telling them to rely on that car to get around. Whilst they may not have a terrible crash, a minor bump or scratch somewhere along the line is likely unless that driver is given the time and support to get to know and understand the vehicle, along with a good set of documentation for backup.
But don’t panic, of course you shouldn’t abandon Excel entirely! Yes many organisations rely on complex and unstable spreadsheets when there are more appropriate Business Intelligence solutions however Excel can also enhance your business giving you the management information you need in a format and style that suits you. It’s particularly suitable for SMEs, freelancers and micro businesses, helping them working more efficiently and effectively understanding their business. Management Information, KPI reporting, Business Intelligence and Data Analysis may seem complex terms but shouldn’t be reserved for large corporate customers, every business needs to be able to lay their hands on their key data and Excel can help you do that. A properly constructed spreadsheet should be an asset to your business and Excel should never be a burden to be endured!
In coming blog posts I’ll cover various spreadsheet tools and some suggestions of best practice when designing a new workbook which can be used to help you avoid problems, but in the meantime what’s the biggest problem a spreadsheet has ever caused you?
In which I use Excel to go Royal Wedding crazy
Apr 28th
I’m not sure, is it actually the law that every blog or press release today has to include references to the Royal Wedding? According to the media the country – if not the world – is currently gripped by Royal Wedding fever. Whilst I’m not sure that I’m “gripped” I am enjoying some of the tenuous press releases on the subject (Dunkin’ Donuts is a particular favourite) so thought I’d add to the mix by a quick analysis of the guest list.
The Guardian’s datablog has put together a spreadsheet based on the snapshot of the guest list released so far. It’s only a few hundred of the 1900 guests and is a simple file with a single field containing the person invited such as “Prime Minister of Saint Vincent and Grenadines and Mrs. Ralph Gonsalves”. I wanted to pull out the titles of those invited, however as you can see it’s not as simple as pulling it out from a single field as the information is mixed in with other data.
Armed with nothing more than a biscuit and a quick bit of excel magic I did some analysis to pull out the titles of both guests and it turns out that of the published guests just over a quarter (27%) have the title of Mr, Mrs, Miss or Ms. Looking to those with aristocratic titles the largest group is the 24 answering to “Lady”, followed by 13 Governor Generals and a dozen princesses. In fact our own Queen is just one of 6 who can answer to that title, with an equal number of Kings and Arch Bishops.
The spread of titles is something like in the chart below, although it’s worth bearing in mind that this is where it’s important to work closely with the client. For example some titles could fall into two or more categories, and as I’m not up to date with the latest information in Debretts I would always discuss carefully to make sure that any analysis rules are defined at the outset.
Whilst Buckingham Palace have not (yet) called upon my skills as an excel detective, pulling out crucial information tucked away in a single field is often needed. It may be that you have a set of address data and need to pull out some specific information that’s embedded within the fields themselves. Or perhaps you’ve a set of data with order numbers or part names mixed up and need them analysed. Rather than sitting down and spending a few hours trawling through manually there are a host of ways you can pull the data out automatically using Excel or other tools, and I can either do this on your behalf, or work with you and your teams to train them on the functions that they need.
Enjoy the long weekend, I’m off to go and find one of those doughnuts