Aid workers have two secret weapons. The tarp is one. Microsoft Excel is the other one.

Any aid worker has a love-hate relationship with Excel. Or at least, I do. Is a piece of software that has indeed countless possible applications – and here lies its greatest limit. While what it does best is analysis and storage of data in tabular form, it can do so many other things, not ideally but just good enough, that it ends up being used as a database, asset management software, budget software, program management tool, and so forth (conversation seen on a Facebook group: “What is the most used asset management software within humanitarian agencies?” “MS Excel” “But it is not an asset management software” “Yes, but…”).

This means that Excel is widely used and abused and that other, more rational solutions are completely overseen. It is not uncommon to see projects for several million dollars spread across several field location, whose budgets are completely managed sharing massive Excel spreadsheets. If it doesn’t sound ideal to you, it is not indeed. (I came out with a rule of thumb that I think is adequate enough: if the size of your .xlsx file exceeds 5 mb, you should start to look for another way to do that job).

I think this is a symptom of a deeper problem within the aid sector, part of it being lack of awareness about what are solutions available in the market (IT and not), part of it being risk aversion and institutional inertia; so that in several instances totally legit, safe and rational solutions are not implemented “because that’s how we have done things so far”. The fact that in the meantime your small organization has become a behemoth managing several millions of dollars in a dozen of countries doesn’t seem to be part of the equation.

This said, there are times when Excel is the only real option to do things, as you don’t have the chance to get a more adequate software, or you don’t have the time to learn it properly, or to instruct your team on how to use it properly.

I found it quite helpful when during distributions we had to register family identity cards and distribute items according to the family size. As this would have required a long time to do manually (we are talking about distributions for thousands of families), I created a spreadsheet that could give the operator the instruction on the right items to allocate to that family. I include the sheet here, should it be helpful for anyone.

It consists of three different sheets. In the first one (named Entitlement) is a list of the items each family is entitled to, based on the family size. The second sheet is a list of all the families affected by the relocation (columns A-J). On column “L” (“item”), I included a formula that automatically tells which items that family is entitled, based on the table in the “Entitlement” spreadsheet. Finally, the last sheet is the one that is used on the day of distribution: card numbers are entered in the first column (if you have a barcode reader your life will be much easier) and items will appear automatically on the second column. On column “G” I added a simple count function to check the overall progress of the distribution. What we would normally do is to give a token based on the items to be received, with whom the family representative could withdraw the item at a dedicated desk. Such tokens could be colour coded – and you can apply a conditional formatting on column B of the Control sheet to have the entry appear with the same colour of the token.

Some clarifications: it is important that the data are entered without altering the order of the columns (unless of course you know how to change the formulas). On the day of the distribution you might want to hide the Entitlement and List sheets, and block all the cells outside of column A in the “Control” sheet, in order to avoid wrong entries that can affect the whole document: Excel files, especially when they include several formulas, are notoriously easy to mess up with. If you have more than a working station, you can try to load the file on a server and share it, so that you have one single document rather than four different files to collate (this will also prevent duplication: be sure however that the operators work on different sections of the document, to avoid overlapping of data (e.g., one will work only on odd lines, the other on even, one will start from line 4000, and so forth). However, if this is the case, you might really want to start to consider if there are alternatives to Excel.

As said, there are a number of programs (or any good IT guy could code one on purpose) that can do this job much better. Even within the MS Office suite, Access would probably be more apt to run the show. However, as a quick and dirty solution it worked quite well, and the final size of the file with the family cards entered was around 4 MB – so still within acceptable limits according to my rule of thumb.