Bespoke Blog – How to Optimise Your Excel Spreadsheets

How to easily optimise your spreadsheets:

1. Create tables
2. Named ranges & formulas
3. Pivot tables
4. Sorting data
5. Manual calculation mode
6. Non-volatile formulas
7. Create effective formulas
8. Keep formulas in a separate sheet
9. Crisis measures
10. Learn new formulas

Now you know best options for Excel spreadsheet optimisation, learn about each one to source the most effective approach for you. So many of our clients know there are better ways to manage and optimise their Excel spreadsheets, but often believe that establishing new systems could be time-consuming or confusing.  The truth is that with a little up-front input of time and familiarity that comes with regular use, your Excel data management could be saving you time and money in the longer run.

Here are some of our suggestions for you to optimise your spreadsheets and get more from them and your team.

1. Excel Tables are Ideal For Data Management

Amazingly, we encounter businesses who still use tables in Word for data management. This makes for analysis, reporting and connections between data incredibly time consuming. Excel tables can be used with formulas and category references, for use in pivot tables for filtering information and drilling into data for precise insights.  Your dynamic formulas mean that as your data breadth and depth changes, analytic systems remain constant.

For example, let’s say you have table called ‘Sale Volumes’ (‘SV’), the formula Autosum (SV[column_header) means you can quickly add all values in that spreadsheet column. Regardless of changes to the data, this formula still works.

2. Named Ranges and Named Formulas

It may seem obvious, but simplifying spreadsheets by using unique names and formulas named in common sense ways helps to smooth workflow. Furthermore, named ranges and named formulas mean you can access arrays of data and values quickly, where you need to regularly refer to information. Using ‘does what it says’ named formulas mean users can quickly make sense of systems.

3. Pivot Tables

Pivot tables allow a lot of data to be investigated quickly with just a few clicks.  Once your pivot is built, you access blocks of data and values simply, reducing the headache of unnecessary calculations. Pivot tables are particularly useful for looking at business essentials from different perspectives e.g. revenues by product type, or geographical sales.  Changing the data is also made easy, via the DATA ribbon, which refreshes all data sets in one go.

Suffice to say, Pivot Tables require your business to use Excel spreadsheets to manage your data.

4. Sort Your Data

One reason for slower performance can be using un-sorted data. Find the ‘SORT’ function on the HOME ribbon in the EDITING menu, or on the DATA menu. Often data is imported without any basic organisation. Simply highlight the data you want to sort or the whole sheet, by selecting the tiny box in the top left corner, with a right-angled triangle in it, sitting between Column A’s header and the first row number.

Sorting your data this way takes a minute or two and helps see trends quickly. However, if you add new data to your list, automating the sorting process means you do not have to revisit the SORT & FILTER function. VBA macros can simplify the process of keeping your data sorted, so you don’t have to repeat that task. It’s a quick and easy fix for competent Excel users.

5. Manual Calculation Mode

Speed can be the price you pay for complexity, however, in reality sophisticated business systems intelligence is complex. Where diverse business makes exceptions to rules more likely, formula calculations can be set to manual mode for greater control over analysis of diverse data sets.

F9 on a keyboard gives you access to run any formulas.

6. Non-volatile Formulas

There are a type of Excel formulas known as volatile formulas. These are re-calculated whenever there is a change in a spreadsheet. Examples these formulas include: NOW, TODAY, OFFSET, amongst others. If your workbooks contain a lot of volatile formulas, they must all be re-calculated with any changes made, making for slow, tedious data management.

The simple solution is to not use them, given other options available. For example, instead of using OFFSET to create a dynamic data range, choose the non-volatile and faster, INDEX, or get expert advice about optimising your tables, so you can cut out learning curves and instantly speed up operations.

7. Create Effective Formulas

Here are some guidelines to follow when creating formulas.

• Established Excel formulas can to be better than reinventing your own version.
• When needing to refer to only selected values in a column, ensure precision i.e. Only write SUM (A1:A10), rather than SUM(A:A), when you only need specific values.
• Use IFERROR to simplify the process of checking data validity.
• Review where data reporting seems to be demonstrating anomalies and where necessary, remove, fix formula errors or find new solutions for reporting
• Review array formulas for their value in work processes; they are not always easy to use.
• Reducing references to other workbooks can speed up data manipulation processes and reduces likelihood of errors.
• Remove any named ranges that result in error or missing links.
• Explore alternative formulas that can offer better business solutions.

8. Keep Formulas in a Separate Excel Spreadsheet

Formulas underpin the easy manipulation of any of your Excel workbook data. By recording them all them in a separate spreadsheet, you can re-use them and apply them to new workbooks, while minimising the risks of errors, omissions or repetitions.

When it comes to troubleshooting spreadsheets, this whole process becomes easier too, as having them all in one place simplifies investigation and debugging. The more complex your data systems, the more important this simple step becomes.

For expert Excel users, this resource also helps to work through new calculations and planning data management in a more systematic and structured way.

9. Crisis Measures

Sometimes, regardless of your staff proficiency, workbooks can remain slow.

• Redevelop a workbook from scratch: This radical approach eliminates outmoded thinking and can result in innovative new systems which update processes and improve systematisation.
• Replace external data links with actual data: Import data by copying and pasting in order to eradicate any formulaic errors arising from another source.
• Reduce the functionality: If a user can still be efficient with fewer features in a workbook, simplification can be a solution.
• Replace formulas with values. Firstly, make a backup copy of your formulas, as we suggested earlier. Then select everything via the corner cell we referred to in the ‘Sort Your Data’ section, then press CTRL+C, ALT+ESV.
• Find an alternative solution: Sometimes Excel is not enough, but an external tool or app may work better and achieve unanticipated efficiencies.

10. Learn New Formulas

Optimisation is an on-going process. Your users should constantly learn new formulas, new uses and have time to explore and learn new ways to make the most of Excel. Bespoke training, which caters to the  specific competencies your business growth requires can be a cost-effective solution to speeding up your operations and innovating.

We could go on, because optimising business systems is our passion, but we have at least given you some things to work on and plan towards for now.

If your business is facing any of these challenges, or you wish to find out more about the services that Bespoke Excel provide, get in touch with our team of expert data visualisation consultants today on 0161 883 2655.