Last night marked the third night of Chanukah, or Festival of Lights and light in general. I’ve been thinking about this a lot lately. One of the many Chanukah miracles, and perhaps the most well known (the oil burned for 8 days) was a reference to literally “keeping the lights on” — especially the extra special light above the Torah. The ritual of adding one candle each night really speaks to me, especially during arguably the darkest week of the year. This Friday, the sun will set later, finally reaching that winter inflection point where the days start getting longer again. The song that’s been in my head lately is Light is Returning.
Light is returning, even though it is the darkest hour
No one can hold back, hold back the dawn
Let’s keep it burning, let’s keep the light of hope alive
Make safe our journey, through the storm
Yesterday, I was helping my friend Rachel with some tricky spreadsheet logic and I made a common mistake. We were working with RSVP data representing attendees at a particular event. We’ll call the event “S’mores Fest 2018.” But when I did a classic “click and drag” to copy the event name down to all the rows, Excel added one each time. So by the end of the spreadsheet, we were at “S’mores Fest 2134.” Either we figured out how to time travel… or Excel had added “+1” to each row (starting with 2018). Hmmm, I think the Excel automation is a better explanation! Has that ever happened to you? It can be a pain in the butt, BUT there’s an easy trick you can use to correct it!
Recursion / Re-Curse-on
Recursion is a fancy term that I first encountered during my dreaded computer programming class that I took back in 2013. In computer code, sometimes you write instructions to loop through data until you get to the very last row. Excel does recursion all the time. If you “double click” the bottom, right hand corner of a cell, Excel will copy that formula, or text, or what-have-you all the way to the last row of your data. When it reaches the last row of your data, Excel will stop. That’s recursion in action.
The problem is that sometimes Excel tries to guess what you want, and it guesses wrong. Yesterday, I wanted to copy “S’mores Fest 2018” down the column, not “S’mores Fest x+1”! However in other scenarios, copying down with an “x+1” rule is really helpful. Maybe because I’m assigning a unique ID to each row, or I simply want to count how many guests I have or I’m making a calendar and I want Excel to smart-fill the dates in order. I’m sure you can come up with SUM more examples too! (har har)
Will you light my (next) candle?
On Chanukah, we light n+1 candles every night until we reach 8! If we were modeling this in a spreadsheet, it would be a great “use case” for the n+1 function. All you have to do is make a spreadsheet, start with a “1” in the first cell, and then drag down to auto-fill 2, 3, 4, etc until you reach the bottom. You can do this with a “click and drag” approach or by “double clicking” the bottom-right hand corner of the 1 cell (your cursor will turn into a ‘+’ sign), and Excel or Google Sheets will automatically fill down. Learn more about the “fill handle” at this helpful tutorial!
Auto fill – MAKE IT STOP!!
Auto fill and the fill handle is like my best frenemy. Sometimes it’s awesome and sometimes it completely gets in the way! If you are trying to copy dates down to the bottom of a spreadsheet and you DON’T want to fill in “date + 1,” simply highlight TWO date cells (one on top of the other) and then do your “click and drag” or your “double click.” You’ll get the same effect, but this time without the n+1 annoyance. Alternatively, once you already filled in the data, you can undo the auto-sum effect by clicking the “expand” button and choosing “copy cells” instead of “fill series” to revert back (see image below).
omg I’m famous!!!!!!!
omg I’m famous!!!!!!!