You’ve spent a few days on this spreadsheet you put together. It’s really elaborate. You think you’ve put your best work in it.Then you and your boss go through this work together.
Boss:” Okay.. this number doesn’t look right? You are saying I paid that much? No way that’s right” He is looking at one of the Expense figure in the sheet. There are at least 100 numbers on the screen.
Now you are shaking…because you are not sure..
Then he goes on to check if that number is right. It turns out…it’s not. You know realized there is an linkage error in one of your sheet.
Boss:”It’s fine. Why don’t you fix this and we can look at this again.”
You walked out of the office..feeling guilty.
That guilty feeling would come up again later in the performance review meeting. If you know this feeling, you are not alone. Here are 5 things you can do in Excel to dramatically reduce the chances of errors in Excel.
Check and Balance
This would probably be obvious to a lot of people. I was an accountant. So check and balance is my habit. But I used to think it was a waste of time to calculate the same thing twice. However, if you just calculate it once. There is no guarantee that your calculation is right. Whenever you are going to present a really important number, ask yourself, how can you be sure that result is accurate. If you can reach the same result in a different way, you should have the confidence to say that is accurate. So try to reach these output numbers in two different ways to make sure they are correct. Or at least, find another way to get a correct range of your output numbers. So that at least you know your ending results are in the reasonable range, avoiding embarrassing stupid mistakes.
Once you find there is some inconsistencies. The next thing is to find the error. Chances are that errors are buried in one of the complicated formulas. Here is a handy function to run the complicated formula step by step: Evaluate Formula function.
Using the Evaluate Formula you can know whether the formula is calculating right. If your formula is not that complicated, but you suspect the linkage may be causing the error. This function is the way to check whether cells are linked as you want. For example, I may made a temporary change before on the cell reference, but I forgot to change back. This function can help identify abnormal linkages.
When I am checking my results. If two results should be equal when the spreadsheet should be correct, but two set of results are far away from each other in the page. You are going to have a hard time scrolling up and down all the time. If you are lazy about it…this will lead to…mistakes…This trick can save you a lot of time since you can then compare them easily.
Memorize key figures
This is not an Excel trick. ..Just something I notice that can separate top performers from others. Also if you are confident that you are sensitive to numbers, you can skip this part. I can’t remember a number unless I try hard to. If you are like me, read along. You know someone knows what he is talking about, if he can tell you the number quickly. We all know well-reasoned arguments. In the business world, a lot of the reason is based on numbers. However, analysts are easily stuck in the doing. Most of the energy is spent on nitty-gritty things, such as formatting, cell linkages, and it’s easy to lose the sight of the big pictures. The solution is to memorize key figures. Try to figure out his or her thinking process first. There must some numbers they remembered based on their experience, then they have some rough ideas about certain numbers in your spreadsheet. That’s why they can find mistakes in your work. These certain numbers are the ones that you need to memorize.