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…
Related Posts
- April 14, 2015
On February 3rd this year I published a post titled My Excel ...
- April 7, 2015
(as simple as Copy and Paste) This is our sample data. First we ...
- February 3, 2015
In my work as a Trainer and also as a Data Scientist (I love that ...
Comments 8
Leave a Reply
Excel Unplugged acknowledgements
Donate
Is it possible that this comes from Lotus 123 compatability?
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 “:”
You are right, you can even put just A1.A100
I didnt know that… You live and you learn 🙂
I’m converting to a dot. awesome
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
Can someone clarify the difference between “a1.a100” and “a1..a100”?
I didn’t know about this either. Thanks for the tip!