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
Or, as I like to think of it, 101 Excel keyboard shortcuts plus a few bonus ones just for luck. But that makes a lousy title for an article. 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
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