Blog • My Online Training Hub

Excel Mini Charts

May 16, 2018 by Mynda Treacy 6 Comments

Excel mini charts are a great alternative to Sparklines. Mini charts are just regular charts made really small. You might consider using them instead of Sparklines when you want something other than column, line or win/loss charts. For example, the table below contains mini area charts: Ok, so an area chart isn’t much different to […]

Excel Multi-colored Line Charts

May 8, 2018 by Mynda Treacy 25 Comments

Excel multi-colored line charts are a little gimmicky, after all we don’t need color to tell if a line is going up or down. However, it can be useful for encoding data that is outside a threshold and can also speed up interpretation, especially if you use familiar traffic light color encoding. We have a […]

Excel 3D Maps

May 3, 2018 by Mynda Treacy Leave a Comment

Excel 3D Maps in 2016, or Power Map as it’s known in Excel 2013, enable you to visualise data in, you guessed it, 3D! No surprises there, however you may be surprised to discover insights you didn’t anticipate. Because unlike 2D maps or tables, with 3D maps we can quickly identify hot spots, anomalies, clusters […]

Excel PivotTable Quick Explore

April 25, 2018 by Mynda Treacy 2 Comments

Applies to Excel 2013 onward for Power Pivot/Data Model or OLAP PivotTables. Excel PivotTable Quick Explore allows us to filter and drill down in a PivotTable: And Pivot Charts: Excel PivotTable Quick Explore requires the source data to come from the Data Model (aka Power Pivot), or an OLAP (Online Analytical Processing) cube. In other […]

Timer or Stopwatch in Excel VBA

April 19, 2018 by Philip Treacy 18 Comments

We had a post on the forum recently asking how to create a timer in Excel, to record how long things took to happen on a model railroad. You can use the same code to time anything of course, like how long your code is taking to execute. A follow up request asked for code […]

Volatile User Defined Functions

April 13, 2018 by Philip Treacy 4 Comments

Excel has the concept of volatile and non-volatile functions. Microsoft define a volatile function as one that is recalculated every time calculation occurs in any cell in the worksheet. There’s actually a bit more to it than that but for the sake of keeping it simple we’ll go with that definition. A non-volatile function is […]

Relative Named Ranges

April 4, 2018 by Mynda Treacy 12 Comments

Excel Named Ranges is a vast topic that includes some simple techniques that we all can and should use to make our spreadsheets easier to build and maintain. Plus, some more advanced techniques like relative named ranges, which are good to know. Especially for that occasion when you inherit a workbook from an Excel Superuser […]

ALT Codes for Symbols and Characters

March 29, 2018 by Philip Treacy 8 Comments

On IBM compatible PC’s, many characters that can’t be typed on a keyboard can be entered by holding down the ALT key and then using the numeric keypad to type the ASCII or Unicode code for that character. For example, holding down ALT and then typing 184 on the numeric keypad, prints ©, the copyright […]

Excel ADDRESS Function

March 22, 2018 by Mynda Treacy 4 Comments

The Excel ADDRESS Function returns a cell address for specified row and column coordinates. Optional arguments allow you to specify the style of address (A1 or R1C1), the sheet name it refers to and whether the reference is absolute or relative. Excel ADDRESS Function Syntax Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Note: Arguments in square […]

Excel Online PivotTables

March 14, 2018 by Mynda Treacy Leave a Comment

Inserting PivotTables in Excel Online is now possible. It’s still in its infancy with many features you may be used to in the Desktop version of Excel not yet available, but it’s a start. Inserting Excel Online PivotTables As with any PivotTable, you need to begin with some Tabular Data. I recommend storing your data […]