Blog • My Online Training Hub

Highlight Cells Referenced in Excel Formulas

September 19, 2017 by Mynda Treacy 6 Comments

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. […]

VBA Shell

September 15, 2017 by Philip Treacy 2 Comments

The VBA Shell function runs a command in the operating system shell. The shell refers to the interface, typically where you type commands, to run programs. This is called a command line interface or CLI. In Windows, the shell is commonly known as the Command Prompt. To access it, click on the Windows button and […]

Excel Pivot Chart Drill Down Buttons

September 7, 2017 by Mynda Treacy 2 Comments

Excel Pivot Chart Drill down buttons are a new feature available in Excel 2016. You’ll find them in the bottom right of your Pivot Chart. They’re available whenever you have more than one field in the Axis area of the Pivot Chart (which is the Rows area of your PivotTable). You can see below that […]

Excel Extract a Unique List

August 22, 2017 by Mynda Treacy 8 Comments

As with most things in Excel, there are many ways to extract a list of unique or distinct values from a column of data. We’ll look at the options, and the pros and cons of each. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email […]

Excel Find and Remove Duplicates

August 15, 2017 by Mynda Treacy 2 Comments

Sometimes we want to find and remove duplicates in Excel, other times we just want to identify or highlight duplicates. There are loads of ways to tackle this in Excel, so let’s look at the options. Download the Workbook The workbook contains screenshots and notes so you can use it as a reference guide later, […]

VBA SELECT CASE

August 10, 2017 by Philip Treacy 10 Comments

VBA SELECT CASE allows you to test an expression against multiple conditions, and then carry out a series of commands when a matching condition is found. It’s like using lots of IF statements, but easier and clearer to understand. Here’s the basic structure of SELECT CASE Select Case Expression Case Condition_1 Code Block Case Condition_2 […]

VBA IF THEN ELSE

August 3, 2017 by Philip Treacy 4 Comments

The VBA IF THEN ELSE statement lets you test a condition, and then carry out other commands if the test result is true (or equivalent to true – more on that later). Excel has an IF function that you use in the worksheet, but this is a different beast and does not work the same […]

Excel Convert Dates to Fiscal Quarters and Years

July 27, 2017 by Mynda Treacy 8 Comments

If your company’s fiscal year runs in line with the calendar year then it’s easy to classify your dates into quarters and years for reporting. Convert dates to quarters with this formula (where cell A2 contains your date): =ROUNDUP(MONTH(A2)/3,0) And if you want to prefix it with ‘Q’, then use this formula: =”Q”&ROUNDUP(MONTH(A2)/3,0) Convert dates […]

Power Query Unpivot Scenarios

July 20, 2017 by Mynda Treacy 9 Comments

In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data layout problems: The Straightforward Unpivot Unpivot Multi-column Data Types Unpivot Nested Column Headers (the worst offender): And to flip things around, literally, we’ll use the Pivot tool to fix Repeating Rows, which is commonly referred […]

Symbols in Excel Slicers

July 11, 2017 by Mynda Treacy 8 Comments

Did you know you can display symbols in Excel Slicers? That’s right, Slicers aren’t just limited to listing boring old items from your PivotTables or Tables. In the animated image below I’ve got a Slicer that displays currency symbols. As you click on a currency symbol in the Slicer, the chart converts to the selected […]