This is the First Blog in my life.
Well I must be using Excel 97 since 1998, when I was in my 10th Grade, Since then I always wondered, why would Excel need so many columns, is there some one in the world who is really going to use so many columns.
Around 2002, when I first came across DBMS, and started creating tables, felt why should I Set Datatype for each field cant Database analyse the Data and assign the database itself. However in this regard Excel was quite compromising for some extent. Then I learnt the difference between file Database and Server database around 2004.
Now Especially given the fact that many Databases would themselves restrain providing on an average 1024 columns.
And our Gentleman Spreadsheet Excel 2010 Started giving 16,384 Columns.
Now in 2011, when I first saw the SuperMan PowerPivot, My god He started storing Millions of records with some Mega Bytes.
Then somewhere I happened to read that PowerPivot, Stores its Data in columnar fashion which is why it takes lesser space than that of MS Excel.
Ok, I thought let me see would Excel also have that behavior, I prepared two Excel Files
1. Only One Row with Values Filled in each Cell, So I could fill from 1 to 16,384. And Saved it. (Saving the Values in Columnar Fashion)
2. Only One Column with Values 1 to 16,384. (Saving the Values in regular Fashion as Rows).
Guess what First Excel File
So there is a difference on 32 KB, for Just One Column with Limited text in it.
Now I got the secret behind PowerPivot File Storage Magic.
Manjunath Ram Venkat 