Thank you Nick Melchior
Recently 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.
My sixth Thank You is not Excel related.
This gentleman is in publishing and in October 2012 he approached me to see if I was interested in writing a book on Excel. I may have said “Hell Yeah!”
Nick Melchior worked for Wiley Australia at the time and because of my CPA articles he thought I could write a book.
He asked me to put a book proposal together and he submitted it and supported it. It got through and 18 months later I had a 420 page book in my hands with my name on it as the author. A life-time dream come true.
Unfortunately shortly after the book was approved Wiley wound down its Australian operations and Nick lost his job. I will always be grateful he approached me and had faith that I could write a book.
Thank you Nick – you helped me achieve a life long dream.
Stopping the Clipboard from being cleared in Excel VBA
How to check if the clipboard is empty
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Thank You CPA Australia
My first thank you messages were directed to people who helped me learn Excel.
This, my fifth Thank You, goes out to the organisation that has helped me share what I had learned with others.
Thank You CPA Australia for publishing my first Excel article, way back in May 2002. Thank you for agreeing to do a regular Excel article in late 2002. The Excel Yourself series started out with a Q & A format.
Initially the questions were based on questions I answered during training sessions. Then I started to receive questions from CPA’s from around the world. I still receive questions, but the quantity has dropped off due to Google and the many excellent online Excel forums.
As at May 2018 there have been 170 Excel Yourself articles plus 8 feature articles. Articles now appear online and each has a companion video and many have companion files.
You can see the articles here.
Over the years I have also worked with CPA Australia in creating and presenting training sessions for conferences and running training sessions in regional areas. I have recently done a few podcasts, plus I have written extra posts and done videos for the INTHEBLACK website.
Thank you CPA Australia for providing a platform to help me share my Excel skills with Australian accountants from around the world.
It all started from that first article all those years ago. By the way the first article was about the Ctrl key and its shortcuts – see image below.
Inserting a Blank Row Between Entries
Manual technique
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
Thank you Bill Jelen
Recently 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 fourth on my Thank You list is Bill Jelen (aka Mr Excel).
Through the Mr Excel website Bill has directly and indirectly helped more people than probably anyone else in Excel. The site
I have used the site’s forum to solve many problems over the years.
The forum has many Excel experts answering questions and solving problems from people from around the world.
Bill has written many books covering most of Excel’s topics.
I own a few of his books and have read many more. He tells it like it is and is not afraid to say if he disagrees with changes made to Excel.
Thank you Bill for being so generous with the your knowledge and for your huge contribution to the Excel community.
My career is better for having read your books and used your website – thank you.
Step Charts in Excel
Hacking the error bars
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
Free Excel Webinar Recording – Text Functions explained and demonstrated
How to text safely in Excel
My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video.
You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn
- the different techniques to split text
- techniques to extract text from text
- how to easily join text
- techniques for tweaking text for dates, numbers, upper and lower case
- the formulas for extracting sheet and file names
- two new Excel 2016 functions for combining text from ranges
As always, I will be sharing a few other tips during the session.
Download Text Function materials
2018-05-15
I prefer to call them Bridge charts rather than Waterfall charts, but Waterfall is the common name.
Excel added Waterfalls in Excel 2016.
I think the name Bridge is more descriptive since a bridge takes you from one place to another which is what the chart does with values.
Waterfalls in nature only fall down, whilst a waterfall chart has measures that rise and fall.
This blog post show many examples. Most examples are not done in Excel.
Thank You Matthew Harris
Recently 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.
My third thank you goes out to Matthew Harris. Here is a link to his website
Back in the 90’s I taught myself VBA using his book – Teach Yourself Visual Basic For Applications in 21 Days. It has pride of place on my bookshelf.
The book really made a difference to the way I used Excel and opened my eyes to so many possibilities.I found I really enjoyed programming when working in VBA and Excel.
I have learned a lot since, but his book gave me a great grounding in VBA.
Thank you so much Matthew for your book, it has made a huge difference in my life.
Show all comments
Cell comments are useful for instructions and documentation.
If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down.
Once visible this shortcut also hides all the comments in one go.
This is an old Excel 2003 shortcut that still works.
Find the first and last dates in a filtered list
Super power to the rescue
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
2018-05-08
A good blog post on the when and how to use dotted lines in charts.
Dotted lines are great for lines you want on the chart but not featured.
Also for showing forecast values on the same chart as actuals.
Thank You Jeff Robson
Recently 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.
My second thank you goes out to Jeff Robson from Access Analytic.
Jeff hired me as an Excel consultant way back in 2006. He asked me if I knew someone who could do the job and I said I did – me!
We had met a few years earlier and kept in touch. We had a few things in common, a love of Excel and a similar hair line and when I started we even drove the same car, a Hyundai Accent.
We worked together for 4 years and my live is better now because we did.
An extract of the thank you email is shown below.
Pie Charts and Negatives – an Alternative
A waterfall solution
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
Don’t forget, many people (mainly men) have issues seeing all the colours.
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.
Seth’s website is a great resource on marketing and business in general.
He has recently updated it to WordPress and it looks impressive.
Lots of videos, podcasts and his over 7,000 blog posts (yes 7,000).