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

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

October 6, 2015

Excel AGGREGATE Function

The AGGREGATE Function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions

September 11, 2015

Excel End of Period Dates

“Funny” days versus funny “Days” Guest post by Roger Govier, Microsoft Excel MVP Do you ever have “Funny” days? You

June 23, 2015

Excel Named Range Shortcut

An Excel Named Range is a powerful tool and something we should all be using to make our formulas quick

March 17, 2015

Give me a SIGN, Excel

The Excel SIGN function returns a 1 if the number is positive, a zero if it’s zero and a -1

October 30, 2014

What the CELL!

Excel’s CELL function doesn’t get a lot of press but it can be handy to know. The CELL Function simply