Excel Formulas

If we want to find whether or not a character or string occurs within another string we can use FIND or SEARCH. But these functions only tell us if a string (or character) exist in another string, they don't tell us if the string we are looking for occurs multiple times. For example, we have …

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling date formulas. Adam, one of our members, sent me in a calendar he has been using for 10+ years and asked if I could explain how some of the formulas worked. The original source of …

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at time of writing. The vast majority of questions are along the lines of I want to multiply a value by a percentage. In some cases, we need to enter the word "Special" instead of the …

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA is not the best answer, using a formula is. What this tells me is that people don't know how to use Excel's functions and when they come up against a problem they think is difficult, …

The SUBSTITUTE function allows you to change text in a string, or a range of strings, but there is more to it than that. By combining it with other functions you can use it to do things like count how many times a section of text appears in a cell or range, or find the …

It’s often difficult enough to figure out the correct Excel functions to use, but it’s significantly harder if you also need to translate the function into the language of your localized version of Excel. Introducing the Excel Functions Translator. It’s a free add-in available for Excel Online, Excel 2013, Excel 2016 for Desktop, Office 365, …

The Excel ADDRESS Function returns a cell address for specified row and column coordinates.  Optional arguments allow you to specify the style of address (A1 or R1C1), the sheet name it refers to and whether the reference is absolute or relative. Excel ADDRESS Function Syntax Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Note: Arguments in square …

We often need to extract the date or time portion from a date-time serial number for use in other functions. We can use the INT function to extract the date portion, and the MOD function to extract the time portion. See examples below. The INT function returns the integer or whole number portion of the …

The Excel MOD Function is probably one of the most confusing to understand, unless you’re a math whizz, which I am not. After all this is why I use Excel. It’s my giant calculator of choice. Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions …

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted as a date. Excel DATE Function Syntax Syntax: =DATE(year, month, day) year The value of the year argument can include one to four digits from 1900 to 9999. month A positive or negative integer representing …

Often, we'll have a list of month names that we want to convert to a date, or even just convert month names to numbers. We can use the MONTH function to do this; see examples below: How it works: By concatenating a 1 to the text month name in column B we provide the MONTH …

If you want to use a date in a chart label, or you want to concatenate a date with some other text, then you'll want to convert dates to text first to ensure it displays correctly. We can use the TEXT function for this. The second argument of the text function allows us to specify …

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. …

If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters and years for reporting. Convert dates to quarters with this formula (where cell A2 contains your date): =ROUNDUP(MONTH(A2)/3,0) And if you want to prefix it with ‘Q’, then use this formula: ="Q"&ROUNDUP(MONTH(A2)/3,0) Convert dates to …

If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American 😉 It’d be a load of work to create a separate file for each language, so instead we can automate it like …

New in Excel 2019* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE. Both TEXTJOIN and CONCATENATE join text together, but TEXTJOIN enables you to more easily join a list or range of cells including a delimiter that you want to insert between each text value. Best of all, TEXTJOIN allows you to ignore …

Last week I received the following question from André: “I need a formula that will count how many values in cells D4:D7 are listed in B4:B13. I don’t want to count the number of times they appear in column B, I just want to know how many values from column D are in column B“. …

This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license. In Excel 2019 there are a load of new functions, one of which is the IFS Function. Given last week’s topic, ‘When to say no to Excel Nested IFs’ I thought it was a good time to introduce the Excel IFS …

This tutorial is applicable to Excel 2019 onward. New in Excel 2019 is the SWITCH function. It looks up a value in a list of values, and returns the result corresponding to the first matching value. It’s a great alternative to nested IFs, or even the new IFS function. I liken it to a VLOOKUP, …

In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest IF functions from 7 to 64. S I X T Y – F O U R! Are you kidding me? If you need to nest any more than 7 IFs then you should be using …

There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely to end up with errors that aren't obvious. Watch the Video Download the workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we …

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here; Dynamic Dependent Data Validation. However the approach I’m going to cover in this tutorial is probably the best I’ve seen, especially if you have a lot of dependencies as it’s easily scalable. What is Dependent …

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions to get the party started. Fair enough I suppose, after all, the more the merrier. In this tutorial we’re going to use choose to toggle through different aggregation methods (AVERAGE, MAX, MIN, SUM) as seen …

Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was making a timesheet and wanted to round the start and finish times to the nearest 15 minutes while allowing for a 7 minute grace period. The table below has some examples: Rounding these times to …