Blog • Page 10 of 24 • My Online Training Hub

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

Change PivotTable Aggregation Methods using Excel Slicers

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

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

January 8, 2019

Excel Sorted Dynamic Unique List

I really wanted the title of this post to be “Excel Sorted Dynamic Unique List Ignoring Blanks and Errors”, but

December 13, 2018

Play Audio and Video in Excel

We've had a few forum questions recently asking how to play audio files in Excel. Normally I'd just use my

December 6, 2018

Excel Dynamic Arrays

If you’re familiar with array formulas, then the simplicity of Excel Dynamic Arrays will be a breath of fresh air.

November 30, 2018

Save Chart as Image

You might want to save your charts as images that can then be used in other applications, like email or

November 22, 2018

Quickly Formatting Markers for Dot Plots

Mynda wrote a post about line charts vs dot plots where one of the steps in setting up the dot

November 15, 2018

Excel Power Query Pivot Chart

The options in the Pivot Chart library are limited, which you’ll know if you’ve ever tried to create a Scatter

November 8, 2018

Excel Scatter Pivot Chart

Ok, that title is a lie. There’s no such thing as an Excel Scatter Pivot Chart. It’s like mixing water

November 1, 2018

Excel Line Charts vs Dot Plot

A common chart mistake is to use a line chart for the wrong type of data. An example of that

October 24, 2018

Web Scraping Multiple Pages from Websites

My last post looked at web scraping using VBA, where I loaded a single page and extracted content. In this

October 17, 2018

Web Scraping With VBA

We can use VBA to retrieve webpages and comb through those pages for data we want. This is known as

October 11, 2018

Hide Blanks in Excel PivotTables

There are a couple of ways you can hide blanks in Excel PivotTables. To be clear, the ‘blanks’ I’m referring