Excel Formulas Archives • Page 3 of 7 • My Online Training Hub

September 12, 2019

Excel CONCAT Function

The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the

Find last occurrence of a string in another string

July 24, 2019

Find the Last Occurrence of a String In Another String

If we want to find whether or not a character or string occurs within another string we can use FIND

June 25, 2019

Excel Calendar Template Date Formulas Explained

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling

May 14, 2019

Power Query Get Data from Web by Example

Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a

May 3, 2019

IF Formula Builder

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at

April 26, 2019

Using a Formula Instead of VBA

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA

January 8, 2019

Excel Sorted Dynamic Unique List

I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but

December 6, 2018

Excel Dynamic Arrays

If you’re familiar with array formulas, then the simplicity of Excel Dynamic Arrays will be a breath of fresh air.

August 31, 2018

Using SUBSTITUTE Function to Find and Count Text

The SUBSTITUTE function allows you to change text in a string, or a range of strings, but there is more

July 19, 2018

Excel Functions Translator

It’s often difficult enough to figure out the correct Excel functions to use, but it’s significantly harder if you also

April 4, 2018

Relative Named Ranges

Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to

March 22, 2018

Excel ADDRESS Function

The Excel ADDRESS Function returns a cell address for specified row and column coordinates. Optional arguments allow you to specify

October 25, 2017

Extract Date or Time from a Date and Time

We often need to extract the date or time portion from a date-time serial number for use in other functions.

October 25, 2017

Excel DATE Function

The Excel DATE function combines separate year, month and day values and converts them into a date serial number formatted

October 25, 2017

Convert Month Names to Numbers

Often, we'll have a list of month names that we want to convert to a date, or even just convert

October 24, 2017

Convert Dates to Text

If you want to use a date in a chart label, or you want to concatenate a date with some

October 19, 2017

Excel NETWORKDAYS.INTL Function

The Excel NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date serial numbers, excluding

September 19, 2017

Highlight Cells Referenced in Excel Formulas

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells

July 27, 2017

Excel Convert Dates to Fiscal Quarters and Years

If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters

June 1, 2017

Excel Dates Displayed in Different Languages

If you share Excel files and reports with users from different countries then you might want to give them a

March 22, 2017

Excel TEXTJOIN Function

New in Excel 2019* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE. Both TEXTJOIN and CONCATENATE join text

November 17, 2016

Excel COUNT MATCH Array Formula

Last week I received the following question from André: “I need a formula that will count how many values in

October 18, 2016

Excel IFS Function

This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license. In Excel 2019 there are a

October 18, 2016

Excel SWITCH Function

This tutorial is applicable to Excel 2019 onward. New in Excel 2019 is the SWITCH function. It looks up a

October 14, 2016

Excel Expression Definition

A couple of days ago I sent out a survey about the name of an argument in one of the

October 7, 2016

When to say No to Excel Nested IFs

In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest

September 1, 2016

Excel Table Running Total Formula

There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely

August 16, 2016

Excel Dependent Data Validation

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here;

May 3, 2016

Excel CHOOSE Function Toggle

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions

April 19, 2016

Excel MROUND Function

Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was