Blog • Page 14 of 24 • My Online Training Hub

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

Clearing Downstream Dependent Data Validation Lists

August 25, 2016

Clear Downstream Dependent Data Validation Lists

Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality.

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;

August 11, 2016

Slicer Controlled Interactive Excel Charts

Slicers are a great tool for easily filtering PivotTables and Pivot Charts, but what if you want to use a

August 3, 2016

Excel Chart Colors

I was reading a health magazine last week and the chart below caught my eye. click image to enlarge (sorry

July 28, 2016

Moving VBA Code and Forms Into PERSONAL.XLSB

After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you

Hide, Delete, Sort Sheets with VBA in Excel

July 21, 2016

Hide, Delete and Sort Sheets With VBA Userform

You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets

July 12, 2016

Excel Clipboard

The Excel Clipboard is an extremely useful tool that’s very well hidden. Most of us are familiar with being able

July 5, 2016

Create Regular Excel Charts from PivotTables

PivotTables are a great way to summarise your data, but PivotCharts can be a pain in the, um, neck. PivotChart

June 30, 2016

How to Record a Macro in Excel

Prompted by a couple of questions on our Excel forum I thought I'd write about how to record a macro

June 22, 2016

Power Pivot does VLOOKUP

If you use Excel PivotTables then you’re probably familiar with the need to consolidate data from multiple tables into a

June 15, 2016

Excel Status Bar

In my previous blog post I created a progress bar using a userform. We can use this progress bar to

June 9, 2016

Excel Progress Bar for VBA

If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback

June 1, 2016

Excel Power Query VLOOKUP

Since VLOOKUP is one of the most popular Excel functions it makes sense that one of the first things you

May 25, 2016

Power Query and Power Pivot Definitive Guide

One of the most common questions I get is, what’s the difference between Power Query and Power Pivot and when

May 18, 2016

Parts to a Whole Excel Charts

For many people when they think of a chart that shows parts to a whole, a pie chart is the