Blog • Page 8 of 24 • My Online Training Hub

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

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

April 16, 2020

Excel LET Function

The Excel LET function* enables you to declare variables and intermediate calculations inside of a formula. It’s like the DAX

April 9, 2020

Searchable Drop Down List in Excel

Creating this searchable drop down list in Excel is so easy I’m wondering why I never thought of it before.

April 2, 2020

Import Multiple Files Containing Multiple Sheets with Power Query

In this post we’re going to look at how you can import multiple files containing multiple sheets with Power Query,

March 25, 2020

Excel Project Management Dashboard

Managing projects or tasks can be improved with a visual representation of progress and milestones. Thankfully, an Excel project management

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

March 5, 2020

20 Pro Excel Keyboard Shortcuts

This post contains 20 Excel keyboard shortcuts used by Excel pros. I’m not talking about CTRL+Z, and the like, these

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

February 20, 2020

Custom Excel Chart Label Positions

When you plot multiple series in a chart the labels can end up overlapping other data. A solution to this

February 13, 2020

Lookup Pictures in Excel

We can use a data validation list or a Slicer to lookup pictures in Excel. This ability to lookup pictures

February 6, 2020

Cross Highlight Excel Charts

One of the cool features in Power BI is the ability to cross highlight from one chart to another by

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

List First Monday Date in Each Month

With Excel formulas we can easily list the first Monday date in each month, or the last Sunday, or whatever

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

January 8, 2020

Excel XLOOKUP Function

Oh, how I wish I’d had the fortune of the new Excel XLOOKUP function back in my accounting days. The

December 11, 2019

Excel Hash 2019 – Dynamic Playoffs Table

Excel Hash is an Excel competition dreamed up by fellow Excel MVP, Oz du Soleil. It’s based on the popular

December 3, 2019

Excel XOR Function

The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array,

November 28, 2019

Extract Start and End Dates with Power Query

A few weeks ago, Matt asked if we could extract start and end dates with Power Query. He has a

November 19, 2019

Excel Small Multiple Charts

Reduce clutter in busy line charts by plotting your data in small multiple charts, or panel charts as they’re sometimes

November 12, 2019

Overlay Line Chart Labels

Labeling charts is useful if the reader needs to know the exact value for each point. We can position labels

November 6, 2019

Excel Actual vs Target Chart

Often, you’ll find Actual vs Target charts based on categorical data in the form of a column chart, however they’re

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

Extract Values Present in Two Lists

Comparing two lists is easily done with Power Query, but maybe you prefer to use a formula to extract values

October 15, 2019

Reducing Data Validation List

Creating a reducing Data Validation list is easy with the new dynamic array formulas. Let’s say we have a list

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

Excel Data Tables

Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They