Blog • Page 9 of 26 • My Online Training Hub

November 24, 2020

Dense Ranking in Power Query

There are several ways to rank things, dense ranking is when items that compare equally receive the same ranking number,

November 19, 2020

Force Excel Slicers to Single Select

There’s no built-in way we can force Excel Slicers to single select, but we can use some crafty tricks to

November 10, 2020

Excel Dashboards vs Power BI Dashboards

One of the most common questions I get is: Should I be using Power BI to build dashboards instead of

November 3, 2020

How to Build Power BI Dashboards

Demand for data visualisation skills is showing no signs of slowing. There are many BI tools available, and this can

October 29, 2020

Create a List of Matching Words When Searching Text in Power Query

My previous post searched for text in columns and returned a true/false result if a match was found. This post

October 22, 2020

Searching for Text Strings in Power Query

Let's say we have a column of text and we want to search it to see if each row contains

October 15, 2020

Introduction to Power Query

What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing

October 7, 2020

Grouped Running Totals in Power Query

In a previous post I looked at creating running totals in Power Query, here I'll be creating grouped running totals.

October 1, 2020

Power Query Custom Data Types

You may already be familiar with Excel Data Types for geography and stocks, but with Power Query Custom Data types

September 24, 2020

Quickly Create Running Totals in Power Query

A running total (or cumulative sum) is when you add the previous value to the next, basically you're summing values

September 17, 2020

Excel Custom Number Format Conditions

Custom number format conditions allow you to specify a different format based on the size of a number. In this

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

Import Data from a Picture to Excel Mobile

A new feature in the Excel mobile app (iPhone and Android) is the ability to take a photo of your

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

Import Data from a PDF to Excel

Office 365/Microsoft 365 users can now import data from a PDF to Excel using Power Query*. It’s super handy for

August 11, 2020

Plan v Actual Excel Dashboard

Reporting on plan v actual variances can be a very dry topic and often the audience for these reports are

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

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss statement is surprisingly easy. And because it’s a PivotTable you can team it

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

Dynamically List Excel Sheet Names

Navigating workbooks with lots of sheets can be tedious. In this tutorial I’m going to show you how to dynamically

July 9, 2020

Label Specific Excel Chart Axis Dates

When you have data that spans a long period of time that you want to plot in a chart, the

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

Excel Functions that Return References

Using a formula to return a reference to a range of cells allows us to generate a reference on the

June 18, 2020

Excel STOCKHISTORY Function

With the new Excel STOCKHISTORY function we can finally get historical price data for stocks, bonds, funds, crypto and foreign

5 Secret Features of the Excel INDEX Function

June 11, 2020

5 Excel INDEX Function Secrets

The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use

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

Gather Data with Excel Forms

Excel Forms are super useful if you collate data manually, for example via email or PDF forms. Or even if

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

Visio for Excel Free Add-in

Office 365 users now have access to the Visio for Excel free add-in enabling you to create Excel flow charts