Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.
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.
Identifying Locked Cells in Excel
How do you know if a cell is locked in Excel? There is an easy was to find out. And it also makes it easier to lock or unlock the cell.
Cole Nussbaumer Knaflic
2018-04-27
2018-04-27
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.
Entering Headings via a Macro in Excel
Handle a range in one go
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.
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.
PivotTable Combinations Trick
How to get a list of combinations
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.
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.
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.
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)
Don’t forget, many people (mainly men) have issues seeing all the colours.