Blog • My Online Training Hub

Excel PivotTable Quick Explore

April 25, 2018 by Mynda Treacy Leave a Comment

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 6 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. I’m going to use the Timer function […]

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 […]

AutoSUM Shortcut in Excel

March 9, 2018 by Philip Treacy 4 Comments

The AutoSum shortcut, ALT+= (ALT and = at the same time), allows you to automatically sum rows and/or columns. How it works depends on what you have selected, and I’m going to look at several different examples here. AutoSum a Column AutoSum Into Cell Below Column Click into an empty cell below the column (it […]

Shortcut for Duplicating Formulae, Text and Objects in Excel

February 22, 2018 by Philip Treacy 24 Comments

If you want to duplicate something you can copy (CTRL+C) and paste (CTRL+V). But there’s a quicker way that only requires one shortcut: CTRL+D. Now you can work twice as fast 🙂 CTRL+D to Duplicate You can use CTRL+D to duplicate formulae, text, shapes, charts, smart art, pictures or pretty much anything you can select. […]

Excel Infographics

February 14, 2018 by Mynda Treacy 10 Comments

Excel infographics, like the one below, are easy to build and there are loads of tools you can use to add some sparkle. Infographics are suited to one off brochure style reporting designed to catch your eye and be easily digestible and memorable. They’re often found in newspapers or other print publications because they draw […]