Excel SUM Ranges – Tips to Avoid Devastating Errors

Once bitten, twice shy.  Spreadsheets have always been trying to bite us in the proverbial. Wolves in sheep's clothing.

Here's a real-life example...

Before Excel appeared I was responsible for building the annual budget for a company with some 30 departments, growing rapidly.  All was well. At the last moment, one department had two extra employees added, joining in month 3. The change was made in the spreadsheet.  The budget approved.  Backs slapped.

I was also responsible for the management reporting, and explaining budget variances.  Months 1 and 2 went OK. Then month 3 arrived.  A £5k adverse variance in one department.  Didn’t take long to find. The two new employees had joined as planned, but the budget didn’t include them. Two rows had been added to the department’s budget spreadsheet, but not picked up by the SUM formula. The costs had not been included.

As each month went by my embarrassment increased. Thankfully I didn’t have to refund the difference!

There are many other similar examples in businesses large and small. Lotus was sued for a similar problem in their 1-2-3 spreadsheet, but unsuccessfully. Using spreadsheet software accurately is down to the user.

So how do you stop such common and potentially disastrous errors?

• How to avoid making such errors? 
• How to check none have slipped through?

TIPS

Tip 1: Be aware of the dangers 

Firstly it pays to make sure everyone who uses Excel is aware of this risk and other spreadsheet risks.

Tip 2: Using blank rows at ends of ranges

The SUM range always adjusts if rows or columns are added within the SUM range. So:

• Insert an extra row above (or one column to the left of) the start of the numbers
• Insert an extra row (or column) after the range of numbers
• Use these extra rows/columns to at the start and end of the SUM ranges
• Reduce the height/width of these extra rows/columns (but ESSENTIAL not to hide them)
• Add a bottom border to the extra row (or to right of extra column)
• Only ever insert rows/columns between the pairs of blank rows/columns

There are two advantages over other tips below:

• The SUM can be placed anywhere in the spreadsheet, such as above or to the right, and the SUM will automatically adjust, even when start with 2 rows of numbers:

• If a row or column is added outside the SUM range, a warning is provided by the blank end-of-range rows/columns displaying oddly before adding the new figures:

Tip 3: Visualising the ranges

To provide a further check on a SUM range being  what you expect:

• Click on the cell containing the formula
• Then click anywhere on the formula appearing above the spreadsheet
• See the range highlighted (in blue)

Sadly this doesn’t work with the OFFSET function mentioned below, which is another reason not to use OFFSET.

But this method does also have the advantage of working with many other formulae to highlight the cells being used in the calculation. For example, only this week I copied a section that I had forgotten used a fixed cell (like $B$1) in formulae, when I had expected a new relative cell based on B1. I spotted the error before making the wrong financial decision!

Tip 4: Getting someone else to check it

Checking your own work is always tricky. The more edits are made that have to be re-checked, the more difficult this is.

Any spreadsheet that is of major importance should be checked by someone else. Including every ‘final’ version!



OTHER WAYS OF PREVENTING SUM RANGE ERRORS

There are some other techniques, which are covered here to show their pros and cons:

Tip 5: Automatic Excel feature

With Excel now by far the dominant spreadsheet, you would have hoped Microsoft would have done something to address the SUM range problem.  And they have.  But it is overly-complicated and works oddly:

• Under “Options” there is “Extend Data Range (or List) Formats and Formulas”, which should be ticked (usually by default). 
• Then set up a SUM range IMMEDIATELY BENEATH the bottom of a list of numbers (with no empty rows):
  
  
• Insert a row immediately above, and type in a number. The SUM range automatically expands to pick this up:
  
  
• Likewise if you insert multiple rows provided you enter numbers sequentially in each inserted row from the top. 

This automatic facility works similarly for a SUM across columns.  It even works if you insert the row/column, close the spreadsheet and reopen it.

But the SUM range does NOT automatically expand:

• If you don’t fill in the figures sequentially from the top, or leave a row blank (such as a row used for a sub-title)
  
• If the range started with only 2 rows
  
• The SUM is above, to the side or otherwise not directly below the range

The limitations apply similarly to SUM ranges across columns.

It is surprising this feature wasn’t implemented to adjust the SUM range as soon as the rows/columns are added, and to also cover 2 original rows.  But it wasn't. So this automatic feature is useful, but potentially fatally flawed.  Best not to rely on it.

Pros: Simple and works automatically most of the time
Cons: Easy to make mistakes when you think it would have worked

Tip 6: Modifying the SUM formula using OFFSET

You can manually achieve what Excel could have done, with a prompt for the syntax (from Excel 2010 onwards).

For a column of figures:

• Set up the formula in the format =SUM(C1: OFFSET(C5,-1,)) where C5 is the Total cell
• Usefully, the column letter automatically adjusts if the formula is copied across columns

For a row of figures across columns:

• Set up the formula in the very similar format =SUM(C1: OFFSET(G1,,-1)) where C5 is the Total cell

The formulae automatically change as soon as the row or column is inserted.

It also works if the total is at the top, or otherwise not immediately below the numbers, though you have to be extra careful with the range definition and subsequently adding rows/columns anywhere near the range.


Sadly “Offset” can slow down recalculations if used extensively in a spreadsheet.   So not perfect, but good if used carefully.

Pros: Reliable method
Cons: Not really any better than using blank rows in Tip 2 above, more difficult to set up, more difficult to check, and can slow down recalculation if used too much

Tip 7: Using 'Tables'


This tip can be useful when values in columns are being summed, i.e. the data is in rows. It doesn't work if cross-casting across columns.

If you insert a "Table" for the initial rows (even just two), you can put a normal SUM function below, above, or to the side of the column of numbers:

Add 2 rows above row 5, and all the totals will automatically adjust:

Add another two rows and put in the sub-heading "NEW JOINERS", and the totals still automatically adjusts:

If you don't initially put a SUM total immediately below the numbers, the other SUMs do NOT adjust unless the row headings are also in a Table column:

As you can see, the totals remain at 100 even after adding another 50. But there are two warnings that the totals are wrong:

•  The new rows are not shaded blue
• There is a little dark blue triangle in the bottom right corner  of the last cell (showing 40)

The problem can be avoided if the row headings are included in the Table:

If you don't want a column heading, set the cell colour to white, and the white writing doesn't show. You can also colour the number cells to a different colour, where you will still see the little dark blue triangle that marks the end of the SUM range:

You can also use Tables for multiple columns. But be careful! SUM ranges that are copied right do not automatically adjust for the columns, as you might expect. You have to set each column total separately:

Ranges have pros but some significant cons. Can be useful if used with care.

Pros: Useful if using Tables as a simple database
Cons: Unrelieable and less useful in financial modelling, as formulae do not copy right properly, and can cause errors if not used carefully. Colouring may not be desirable.

CHECKING SUM RANGES

Assuming you don’t have any Excel auditing software that can assist, there is another way of checking SUM ranges:


Tip 8: Re-calculating SUM

Instead of getting Excel to show the range, highlight the relevant range manually and Excel will display the SUM in the bottom bar. This can be easily compared to the SUM being displayed in the spreadsheet itself. This DOES work with the OFFSET function.

Pros: Quick and simple, and works if use OFFSET
Cons: Only works for SUM (and COUNT and AVERAGE) formulae

IN CONCLUSION

Spreadsheets are powerful but dangerous things. It is easy to make a material error in something as simple and important as adding a range of figures.

1. The first step is to realise there is a danger. 
2. Reduce the risk of error by using Tip 2 by adding blank rows/columns at ends of SUM ranges
3. Review formulae as in tip 3 
4. Use the other tips and techniques when circumstances are appropriate
5. Get someone else to check the spreadsheet model

Microsoft Responds. Or Have They?

News reaches us today that Microsoft have confirmed that all the limitations of Excel for forecasting listed in our recent blog article will be addressed in the next release of Excel. 

Or have they?

EXCEL TIP: Formatting Negative Numbers

Type in the two numbers for Revenue and Cost of Sales, and you'll get this:

Can you spot the negative sign before the Cost of Sales figure? Not always easy.

So let's format all the numbers to be in brackets if they are negative (using Format Cells, Number, Custom, typing in the formatting as shown):

The problem now is the figures don't line up. Just adding a space before the semi-colon, and the numbers look a lot better:

The formatting is set up as follows:

When Excel. When Not Excel.

Excel is rather like a domestic lawnmower. A powerful tool for use in personal situations. Like mowing the lawn, or using pivot tables for a one-off analysis of data.

But would you use your lawnmower to mow a playing field?  Well you could, but it would take you a long time. You might do it once.

Or you could ask your colleagues who have lawnmowers to come and help. But it would be very difficult to make sure all the field was cut, and inevitably there would be much overlap. A lot of expensive people taking a lot of time to do an iffy job.

More likely you would go and buy a larger mower designed for the job.

That’s just like using Excel in any multi-user situation.

Why do people use Excel when a properly-designed multi-user system would be far more effective?  People already have Excel  on their PCs, but that doesn’t make it the right tool.

Here’s a more detailed assessment of the pros and cons of Excel for multi-user budgeting.  Similar principles apply to other business applications.

Indeed if you are using Excel in any “multiple” situation, it’s worth considering an alternative system:

• Multi-user
• Multiple linked spreadsheets
• Multi-currency
• Multi-dimensional (multiple products, entities, departments etc)

So why do people use Excel in such situations when more appropriate solutions are available?

Using Excel for Forecasting – The Pros and Cons

Excel is widely used for forecasting and budgeting in businesses large and small, for a number of very good reasons. Excel is awesome for ad-hoc data analysis.

But for structured and repeatedly-used forecasting models, the limitations of Excel become apparent. These are more of an issue as a business increases in size, with revenue into the millions and higher. Other forecasting software is then worth considering, some of which can be used with Excel.


EXCEL'S STRENGTHS FOR FORECASTING

1. Excel is a very flexible tool, to build forecasting models to suit any business
2. Excel is typically already installed on the PCs of every person involved in forecasting, so is effectively free of charge
3. Every forecasting “contributor” out in the business has at least a basic understanding of how to use Excel
4. Using Excel can avoid the IT department’s involvement, notably to control models that need to be tweaked rapidly
5. Excel has very useful graphical facilities to present the results

There have been recent improvements in Excel relevant to forecasting and reporting, notably PowerQuery and other PowerBI functions. Excel is now at the 2016 version and online in Office 365.

But most of the key issues with using Excel for forecasting remain. So specialist forecasting software can provide significant improvement.


KEY LIMITATIONS OF EXCEL FOR FORECASTING

(1)    Excel is Fundamentally Single-user

Excel is designed to be used as a “personal productivity tool”. Any one spreadsheet file cannot be updated by more than one person simultaneously. Using Excel amongst multiple users in “collaborative forecasting” involves workarounds.

(2)    Drawbacks of Multiple Spreadsheets

A very common workaround is to split the model into multiple files, each to be updated separately and pulled together into a summary.

Correctly maintaining links with the correct spreadsheet version is a major issue.  Furthermore, contributors must be stopped from changing a spreadsheet format even slightly, or it ruins links. However the summary is structured, there’s risk of major error, and significant time needs to be expended to minimise errors.

(3)    No In-built Aggregation

Much of forecasting involves simple aggregation, for example:

• Adding together the sales projections for multiple products
• Adding the staff costs of multiple departments
• Adding multiple companies or business units

Excel has none of this structure built-in.  Formulae or pivot tables need to be set up, including cross-references across sheets.  The bigger the model, the more formulae and complexity, and the more difficult it is to ensure all ranges and links continue to work correctly.  Late nights. Weekends. Missed deadlines.

(4)    Lack of Audit Trail

Most formulae provide no immediate audit trail analysis of each aggregated figure. Pivot tables can help. But manually tracing through the origins of each figure becomes too time-consuming to do regularly.

There is also no audit trail of who made what changes, which can be important in larger situations.

(5)   Lack of an In-Built Database

Excel does not have its own underlying in-built database. There are two workarounds:

• It is possible to link Excel to a customised database, such as Microsoft Access. But this adds considerable complexity.  
• A “data sheet” can be created, but this has to be reliably populated and analysed, and can quickly become too large and unwieldy.

(6)    Comparison of Actual to Forecast/Budget Figures 

Actual data can be imported into Excel, but this is complicated to do in the equivalent way to forecasts and budgets at each level of each hierarchy.  Detailed comparison is similarly difficult between actual and forecast, or between versions of forecasts.

(7)    Calculation of Balance Sheet and Cash Flow 

Many forecasts are built with the P&L as the starting point. Cash flow is built using formulae, such as applying debtor days. The balance sheet is produced from both, adding in depreciation and other adjustments driven by formulae.

The beauty of Excel is total flexibility. But keeping control of formulae remains a major headache as the format of the P&L or cash flow sheets change.

(8)    Lack of Documentation

Excel spreadsheet models are notorious for lack of documentation of their logic, especially as they grow. The bigger the model, the more difficult it is for new personnel to understand it.  As time goes by, many Excel forecasting models remain untouched for fear of breaking them.

(9)    Lack of Workflow

Controlling the submission and approval of forecasts becomes a major headache as the number of contributors increases. It can be the biggest single problem in larger businesses.

Excel has no in-built workflow controls. Submissions have to be controlled and approved manually, or by superimposing separate workflow software.

 
ALTERNATIVES FOR FORECASTING

Specialist forecasting and budgeting software can overcome all these issues, and can be affordable for businesses large and small. Depending on the cost, good software will include some or all of:

• A central multi-dimensional database, with each dimension describing entities, time periods, products, etc. These are in multi-level hierarchies such as country/region/area.
• Versions of forecasts, budgets and actuals can be clearly identified
• Automatic and accurate aggregation at each level of the hierarchy, across all dimensions and versions
• Automatic analysis of composition of aggregated figures
• Actual figures can be imported in the same hierarchies as forecasts, allowing easy comparison.  Versions of forecasts can also be easily compared.
• The database and software is multi-user, allowing everyone who updates the figures to do so in parallel in one central easily-controllable place
• In “collaborative forecasting”, quick and automatic generation of templates for contributors’ submissions, which they can’t tamper
• Centralised formulae for producing cash flow and balance sheet, used repetitively and reliably
• With much functionality automated within the software, the need for application documentation is reduced. The model is easier to learn when personnel change, especially if someone has previous experience of the software.
• Workflow in-built to control submissions, approvals, and automating the chase of overdue actions.
• Changes to data can be logged in an audit trail by user.
• Some software uses Excel with an add-on for data templates, whilst some are used intuitively through a browser. This minimises the need for additional software on contributors’ devices, and minimises training.
• Excel can often be easily used for reporting, thereby maintaining its benefits
• Overall providing quicker, easier and more reliable submission control and analysis of the summary position

For larger businesses, a fully-functional system for collaborative forecasting may have an entry level for the software alone of £50,000 or more.  Nonetheless this can be money well spent, given the benefits.

At the other end of the spectrum, small businesses often used to use WinForecast, but this is no longer available or supported. The market is developing for equivalent tools.

In the mid-market there are now affordable solutions, either cheaper versions of the big software, or tools focusing on specific aspects such as controlling submissions.  Larger businesses can potentially re-grade to save money.

Otherwise there can be more efficient and effective ways of using Excel.

One way or another, the forecasting, budgeting and period-end analysis processes can be made quicker, more reliable, and better value for the business.


INTERESTED IN TALKING FURTHER?

Do leave a comment, or call Chris Challis on 07836 774439 to discuss your specific situation and potential solutions.