Have you heard the saying “Change is the only constant”? It is often attributed to the Greek philosopher Heraclitus, who wrote Panta Rhei or Life is Flux. Basically, everything is always in motion, always changing, always in flux, making “change” the only thing that is constant in the world. Sometimes change is awesome - get a new job, move to a new city, meet a new friend, or get surprised by good news like your rent is going down, or you are getting a raise, or you win the lottery. Any of these things could change our lives for the better a lot or a little. But other times change hits us with the unexpected that throws us off our path - an unexpected tax bill, a shift at your job, a hurricane.
Whether it is good, bad, or even neutral change - it can be disruptive. Even if your entire life isn’t up-ended by some big change, making any re-arrangement to our view of the world takes time and energy. This often times feels particularly taxing when it comes to our data and spreadsheets. You set up your spreadsheets to handle one set of data, and then another set comes along to throw everything out of whack. Or you did a bunch of calculations to get to a final number, but then you get some new information and the parameters change, making all your calculations wrong since they were based on a different set of rules or assumptions.
If change is constant - in life and in spreadsheets - then what can you do to deal with it? You may be surprised to learn that the same things that drive people to successfully deal with change in life are the same things that can be done in Excel to deal with change in your data. It is all about setting yourself up to:
Thinking about the future
This one never came naturally to me. I live mostly in the present, and I don’t think about what the future looks like. But too many times I ran into situations where I didn’t think about the future and when it hit me, I wasn’t ready for it. If you are like me, you don’t need to make any big changes to your personality or have a crystal ball to see into the future - you just have to stop for a moment or two to think about what could be. There are innumerable ways that life could twist and turn, and we can’t imagine and hold all of them in our heads - there are just too many variables. So why worry about trying to hold on to all of that? Just take a minute or two to sit back and think about what changes might be possible to come your way in the future. When you are working with Excel, before you start building your spreadsheets or adding your data, think for a minute or two about how your data or needs could change in the future.
Being flexible and adaptable
A natural and very common human reaction to change are feelings of disruption, confusion, frustration, and fatigue. It can be mentally taxing to think things are going one way and then you are hit with information that changes your path. Research suggests that the natural reactions to the stress of change have some evolutionary origins, but that chronic stress - from chronic change - can contribute to a number of physical health problems. Luckily, research has also been done to identify the ways to copy with change, and finding ways to be flexible and adaptable top the list. It may seem comfortable to find a specific path and try to stick to it, but we know that change will take us off that path. It won’t look the same for everyone, but however you can find to “go with the flow” will help you in life, and of course in Excel.
Get prepared for change
What does it mean to get prepared for change when you can’t possibly know when or what change will come? Sometimes you can arrange the plans you lay and the tools you use so that they can set you up to accept changes. In Excel, there is a built-in approach for dealing with changes to your data: it is called Dynamic Referencing. This is actually a fairly simple, yet powerful, concept. The basic idea is that if you are going to be calculating anything, you want to be prepared for the fact that your data could change. For example - let’s say that you are calculating the average score of grades in your class. You’ve got 10 students, and you enter their test scores next to their names in your spreadsheet. Seems pretty simple, right? Since Excel is just a big calculator, you can just add all those numbers up in a cell, like this: =98+87+70+96+80+98+87+70+96+80 etc etc. Then you know that to get an average you divide that total by the number of students, so in another cell you put the total you got over 10, like so:
And Excel spits out your average - 86.2%. That would all be fine and good...if change wasn’t inevitable. So you go back and realize that you entered one of the student grades incorrectly, and then two students unexpectedly transfer into your class and quickly take the test, so now you’ve got to add in their scores. Now not only do you have changes in your data, but you would have to make those changes in your calculations as well. On this small scale, that may simply be annoying and time-consuming. But imagine this on a large scale, and it can become impossible.
Instead of writing your calculations out the way you did, you can instead “reference” the cells that have the information you need, so that when that information gets updated, your calculations are prepared to take those changes in stride, and give you the correct information you need in under 1/1000th of a second. This is done with what is called “Dynamic References.” The “dynamic” part is all about you know what - change! When something is dynamic it means it is updating and changing. And “reference” means pointing to a cell or range of cells, or “referencing” those cells. There are many different ways to use dynamic references, from the very simplest to the most complex. Let’s start simple. You can add 2 and 2 together in one cell using Excel as a calculator, and it looks like:
Of course you will get 4. Or instead, you can have the number 2 in two different cells, and in a third cell you will reference whatever values are in these first two cells, like so:
You get the same answer either way, but with dynamic references you’ve unlocked the potential and the plan for dealing with change. This simple example can be taken up to any level of complexity. But the thing to remember is this: If you find yourself typing numbers into your formulas (or “hard-coding” them into your formulas), then you know to step back and prepare for change by using dynamic references instead. It will save you time and energy in the future, since nothing is constant but change.
Like this post? You might also enjoy my FREE 7-day email course: