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

 

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.

 

Waterfall charts

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.

Link to blog post.

 

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.

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.

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.