Philip Treacy, Author at My Online Training Hub • Page 2 of 5

September 3, 2020

Power Query Custom Functions

If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically

August 20, 2020

Display All Matches from Search in Userform ListBox

I've previously described how to use a form to search for a distinct value, like a unique ID. This time

August 6, 2020

Connecting to an OAuth API Like PayPal With Power Query

My previous post on using Power Query to access API's looked at API's that required either basic or no authorization.

July 23, 2020

Getting Started with API’s in Power Query

An Application Programming Interface (API) allows a program to retrieve data from a system. A 'program' could be something written

July 3, 2020

Animating Excel Charts

If you are going to use animation in your charts, you should do it to enhance the story your data

June 4, 2020

Dynamic Data Validation Lists in Userforms

Let's say we need to assign staff to work different shifts and no person can work both early and late

May 27, 2020

Show Report Filter Pages for Power Pivot PivotTables

If you are using a filter on a 'regular' pivot table (one that isn't using source data from Power Pivot),

May 8, 2020

Charting Real Time Data in Excel

I've previously written about getting real time data (RTD) into Excel using Python. In this post I'm going to chart

April 20, 2020

Select Multiple Items from Drop Down (Data Validation) List

Data validation lists will only let you choose one item from the list. But what if we have a situation

Excel Calendar (Date Picker) to Use in Worksheets and Userforms

March 18, 2020

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

I've written about using calendars/date pickers in userforms before but this one is particularly easy and flexible to use, even

March 11, 2020

Automating and Emailing Pivot Table Reports

Here's the scenario: I want to send different pages of my pivot table report to different email recipients. I can

February 26, 2020

Jitter in Excel Scatter Charts

If you use a scatter plot for a dataset that has discrete values in one dimension, for example your x-axis

January 28, 2020

Searching for Data With a User Form

I was recently asked to provide the ability to search a list of records (like a table) using a user

January 11, 2020

Using Jitter to Avoid Over Plotting in Power BI

If you need to plot data that has one variable where values can be the same or very similar, for

October 30, 2019

Checking Values in Range Objects With VBA

One thing I often found frustrating with VBA is the lack of any easy way to see the values in

October 9, 2019

Excel DGET Function

Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH. However if your data

Extract Date and Time from DateTime Value

September 24, 2019

Extracting Date and Time from a DateTime Value

Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole

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

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 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 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 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

January 15, 2019

VBA Like Operator

The VBA Like operator allows you to compare strings against a pattern. You can search a string to check if