When do you require Excel training or an Excel expert?

What follows are a number of problems set by our clients over the years and when they should be addressed by training or by bespoke development work. A summary of the common problems are listed in the table below:

We waste hours moving data in Excel every day. Will training help?

Bad Excel use is a huge overhead for businesses. If employees are typing when they could be using formulas then training could be a solution. Unfortunately an Excel course can only scratch the surface of Excel’s time sinks so it is unlikely a half-day Excel course is going to touch on the exact problem you have.

If you’re getting sales leads and have to split thousands of names into first names and surnames then the quickest method is to use a formula. By combing the LEFT and FIND functions, you can separate the first word or name from anything that follows. If you copy the formula down your list of names, you will save hours on the person who types them all out manually. If that happened to be covered in an Excel training course at the time you had the problem, you would be very happy. If you heard it mentioned two years earlier, you’d probably have forgotten the solution.

An improvement on off-the-shelf training is a Q&A with an Excel specialist. However, AI makes that obsolete. You can ask CoPilot to split names any time you like and, if you want to know how it was done, you can ask. These days, Excel training is unlikely to save employees any time because, if they are motivated to speed up a simple process, they will already have asked AI.

There are business processes where time is seemingly wasted from start-to-finish. When prompted, an AI may be able to make incremental improvements but it’s unlikely to look at the bigger picture. That’s where you need a consultant and, if the problems are spreadsheet based, it would be best to start with an Excel consultant. Excel4Business often talks to prospective clients who want to save time through training in the hope that incremental improvements will add up. When challenged, it transpires 90% of their time is being wasted on a single process that needs streamlining.

Will learning advanced formulas make my spreadsheets faster?

Unfortunately not. Beginners tend to learn SUM and IF formulas first. Intermediate users move onto VLOOKUPs and XLOOKUPs. Advanced users use SUMPRODUCT and array formulas. The more sophisticated the formula, the slower it tends to run. That’s because more advanced formulas let you perform more sophisticated data analyses and advanced users are considered experienced enough to use such formulas discerningly.

This creates a small gap in the market for Excel training. AI has given less experienced Excel users the opportunity to use more complex formulas without being as familiar with the rest of Excel. In the past, Excel4Business would hear from businesses burdened with spreadsheets designed by a power user who had since left the company. A bit of training can help the business use those solutions more efficiently.

A simple trick is to stop Excel recalculating thousands of formulas whenever new data gets added. Sophisticated formulas often refer to huge ranges of data and are prone to unnecessary recalculation. Showing users how to set calculations to manual and guiding them through Excel’s menu settings can help people use files more efficiently. Calculation settings can only be tinkered with if understood by all users of a spreadsheet.

Advanced formulas can make the spreadsheet equivalent of Frankenstein’s monster. Whether built by AI or a power user, formula intensive Excel files have a habit of becoming slow, inflexible and fragile. Our experts can talk through the underlying intent and simplify such files. With careful consideration, complex formulas can often be removed entirely.

How do I summarise my monthly sales?

The best solution for this is generally a pivot table supplemented by pivot charts. With large datasets, especially where they come from external sources, Power BI offers a number of extra options such as drilling down on data within charts but it is essentially the same technology.

Learning how to create pivot tables has traditionally been considered an important part of one’s Excel education and has been a staple of training courses. Pivot tables have become a cornerstone of Excel training sessions because Microsoft have made them easy-to-use but, without some exposure, you might not know they existed, wouldn’t know how to ask for them, and might not have the confidence to try them. The fact they can summarise any data set means practically any intermediate Excel user benefits from using them.

Another reason pivot tables are taught in group training sessions is that the person browsing a monthly sales chart may not be the person who first developed the sheet. As pivots can be filtered and underlying data needs refreshing, it’s good for all users to have encountered them.

If training can provide institutional exposure, AI can now be employed to help design dashboards. However, an AI can only summarise data that exists. The job of an Excel consultant can be to audit your data collection processes, then help you structure your underlying data in such a way that you can develop any outputs you want. At Excel4Business, we can also help create niche outputs such as charts that can only be built by overlaying several charts on top of one another and ensuring their axes remain in sync.

How do I stop other people breaking my spreadsheets?

Ideally, the only unlocked cells in a spreadsheet would be those where users need to enter data, and the only visible cells in a file would be those users need to see. Excel lets you protect worksheets and hide anything that’s only used in calculations or dropdown lists. If a user doesn’t need to edit a spreadsheet, make it read-only.

Excel training can teach you how to lock cells or restrict user’s ability to sort data. However, the main way to stop other people breaking your sheets can be for them to have some basic Excel training.

Beginners can be taught how to use the Undo function to help them recover anything they’ve deleted accidentally. Beginners can be shown the basics of filtering data which may remove their urge to hide rows manually. They can be shown how to enter dates in a way that Excel can recognise. They can be shown how to save a copy of the file prior to making significant changes. What your users know is often more important than how the sheet is put together.

At Excel4Business, we often find there is an arms race between a spreadsheet’s creator and their users. A beginner will accept the constraints imposed on them by a spreadsheet. A more advanced user may become frustrated at how a sheet is locked down and try to develop the file further themselves. This is typically a problem with engineers, and we’ve seen the issue most frequently in the oil and gas industry where users in the field are used to having a degree of autonomy.

The only way to win the arms race, especially now the engineers are armed with AI, is to get an Excel consultant to create a spreadsheet that doesn’t frustrate the users.

How do I ensure my spreadsheets print cleanly?

If you need an Excel spreadsheet to print in portrait then you should use a limited number of columns in the output and not be afraid to wrap text over several rows. You should then adjust column widths, font sizes and margins until everything fits on a page. You may also want to repeat table headers across multiple sheets by defining rows to repeat at the top of the page.

Anyone producing PDFs or printed pages from Excel should be familiar with the page set-up options. Some, such as margins, can be found in Microsoft Word. Some, such as the ability to shrink a page to the correct width without shrinking the content to a single page, are only found in spreadsheet packages. A good Excel training session can cover the full range of options and make users aware of what is possible.

One of the biggest challenges in spreadsheet design occurs when you want to print out a small subset of data from a large table. The least efficient solution is to copy and paste the data you want every time you want to print an output. Formulas can be effective but are prone to breakages or, if the subset varies in size, they may produce an output with large areas of white space. Pivot tables can be awkward to format which is an issue when you’re preparing PDF reports.

The best method is to decide what you want the output to look like, define the spaces in which data should be inserted, then automate Excel to fill in the gaps. Condensing an almost infinite grid to a fixed number of pages nearly always involves compromise but an Excel expert can ensure you don’t end up compromising further than necessary.

How do I avoid entering the same data into multiple spreadsheets?

To enter data once, you need to decide where the data originates, and ensure any other spreadsheets take the data from that one source. This can take the form of a formula link, a data connection, or a script that extracts the data when required.

Excel training cannot offer much help with this problem except in showing users how to link one cell to another using a simple formula like ‘=A1’ and, at a slightly more advanced level, some sort of lookup formula. If the formulas link different files, a user can learn how to fix broken links.

Excel4Business would ask you why you are entering the same data into multiple spreadsheets. Sometimes the repetition is being imposed externally. The most common example we find would be when a retail portal requires you to provide product information in a specific format that doesn’t fit with other internal processes. If it’s not a one-off, our experts can write scripts to auto-generate the required output.

More commonly, repetition occurs because a company’s internal processes are no longer appropriate. Spreadsheets are thrown together as small businesses expand and, as databases grow, a few wasted minutes become a few wasted hours. At that point, the administrative team have often got comfortable with the existing Excel tools but are becoming increasingly overstretched. It can make sense to replace spreadsheets with an enterprise resource planning (ERP) system but these are often unable to replicate the flexibility of the company’s Excel tools.

The ideal solution is to keep key features of the existing tools whilst storing data centrally to avoid duplication of effort. Our Excel consultants are experts at navigating company politics and figuring out how to futureproof new processes.