Easy Financial Year Formula
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
You can add 184 to the date and then use the YEAR function. See table and formulas below.
The formula in cell B2 is
The formula in cell C2 is
Both formulas have been copied down.
A simple solution to a frustrating issue. Thanks Matt.
A great book to help you create better charts/graphs. (Note: I call them charts because I use Excel – graphs may be the correct term.)
The book explains many visualisation concepts and backs it up with step by step examples of improving charts.
The before and after charts speak for themselves.
The book also places emphasis on identifying the message you are seeking to convey to the reader of your chart and making sure you are using the best chart to get that message across.
With dashboards being the buzz at the moment learning how to best display your information is a good skill to have.
This quote was used in the book and encapsulates many of the recommendations. Highly recommend this book.
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.
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 in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
Macro Webinar Materials
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.
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.