Excel Unplugged

Who knew (Just another day with Excel)?

One day as I was giving a basic(!!!!) Excel training, my mind got sort of blown. We were discussing the use of A1:A100 in the Name Box for the selection of a range of cells from A1 to A100. You can see this in action here. It went by as it did a million times before except for a lady that said: “You could just as well put A1..A100 in the Name Box”.

You can imagine my reaction which was in the neighborhood of “Well…, you cant do that! Can You? I mean, well… I don’t know. Lets try it out…”

And we did. Found out it really works, you can even use it in a function for example =SUM(A1..A100). Excel will “auto correct” the .. to a : when you will view the function later, but it will work. Now as mesmerized as I was by this, I started wondering…
I wonder why this is so and who else knows about this, and I saw this as a perfect opportunity to start a very first Excel Unplugged Poll.

If anyone knows of a logic behind this and how you can use it please share by commenting…

Comments 8

  1. argeedblu says:

    Is it possible that this comes from Lotus 123 compatability?

  2. Frank Byl says:

    I’ve understood that it was a feature added by the early Excel developers to woo over the Lotus users of the time who were used to that syntax; basically make it easy for them to convert. I use it all the time since the “.” is easier to enter then a “:”

    1. You are right, you can even put just A1.A100

      I didnt know that… You live and you learn 🙂

      1. Steve Chase says:

        I’m converting to a dot. awesome

  3. MF says:

    This is really cool.
    Tried both A1..A100 and A1.A100. Both works. However, the single dot doesn’t work for whole column/row referencing

  4. Frank Byl says:

    Can someone clarify the difference between “a1.a100” and “a1..a100”?

  5. Karina Stavenes says:

    I didn’t know about this either. Thanks for the tip!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.