I have found out yet another feature of the Name Box. The Name Box is on the left of the Formula Bar and above the column letters - see image below. During a recent macro webinar I tried to create a new range name called Test using the Name Box. But I also has already created a macro called Test as part of the training. As soon as I pressed Enter after typing Test into the Name Box to create…
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
(more…)To get the Australian financial year from a date you usually use an IF function based on the month number. I recently learned a new hack from Matt Allington of Exceleratorbi. (Have since found out Matt learned the technique from David Marriott) You can add 184 to the date and then use the YEAR function. See table and formulas below. The formula in cell B2 is =YEAR(A2) The formula in cell C2 is =YEAR(A2+184) Both formulas have been copied down.…
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable. (more…)
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names. Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel. This is the first…
Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data. (more…)
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this. (more…)