ICAEW Excel tips: New Year’s resolutions
As the old gag goes: What’s your new year’s resolution? Mine is 1920 x 1200.
More seriously, many people set themselves fitness, personal, and other goals at this time of year – and almost as many people give up on them before the month is out.
It’s the time of the discarded gym membership, the abandoned diet, and the classic literature left unread. But it is possible to achieve your resolutions! The key is habit.
If you keep up a new activity for a little while, it becomes a habit and at which point, doing it becomes automatic and easy. We all have these kinds of habits, such as brushing our teeth or indulging in a midmorning biscuit, and changing them can be hard. But I’m not here to be a self-help columnist, but an Excel one. So let’s talk Excel resolutions and habits.
One of the first pitfalls is to set general, long-term goals like “I want to get better at Excel this year”. There really isn’t anything actionable in that, and it’s hard to work at such a vague target.
A more workable resolution might be “I’m going to spend five minutes each working day trying out something new”.
I’ve spoken in this column before about the worthiness of setting challenges and goals. Even if it’s just reading through the list of functions to familiarise yourself with what’s there, or browsing other people’s solved problems on an Excel help forum to see how the solutions worked, taking small steps can have a big payoff.
My main goal throughout 2017 was to construct a challenge that required the use of VBA, and then work at it until it came together.
I like to try and create classic games and puzzles in Excel, because the rigid rules help guide what you need to do. And the skills you pick up doing these things are excellent for a host of other tasks.
Whatever your goals are, just remember to start working on them soon and try to make them into good habits, rather than just good intentions.
Industry insightsView more
Tip of the Week highlight – Data tables
This week’s tip is based on Excel Tip of the Week #218 from ICAEW’s Excel Community.
A data table is a tool for performing sensitivity analysis. It can be used to compute how a particular key output will alter as one or two input values are adjusted.
You need to lay out your table in a certain way, which is slightly different depending on whether you want to adjust two variables or just one:
The colours are just to aid in comprehension. In either case, the pink cell at the top is a link or calculation of the value we wish to track. The green and red sections are alternative values for our key input(s). Once laid out, we need to select the whole area and then head to Data => What-If Analysis => Data Tables.
This popup is then used to indicate which cell(s) the trial inputs should be put into the row input cell for the red inputs (or left blank if there is only one) and the column input cell for the green inputs.
Pressing OK will then populate the table with the values that result from altering the input cells as shown.
There’s plenty more to say about data tables, including how to amend them, how to speed them up, and how to use them for Monte Carlo analysis. The full tip is linked above and is free even to non-Excel Community subscribers.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #14:
Never embed in a formula anything that might change or need to be changed.
Putting any kind of input or variable directly into a function is very dangerous. Not only is the input hidden from the user, it can be very easy for them to not notice if one formula is different from the others, leading to extra review time and the risk that the exceptional formula is inadvertently overwritten. What’s worse, if the input has to be changed, then you will need to track down every single instance of that formula to update, and missing one is both easy to do and potentially large in impact.
Instead, gather your inputs (in one place as per Principle 10), and link formulas back to that input cell. If cross-sheet references are making your formulas hard to follow, consider the use of named cells to help clarify them. This way, any change in an input can just be made once, with the certainty that it will propagate through the rest of the workbook from there.
This is the twentieth and final of the Twenty Principles for Good Spreadsheet Practice to be covered in this blog. The full list is linked above and is well worth keeping at the forefront of your mind whenever you’re working with a spreadsheet.
See you next month,
David Lyford-Smith is a technical manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more at here.