Spreadsheets are a stable in the business world. If you work anywhere in money, finances, business, or anything similar to those then you likely use a spreadsheet. They’re even used in the military to keep track of supplies. With so many people in so many industries using spreadsheets, you would imagine that people make a lot of errors. Correct! Here are ten common spreadsheet mistakes that you’ve probably made before.
1. Mistakes in spreadsheet logic
Spreadsheets are notoriously strict with things like order of operations. If you don’t type your equations and formulas in properly, you will get the wrong math which can wreck an entire spreadsheet. Thankfully there is something you can do about that and it’s simply a matter of using more parenthesis. Put parts of your equations in them in order to get the desired effect.
2. Misuse of built-in functions
A single wrong letter in a function can change its entire meaning. For instance, AVERAGE will ignore all text and false entries. AVERAGEA converts all text and false entries to zero. That one letter can totally change the outlook and values in your spreadsheet. Companies have misused functions and had wrong values in their spreadsheets for years and it caused damage of millions of dollars. If you’re using a function, then double check to make sure you’re using the right ones.
3. Not copying all the cells you need
When you work with spreadsheets, there can be a lot of copying and pasting. It’s a very common problem that people don’t select all the cells they need to copy and paste into the next workbook. If you don’t, you’ll have missing information. That can cause a lot of problems if it isn’t caught. Since spreadsheets can get ridiculously big, this is actually harder than it sounds. If you have 15 years of financial history and you only copy ten years of it, you may not catch that right away.
4. Keep your data entry and formula cells separate
Formula cells and data entry cells should never be anywhere near each other. The reason for this is relatively simple. If you put values in the formula cells that are supposed to be in the data entry cells and vice versa, then you can seriously mess up your books. When you keep them separate, you run little risk of mixing the two up and that keeps your books and your bosses happy.
5. Save only when you need to
This one seems odd but it’s true. You should only save when you need to. This bit of advice flies right in the face of the oft-told advice to save frequently. Here’s why you should only save when necessary. Assuming you hadn’t made a mistake up to the point when you opened a saved spreadsheet, you went through the project with a spreadsheet without a mistake. If you make a mistake and save it for a bunch of times, then your mistake gets saved and you won’t be able to recover your flawless copy later. Thus you should only save when you know what you just did was correct.
6. Create new spreadsheets if you want to experiment
If you want to play around with your spreadsheets, you should make a copy and alter that instead. You should never tinker with your main spreadsheets because you could lose your original copy in the event that you accidentally saved. To avoid what could be a cataclysmic mistake, do yourself a favor and keep copies.
7. Give new cells a range name
Range names can help you keep things organized. You can sort by range name and it helps you find information more quickly. It only makes sense that if you create new cells that you should give them a range name. Not giving new cells a range name can limit how they’re sorted and searched which can make them hard to find. You’d be surprised how many people add new cells to a huge spreadsheet and don’t give them range names. It makes things cluttered and more difficult to work with.
8. Delete range names when you delete all of its cells
You should also delete range names when you delete all of its cells and it’s for exactly the same reasons as number seven. When you make major changes to your spreadsheet, you need to update everything around the change in order to ensure proper documentation. Not doing so can cause others to re-enter information that was supposed to be deleted and can also hinder things like searching and sorting.
9. Check your math
This seems like an obvious mistake but it’s absolutely essential. A company named Fidelity once had their books messed up by $2.6 billion over a silly minus sign. You can imagine what effect this had with investors and with the company’s higher ups. One tired night and you tap the minus sign on accident and you can make your company look like it has gained $1.3 billion more than it actually earned. Make sure you check even the most basic arithmetic you perform.
10. Have someone double check your work
Most of the largest mistakes made in the world of spreadsheets came from people not double checking their work. This mistake is then transferred up the chain of command without anyone else seriously checking everyone else’s work, and it can result in horrible problems. If you work with spreadsheets, then you should find someone to check yours every now and again to make sure the numbers add up. If you’re in charge of people who work on spreadsheets, then you should double check their work more often. An article from 2013 posits that 88% of spreadsheets have an error in them. That’s not a promising number.
At the end of the day it comes down to just paying attention. When your eyes stare at tiny numbers in tiny blocks all day long, they’re bound to start missing things. Make sure you take a step away from the computer every now and then and give your eyes and your brain a break. Spreadsheets are great but they’re also very tedious and it’s very easy to make a mistake if you’re not paying attention.
Featured photo credit: Sunoray via sunoray.com
