Thank you John Walkenbach

Last week I learned about the passing of Chip Pearson an Excel legend.

He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way.

The first is John Walkenbach.

John’s books got me started in Excel.

He has written about most versions and most topics on Excel.

I sent him an email – an extract is shown below.

 

Name Box and Macros

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 the Test range name, I was magically transported to the VBA window to the Test macro – Wow!

This means you can’t create a range name in the Name Box that is the same as a macro name.

You have to use the Define name icon on the Formulas tab to do that.

 

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.

 

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.

Macro Webinar Materials

 

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.

Gannt Charts in Excel

2018-04-02

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.

 

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.

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.

 

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)

Download Materials