For many small organisations a spreadsheet is the core of their monitoring and evaluation system. If you're working on smaller scales or with less standardised monitoring approaches, spreadsheets are a great option. They are both flexible and powerful.
However, they also have their limitations, particularly when it comes to managing data quality and disaggregating data. Take a look at my earlier post for some of the pros and cons of using spreadsheets for your M&E system.
But which spreadsheet should you use? More specifically, which is best for M&E: Excel, Google or OpenOffice? In this post I evaluate these three options in relation to areas that are important for M&E. Read on to see how each rates and who the winner is.
Let's start with cost. This is clearly a major factor for many small organisations, particularly when you are still refining your M&E approach.
The table below attempts to provide a price comparison for the three products.
- Things are a bit more complex with Excel, which is generally packaged with Microsoft Office with an emphasis placed on the subscription version.
- Many NGOs are eligible for a free version of Office 365 - assuming they are based in a country supported by this scheme. See this link for details. However, currently only 7 African countries are included. Coverage is better in Asia and South America.
- Prices also vary from country to country. I've used US pricing for this comparison, but it may well be that prices differ in other countries.
- This comparison is part comparing apples to apples and apples to oranges. Both Excel and Open Office have software that you download and run on your computer. Excel also has an online version and Google is online only.
Regardless of these caveats it's clear that the two free options (Open Office and Google Spreadsheets) both win out on price. The pricing for the online version of Excel and Google Spreadsheets are similar.
Winner - Open Office and Google Spreadsheets as they are free. Five points each.
I'll give Open Office a bonus point as it's free in open source sense of the word too.
Price is one thing, but convenience is another. Teams are increasingly working from home and across different offices. The ability to collaborate (by which I mean view and edit the same spreadsheet) is a key criteria for many organisations. We've got a few options to contrast here:
- Share a spreadsheet by email
- Share a spreadsheet by Dropbox or similar
- Share an online spreadsheet
Emailing around versions of a file is a shortcut to serious data quality issues. Unless you have one (very diligent) person tracking what is being sent around it's easy to run into problems. While Dropbox can help it falls short in two key ways. First, it's not easy to see who has made an edit and when. Second, if two people edit at the same time it creates a separate version of the file. Again, you need someone diligent to track and merge versions.
Enter online spreadsheets. This is where Office Online and Google Spreadsheets really shine. Not only can you choose who can edit, delete or view the file, you can also track who changed what. The last feature will be familiar to those that use track changes in Word.
Winner - Office 365 and Google both win out here for me. Five points each.
OK, so we've looked at price and collaboration, but what about features that are needed for an M&E system? Well, forms is a good place to start. Earlier I mentioned that spreadsheets can be prone to data quality problems. One common issue happens when you ask people to enter their data directly into the spreadsheet. Let's imagine a scenario where each month five teams need to open the same shared spreadsheet and enter their data in rows that relate to their work.
Unless the spreadsheet is locked, anyone with access can open it at any time and (accidentally or on purpose) change any of the data. It's very difficult for M&E Managers to control who can enter what data and when. It's also difficult to control who is responsible for reviewing and approving that data and locking it to prevent changes once it's been approved.
Open Office, Google Sheets and Microsoft Excel all offer the option to create forms to manage data entry. These include validations to control which responses are allowed to specific questions. Data entered from the form goes directly into the spreadsheet, where it can then be reviewed. This means you can give people access to the form, but not the spreadsheet.
Winner - Tie. All three options offer forms. Five points each. One extra point to Google though for the ability to then embed these forms in a webpage for easier access.
Aggregation, analysis and filters
Once you've collected some data and reviewed it, the next step is working with it. This typically comes down to using formulas to aggregate and transform your data. It's not entirely clear if the (online) Office 365 version of Excel has the same capabilities as the offline version. If you know of a feature by feature comparison, please let me know in the comments.
All three options have you covered for the basics. They can sum, count, average and perform all arithmatic that you would expect. See the following links for a list of calculations that each offers:
However, if you are getting into more advanced territory then Excel and Open Office start to win out. They include a far wider range of calculation functions to help with more complex statistical analysis. Both also offer some useful ways to aggregate your data for analysis. One function I find very useful is the ability to sum or count values in a filtered list.
Imagine you have survey responses from 1000 people across ten different schools that are in five different districts. You may want to aggregate your data to see the totals across all schools. Easy, you can use SUM to calculate the total.
But what if you want to see the totals for one district or one school? If you apply a filter to limit the dataset you'll notice that the totals don't change. To get round this problem you can use the SUBTOTAL function. This means your calculation will now only apply to the rows visible when you apply a filter. Very useful if you want to compare responses by a specific criteria.
This is an example of a useful function is only available for Open Office and Excel.
Winner - Excel, closely followed by Open Office. I’ll give five points to Excel, four to Open Office and two to Google.
Working with large data sets
How much data can a spreadsheet handle before you run into problems? In my experience Google Sheets slows to a crawling pace as soon as you start working with a few thousand rows of data. Excel and Open Office are quite happy working with datasets up to hundreds of thousands of rows. To be fair, if you’re working with datasets this large then maybe a spreadsheet is not the best tool for you. Some options you may want to look at are:
- Google Fusion tables
Winner - Joint tie with Excel and Open Office, five points each. Two for Google Sheets.
Open Office, Excel and Google all offer ways to visualise your data using charts. Personally I find Google’s visualisation tools more intuitive and easier to work with. The new 'explore' option makes it really simple to visualise your data. Just click on the button for suggested charts.
However, there is no question that Excel has a wider range of options and more powerful capabilities than both alternatives. Key to this is the close integration with Word, PowerPoint and SharePoint.
Take a look at my previous post for a run down of Excel Power Charts and the features that it offers. While it’s only available on Excel 2013 it makes it easy to:
- Create visualisations that people can interact with
- Embed the visualisation in a Power Point slide, complete with your data
Winner - Excel gets five points, Google three and Open Office two.
Web service integration
The term web services refers to ways of different applications sharing data and functionality. Applying this to spreadsheets brings enormous potential. Unsurprisingly, Google leads in this area. Clicking on the 'Add-ons' menu at the top opens up the following pop-up.
This lists an enormous range of add-ons, most of which created by other people, that extend the capabilities of Google Sheets. These include options like:
- Geocoding data - sending your data to a web service that attempts to match it to a location and return geographical coordinates
- Importing RSS feed - automatically importing data from a website via an RSS feed
- Translations - using a web service to translate your data
These are just a few of the many options that may be helpful to you.
Winner - Five points for Google.
And the winner is?
See the table below for a comparison of the scores for each area. While Google just takes the first place from Excel, it's clear that it's weak in some key areas.
For me Google wins out on cost, collaboration, forms and of course web service integration. Unless you're working with larger datasets or need to do specific types of analysis and visualisations, it may also cover these areas for you too. Excel (and to an extent) Open Office are clearly better for these areas.
Are these differences enough to make you want to switch? The only way to find out is to give it a go so you can make up your own mind.