With Excel 2007, Microsoft introduced the "Big Grid" – 1,048,576 Rows and 16,384 columns. Back in October 2005, there was a debate at Daily Dose of Excel (Too Many Cells) on whether the Big Grid was a good move or not.
Did users really need to jump from 65,536 rows in Excel 2003 to over one million rows? At the time, I thought it was too much of a leap and a bad move on Microsoft's part.
Two years later, I wouldn't say it was a "bad move". But I do feel the value proposition has turned out to be a bit thin. Over the last two years, I've made some observations.
1. From a practical standpoint, spreadsheets growing to over 100,000 rows aren't fun to deal with. As data grows, you start to notice performance issues. Your spreadsheet become slow to load, and then slow to calculate. Why does this happen? I'm pretty sure it has to do with the way Excel handles memory. As I understand it, when an Excel file is loaded, entire file is loaded into RAM. Excel does this to allow for quick data processing and access. The drawback to this behavior is that each time something changes in your spreadsheet, Excel has to reload the entire spreadsheet into RAM. The net result is that, in a large spreadsheet, it takes a great deal of RAM to process even the smallest change in your spreadsheet. Eventually, each action you take in your gigantic worksheet will require an excruciating wait.
2. Although the Million row limit does make it convenient to use Excel as a temporary ETL platform for large data dumps, I've found that I can apply any data cleanup or transformation just as easily in Access, or SQL server. Why quick risk extra time or potential errors by adding the Excel layer?
3. Although Excel can handle more than 16,000 columns, Access can only handle 255 columns. This mismatch has caused some frustration along with some confusion.
Now that I sound like a complete curmudgeon, I should say that I was never satisfied with the previous row and column limits of 65,536 and 255. I do, however, think a more modest row and column increase of would have been sufficient. Oh….I don't know…..around 200,000 rows.
I haven't personally experienced the "thank god Excel has 1 million rows" moment. I'm sure there are those of you who have. After working with the Big Grid for a few years now, I still struggle to find the value in it.
I'm interested in hearing your vote on the Big Grid. Here are your choices.
- I hate it like poison: It was a huge mistake
- It's never useful to me
- It's sometimes useful me
- It saved my life: I want to marry it
"It's sometimes useful to me"
The increase in columns has never done it for me, however the row size increase was a life-saver.
Regular pivot table creator (and with up to 200,000 rows at times) it trumps Office 2003 easily.
It's never useful to me.
I could do with more than 256 columns, but I've rarely run up against the 65k row limit.
Of course, 512 columns would have been more than sufficient.
nerver,
More rows yes, about 370? would work!
rows never an issue, if your looking at spreadsheet with a million rows on it, your in trouble!
It's sometimes useful me… (rarely)
I like the additional rows but I don't care for the additional columns. Interesting point about Access only supporting the 255. Although I never use so many I still think it should be consistent.
Rows–> definitely. I often need to be able to work with datasets over 64k, but very seldom over 200k
Columns –> no real opinion worth sharing
Rows – I had an 800 – 900K data dump I used to analyse every month using pivots. Fantastic. Column limit was never an issue.
Excel having this feature saved me from having to build a database (somehow – as my excel skills are far superior to my access).
"640K ought to be enough for anybody."
-Misattributed to Bill Gates, but you get the point.
It saved my life: I want to marry it
I'm hoping for even more rows in Office 2010.
Jared: My god! More rows than 1 million? You need to get a new job!
Only once…
I know that option aint there. But I ended up using more than the 64k rows once.
PS: Please add the subscribe to comments plugin. I get paid to suggest it
I have reached 300K rows ocassionally, but only to take the advantage of pivot tables on such huge amount of info. Never looked at the raw data behind.
I celebrated the additional rows in XL2007, simply because I needed for certain works. I like to know I have 1049K in case I need (and yes I slightly hate Access).
I have never passed, however, from column 100.
Sometimes useful.
I've been working with multiple reports to get around the limit before. Now I can use one report with an extra column displaying the old filtering criteria.
Also this simplifies my NFL pool. I run a combination report each week showing all possible outcomes and who wins in each case. With 2 ^16 outcomes, I couldn't show a header row.
It's sometimes useful me, rows only – haven't been able to use the column increase. I just wish I could get work to upgrade to 2007, then I wouldn't have to transfer files back and forth! (haven't cared for Access since 1.0 – even though I keep trying on each new release).
If I had a spreadsheet with anything close to a million rows I'd slit my wrists. Use Access for that kind of data transformation, if you have more than 255 columns you might be reconsidering your table structures anyway…
BTW, good stuff Mike
MS
Seriously? What if you had a log of internet traffic from your firewall that was half a million rows and you wanted to find all the entries to a certain IP? You wouldn't just load that in Excel? I would. That's why I lobbied MS for more rows and columns. Do you also want a car that has a governor that limits it's speed to 55?
I think the increased limits are good on both axes. As Dick says, there are lots of worksheets that are event logs, and 64K could easily be too small. Not so sure about columns, but again, 256 was way too small. I can imagine modeling/scientific/analysis applications where you want square matrices with size bigger than 256 x 256.
DK: I suppose it comes down to how comfortable we feel with a certain tool-set. I would actually bring half a million rows into Access. Indeed, it would be faster for me to analyze that many rows in Access.
I am surprised at how many people have commented that they love the increased rows because they can now create pivot tables on large data dumps " 800k – 900k rows".
Again, a broader skill-set would help here. I would find it far easier to connect a pivot table to extermal data sources than to actually bring the data into Excel.
I cringe when people say "I hate Access" . Not because I'm gay for Access. Because limiting yourslef to a single tool (Excel) can limit options that can potentially help you do things in more productive ways.
Again, I don't think the increased limits are "bad". But of the all the tools I am comfortable with using, Excel is the least adept at handling and analyzing half a million rows.
So my tool set is limited? OK, yeah, got it. Message received.
Joking aside, I'm calling bullsh*t that you can do it in Access faster.
Open the text file in Excel, go through the OpenText wizard. Sort on IP address. Delete all rows that don't have the IP address you want.
Now do the same in Access. Faster? OK, now get the count of the number of records, email to that number to someone, and throw everything away. Yep, you only needed the count. In Excel, I hit Ctl+w and say 'no' to saving. What you do in Access? Close the database without saving? I don't think so.
If I may be so bold, Excel is the right tool for the above contrived job. The number of rows is only one factor in deciding which tool you use, and it's only the most important factor when you have more rows than the tool can handle.
I like Access, but I'm not going to put scaffolding around my house to clean the gutters. I'll just use a ladder, thanks.
Yeah but scaffolding is safer, you are less likely to fall of.
It's rare, tops twice per month, but I do use over 65,536 rows usually for data downloads from online software where your options are .pdf or .xls–obviously, I do not want my local tree-hugger to paper-cut me to death with the printouts… But 1,048,576 rows does seem excessive.
As to the column increase I LOVE THIS!! Had a performance management file where there needed to be a column for every day of the year. 365 days > 256 columns so I had to have one file for the first half of the year and a second file for the second half. Invariably at mid-year, no matter how ofter I explained why there had to be two separte spreadsheets, I would get the questions of: "Why can't I see everything? Did you delete it?" Which would translate in the pointy-hair boss's mind to: "Oh, this is just a smoke-screen and she's not doing her job of daily tracking." ARGH!! So for all the haters, and any Microsoft developers out there considering decreasing the number of columns in 2010, stop the hate.
DK: We should have a race the next time we're in the same room.
You'll drive Excel, and I'll drive Access.
Don't believe Mike, he really is gay for Access, but seriously folks…..Dick, I have to agree with Mike that we could probably do it quicker in Access, plus Access has a better toolset for automating the process, but I like your thinking and analogy. And in this case I would NEVER attempt to try and kick your 'ladder' because you are comfortable on it, and if you can do this particular task better in Excel, I'm all for it.
Here is my problem with Excel…every college in the world teaches every student how to 'use' Excel to do everything, without teaching them anything about data. So in the real business world, you wind up with Excel-Hell because people try to solve everything with Excel, and Excel doesn't enforce any data integrity at all. I'm not lobbying for everyone to be and Access or database guru, just for people to realize that there IS a valid reason to use a database (most business cases that I can think of) and a reason to use Excel (primarily accounting but there are some other valid reasons too).
In the end, if you have a need to do something, and you have a method or process in one or the other, fine and dandy, but be open to other options…
Now, back to my original post…I could have elaborated a bit and stipulated that if I had a million rows in a spreadsheet on a regular basis and had to regularly grab data from it, I would slit my wrists…
You will now be returned to your regular programming…and thanks for listening…;^)
Marlin: Thanks for backing me up. It's lonely out here in Access-Land.
I often have to work with data files of over 1 million rows (3 mil+) – but i would never try it in Excel.
In the end (i feel) – databases are for data processing, spreadsheets are for data analysis.
I use SQL Server to get the data down to what i need – then Excel to do the reports etc.
Like your post. Maybe plus size sundress info might assist someone out there.