How to create an animated ticker in Excel without VBA?

Last night, around 3 am, I woke up and thought: Wouldn’t it be nice to have an animated ticker in Excel? Like text that scrolls from right to left? With just one big formula? Welcome to the second episode of my series “How to create weird algorithms” First one was about creating a maze in JavaScript). In this article we will create a nested Excel formula to solve a problem that probably not knows exists. Until now.

TYL — Today You’ll Learn

• How to build complex nested formulas from the scratch
• The MAP-formula to create an array and modify it’s values
• The LET-formula to declare variables to clean up nested formulas
• How self-referencing cells in Excel could be useful
• How to use INDIRECT and R/C referencing

Spoiler Alert!

Preparations

First let me introduce you into an widely ignored feature in Excel: Iterative calculations. Excel tries to protect you from “circular references” and therefore disables this feature by default.

Our ticker requires a formular that references itself. So you need to enable “iterative calculations” in the settings and set “maximal iterations” to 1:

Activate “Iterative Calculations” and limit the iteration count to 1

Now let’s get back to your worksheet and start from the scratch:

How to draw a little canvas from just one cell?

In our case we need a one-dimensional array to use as a canvas. But how to create an array over multiple cells, just using one formula?

Well, there are a couple of ways to do that, like SEQUENCE, MAKEARRAY or INDIRECT (and probably more). For our problem we will use MAP and SEQUENCE. MAP allows us to create an array and modify the values within this array. Let’s try a simple example:

=MAP({1;2;3;4;5;6;7;8;9;10};LAMBDA(var;var))

This will put the numbers from 1 to 10 to your worksheet. You could even add additional rows. For our case the one dimensional array is sufficient. The LAMBDA function allows you to modify the values. To make it more handy, let’s add SEQUENCE to construct the array and LET to define variables:

=LET(
    length; 10; 
    MAP(
        SEQUENCE(1;length);
        LAMBDA(val;val)
    )
)

LET is a good way to declare variables that you may need in your nested formula.

How to simulate an “animation”?

We’re not really going to animate something. We’re just going to create the impression of animated text. To achieve this we first need to add some momentum. That’s were rely on the self-referencing feature. We just add the value of the first cell to all the other cells:

=LET(
    inc; INDIRECT("R" & ROW() & "C" & COL(); 0);
    length; 10; 
    MAP(
        SEQUENCE(1;length);
        LAMBDA(val;val + inc)
    )
)

OK, this probably looks weird, so I’m gonna explain it. In the second row we are assigning a calculation to a variable called inc, this will be our “incrementor”. Instead of a fixed reference like A1 we use an indirect reference. This makes it easier to use the final result everywhere we want. INDIRECT takes the value from the current row and current column: This is the 1 from the sequence. The second parameter of INDIRECT is a 0. This enables the R/C reference: referencing a cell by it’s row and column index instead of the “common” A1 notation.

Now as we have this value from the first cell, we just add it to every cell of this range — which results in a row of incrementing numbers. Press and hold F9 and see what happens:

From now on, when I say “let it run”, you need to press and hold F9 and see the result.

Now let’s add the text. We want to display “hello, world” and therefore provide this as a variable named ticker:

=LET(
    ticker; "hello world";
    length; LEN(ticker);
    inc; IF(
        INDIRECT("Z" & ROW() & "S" & COL(); 0) > length;
        1;
        INDIRECT("Z" & ROW() & "S" & COL(); 0) 
    );

    MAP(
        SEQUENCE(1; length);
        LAMBDA(val;
        IF(val = 1; 
            val +  inc;
            MID(" " & ticker; val + inc; 1)
        )
    )
)

The ticker-declaration should be clear as well as the length-dependency. The length was set to 10 before, now it’s the length of the ticker-text.

What looks a bit complicated is the improved declaration of the incrementing variable (inc): Actually we just check if the variable is greater than the amount of available letters. If so, reset it to 1 to start over.

Before we look at the LAMBDA let’s be clear about the “canvas”: We need one cell that contains an incrementing value — the first cell. All the other cells are meant for “rendering”.

The first IF-condition checks if the value of the particular cell equals 1. This would be our counting cell and that’s why we increase only this cell’s value.

The MID-formula takes care of the rendering-part: The second cell in our one-dimensional array should start with the H, the first letter of our ticker. The third cell displays the second letter E and so on.

You see the discrepancy? The second cell holds the value 2, but we need the first letter. We are one counter ahead, that’s why we add the empty char before the ticker text.

What happens if your run it?

Well, we have a pretty good loop. It’s not that smooth, but we get to that in a second.

Finish the animation

The main work is done, but we still need some improvements. First let’s add a second variable to define the canvas width. It makes more sense to use a fixed value here. So we can render even long text on the small canvas.

We just call it “width”. And we also want to start the ticker on the most-right position of the canvas. That’s why we take the width of the canvas we just declared and use it as a substractor.

=LET(
    ticker; "hello world"; 
    width; 10;
    length; LEN(ticker);
    inc; IF(
        INDIRECT("Z" & ROW() & "S" & COL(); 0)
            > (length + width);
        1;
        INDIRECT("Z" & ROW() & "S" & COL(); 0)
    );

    MAP(
        SEQUENCE(1; width);
        LAMBDA(val;
        IF(val = 1; 
            val +  inc;
            IF((val + inc) > (width - 1); 
                MID(" " & ticker; val + inc - (width - 1); 1); 
                "")
        )
    )
)

Please note how the width-variable replaces length as an argument for SEQUENCE. We also need to adapt the inc-part a little. We need to reset the incrementation a little later, because the animation should start at the most right column and therefore needs more steps to finish.

The second and nested IF-condition checks if the current incremented value of the particular cell is greater than the width of the canvas. That ensures that we address a valid value from the ticker text when using MID.

And that’s it. I adjusted the cell widths a little, hid grid lines and made the first cell invisible to make it look a little better. This is your ticker:

A ticker in Excel

You can just copy and paste the formula to every sheet you want or you can create a LAMBDA function to get a handy formula. Do not forget to enable “iterative calculations” and change the ticker text.

Have fun.

Nota bene

This does not work in Google Sheets, out of the box, because Google Sheets do not support the LET formula. You still could just fixed values or named ranges. But this makes it less fun.