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.
Free Excel Webinar – recording Macros Tips Tricks and Traps
Getting started with macros
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.
Excel and Outliers
New functions make it easier to find them
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.
My free Excel webinars are back
All webinars are not the same
I recently ran my first public webinar in nearly a year – Materials Link and streaming Video below.
I run free and paid Excel training webinars. Watch a recording or come along to a free session and see if you like them.
My aim is to make my live training webinars as enjoyable, useful and practical as possible, which means
- no PowerPoint slides EVER
- zero marketing or up selling – just Excel training
- no recorded content – its all live and interactive – comments and questions can be sent via a chat box
- detailed instruction manuals – yes, even the free sessions have a detailed pdf manual
- the free session recordings are always available – no time limits on watching
- paid recordings can also be downloaded
- annual attendance listing for CPD purposes when you attend a live session (sent out late January each year)
- you must record your own CPD when you watch a recording (like the one below)
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 Exceleratorbi.
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.
A simple solution to a frustrating issue. Thanks Matt.
Storytelling with Data (Wiley, 2015)
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.
2018-04-02
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Webinar Feedback
Its great to get feedback like this. Webinars can be an effective alternative to face to face training.
Power corrupts. PowerPoint corrupts absolutely.
Edward Tufte – Yale Professor Emeritus
Essentialism (Virgin Books, 2014)
Essentialism is a great concept and well explained and described in this book. Lots of examples and ways to include it in your life.
It is a different mindset and will take a while to implement and incorporate.
I read this book with Deep Work by Cal Newport they compliment each other.
Highly recommend.
Identify Milestone Years in Excel
MOD function to the rescue
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.
Don’t wish it was easier, wish you were better.
Don’t wish for less problems, wish for more skills.
Don’t wish for less challenges, wish for more wisdom.
Jim Rohn (1930 – 2009)
Deep Work (2016)
A good book to encourage and motivate you to focus more.
Social media and the internet are designed to keep your eyeballs on the screen. This doesn’t get the worthwhile work done.
Moderation is tough in anything and with the dice loaded against you its hard to limit time on the net.
Some good suggestions and ideas to help you get more great work done.
I read this book with Essentialism by Greg McKeown. they compliment each other.
Highly recommend.
Excel Test For One of Two Conditions
A shorter alternative
In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.
Amazon
Bless their algorithmic hearts, Amazon are still trying to sell me my book.
It was released four years ago in April.
In the hopes of reaching the moon men fail to see the flowers that blossom at their feet.
Albert Schweitzer (1875 – 1965)
Peak Performance (2017)
Some great practical advice on areas to improve on.
Applies to many different areas of life.
Basic premise is simple, but some great suggestions on how you can improve in all areas of your life.
Excel Formula to Extract the Domain
Using the SUBSTITUTE function
I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Keyboard
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
Mouse
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
Jon Peltier is a legend when it comes to charts in Excel – check out his post on Gannt charts in Excel.
Gannt charts are a project management tool that help you visualise and understand the relationships between tasks and how the project time line is progressing.