Blog • Page 13 of 24 • My Online Training Hub

April 3, 2017

Excel PivotTable Distinct Count

With the integration of Power Pivot into Excel 2013 (known as the Data Model), we now have access to 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

March 16, 2017

Excel Map Charts

New in Excel 2016 (for Office 365 subscription users only*) are Map Charts. *If you have Office 365 and can’t

March 10, 2017

Excel VBA String Functions

If you are working with strings (a sequence of letters, numbers or other characters) in VBA then you are going

March 1, 2017

Power Query Unpivot

Power Query’s Unpivot tool allows us to fix one of the most common mistakes (many) Excel users make when capturing

February 22, 2017

Excel Conditional Formatting Highlight Matches in List

In this tutorial we’re going to look at how we can use Excel Conditional Formatting to highlight rows in a

February 17, 2017

Excel Custom Number Format Guide

I’ve put together this Excel custom number format guide as a resource for our members. There are loads of ways

February 9, 2017

Excel Conditional Formatting to Highlight Matches

Using Excel Conditional Formatting to highlight matches is easy when you team it up with a data validation list like

January 31, 2017

Return An Array From A UDF

This post is going to look at how to return an array from a udf. My last post looked at

January 24, 2017

Return a Range from a UDF

I've previously written a UDF to count colored cells and then perform various maths functions on the values in those

January 17, 2017

Locating Values in Large Excel Tables

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals The previous post on this topic included a

January 12, 2017

Finding a Needle in an Excel Haystack

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals When dealing with large tables containing ‘buckets’ of

January 5, 2017

Toggle Excel Conditional Formatting On and Off

One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and

December 29, 2016

Excel Icons

An awesome new feature available in Office 365 are Icons. You’ll find Excel Icons in the Insert tab of the

December 15, 2016

Excel Charts for Surveys

Survey data can be tricky to display in a chart so in this post I’m going to give you some

December 7, 2016

Excel Heat Maps

Excel Heat maps can speed up interpretation of data and focus the reader’s attention to problem or key areas. And

December 1, 2016

Excel Slopegraphs

Slopegraphs were first introduced by Edward Tufte in his book, The visual Display of Quantitative Information (1983) where he referred

November 25, 2016

Count, Sum and Average Colored Cells

We often get asked if we can count colored cells on a worksheet, and yes you can. You could count

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

November 11, 2016

Create an Excel Add-In for User Defined Functions (UDF’s)

In a previous post I discussed storing your UDF's in PERSONAL.XLSB so that you could use them in any workbook.

creating a reference to personal.xlsb for user defined function udf

October 27, 2016

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

You've created your own PERSONAL.XLSB and want to use it to store VBA that you are going to use all

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 28, 2016

Excel Styles

Using Excel Styles in your workbooks can not only make formatting them quicker and easier, but also result in more

September 21, 2016

Creating Multi-Function UDF’s

My last post looked at creating a simple user defined function (UDF). This post looks at a little more advanced

September 15, 2016

Creating a UDF (User Defined Function) in Excel

Any user of Excel will have used its functions. From the humble SUM to VLOOKUP, INDEX and MATCH, OFFSET and

September 7, 2016

Excel PivotTable Show Values As

The Excel PivotTable Show Values As menu has a load of handy instant calculations you can use. For example you

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