Search Not Just Numbers


Tuesday, 14 July 2015

Is Excel error-prone?

As you may be aware from one of my earlier posts, I was attending an ICAEW event last week, marking one year since the launch of the Twenty Principles for Good Spreadsheet Practice, as I was privileged to be part of the team that worked on devising the principles.

One of the age-old accusations against Excel was addressed by Professor Ray Panko, the keynote speaker at the event. Namely that it is error-prone.

I thought it might be good to highlight his research into this accusation, as you might find it interesting reading (and a useful defence when senior managers say that "we must get rid of these spreadsheets").

The research paper behind Professor Panko's talk is available to read here:

What We Know About Spreadsheet Errors

If you want to see some UK-based research into Spreadsheet errors, take a look at F1F9's paper:

Capitalism's Dirty Secret

Professor Panko is possibly the world's leading academic when it comes to research into spreadsheet use and it was very interesting to hear his take on this subject.

A telling quote from his talk was the wonderful:

"Spreadsheets aren't error-prone, people are!"

Professor Raymond R. Panko, University of Hawaii

According to his research, the error rates in spreadsheets are roughly the same as in other types of activities with a similar level of complexity (such as computer programming), i.e. around 3%-5%.

The big difference is that this is recognised in software companies, whose testing processes tend to reduce this rate considerably, whereas most spreadsheet development is carried out in a much less rigorous environment. It's not that spreadsheets are error-prone, it's that the errors aren't removed by rigorous testing - i.e. it is nothing to do with the platform, but the different approaches of those carrying out the work.

To reduce the rates of errors in the finished (post-testing) solution doesn't require choosing a different platform, just adopting some of the best practices used in other types of software development.

This means applying good practice as per the Twenty Principles, and in particular, rigorously testing the solution (Principle 18), preferably by more than just the original developer, who is much less likely to spot his/her own errors.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 7 July 2015

Excel Tip: Find out how a cell is linked to others

Before I get into today's post, just a quick reminder for those of you in London about the Twenty Principles event I mentioned in my last post.

I believe that there are still some places available for Wednesday evening, and it would be great to see you there.

I am currently working on a large project trawling through some incredibly complex spreadsheets built by someone else, and I thought that I would share with you a simple, but very valuable Excel feature that I am using a lot on this project.

Before I can make decisions about any particular cell in the spreadsheet, I need to know how it is linked to other cells.

These links go in both directions. If the cell contains a formula, I might want to know what other cells this cell is dependent on (referring to the formula will help in this case).

The contents of the cell will be of no help with the other direction, and this is the one I need to know most often, i.e. what cells are dependent on this one. This is crucial, because I can't delete it or change it, until I know if there are any implications, and if so, what they are.

Fortunately Excel has a tool for both of these.

On the Formulas ribbon, there is a section called Formula Auditing which includes, among other things, the following three buttons:

  • Trace Precedents
  • Trace Dependents
  • Remove Arrows
If you select a cell with a formula in it, and click Trace Precedents, a number of blue arrows will appear, pointing from the cells that are referred to in the formula to the cell itself. If the formula refers to cells on another sheet, a broken arrow will come from a small box. In this case you can double-click on the arrow itself, and the references on other sheets will be listed. You can even double click on a reference in the list to go to that cell.

Even more useful is the Trace Dependents button. This works in exactly the same way, but the arrows go from your cell to the cells whose results depend on that cell. Where the cell is referenced from other sheets, the same approach (with the little boxes and broken arrows is used).

Finally, the last button simply removes the arrows from the screen.

Have a play with this. It can be very useful when you are trying to understand how a particular spreadsheet works.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 23 June 2015

Excel Event: 20 Principles for Good Spreadsheet Practice from the ICAEW, one year on

You may remember that last year I told you about the 20 Principles for Good Spreadsheet Practice from the Institute of Charterered Accounts in England and Wales (ICAEW).

I was part of the committee that produced the 20 principles which were launched at an event at Chartered Accountants' Hall in Central London last June.

Well, the principles have been out "in the wild" for a year now and the ICAEW are holding another event to look at how it is being used.

The event is being held at the same location on 8th July 2015 and will offer the opportunity to not only hear about practical experience of applying the principles and Microsoft's plans for Excel, but also from world-renowned researcher into spreadsheet controls, Professor Ray Panko of the University of Hawaii.

There's also a drinks reception afterwards so maybe we could meet up too.

You can find out more, and book your place at the link below:

I hope to see you there.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 16 June 2015

Excel Tip: Colour-coded tabs make life much easier

This week's tip is a really simple one, but can make using your spreadsheet so much easier.

It is something we have used with paper files for decades, if not centuries, but is sadly not used anywhere near as much in spreadsheets. This is a shame, because it is really simple and very effective.

I'm talking about colour-coding your worksheet tabs, i.e. the little tabs along the bottom of the screen for switching between your different worksheets.

We can change the colour of a tab simply by right-clicking on the tab name and selecting Tab Color (the menus have the US spelling)., we can then select our chosen colour from the palette.

Despite how easy it is to do, used well it can make a huge difference to the usability of the spreadsheet.

By 'used well' I mean the following:

  • Use a small number of colours to identify particular types of worksheets. For example I often use red to identify sheets that contain external data that must be refreshed, green for output reports and orange for data entry.
  • Order the tabs sensibly. You can move tabs simply by clicking and dragging them. I find ordering them in typical sequence of use is useful (left to right), so in a reporting spreadsheet we might have external data sheets that need refreshing first, followed by the reports that are generated from them. If there are also data entry sheets, I would tend to include these last if they are just standing data that it is not always necessary to update, or if they need editing every time, include them at the appropriate place in the workflow. Combined with the colours and well-chosen tab names, this can make the spreadsheet quite intuitive to use.
  • Keep tab names short but meaningful. The shorter they are, the more that will fit on the screen without scrolling (thanks to Jim's comment on last week's post for this simple tip). 
My suggestions above are just that, play with this and you will find what suits you and the particular spreadsheet, but don't ignore this ability to add very clear visual cues to your spreadsheet.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".