I did say, in the post Excel: moving from menu to ribbon, that getting a friendly developer to write a custom menu set-up for you to replicate the old 2003 menu is not the way to go. Irrespective, I consider myself both friendly and a developer so we do actually have an add-in available, for those who’d like to disregard my previous advice. Continue reading
For users of Excel the change from the familiar old menu bar, in Excel 2003 and earlier, to the new ribbon, in Excel 2007 or 2010, things can be a bit of a shock. Things aren’t where you expect, some may not even be in a place you consider sensible. But, now you’ve upgraded, let’s look at a way to deal with this. Continue reading
Now there’s a whole heap of posts, forum answers and articles out there on how to find a substring in Excel by playing around with formulae. The problem that almost all of these have is that they assume you always want to start from the beginning of the string. But what if you need to find information from within the string instead? Continue reading
For those who’ve now experienced Excel 2007 or 2010, you may well be aware that there is a lovely replacement for the good old scenario of:
This is, of course, the IFERROR function. But for those still using 2003 you’re stuck with the above. Now we know that this works, but it can very quickly get big, bulky and nasty. And with multiple nesting formulae it’s hard to spot when or where it’s gone wrong.
So, how to deal with this? Continue reading
This question about Pivot Tables came my way recently:
I have to add anywhere between 30-100 items to the values area on a series of worksheets. Is there a quick way to add these items (other than drag & drop/right-click on each one)? Once on my table, I want to display the average of the columns of data (as opposed to the default Sum). Is there an easy way to convert them to averages?
Due to security restrictions, I am unable to download software and/or custom add-ins. Continue reading
For reasons which are best not explained, I was given some large data exports that had been created as a dozen or so Excel sheets (to the tune of 40,000+ lines per sheet) and asked to convert them into something more useful. Specifically, a CSV file. Continue reading
MS Word is a peculiar beast and it should be remembered that, just because it comes out of the Microsoft stable, this doesn’t mean it works the same as MS Excel. There are some obscure reasons for this peculiarity but, rather than go into these in depth, I’ll limit the scope of this article to a specific question posed recently. Continue reading
Excel was designed so that a user can sort by 3 columns, and that’s it. Why 3? Who knows, maybe the developers figured that 3 would be enough for any user of Excel and if they wanted more they’d be using a different application entirely. For whatever reason, that’s what we’re stuck with when using the Data>Sort tool provided. Continue reading
The following question was asked by a user on an Excel forum where I lurk on occasion: Continue reading
It turns out (okay, so this was no surprise to me) that what goes into a dashboard matters. And to make it clear, I’m talking about Business or Management Information dashboards in Excel, not vehicular ones, although no doubt the same general rule applies. Continue reading