Blog • Page 15 of 24 • My Online Training Hub

May 10, 2016

Interactive Excel Charts

Using interactive Excel charts in your dashboards and reports allows the user to pick and choose what they want to

May 3, 2016

Excel CHOOSE Function Toggle

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions

April 27, 2016

Maximize Excel Across Multiple Screens

If you want to maximise Excel on a single monitor you can just click on the maximize button That's ok

April 19, 2016

Excel MROUND Function

Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was

April 11, 2016

Classic PivotTable Default Layout

In Excel 2007 Microsoft thought they’d get clever and impose nested row labels in our PivotTables: If you’re a PivotTable

April 11, 2016

Excel PivotTable Default to SUM instead of COUNT

If you put a field in the values area of a PivotTable and it defaults to COUNT as opposed to

show text, numbers, dates, formulas on sheet

April 6, 2016

Highlight Text, Numbers, Dates and Formulas on a Sheet

Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells

March 29, 2016

Excel Compare Two Lists

A common task in Excel is to compare two lists to identify which items are duplicates, different or missing from

March 21, 2016

Avoid Writing Complex Excel Formulas

There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise

March 15, 2016

Get Started With VBA – 3 Easy Examples

You can create very complicated and powerful VBA routines in Excel, but not all VBA code has to be like

selecting multiple items in data validation list

March 3, 2016

Populating Multiple Cells from Single Data Validation (Drop Down) List

If you want to limit the choices available for the values that can be entered into a cell, you could

February 23, 2016

Excel Alternatives to Radar Charts

I get the fanfare around infographics. They’re used to make information more appealing, grab attention, awaken interest, lessen boredom and

February 17, 2016

Excel Dynamic Text Labels

I use Dynamic Text Labels all the time, whether it’s in a Dashboard report or chart title, and even to

February 9, 2016

Power Query Reformats Ugliest Report Ever

In this tutorial we're going to reformat Excel reports with Power Query. Why? Because external systems can create some pretty

February 1, 2016

Power Query Version Compatibility and Installation

When you know what Power Query is capable of you’ll want to get your hands on it immediately. It’s available

January 27, 2016

Transpose Data in Excel

There are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Enter

January 20, 2016

Excel Multi-cell Array Formulas

Excel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’

January 12, 2016

Excel ROWS and COLUMNS Functions

You'll typically find the ROW(S) or COLUMN(S) functions nested inside other functions because they make formulas more efficient to write.

December 15, 2015

Use Excel Slicer Selection in Formulas

Slicers are a great tool for incorporating interactivity into your reports but we don’t always want to analyse our data

December 8, 2015

Creating Excel Charts from Multiple PivotTables

In an ideal world our data will be in one table so we can easily analyse it in a PivotTable

December 4, 2015

Error Handling in VBA

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when

November 24, 2015

Excel Advent Calendar 2015

Around this time last Year I created an Advent Calendar in Excel: To Count Up or To Count Down? Now,

November 17, 2015

Excel Pivot Cache

Updated 16 May 2023 Note: This does not apply to Power Pivot aka Data Model PivotTables. When you create a

November 10, 2015

Excel User Voice

I bet there's been the odd time you've said to yourself "wouldn't it be nice if Excel had..." Or maybe

November 6, 2015

Finding File Meta Data Using FileSystemObject

In a previous post I looked at using the FileSystemObject to search through folders on your computer's disk and create

October 27, 2015

Calculating MEDIAN in PivotTables

Unfortunately there’s no built in way to calculate the median in a PivotTable. The approach is different depending on whether

October 20, 2015

Combine Excel Worksheets with Power Query

A recurring task in my previous life as an accountant was to analyse actual vs budget figures. However the figures

October 13, 2015

Excel Slicer Trick

That’s a rubbish title, I know. I couldn’t think of a succinct way to describe what I’m about to show

October 6, 2015

Excel AGGREGATE Function

The AGGREGATE Function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions

October 1, 2015

Save Settings in Registry Using Excel

If you want to save data from one Excel session to another, you can store this in the Windows registry.