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 – https://support.microsoft.com/en-us/office/excel-functions-by-category-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.
- =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.
- =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.
- =Substitute – This function allows you to replace values within a string, for example: =Substitute(“Anthony”,”hony”,”onia”) would change Anthony’s name to Antonia.
- =CountA – Counts the number of non-blank cells. This is something I’ve used a lot when creating custom reports for customers.
- 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?
- 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?
- 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.
- 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.
- 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.
- 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.
- 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.
- Excel 2021 is a new version of Excel that was released in October 2021.
- Finally, many people who are far more nerdy than I am seem to be getting excited about Excel and Lambdas: https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 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.