If you’re a new starter to Microsoft Excel, or to any function software for that matter, there are a few basic approaches to take:
- Save, save, save
- Build a formula step by step
- Use a text editor
- Be prepared to make mistakes
- Keep your own reference list.
Save, save, save
- Save your file regularly as you work on it, and create separate backups so that, if everything goes really pear-shaped, you’ve got a fall back position.
- This also creates a comfort zone so that you know you can try stuff out and won’t worry if previous beautifully crafted work turns into a heap of unpaired socks.
Build a formula step by step
- Start with the first element of a formula and get that to work.
- Add the next element, get that to work, and so on.
- That way, if the formula has an error, it’s quicker to pinpoint the break point.
Use a text editor
- Use a text editor when building a formula – it avoids losing a draft of the formula if it’s so flawed you can’t do anything except back out of the worksheet cell and have to start from scratch.
- You can save variations as you go along to go back to.
- There may be a long formula to update references in, and doing a find and replace in the text file can feel less scary than trying it in the worksheet; plus you have the original to go back to.
- Within Excel, you can add a ‘ in front of the formula you are trying. Excel treats the cell contents as text, and won’t start interfering before you’re ready to run with your formula. Just delete the ‘ when you’re ready to roll.
Be prepared to make mistakes
- Yup, this sounds cliched, but it CAN help to get errors along the way, you really can learn about how a function or formula works when it doesn’t work!
- And doesn’t it feel satisfying when you have to work a bit to crack a puzzle!
Keep your own reference list of examples and solutions
- Every Excel user has a different set of data requirements and preferences.
- Sometimes you might spend some time researching and finding out a good solution.
- Before moving on to the next task, try to make a few notes of what you’ve done, with your own example solution. Create your own resource directory to refer back to.
- A few weeks or months later you’ll be glad to be able to quickly get back to your notes. If all your notes are in a separate document, it’s a great deal easier than hunting back through lots of Excel workbooks.