As an application that is constantly evolving, Excel is always sprouting features and facilities that enhance ease of use – but which can end up buried or forgotten amid the pace of work.

Luckily, there are Excel gurus who can keep those sources of assistance visible. One of those people is Simon Hurst – author of the ICAEW’s set of six on-demand Excel courses.

Here are his top tips and tricks for boosting your everyday experience of Excel:

1. Training

“There’s a touch of ‘He would say this, wouldn’t he?’ here, as I’m an Excel trainer,” Hurst says. “But there’s an important point. Many Excel users are avowedly self-taught. If they want to know how to overcome a particular hurdle, they will Google the issue they’re up against and perhaps get an answer that mostly works. But in Excel, there’s a very big difference between ‘an answer’ and the best answer.”

The problem with being self-taught on Excel and relying on web searches – or even artificial intelligence – to troubleshoot, Hurst stresses, is that you may come up with a fix that works for the time being, but may not continue to work. Or it may lead you into a slow, inefficient way of working that has a much simpler solution.

“You need the right answer,” Hurst says. “One that automates as much of the process as possible and works far into the future. For that, training is vital – and ICAEW’s 20 Principles for Good Spreadsheet Practice is a great place to start.”

He notes: “Invest in understanding Excel in greater detail before you get to the shortcuts. You can transform the way someone uses Excel in a single morning.”

2. The F4 function key

Hurst points out that F4 can be particularly helpful when fixing cell references by row, column or both to allow you to copy a single cell formula to hundreds of other cells.

“If you are entering a cell reference,” he says, “putting a dollar sign in front of the different elements of the reference lets you fix it to a particular row, column or cell. Instead of fiddling around with standard keys to put the dollar signs in the right place, you can simply press F4 when you’re entering your cell reference and it will automatically cycle between the four available options.”

Hurst says that each time you use F4, it may feel like it is hardly saving any time. However, if you use it 50 times in the course of a day, that “starts to build up into quite a nice, cumulative time saving”.

3. Ctrl-Shift-V in unison

In Microsoft applications, Ctrl-V has always been the shortcut for paste. In Excel, though, putting Shift in the middle enables you to paste a value.

Hurst notes that, for accountants, one use for that shortcut would be to fix comparatives.

He explains: “Typically, if you want to fix a set of figures at a certain point and they’re based on a formula, then every time the underlying data changes, the values will change, too.

“However, you can take a snapshot of those values by copying them and then pasting them as values, rather than formulas, using the Ctrl+Shift+V shortcut.”

4. Tables

Hurst notes that, while tables were introduced as long ago as 2007, not enough people are taking full advantage of their capabilities. As a result, Excel users often take the long way round on tasks with simpler and quicker routes.

He says: “One of the least efficient and most error-prone ways to add data in Excel, particularly if you’re reporting monthly, is to add a new month, then have to manually change all the formulas that refer to the column to reflect the additional row. If you add the data to a table instead, any formula in the workbook that needs to look at the total of the column will update automatically.”

In recent years, Hurst points out, users have also been able to add a ‘slicer’ to tables to filter the values they want to see and make applying filters more visual.

5. Power Query

Hurst believes that Power Query is the most important shortcut of the past 15 years – yet is often misconstrued as overtly complex.

“Lots of people think it’s quite a difficult tool,” he says. “There’s a common misconception that it’s all geared towards working with big datasets. However, it has clear uses within just a single Excel worksheet – indeed, there are people who’ve used Power Query to convert monthly processes from taking them days to being fully automatic.”

He notes: “It’s a different way of working and, in many ways, simpler than legacy Excel. Much of it is driven by the user interface, which saves you from needing to know dozens of different functions.”

6. Excel templates

In Hurst’s assessment, one of the best recommendations in ICAEW’s ‘20 Principles’ is that any workbook should include documentation to explain what the workbook is meant to do and how it functions. Sharing a team template can make including documentation the default, not the exception.

He notes: “One potentially big problem with Excel is that if the member of staff who began a spreadsheet and has been updating it for years leaves, the team member who inherits that spreadsheet must understand how their predecessor put it together. The danger is that they could, say, overwrite a formula with a fixed value and cause the spreadsheet to return a completely incorrect result.”

Consistent use of formatting can help show users which cells can be edited and which should not, Hurst says. Using a template can promote that consistency.

“Very few people use Excel templates,” he adds. “But if there’s stuff you do every time you set up a worksheet, they can be a major time saver.”