Blog • My Online Training Hub

Creating Multi-Function UDF’s

September 21, 2016 by Philip Treacy 2 Comments

My last post looked at creating a simple user defined function (UDF). This post looks at a little more advanced subject of combining multiple functions into one. Sometimes you’ll write code to do similar jobs. Maybe one function to remove alpha characters (letters) from a string, and another to remove non-numeric characters. Function RemoveAlphas(CharString As […]

Creating a UDF (User Defined Function) in Excel

September 15, 2016 by Philip Treacy 7 Comments

Any user of Excel will have used its functions. From the humble SUM to VLOOKUP, INDEX and MATCH, OFFSET and myriad others. Excel also allows you to create your own functions, these are known as user defined functions, or UDF’s. By creating a UDF you can expand the functionality of Excel and get it to […]

Excel PivotTable Show Values As

September 7, 2016 by Mynda Treacy 4 Comments

The Excel PivotTable Show Values As menu has a load of handy instant calculations you can use. For example you can choose to show the values as: Percentages of the row, column or grand totals The difference from or % difference from Running totals Ranking And index Kevin emailed me with a vehicle log book […]

Excel Table Running Total Formula

September 1, 2016 by Mynda Treacy 29 Comments

You’ve probably seen a running total formula like this before: = SUM($E$3:E4) You know the one, where the first cell reference is absolute and the second isn’t so that when you copy the formula down the column the range increases by one row at a time. Clever huh. However when you put a formula like […]

Clear Downstream Dependent Data Validation Lists

August 25, 2016 by Philip Treacy Leave a Comment

Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality. What this code does is to clear any cells dependent on the value of another data validation list. I'll call these downstream cells. So if I change the Country, I'm going to clear the values […]

Excel Dependent Data Validation

August 16, 2016 by Mynda Treacy 14 Comments

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here; Dynamic Dependent Data Validation. However the approach I’m going to cover in this tutorial is probably the best I’ve seen, especially if you have a lot of dependencies as it’s easily scalable. What is Dependent […]

Slicer Controlled Interactive Excel Charts

August 11, 2016 by Mynda Treacy 13 Comments

Slicers are a great tool for easily filtering PivotTables and Pivot Charts, but what if you want to use a Slicer to control a regular chart like this: Note: This technique applies to Excel 2010 onwards. Download the Workbook Download the Excel Workbook. Note: this is a .xlsx file please ensure your browser doesn’t change […]

Excel Chart Colors

August 3, 2016 by Mynda Treacy 15 Comments

I was reading a health magazine last week and the chart below caught my eye. click image to enlarge (sorry about the quality, it’s a photo of the magazine and photography is not my ‘thing’ 😉 ) × There’s something fundamentally wrong with this chart. Can you see what it is? Overall the layout is […]

Moving VBA Code and Forms Into PERSONAL.XLSB

July 28, 2016 by Philip Treacy Leave a Comment

After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you might want to put this into your PERSONAL.XLSB. If you leave the code/form in the workbook it was created in, then that workbook needs to be open every time you run the code or use […]

Hide, Delete and Sort Sheets With VBA Userform

July 21, 2016 by Philip Treacy 6 Comments

You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets or just sort them. By using a very simple userform and some VBA we can do these things with a few clicks of our mouse. The Form The forms consist of one ListBox and four […]