Share
Common spreadsheet mistakes that can be easily fixed
Spreadsheets do have a place in this world of accounting software, but they must be used with care. One wrong formula can change everything and it may not be obvious.
If your spreadsheet is complex, you may want to consider alternative software.
When to use a simple calculation or a formula
Over the years, the most common mistake I’ve seen is incorrectly using the “sum” function instead of a simple addition, subtract, multiple and divide calculation.
Functions are a programmed series of characters.
= Sum (…) asks the software to add up a “range”.
A “range” is more than one “cell”.
A “cell” is the box (also known as “field”) in which you type. (Keep reading, it will make sense eventually!)
Spreadsheets have numbered rows and alphabetised columns. The “address” of a “cell” is its row number and its column letter, for example D3 is Column D, row 3.
A “range” might look like this:
B2:G4 includes the top left cell and the bottom right cell and will cover all cells in between.
Note the colon in between B2 and G4 means “the range to include”.
The Sum Function
= Sum (B2:G4)
Out loud could be spoken as:-
“Add up everything in the range B2 to G4”
When the sum function is used incorrectly i.e. =Sum (10+2), the software ignores the first bit “sum”.
A simple calculation should look like this:-
=6+4
=10-2
=18×6
=25÷5
Whilst the incorrect calculations work (=sum(6+4)), incorrectly using them (or incorrectly understanding them) may hinder your ability to use more complex calculations and formulae.
Using =+
To be fair to those who have used spreadsheets for many years, this usually stems from compatibility issues with different software, where one may expect a calculation to begin with = whilst others expect +. Sometimes, however, users just get confused.
For a spreadsheet to calculate anything, you must always start with =
A spreadsheet will always assume a number is positive unless you say otherwise.
=+5-3
In the above example, the + is superfluous and only needs to be:-
=5-3
Mouse click-v-enter key
Most software has a common use for the enter key. Users very often click with their mouse when they should be using the enter key.
Using this key correctly can remove many frustrations when working within spreadsheets.
Pressing the Enter key tells the software you have finished or completed an entry or task.
When you type a calculation, or formula, into a cell in your spreadsheet, press enter when you’ve finished.
=8+3 enter
Although clicking away from the cell would work in the case of a simple calculation like this, it won’t work for the more complex calculations.
Still confused?
We’ll be running Introduction to Spreadsheets courses from September. Click here to register your interest.
