Pros and cons of using Excel for monitoring and evaluation

For many organisations their experience of monitoring and evaluation starts with Excel.  In this blog post I look at how Excel (and other spreadsheets) is used as a monitoring and evaluation tool, assessing the pros and cons of this option in relation to database driven alternatives.

Before talking about Excel in particular, it’s important to note that there are several other free and paid alternatives to Excel.  These include the open source Open Office suite (which also has an equivalent to Word and Power Point included) and of course Google Docs, which includes some interesting additional options.  Excel also now has an online version that makes it easy to share data and collaborate with other people.

Benefits of using Excel

Excel (and it’s peers) are a great starting point as they are so widely used and affordable.  This lowers the cost (in real financial terms as well as time needed for staff training) to get started.  Additionally, most logframes or other M&E frameworks are created in Excel.  Clearly as a tool it has a key role to play.

Easy to create data collection tools

Creating a data collection tool in Excel is about as easy as it gets.  No need to learn any programming language or configuration tool.  Just type in headings for rows and columns.  It's also easy to add formulas to calculate totals or averages.  With a bit more advanced knowledge you can also create locked fields and macros that restrict what data can be entered and deal with how it is processed on entry. 

Use templates and formulas to aggregate data

If you are not comfortable learning about some of Excel's more advanced features then luckily there are plenty of places you can turn for help.  See the following tool developed by Capacity4Health as one example.

Excel Data Analysis and Reporting Template - This Excel file is a template for how to build an Excel file to analyze data and automatically generate easy reports. There is an accompanying webinar which walks through an example of how to use Excel to analyze questionnaire results and generate automatic reports.

Templates like this offer an excellent starting point, particularly for smaller organisations.  Templates often include pre-defined formulas and macros to aggregate data entered on one sheet into a summary table on another sheet.

Simple to create charts

Excel has great built in charting capabilities.  Simply select the data range you want to work with and a wizard guides you through the options available.  Google Docs also has some good options, including the fantastic motion charts pioneered by Hans Rosling.  A great way to wow people during your presentation. 

Cut and paste into reports

Finally, since most people write their reports using Word (or similar), it's a great help that you can simply cut and paste data or charts into Word. 

Limits of using Excel

So far we’ve covered the positives.  What about the downsides of using Excel for monitoring and evaluation?

Managing data quality

For many smaller organisations Excel is a great tool to use and the downsides are manageable.  However, for larger organisations - particularly those where the task of data collection is spread across several people - the biggest challenge that comes with Excel based monitoring is that of data quality.  Poor data quality can come about for various reasons.

First, data may simply be missing or at least reported late.  Managing this often means using multiple spreadsheets.  One template sent to colleagues to enter their data.  A master file in which data is aggregated from different sources.  And finally a tracking file to check who has or hasn't sent in their data.  As your team grows the task of simply tracking who has sent data gets harder. 

Second, it’s easy to make errors when cutting and pasting from each source sheet into your summary one.  Well designed templates help cut down on the risk of errors like this.  However, if the people collecting data change these templates (as they often do) then problems can come.

Third, you may start to see in-consistent data.  One of the partners we work with had struggled with this problem for years.  They used to use spreadsheet templates to aggregate data at the district level.  These were then sent to the provincial level, where they were copied and pasted into a central file.  Each year they saw data that does not reconcile between these two levels.  This was due to simply transcription errors, or worse due to reporting errors at the district level.  Identifying the source of these mistakes meant hours of reviewing spreadsheets followed by phone calls to verify numbers against the source data. 

Disaggregation

Depending on the size of your organisation, data is often aggregated at lower levels before it is centralised.  This means that those working at the centre see data that's already been summarised to an extent.  Each time you aggregate data it takes time to process and select what is needed.  The challenge comes when you now need to access data disaggregated in a way you had not planned for.

Imagine that you are doing cross-cutting analysis on the gender, HIV or climate change contribution of your work?  Or your donor asks you to provide data disaggregated by a new criteria.  Typically this means requesting your colleagues and partners to re-submit more detailed data that you must then piece together in a different way.

The underlying problem here is that often no-one has all the data at the lowest level of disaggregation.

Data validation and rules

Excel and it's peers offer some options in this area, but they are limited.  This allows you to lock some fields and limit the kind of response that can be given in other fields.  However, these are very basic compared to what is possible with more advanced software that uses forms and workflow to manage data collection.  This will typically bring with it more advanced data validation and rules that help to avoid the following types of problems:

Double-counting - Avoiding double-counting can be a complex challenge.  While there are many ways to tackle this challenge, the ability to first check for duplicates based on one or more fields (eg's a person's name or ID number) is a key mechanism for preventing duplicate records being created.  Similarly, workflow can be invaluable to manage the process for who is responsible for checking new records added and approving them.

Change logs - Excel and it's peers offer some options for tracking changes.  However, again these were not designed to be used to manage a reporting process.  Database driven alternatives will use user roles to determine who can report on what data.  They also understanding reporting periods and know that data for one period should only be entered during that timeframe.   They often use workflow to lock data once it's been entered and appoved to ensure it cannot be changed later.  Finally they will track who did what and when, which is essential if you need to do a data audit.

In-consistent data - Data validation and rules are also a key way to avoid in-consistent data.  Using more advanced field validations can ensure that a field will only accept a date that is in the past. Or a number that is greater than one entered on another field.  Or select from a specific list of terms.  These kind of advanced validation and rules can help avoid many common data quality issues from the outset.

Distributed data collection at the activity level

For me the key challenge when using Excel is collecting data in a distributed way.  From my experience I often see the following process:

  • Field worker uses a notebook or paper form to log data on activities that they are doing (a workshop they run, a home visit made etc.)
  • Once a week or once a month this is summarised into an Excel template.
  • The Excel template may be shared automatically with a central team (via Dropbox or using the online version) or emailed manually
  • Data is then aggregated (either using macros or manually by cutting and pasting) to give a higher level picture
  • Analysis is done, charts created and data shared back with colleagues

My interest is in how systems can start at the bottom of that process with the person carrying out the activity.  If your M&E system is designed to help collect activity level data in a way that follows the how the activity is implemented then you have the chance to address most of the challenges mentioned above.

This approach presents monitoring as an integral part of each activity and ensures that data is captured as (or as soon after as is practical) each activity is implemented.  This means that source data (and accompanying evidence) is available and can be aggregated upwards as and when you need to.

This in turn simplifies analysis, as it becomes a question of looking at which field(s) from which form(s) you need to aggregate for your reporting and analysis.  Making this possible means going beyond the capabilities that Excel has to offer.  Our work at Kwantu looks at making affordable ways of creating systems that match your activities that work on mobile devices with or without an Internet connection.

When have you reached the limits of Excel?

While it's a great tool, clearly there are situations where using Excel for monitoring and evaluation is not the best choice.  How do you know when you've reached the limits?  I see two key criteria to consider.

Scale

When your organisation or project is small it's easier and cheaper to create your own systems and tools using software that you are comfortable with.  Once your work is happening across multiple countries or multiple places in a country things start to change.  At this point the challenges of working with decentralised teams in different locations can start to increase the human cost of using adhoc systems.  You'll typically begin to see some of the issues discussed above coming up in your work.  At a certain point moving to a database-driven system will save you significant time and greatly improve the quality of your data.

Standardisation

If you are experimenting with new approaches and activities then the kind of data you need to collect to help manage, monitor and evaluate your work may not be clear.  As you gather evidence to test the assumptions inherent in your approach you can make adjustments.  Once the activities you follow become more standardised it makes sense to invest in better tools to collect and process data.  Making this switch too soon can result in expensive changes as your work continues to evolve.

Looking for alternatives to Excel?

If you need help assessing alternatives to Excel then get in touch.  We offer a free one hour consultation to discuss any challenges you are facing and make relevant suggestions for you to explore.