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

March 31, 2022

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

It’s been a long wait, but we finally have some exciting new Excel text functions that are going to make

March 10, 2022

Top 10 Intermediate Excel Functions

Most Excel users know the basic functions like SUM, COUNT, AVERAGE etc. Therefore, in this tutorial I’m going to cover

January 6, 2022

5 Excel SUM Function Tricks

I know you’re probably thinking that everyone knows how to use the SUM function, but I’m willing to bet that

October 21, 2021

Hash Sign in Excel Formulas

If you’ve worked with dynamic array formulas*, you’ll have noticed that they’re able to spill the results to multiple cells.

March 4, 2021

Recursive LAMBDA Functions

The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions.

February 25, 2021

Excel LAMBDA Function

The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means

July 16, 2020

Dynamically List Excel Sheet Names

Navigating workbooks with lots of sheets can be tedious. In this tutorial I’m going to show you how to dynamically

June 25, 2020

Excel Functions that Return References

Using a formula to return a reference to a range of cells allows us to generate a reference on the

June 18, 2020

Excel STOCKHISTORY Function

With the new Excel STOCKHISTORY function we can finally get historical price data for stocks, bonds, funds, crypto and foreign

June 11, 2020

5 Excel INDEX Function Secrets

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use

April 16, 2020

Excel LET Function

The Excel LET function* enables you to declare variables and intermediate calculations inside of a formula. It’s like the DAX

January 23, 2020

List First Monday Date in Each Month

With Excel formulas we can easily list the first Monday date in each month, or the last Sunday, or whatever

January 8, 2020

Excel XLOOKUP Function

Oh, how I wish I’d had the fortune of the new Excel XLOOKUP function back in my accounting days. The

December 3, 2019

Excel XOR Function

The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array,

October 22, 2019

Extract Values Present in Two Lists

Comparing two lists is easily done with Power Query, but maybe you prefer to use a formula to extract values

October 15, 2019

Reducing Data Validation List

Creating a reducing Data Validation list is easy with the new dynamic array formulas. Let’s say we have a list

October 9, 2019

Excel DGET Function

Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH. However if your data

Extract Date and Time from DateTime Value

September 24, 2019

Extracting Date and Time from a DateTime Value

Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole

September 19, 2019

Excel Data Tables

Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They

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