Category Archives: Excel

What is a Spreadsheet Engineer?

Well, there’s a question: what is a Spreadsheet Engineer, as opposed to a Spreadsheet Developer?

We’ve already skirted around this in my previous article about Spreadsheets and Risk. Since nearly everyone can ‘develop’ a spreadsheet model by entering some data and saving the file we can extend the idea of ‘Spreadsheet Developer’ to anyone in your business who is using a spreadsheet package and creating their own spreadsheets. And make no mistake, there’s a definite risk involved in this, most often not even recognised. Continue reading

Spreadsheet Use And The Risk To Your Business

This is a simple idea with, perhaps, complex and, from a business perspective, important implications. Nowadays, with the near-universality of spreadsheet use in so many businesses, it has become almost too easy for every user to “develop” a spreadsheet, whether for use only by themselves, by a small team or across the whole business. In fact most organisations have a de facto spreadsheet culture.

But maybe first we need to have a little background and what better than the results from a survey of nearly 1,600 spreadsheet users? Continue reading

Excel 2010: the big macro turn on!

Not what it may sound like, but instead a quick guide to making sure you can run your macros in Excel 2010.

Macros are disabled by default in Office 2010 (Word 2010, Excel 2010, PowerPoint 2010, etc) for enhanced security. Enabling Macros is easy but Microsoft Office team has hidden it deeper inside Options. Continue reading

Excel: moving from menu to ribbon, pt. II

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

Excel: moving from menu to ribbon

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

Excel: Extract a substring…starting from the end

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

Excel 2003: IFERROR

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:

 IF(ISERROR(myFormula),myOtherFormula,myFormula)

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

Excel Pivot Tables: Manipulation, Averages & More

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

Excel: Merging CSV Files The Easy Way

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