MSC Bobs
Member
Hi everyone,
I was hoping to tap into the vast knowledge base here in preparing me to make a proposal.
I'm trying to get everyone where I work to start structuring their files database-style; flat-file tables.
Before I get to my question, I want to give a little background on how I do stuff (which I think is vastly superior).
Every new workbook I start has (at least) three tabs: Summary, Calcs, Data. The Summary tab is for my charts and dashboard. The Calcs tab is for intermediate calculations that pull from the data and flow to the Summary. The Data tab is just that. Mostly just one or two tables. I use tables religiously and I structure them so they're flat file; they grow downward and not out to the right. Anything I might need to sort or look up, I add a new field to the table. I've got no empty fields and no empty space in any record. Nobody taught me to do it this way. I just use enough spreadsheets that I figured it out on my own. It was actually learning SUMPRODUCT and SUM(IF( that helped me figure this out.
The problem is that if nobody is taught this stuff, they do what feels natural which usually leads to matrix-style and cross-tab file structures. We all know this severely limits the functionality, efficiency, and ultimately the effectiveness of the spreadsheet. The data winds up being a really bad manual pivot table that cannot grow or expand.
So, my question is how do I explain database-style structure to people in a way that non-experts can understand and also so that they see the value in it and don't resist changing? I'm trying to articulate that mixing data and intermediate calculations is a bad thing, but I'm really struggling to come up with an explanation that's easy to understand.
I was hoping to tap into the vast knowledge base here in preparing me to make a proposal.
I'm trying to get everyone where I work to start structuring their files database-style; flat-file tables.
Before I get to my question, I want to give a little background on how I do stuff (which I think is vastly superior).
Every new workbook I start has (at least) three tabs: Summary, Calcs, Data. The Summary tab is for my charts and dashboard. The Calcs tab is for intermediate calculations that pull from the data and flow to the Summary. The Data tab is just that. Mostly just one or two tables. I use tables religiously and I structure them so they're flat file; they grow downward and not out to the right. Anything I might need to sort or look up, I add a new field to the table. I've got no empty fields and no empty space in any record. Nobody taught me to do it this way. I just use enough spreadsheets that I figured it out on my own. It was actually learning SUMPRODUCT and SUM(IF( that helped me figure this out.
The problem is that if nobody is taught this stuff, they do what feels natural which usually leads to matrix-style and cross-tab file structures. We all know this severely limits the functionality, efficiency, and ultimately the effectiveness of the spreadsheet. The data winds up being a really bad manual pivot table that cannot grow or expand.
So, my question is how do I explain database-style structure to people in a way that non-experts can understand and also so that they see the value in it and don't resist changing? I'm trying to articulate that mixing data and intermediate calculations is a bad thing, but I'm really struggling to come up with an explanation that's easy to understand.