Poor Excel skills are costing UK businesses millions in lost revenue. According to an article in the Financial Times more than half of financial service groups have “poorly applied or no controls for managing business critical spreadsheets”.

The article blamed losses in part due to “avoidable errors in MS spreadsheets”. So, what could thousands of UK businesses do differently?

THE PROBLEM: “Fat finger” input mistakes

Excel spreadsheets are an integral part of many UK businesses. Used for anything from accounting to CRM, they are a system with a low barrier to entry. Business users start one up and start recording data.

Mistakes in Excel, however, can often be harder to spot than those in other MS applications. Though error messages will appear for misspelt formulae, they won’t pick up incorrectly populated fields. Help is at hand though, here’s how to reduce the risk of “fat finger” input mistakes:

THE SOLUTIONS:

Data Validation

All but eliminate the room for error with data validation formatting. This Excel feature will allow you to:

• Make a list of possible entries, restricting the values allowed in a cell
• Create an automated message when incorrect data has been inputted
• Set a range of numeric values that can be entered into cells
• Determine if an entry is valid based on calculations of other cells

By restricting the values allowed in a cell and setting formatting properties, you’ll have tighter control over editing functions and are likely to experience fewer cases of fat finger mistakes. Sure, these will still be made, but they’ll be visible right away.

To view a step by step guide of how to tutorials, visit the Microsoft Office support centre.

VLOOKUP

If there are two tables of data that need to be cross-referenced, or you need to pull data from one table to another, don’t copy and paste. VLOOKUP is arguably the most useful function in Excel, and mastery of it will ensure that exactly the right data ends up in exactly the right place.

IFERROR

Pre-empt the fact that your formulas may run into errors. Assume they will and use the IFERROR function in Excel to provide a ‘catch’ scenario – replacing the error with a blank or zero for instance.

IFERROR’s are a quick and easy way to see null values, often a result of human error, whether that’s a formula, data entry or another error.

THE PROBLEM: No audit trail

With poor controls over quality control being blamed for huge monetary losses, auditing things like who has edited a workbook can be a useful way of keeping track of minor changes, that could have major consequences for your business. Excel offers a number of solutions to best fit your company’s skills set.

THE SOLUTIONS:

Workbook tracking

Quickly and simply see the changes made to your shared workbooks by tracking changes. This is great for organisations with multiple editors, allowing each author to see the additions and overwritten fields upon reopening the spreadsheet.

Excel offers three methods of workbook tracking:

• On-screen highlighting

A great way to see changes quickly, with edited boxes highlighted in a different colour for each user. Hold the cursor over the changed cell to see a brief description of the edit. It’s ideal for at a glance reviewing or for workbooks with few changes.

• History tracking

Excel can produce a separate history worksheet that provides a list of change details which you can filter and search for. This method is ideal for worksheets that have incurred a series of changes.

• Reviewing of changes

If you’re evaluating comments from other users, this method is especially useful. Excel can step you through the changes made in sequence so that you can decide whether to accept or reject the amendment.

Utilising Excel systems

You can also use Excel systems as a way of preparing reports or standardising systems in more detail.

Creating reports using PivotTables

There’s no faster or more convenient way to generate dynamic reports. Better still you can build controls so end users can manipulate the results with ease.

Automation with macros and VBA

Performing repeated tasks in Excel can be tedious, which in turn can lead to laziness and human error. Macros capture repetitive tasks for easy playback.

The language that feeds macros is VBA. It’s a programming language that sits alongside Excel. It allows you to program and automate processes and while the learning curve is steep, it opens up the possibilities in Excel exponentially. The amount of human error reduced and time saved is staggering.

Conclusion

In training and consulting scenarios we have seen spreadsheets with errors and gaps in them that are waiting to be exploited. But with just an hour or two’s education we have been able to transform leaky models and flabby formulas into watertight, lean applications.

We’ve been training Excel for years and are proud of our 98%+ recommendation rate, delivering value with every course. Here’s what a representative of Credit Suisse had to say about an Excel Advanced course:

“There were functions in Excel that have always been there until today I had no idea what purpose they served. The trainer was very informative, knowledgeable, pleasant to work with and above made the session very well run.”

Anyone concerned that their spreadsheets are the weak point in their projections should put some research into Excel training, and stop financial losses today.

Intrigued? Get in touch with Best STL today to discuss your training needs.