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

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

October 6, 2014

Interactive Excel Formulas

Dynamic formulas are the cornerstone of interactive reports like dashboards. They enable our report recipients to interact with the data,

September 1, 2014

Deleting Rows in Excel Containing Blanks Returned by Double Quotes

We should all be using Go To Special to quickly remove rows containing blanks, but if those blanks are returned

August 26, 2014

Text to Columns with an Excel Formula

If you reformat data brought into Excel from an external source regularly then you’ve most likely come across Excel’s Text

August 5, 2014

Excel Formula to Spread Income or Costs Over Months

WARNING: this tutorial contains some advanced formula concepts which may incur the following side effects: Negative side effects: Headache, urge

May 27, 2014

Writing Excel Formulas Efficiently

Struggling with Excel formulas can turn simple tasks into time-consuming challenges, leaving you frustrated and behind on work. Fortunately, a

February 18, 2014

6 Ways to Fix Dates Formatted as Text in Excel

If you import data to Excel from another program chances are the dates will come in formatted as text, which

January 6, 2014

Excel TEXT Function – handy but limited…or is it?

At first glance the Excel TEXT function appears quite limited. Its purpose is to convert numbers to text in a

December 4, 2013

Excel Search a Cell for a List of Words

A while back Vernon asked me how he could search one cell and compare it to a list of words.

November 13, 2013

No, you’re not going mad, it’s just Excel’s Implicit Intersection

For many years there has been something kooky going on in my Excel worksheets. It was this strange phenomenon that

October 9, 2013

Return the First and Last Values in a Range

I’ve been asked many times how to find either the cell reference of the first or last value in a

October 2, 2013

Excel Average IF Excluding Outliers

I stumbled upon an interesting question the other day, which was; ‘how do I find the average of a range

September 25, 2013

Excel Find Partial Text Strings

This is a guest tutorial written by Ruthie Ward of Idaho, USA. I wanted to find a partial match to

September 11, 2013

Excel Formula – List Missing Numbers in a Sequence

A little while ago I received an email from Deborah asking what formula she can use to list missing numbers

September 4, 2013

Extract Text from a Cell using MID Formulas

Last week we looked at how to use Excel’s Text to Columns tool to extract the domain name from a

August 21, 2013

Calculate Elapsed Days, Hours and Minutes in Excel

I had an email from Steve a little while ago asking how he could display the number of elapsed days,

August 14, 2013

Excel Test if a Range Contains Text, Numbers or is Empty

I received an email from Bill this week asking how he can check if a range of cells contains text

July 18, 2013

Excel Formulas Not Working?

Excel Formula Help is Here Excel formulas can be frustrating to understand, especially when you start nesting functions. If you've

July 17, 2013

Excel CLEAN Formula

Ever copy and paste some data from a web page, or import it from another program, and end up with

July 17, 2013

Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new_text for old_text in a text string. The Excel SUBSTITUTE Function Syntax is: SUBSTITUTE(text,old_text,new_text,[instance_num]) The