Why is it so important that your data looks good?

Why is it so important that your data looks good?

Written by Katie Cagney, Database & Research Manager at TWAM

 

In my last 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!

 

What’s your top tip for excel for fundraisers?

 

A series of blog posts

You’ll see a series of blogs here on Arts Fundraising & Philanthropy about some of the data decisions and processes that an arts organisation might be considering – and particularly who might be responsible for this kind of work. I’ll be focussing on fundraising data but this also has impact in many areas of arts and cultural organisations work from visitor footfall to ticketing.

 

Further Reading & Training

Treehouse has a course on excel

Microsoft has an extensive website on excel

Business Insider’s top ten things you can do with excel

“You can paint as long as you have excel!” This, on the Michaelangelo of Microsoft Excel is amazing

Or how about the awe-inspiring Spreadsheet Architecture of Emma Stevens

Last but not least, a song about the wonder that is pivot tables

 

You can find Katie’s last blog on using data in your fundraising here.