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

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

March 21, 2016

Avoid Writing Complex Excel Formulas

There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise

February 17, 2016

Excel Dynamic Text Labels

I use Dynamic Text Labels all the time, whether it’s in a Dashboard report or chart title, and even to

January 27, 2016

Transpose Data in Excel

There are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Enter

January 20, 2016

Excel Multi-cell Array Formulas

Excel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’

January 12, 2016

Excel ROWS and COLUMNS Functions

You'll typically find the ROW(S) or COLUMN(S) functions nested inside other functions because they make formulas more efficient to write.

December 15, 2015

Use Excel Slicer Selection in Formulas

Slicers are a great tool for incorporating interactivity into your reports but we don’t always want to analyse our data