Why is it so important that your data looks good?

SUMMARY

Katie Cagney, Database & Research Manager at Tyne and Wear Archives and Museums, TWAM highlights three simple spreadsheet tricks that can help you get your data tidy. Plus best practices to help you create a data legacy for fundraising in your organisation.

In a previous post, I highlighted some of the insights that jump out at fundraisers when you have the right data visualisation tools to interrogate your data.

However, getting this kind of insight just won't happen if your source spreadsheet is in a state.

With this blog post, I'm going to highlight three simple spreadsheet tricks that can help you get your data tidy, and some best practice that might help you create a glittering data legacy for fundraising in your organisation.

Pivot

A pivot table is a marvellous thing. A pivot table will summarise huge amounts of information for you in one, clean table. Within excel you go to Insert>Pivot Table.

Pivot creates a handy visualisation, because it wants to enable you to play around with your data, dragging and dropping columns to pull out insight. At the top you'll see your columns – descriptive column headings are important - and at the bottom you have four ways to slice up your data. The key fields to start with are rows and values, and if you just click the checkmarks next to each column, Excel will try to automatically decide which of the four fields to place them in!

How is this useful for a fundraiser?

Pivot tables can highlight the places where your data is bad or missing
Pivot tables summarise your data and are particularly useful if you want to see gift totals.
You can play with them without affecting your source data at all
You can refresh with a click when new data is added

How about some highlights?

Conditional highlighting can change the way you look at your data. In Excel, this is managed in Home > Conditional Formatting.

How is this useful for a fundraiser?

Highlighting data may help you draw out some sense from those old legacy spreadsheets (with relatively little work needed)
You can understand the breadth and depth of a large spreadsheet quickly
Icon sets show quick visualisations based on the data you have (in this case 1,2,3) and will show an icon illustrating the highest, the lowest and those in between
If you want to see all the references to projects around education, for example, Equal To will highlight that word occurring in your spreadsheet
You can pin down giving in a certain time range or gifts that are above average by using Greater Than or Top/Bottom

Data validation (again)

Validation tools within excel are so useful as they help you restrict data entry and help keep columns formatted correctly. In Data > Data Validation, you can change the types of data allowed by using the Allow field – say, if you want this column only to show text in a certain format.

You can also use this section to add a drop-down box to a field, so you have less typing to do, and there’s less room for mistakes. To do this, you use a reference list within your document, and go to Data then Data Validation. Change Allow to Lists, then click in the source field & highlight your list of values.

How is this useful for a fundraiser?

Data entry is quicker and easier
Your spreadsheet is much more future-proof
Everything will work better if the data is consistent
You only have to set these parameters once

Some good practice

If we all created our spreadsheets as if they were going to become fundamental tools for our whole organisation to use, they probably would end up being more understandable, more interactive and more readily used. The key thing is to keep it simple.

Name your columns. Name your columns clearly, so your grandma could understand what’s in them.

Consider a tab which explains the workings of the spreadsheet, or a tab at the beginning which explains how the sheet is used. A few notes can make everything easier.

Mark any new copies of the spreadsheet with a date that they can be deleted, so that it's easy to see immediately when you open a folder. Not only will this make it clear in the future what can be deleted, but it will also discourage people from making too many copies of a spreadsheet.

Don't merge cells or hide data. Merged cells and hidden data can cause all sorts of problems. Be cautious of things like this which sacrifice usability for looks. If you have to do it, make sure you mention it in your help tab.

Create reference boxes for fixed values that can change (such as VAT). Create a box that your formula then refers to, rather than just writing the amount into a formula. That way you can change all of your formulae in one go, rather than needing to edit every one.

=sum(B2*17.5) =sum(B2x$C$4)

Pro tip: the dollar signs lock the reference cell, so you could drag to copy this reference and C4 would stay the same whilst B2 would cycle to B3, B4, B5 etc.

Limit the number of tabs within one spreadsheet. No more than what is visible along the bottom of the screen at one time. Too many more and they’ll just get missed, and your file will take ages to open.

Finally, one of the tricks I use the most in Excel is paste as values. This marvellous tool is vital when you’re pulling calculated data out for another purpose. If you just click paste (or CTRL V), hidden formulae will break, and you’ll get a N/A or error, but if you right click and choose paste as values, only the final value will paste, leaving your data lovely and clean.