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

August 15, 2023

Excel Dynamic Named Ranges

Excel dynamic named ranges bring flexibility and efficiency to your spreadsheets. Unlike static named ranges, dynamic named ranges automatically adjust

August 8, 2023

Excel Functions for Financial Modeling

Excel functions are the backbone of a financial modeler’s day to day work and as such, it’s essential to have

May 30, 2023

Excel Formula by Example

Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to

February 23, 2023

AI Aided Excel Formula Editor

Writing Excel formulas is one of the most important tasks to master in Excel, but it can be a mine

January 12, 2023

Top Excel Functions for Data Analysts

A data analyst gathers, cleans, models, analyses, and interprets data. The gathering and cleaning should be done with Power Query.

December 15, 2022

Advanced Formula Environment

The Advanced Formula Environment (AFE) is part of the Excel Labs add-in and is still in its early stages of

November 30, 2022

Pro Excel Formula Writing Tips

Excel Formula Writing Tips, Tricks and Tools that will change how you work in Excel Formulas are THE MOST IMPORTANT

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

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