Article 23: What Excel Skill leads to the greatest source of inefficiency?

Summary

There are many ways to use Excel badly.? And many courses are available for you to learn Excel as badly as you can.? However there is one skill that stands above them all.? In this article I will explain what it is, and of course provide a far better option.

Excel Courses

For many Excel users it is difficult to know where to start in order to learn Excel.? Some courses are similar in title to ‘All you need to know about Excel’.

Yet other courses are somewhat more humble, and focus on specific skills, which hopefully will lead you to use Excel efficiently, logically, faster and with less errors.

I started teaching Excel some 5 years ago, and it evolved because I used Excel to write software, and realised then, many people were using Excel inefficiently.? My course participants are mainly scientists and engineers, as their cognitive processes are similar to mine.

However it does not matter where I go or who I teach there is one skill that is ‘essential’ to learn, but when I show them the alternative it appears I wasted their time by teaching the ‘essential’ skill in the first place.

Formula Referencing

Some of you may have already figured it out.? That skill is formula (or cell) referencing.

Now before you become a bit too hostile to this article I should add that I also use formula referencing.? However, I use it sparingly; and would never teach formula referencing without also teaching the alternative.

What are the Advantages of Formula Referencing?

Now in order to avoid being too hostile, I will first consider the advantages.

1.?????? It works.

2.?????? It was the basis of the initial versions of Excel.

3.?????? If one likes A1 notation, cell referencing is essential.

4.?????? It is impossible to understand most spreadsheets without knowing cell referencing.

5.?????? It is likely that in your work environment, this is the way things are done.

What are the Disadvantages of Formula Referencing?

1.?????? It is clumsy

2.?????? It is often difficult to understand the spreadsheet structure by using formula referencing; that is the workbook is not transparent.

3.?????? It is not logical.

4.?????? It is error-prone.

5.?????? It is often inefficient.

Now in some ways we have scored 5? advantages and 5 disadvantages – so we are even.? However there is one overwhelming disadvantage.? Most people find the use of relative referencing very difficult.

I will often give participants middle-difficulty exercises and ask them to use cell referencing to apply a formula.? I have been shocked with how this often takes a long time – and in most cases I have to provide assistance.

That is, it is easy to say to a participant:? just repeat what I do, but quite a challenge when I do not provide much guidance.

Furthermore they will construct formulae that are often difficult for others to understand.

I teach this skill because then they can grasp the advantages of the alternative.

The Alternative

Now before I discuss the alternative, I again have to make a few things clear.

There is no silver bullet – but requires a number of skills.? Furthermore, Microsoft has not developed Excel as best they could. In order for one to use the alternative approach, efficiency is increased by using 3rd party add-ins.

The alternative? is therefore based on 3 (and possibly 4) skills.? The essential 3 skills are:

1.?????? Named Ranges

2.?????? Arrays

3.?????? VBA

Here, Named Ranges cannot be separated from arrays.? Once one masters Named Ranges and Arrays, the need for VBA is obvious.

The 4th skill which is not essential but also highly valuable is data flow diagrams (or flowcharts).

Here I will only focus on the first 2 essential skills.

Applying Named Ranges

Now let us consider a simple example.? I have a formula:

And I asked what is the purpose of this formula.? Unfortunately many dedicated Excel developers will say it is calculating the SumProduct of data from two different columns.? They are of course, correct, but totally unhelpful.

Instead one has to look at the referred to data, identify what it is, and then identify why the SumProduct is used.

They may then give an answer.

However the alternative equation may be:

A Named Range has largely captured the type of data the formula is being applied to, and then it is not difficult to figure out why Sumproduct is being used.

This is an elementary example of the use of Named Ranges.

However the cognitive jump in using Named Ranges is that we apply formula to groups of data, rather than a set of cells.

This type of thinking has similarities to object-based programming.

I could go on, and go into more detail – particularly with the use of arrays.

However my point is that once Named Ranges are created, participants can easily create formula.? So much so that once the feedback I received for a course was that the section on teaching formula referencing was a waste of time.

Course participants find it much easier using Named Ranges for formulas than using A1 Notation (cell references).

The two Schools of Thought

Therefore there are two schools of thought:

1.?????? Do not teach Named Ranges

2.?????? Only use Named Ranges (not cell referencing)

Whilst I am more slanted to the latter, I should point out the following:

1.?????? For simple formula, formula referencing is useful.

2.?????? Microsoft has not fully developed named range capability, and so I personally use Add-ins that improve the use of Named Ranges.

3.?????? If my workbooks were to be provided to a large number of clients I would lean more to using Named Ranges.

Now you may also lie somewhere in the middle.? However for many Excel users they may have not been taught Named Ranges at all.? Those people are the ones who are likely to be wasting the most time.

Action Plan

• Whilst I provide some Udemy courses, the most relevant here is ‘Effective Use of Named Ranges’.
• If you work for a Company that values efficiency and training, I strongly encourage the direct course.