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

October 6, 2022

New Array Shaping Excel Functions

Microsoft recently released 11 new Excel functions for shaping arrays (data). I already covered VSTACK and HSTACK which was super

excel nested if functions what not to do

September 22, 2022

Excel IF Formulas and What Not To Do

We can learn a lot from looking at what others do in Excel, both the good and the not so

September 8, 2022

Excel IMAGE Function

The new Excel IMAGE function enables you to insert images into cells with a formula. From there you can resize

September 1, 2022

Excel VSTACK and HSTACK Functions

Excel VSTACK and HSTACK functions are just two of a raft of new text manipulation functions available to Microsoft 365

July 21, 2022

Identify overlapping dates and times in Excel

I often get asked how to identify overlapping dates and times in Excel. Excel doesn’t have an ‘overlap’ function, but

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

5 Secret Features of the Excel INDEX Function

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