Blog • Page 9 of 24 • My Online Training Hub

September 12, 2019

Excel CONCAT Function

The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the

September 6, 2019

Easy PowerApps Customizations

Following on from my Introduction to PowerApps and Excel, this post looks at some customizations we can do to our

August 30, 2019

PowerApps and Excel – An Introduction

What are PowerApps? Microsoft describe PowerApps as a suite of apps, services, connectors and data platform that provides a rapid

August 22, 2019

Segmented Survey Data Chart

Charting survey data can be tricky, and when you want a segmented survey data chart it adds another layer of

August 14, 2019

Year on Year Charts

Year on year charts or data over time is one of the most common data visualisations you’ll see. However, the

August 8, 2019

Excel Bar Chart with Vertical Line

In this post I’m going to show you a way to create an Excel bar chart with a vertical line.

August 1, 2019

Working With Comments in VBA

To start with let's understand that I'm talking about the 'old' style comments like the one on the left. In

Find last occurrence of a string in another string

July 24, 2019

Find the Last Occurrence of a String In Another String

If we want to find whether or not a character or string occurs within another string we can use FIND

July 16, 2019

Excel PivotTable Field List Tips

The Excel PivotTable field list automatically appears in the task pane on the right-hand side of the Excel window when

July 9, 2019

Referencing the Next Row in Power Query

Unlike Excel, referencing the next row in Power Query, or even the previous row, is not as simple as we

July 2, 2019

Custom Data Types in VBA

If you are working with several variables that relate to one thing, for example you are working with employee data,

June 25, 2019

Excel Calendar Template Date Formulas Explained

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling

June 3, 2019

Excel Freeze Panes vs Split Panes

One of my bugbears is opening an Excel workbook that has Split Panes set when it should have Freeze Panes

May 30, 2019

Web Scraping – Filling in Forms

I've written about web scraping before using VBA, but never about interacting with the website to do things like filling

May 21, 2019

Scrape Data from Multiple Web Pages with Power Query

Previously we looked at scraping data from the web by example, where the website paginated the data. That is, it

May 14, 2019

Power Query Get Data from Web by Example

Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a

May 6, 2019

Include Grand Totals in Pivot Charts

If you work with PivotTables, then you’ve probably found that you can’t include grand totals in Pivot Charts, or subtotals

May 3, 2019

IF Formula Builder

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at

April 26, 2019

Using a Formula Instead of VBA

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA

April 16, 2019

Power Query Approximate Match VLOOKUP

A while ago I wrote about to how perform the equivalent of an Excel exact match VLOOKUP formula with Power

April 10, 2019

Find and Unmerge Merged Cells with VBA

If you've worked with merged cells you may know that they can cause issues with things like copying and pasting,

April 4, 2019

Static Variables in VBA

Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a

March 28, 2019

Get Data from the Web with Power Query

The ability to get data from the web with Power Query is super handy and Power Query’s user-friendly GUI makes

March 14, 2019

GETPIVOTDATA Function for Power Pivot

The GETPIVOTDATA function divides Excel users. You either love it or hate it, but there are good reasons for learning

March 7, 2019

Show Values as Percentage of Another PivotTable Column Total

Occasionally you may want to show values as percentage of another PivotTable column total. For example, below we want to

March 1, 2019

Scheduling Macros in Excel With Application.OnTime

If you need Excel to run some VBA at a specific time, or repeatedly at set intervals, you can use

February 22, 2019

RegEx (Regular Expressions) in Excel

RegEx stands for regular expression and is a sequence of characters that defines a pattern that can be used to

February 12, 2019

Change PivotChart Aggregation Methods using Excel Slicers

With disconnected tables in Power Pivot* we can change PivotChart aggregation methods using Excel Slicers. It allows us to create

January 30, 2019

Excel Five Star Rating Chart

Creating an Excel five star rating chart is easy with Conditional Formatting. They’re not limited to stars, you can also

January 24, 2019

Numbering Grouped Data in Power Query

Numbering items within grouped data is easy with an Excel COUNTIF formula, but numbering grouped data in Power Query requires