Excel Functions for Database people

The ability to use Excel is a critical tool in a database professional’s toolbox. There are a few key formulas that you should be familiar with. Here are a few of my personal favourites.

But, before we get there, I thought I’d mention some of the other things Excel is good at and bad at.

I’ve used Microsoft Excel (full name) for a variety of purposes. I’ve used it to transform data and to create desktop apps with the help of Macros and Forms. I’ve used it for reporting as well as, dare I say, data capture. Please see my other post about Excel not being a database!

It can be a very flexible and powerful tool, and it is also an application that most businesses have, making it simple to roll out solutions to customers. Another great feature of Excel is that it includes Microsoft’s new(ish) power query tools. If you’re unfamiliar with Power Query, check out my post about how APIs have become much easier to use!

Right, I’m getting carried away, let’s get back to formulae or, as they’re sometimes called, Functions. Here is the official Microsoft function help: 5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb – – Never be afraid to seek assistance (F1 is the shortcut key for help in all Microsoft Applications). If you get stuck, remember that Google is your friend.

So, to get you started, here are some formulas that I use frequently when working in Excel; these are the slightly more obscure ones rather than the standard SUM, AVERAGE, COUNT, and so on.

  1. =IF – This allows you to generate a Boolean logic value based on a set of criteria. =If (CELL>=50,”Top”,”Bottom”), for example, would identify if the value in a specific cell was greater than 50, in which case it would say top, otherwise it would say bottom.
  2. =LEN – This function returns the CELL’s length. When importing data into fields with a maximum length, such as telephone numbers, this feature comes in handy.
  3. =Substitute – This function allows you to replace values within a string, for example: =Substitute(“Anthony”,”hony”,”onia”) would change Anthony’s name to Antonia.
  4. =CountA – Counts the number of non-blank cells. This is something I’ve used a lot when creating custom reports for customers.
  5. CountIFs and SumIfs – I’ve combined these two because they are used in Reporting, where I need Count or Sum functionality based on a Logical Statement, for example: Count this row if the Town is London. Sum if does the same thing but adds a value, for example: What is the total income from London residents?
  6. NETWORKDAYS – This is a sometimes useful function that returns the number of working days between two dates, such as how long it takes to send a thank you letter after receiving a gift. I’d use networkdays based on the gift and acknowledgement dates. This could also be used for supporter journeys. How long does it take to turn a prospect into a participant?
  7. LEFT / RIGHT – This returns a number of characters from the left or right side of a string, for example: =Left(“Anthony”,3) returns “Ant” because these are the first three characters.
  8. ISDate – This checks to see if a value is a real date, which is useful when you have data that will not import into a system.
  9. ISBLANK, ISERROR, and ISNA are all used for error checking. All of these are extremely useful when working with reporting and data that may not meet your report’s criteria.
  10. Finally, all good database professionals will be well-versed in Vlookup. This is the ability to take data from another source and match it to your row using a common field. For example, if you want to add a postcode to your contact sheet but the address is in a different table, Vlookup is the way to go. It has some quirks that you’ll need to get used to, but once you do, you’ll be hooked. It reminds me of pivot tables, but that’s a story for another day….

Finally, some new developments with Excel functions, which, if you’re still reading, must mean you’re interested.

  1. XLookup – A new variation on the Vlookup function that allows you to go forwards and backwards in time. It has a search mode, which is very useful.
  2. Excel 2021 is a new version of Excel that was released in October 2021.
  3. Finally, many people who are far more nerdy than I am seem to be getting excited about Excel and Lambdas: This appears to me to be a solution looking for a problem at the moment, but I’m confident that once I understand it or have used it, I’ll be raving about it.

So, what have I overlooked? Excel has so many functions that I’m sure you have favourites as well. As previously stated, this list is by no means exhaustive of all the functions available in Excel; rather, it focuses on the ones that I use the most frequently. Please leave a comment if you believe I have missed any.


Database Person 101

One of the things I do is assist with interviewing database people for roles. It’s usually where I already have a relationship and have worked on a project for a client. I recently hired a database person at a charity where I’d been assisting with Raiser’s Edge database support for the past few years.

The pandemic has made conducting interviews quite different. Zoom or Microsoft Teams interviews can be difficult to conduct, but they can also be beneficial since, in my opinion, interviewees are less anxious when they are not present and are not dealing with the pressures of travel. Some people experience the opposite, where they become more anxious when they cannot meet in person. Keep in mind that the interviewee wants to perform at their best for all parties.

However, I digress. The purpose of this piece is to discuss the onboarding process for new hires, what it’s like to join a team, and what you should try to consider. But before that, I would note that it has been challenging to find candidates during the pandemic. If you’re trying to hire, all I would advise is to persevere because the right person will come along. You should also be realistic about what you can offer, as I believe that remote working is here to stay and that database professionals do need some time away from the office to focus and do some of the “doing.”

Let’s get back to it. Although the individual I hired had experience with databases, they hadn’t worked on the particular database at this nonprofit, so as part of the onboarding process, you need to give them time to get up to speed. Keep in mind that not everyone learns in the same way; some prefer to read, some prefer to watch videos, and some prefer to just dive in. If you’re writing handover notes, consider whether making a video of a brief passage that can be utilised as training material or to explain something a little more technical would be the best course of action. The majority of companies offer current films for system overviews, and if you’re just getting started, YouTube is your buddy.

Training: Even while you want the new employee to become acclimated as quickly as possible, try not to schedule all of your training sessions or orientations during the first few weeks. Joining new organisations can be overwhelming for database professionals because most database professionals are introverts. I don’t mean this in a negative way; I’m just saying that meeting lots of new people can be exhausting and that trying to remember names and roles on top of systems and processes takes time. Create a strategy for the information your database person needs to know and make sure there is room for it.

I chose a half-dozen things for the person to learn each month for the first three months, and then a few of the more important things for the last three months of their probationary period. Which is a good point; I would always estimate that it takes approximately 6 months to onboard and embed a multifaceted database person; obviously, if your database person is only doing imports or selections, it can be much faster, but as a guide, that is what I would suggest. Everything will be fine if they arrive sooner.

When your new hire is no longer new, the honeymoon period is over, and they must work on their own. Hopefully, the following items are in place:

Task Management – They’ve found a way to manage their day-to-day tasks, whether it’s Outlook tasks, a separate calendar, or something completely different like Todoist or Trello.

Documentation – There are notes on what you’ve gone through that are saved in a location where you can refer to them or share them with other team members. This documentation should include process notes, your personal notes, and contact information. Using a programme like OneNote or Evernote, which allows you to save links to websites as well as documents and pictures, can be useful at times. You may also be able to embed videos if you’ve decided to create your own collection of how tos.

Projects List – Before you set out to change the world or annoy colleagues with the “This is how we did it at my previous job…” I’d make a projects list and a tracker for everything that happens on a regular basis, such as imports from JustGiving or Direct Debit Processing, and then look at the bigger projects / clean up type stuff that you might want to tackle when you have the time, such as Contact Types, Communications lists, or teams that aren’t using the CRM as well as you’d like. They are not 5 minute tasks.

Again, you can keep this separate from your regular day-to-day tasks, or you can keep it all in one place. Personally, I keep them separate because I would be overwhelmed by a long task list followed by a long list of projects I need to complete.

Finally, make time for self-learning and keeping up with what’s going on in the industry. Database professionals don’t have many options for getting help, coaching, or mentoring on a daily basis. So start with the Facebook groups; there’s a fundraising database group as well as Raiser’s Edge groups for the UK and overseas. Other CRM systems are available, and there are groups for them. However, I would say that many of these groups are places where people vent about their CRM system, sometimes for good reason, but you’ll need to wade through this or add a picture of your pet to cut through the noise.

You can, of course, look for people on LinkedIn and connect with them; who knows, we might even be able to hold events where we can put like-minded people in a room for a discussion – stranger things have happened!

Finally, this isn’t my typical type of post, so if you’d like to see more of this type of thing, perhaps about specific database challenges, let me know and I’ll keep typing; if this is completely off the mark or if I’ve missed something obvious, let me know and I can amend or leave this type of thing to people much more qualified than myself.

Here are some examples of common job roles on a database team in the UK charity sector:

  1. Database Administrator (DBA): A DBA is in charge of managing and maintaining the organization’s databases, which includes tasks like data modelling, storage, integration, and security.
  2. A data analyst is in charge of analysing data in order to extract insights and inform decision-making. Data cleaning, data visualisation, and statistical analysis are examples of such tasks.
  3. A data engineer is in charge of designing, constructing, and maintaining the infrastructure and systems that support the organization’s data needs. Data pipelines, data lakes, and data warehouses are examples of such tasks.
  4. A data scientist is responsible for analysing data and extracting insights using advanced statistical and machine learning techniques. Predictive modelling, natural language processing, and image recognition are examples of such tasks.
  5. Data Manager: A data manager is in charge of overseeing the overall management and governance of an organization’s data, which includes tasks like data quality, data security, and data privacy.

A database team may include one or more people in each of these roles, depending on the size and complexity of the organisation, or it may have a more specialised structure with additional job roles.

Fundraising Databases Training

Why CRM systems fail!!

Following on from my previous post about how to healthcheck a CRM system, I wanted to share with you why I think people either love or hate their CRM systems. In most cases its not because one system is better than the other, though there are some systems that don’t fit the not for profit market, it is because users have either bought into the system and can see the benefits or they can’t.

I tell this story a lot but it is one of my most vivid memories of when I started working in the sector. I remember a member of staff, who in the run up to the London Marathon was struggling under the burden of paper that was printed out and then manually entered into the CRM system. It was taking about 3 out of the 5 days in the week to process this data. Now, this wasn’t because the CRM system was rubbish or even that the person didn’t know better, but more because the person who was spending their day wrapped up in paper wasn’t aware of the different things that their CRM system could do.

It was a lack of training.


YouTube Launches Non-Profit Simulator

YouTube has announced a new way to help non-profits better tell their stories on the video platform.

YouTube Next Cause, the latest addition to the Creator program, is a day-long summit in San Francisco April 2 during which the company will offer non-profits training and consulting on YouTube best practices for promotion and community engagement.“We want to help non-profits turn their view counts into actions — dollars raised, petitions signed,” YouTube spokesperson Jessica Mason told Mashable.

via YouTube Launches Non-Profit Simulator.