My daughter got a little distracted doing her maths homework this evening, which involved making charts in Excel. She drifted off into the apparently infinitely unfolding edges of the spreadsheet, calling out, “Look mum! I got to 25,000!” and so on as the numbers of the cells grew and grew. Finally, to my surprise, she actually found an edge to Excel’s spreadsheet.
Did you realise that “the ultimate square” of an Excel spreadsheet (as my eleven-year-old has named it) is IV65536?
That’s right. IV is how far across you can get, and 65536 is how far down you can get.
That’s a long way. But it’s not infinite.
Martin GL
I remember doing that back in the days of Windows 3 point something! It was nothing like 65.000 cells. I think it was something like 9000, but also some odd number that makes you wonder why they chose *that*.
Funny thing is, I would never do that today. I’d just take it for granted that there was a mathematical algorithm that would just keep adding numbers.
David
For years and years there were just enough columns so that you could NOT (just barely) do a calendar where every day had its own column. Bummer.
Jill Walker Rettberg
So other people have done this? I never did – but then I didn’t actually use Excel until I was an adult, and adults are just really quite boring in their explorations of virtual worlds 😉
I tried dividing 65536 by 8 to see if that number has to do with bits and bytes – and it’s actually 16*8*8*8*8, or (if I got the scientific calculator right – I haven’t done this in a while) 2 to the power of 256. Or maybe it’s 256 to the power of 2. Both are computerish number, anyway 🙂
Oh, and look, Microsoft has expanded the Excel world in newer versions: “Microsoft has been listening, the last cell in Excel has gone from IV65536 to XFD1048576. That is going from 256 columns to 16,385 columns and from 65,536 rows to 1,048,576 rows.” We’re using the Mac version, so our latest version is Office 2004.
I have to wonder who they’ve “been listening” to. When on earth do you need that many cells in your spreadsheet? And why not make it potentially infinite?
Martin G. L
I suppose really huge corporations would need that kind of space for their accounting, but why would they put it all in one spreadsheet?
rainer
65536 is the maximum value for the unsigned integer datatype. (2 ^ 16).
further reading.. http://en.wikipedia.org/wiki/Integer_(computer_science)
mark bernstein
65536 is 2^16 — the largest unsigned integer you can represent in 16 bits (provided you don;t need a row zero)
16384 (not 16384, surely?) is 2^14.
1048576 is 2^20
It’s not *that* hard to find yourself with a table of 16,000 people or transactions — that’s 20 a day for a few years.
2ndhandsoul
I love how it’s usually children that dare to discover these things. We’re so willing to follow oru self-imposed boundaries without testing them out first. Kids don’t understand that quite yet. Refreshing. 🙂
Jill Walker Rettberg
Thanks for the explanations! I’ll explain this to my daughter and we’ll both be the wiser 🙂 And yes, I can see that you might actually end up with that many cells needed in a table. What a huge table though.
Matt Whyndham
So some other people have *never* done this? Another personality test, then.
Matt Whyndham
And young people should be taught what the “end” key is for in Excel.
CharlieO
Last year I “ran out of rows” in Excel. The company I work for had to upgrade to Excel 2007 which is supposedly capable of handling up to 1 million rows!
I ran into this problem when having to compile lists of keywords to trigger paid search or ‘sponsored link’ adverts that run on search engines. The process involves stemming out combinations of words. Large travel and finance companies will often run different ads for millions of different combinations of words.
The trick is to predict the information need of the searcher, write relevant ad copy to be triggered by these keywords (also geotargeting, dayparting etc.), and link the ad to the page that most accurately matches the likely intent of that person’s search at that time.
Being able to group, sort filter etc. millions of phrases/words on one sheet definitely helped to stop my brain bleeding quite so much!
Jill Walker Rettberg
Wow. Thanks, Charlie, for a real world example of why you’d need that many cells. Does it take ages to manipulate data in such a large spreadsheet, or do our newish computers handle it pretty well?