Excel is a great tool for data analysis, with a powerful suite of built-in functions to help. You’re probably already familiar with some of the most common features, like SUM, AVERAGE and VLOOKUP. Here are three that you may not have heard of, but that are likely to help with your Excel projects.
1. DAYS
The first function is DAYS. This function calculates the number of days between two dates. Here’s an example of when this might come in handy:
In this example, I am calculating the number of days between when an order is placed and when it is shipped. The formula is cell C2 looks like this:
Notice the sequence of the two arguments: ship date first, and then order date. With this function, the first argument is the End Date, and the second argument is the Start Date. Seems a little backwards, but that's how it works.
You can also use this feature to calculate the number of days between today and any other date. That would look like this:
In this example, I’m calculating the number of days between today (which is 7/13/2020, as I write this) and the invoice date in column A. To get today’s date, I use the “TODAY” function. Notice that I need empty parenthesis after TODAY, since this function does not require any arguments.
2. SUMIF
The next function that might come in handy is SUMIF. In the following example, I have a list of sales orders, each of which identifies the region it was sold in. I’d like to sum up the order amounts for each region. Here’s how the finished sheet should look:
Columns E and F contain the calculated totals. Take a look at the formula for the Northeast total in cell F2:
There are three arguments of the SumIf function. They can be a little confusing, so you might need to study before it clicks together in your mind. The arguments are:
The range of cells that have the criteria. In my case, the range is B2 through B12 (the list of regions for each order).
The criteria under which I want to sum. I’ve got “Northeast” in there, meaning that I only want to sum the value on any given row if “Northeast” is in column B.
The range of cells which contain the numbers that I want to sum, which, in my example, is C2 through C12.
As you can see, the function is summing the values in column C only if the value in column B is Northeast.
Just to finish things off, let’s see what the formulas look like for the West total:
And the South total:
3. LEFT
Lastly, let’s take a look at the LEFT function, which allows you to parse out part of the text in a given cell. The scenario for this one is: you’ve got text in a field, and you need to extract a few characters from the beginning of the cell. Let’s say you’ve got a list of club members, and you want to create a directory of those members. You’d like your directory to lump people together based on the first three letters of their last name.
The first step is to create a column that contains the first three letters of the name. You would then be able to sort on that value, and appropriately format for your directory. Here’s our goal for this exercise:
Notice that column B includes the first 3 letters of each member’s last name. Let’s see the formula that does this:
This is a simple function with just 2 arguments:
Which cell do you want to pull some characters from?
How many characters do you want to pull out?
In my example, I am telling the LEFT function to pull out the three left-most characters from column A.
Out of the 400+ functions in Excel, these are just a few of the very useful ones that will make Excel perform better for you. I hope this helped generate ideas about ways to capitalize on the power of the tool.
Need help implementing Excel functions? Sign up for a free consultation with me. This no-obligation meeting will last 20 to 30 minutes. We'll discuss the issues you're having, and if possible, I'll give you some quick advice to fix them. If that's not enough time, I'll create an estimate for working through the problems. To sign up, visit my Excel Help page.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2020 North Port Solutions, LLC. All right reserved.