For every person that loves Excel's ability to crunch numbers and process data, there is someone that finds spreadsheets maddening and wants to tear their hair out every time they get a #REF! error.
Earlier in 2022, Acuity Training carried out a 500 person user survey to understand how people use Excel in the real world to help us when running our Excel training courses.
In the first article of this series, we looked at how people use Excel day-to-day in their work.
Everyone gets stuck sometimes, but what causes the most problems? How often do people hit problems? How long do Excel problems typically take to fix? And finally, where do people go for help to get them fixed?
In case you're wondering, there is one final article in this series, which will be out shortly.
In it we’ll look in detail at what functions people use most and how they use Excel. So it explores things like VLOOKUP vs XLOOKUP vs INDEX/MATCH.
Right, let's dive in and have a look at Excel problems.
As you can see, three issues make up around half the answers.
The number one cause of Excel issues is formulas producing errors like #REF! and #NUM. This issue has almost twice as many people complaining about it as the next biggest one.
This was followed by 'Formatting Excel Charts' as the second-biggest cause of issues. Interestingly, this specifically relates to the formatting of charts.
The sixth biggest was getting charts to show the correct data, so it’s the presentation of charts that is causing people so many headaches.
The third most frequent problem was caused by formulas returning incorrect and/or unexpected values.
This is another category of problems, with formulas being the number one issue.
In this case, people are using the correct types of data in the right place, so the formula is not giving an error.However, they don't have the formula set up properly in some other way, as the output is obviously not correct.
After these three errors, there is a long tail of other issues. Most of these sit in the intermediate category of Excel problems.
As this research was carried out on office workers in general, rather than experienced Excel users, it’s unsurprising that basic issues crop up more frequently than errors related to advanced areas. The basic areas get used by a much larger number of people, who are less advancedExcel users on average.
29% of people (defined as once per month or less) say they never hit an Excel problem they need help with.
This leaves 71% of people needing help at least once per week or more frequently.
As you can see, 57% of people require help weekly, and in total 13% of people need external help with Excel at least once per day, with 1.6% needing help at least four times per day.
Interestingly, when we analyse the data, it is intermediate Excel users that require the most help.
Beginners actually need less frequent help than intermediates. This is down to beginners using Excel less often.
They may not become experts, but because they use Excel often, they pick up skills that take them to at least an intermediate level of expertise.
Also, the types of issues that intermediate and more advanced users have are different to beginners.
If we look at the proportion of each category who have issues with #NUM and !REF errors, it reduces significantly as you go from beginner to intermediate to advanced, and finally none at expert level.
More advanced Excel users use Excel for more complex tasks, which, in turn, leads them to hit more complex issues than beginners.
This is borne out by the fact that the only people who mention VBA-related issues are advanced and expert users. Beginners don't even try to create macros or code in VBA.
Most problems don't take long to get fixed when someone goes looking for help, thankfully.
The average is approximately eight and a half minutes.
As you might expect, when we analyse the data in more detail the issues that different levels of users (as discussed above)have gives us varying answers.
For beginner and intermediate-level Excel users, the most frequent answer is it takes 5-10 minutes to fix an issue.
For advanced users, the most frequent answer increases to 10-20 minutes, and for expert users it’s 20-30 minutes!
You will have fewer difficulties, but the ones that you have will be that much harder to fix!
Asking colleagues and asking Google are effectively tied as the places that people are most likely to go for help.
Google just beats asking colleagues at 41%versus 40%, but there is really nothing in it.
We were surprised that so many people used Google over YouTube, given how easily a short video can explain something that takes multiple screenshots to explain. We assume that many people are effectively using Google to surface appropriate YouTube videos, as these results appear increasingly frequently.
We assume that it is not used very much by less advanced users, as other online resources are so much more approachable and better presented. While as Excel nerds we like it, we would be the first to admit that its presentation leaves something to be desired as it is a technical guide.
We assume that more advanced users are using it as such. They just need the technical details of a formula that they've not used before (to pick an example) to allow them to understand how to use it.They do not need or want worked examples and longer explanations.
As we found in our analysis above, if you analyse the data by people's level of Excel expertise the answers vary considerably.
Beginners and intermediate users are similar. In the main, they use Google, colleagues and YouTube for solutions.
As you get to more advanced users, they tend to use Google and colleagues more.
Expert users are the ones that use StackOverflow and disproportionately use their IT department, presumably as they know other expert users who work there.
Hopefully, this review of what causes people issues in Excel, how often they have problems and how long it takes people to fix them agrees with your experience.
If not, don't hesitate to get in touch. We’re always keen to learn more about Excel and how people use it.