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
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
This one was prompted by a problem posed on an Excel forum, for which I provided a solution. The original issue was actually to do with hiding/unhiding rows programmatically based on user selection from a drop-down, a function that required use of the Worksheet_Change event and Set Intersect, with additional code to hide/unhide…but that’s beside the point, so we’ll skip that one for now. Continue reading
One of the problems I have to deal with on a regular basis is that of connecting, whether to SQL DWs, SFTP servers or whatever. The biggest problem with this is the fact that it takes Excel VBA coding 30 seconds to fail and only a few to work. Continue reading
A Defined Name is a text descriptor created by the user/developer to describe content, meaning or use of a cell or range of cells, a constant, or a formula. Once defined this can be used in place of cell addresses or other content and makes formulae much easier to understand and/or maintain. Continue reading