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

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 19, 2014

Excel Table Absolute Structured References

Excel Tables are one of my favourite features. They make working in Excel much more efficient in so many ways,

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

June 30, 2013

Excel ACCRINT Function

The Excel ACCRINT function returns the accrued interest for a security that pays interest periodically. The Syntax is: ACCRINT(issue, first_interest,

June 25, 2013

Excel Bank Reconciliation Formula

This week I had a question from Dominique that took me back to my accounting days. She asked “how can

June 12, 2013

Excel Find Column Containing a Value

Last week I had an email from Mike asking how he can lookup a suburb in a range of columns

May 29, 2013

Excel Formulas

Here you'll find a list of common Microsoft Excel formulas and functions explained in plain English, and applied to real

May 16, 2013

VLOOKUP Multiple Values in Multiple Columns

A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited

May 9, 2013

Excel BETWEEN Formula

A little while ago Brendan asked me how he could check if values in a list fall between two limits.

March 6, 2013

Excel Conditional Formatting with Formulas

Excel’s Conditional Formatting tool is diverse with loads of built in rules that you simply point and click to use,

February 13, 2013

Excel Shortcut When Using Dates as Formula Criteria

Working with Dates in Excel can be frustrating because a date criteria in one formula doesn’t always work in another.

February 6, 2013

Excel Ignore Blanks in Data Validation List

Last week I showed you how you can extract a list that excludes blank cells for use in a data

February 1, 2013

Excel Remove Blank Cells from a Range

Last week Keith asked how he can ignore blanks in a range referenced by a Data Validation list. I think