Excel Data Organization: Array Formulas? Tables? Named Range?
- by Joe Arasin
I'm trying to make a huge Excel sheet reasonably maintainable, but it's huge in the "hundred-table-db" direction, rather than the "hundred-thousand-row-table" direction.
I want to have a baseline data table that looks something like this:
| Indicator | Units | 2010 | 2015 | 2020 | 2025 | Source |
| GDP | $Gazillion | 300 | 350 | 400 | 450 | BLS |
| Population | Millions | 350 | 400 | 450 | 500 | Census |
| PetMonkeyPopulation | Thousands | 50 | 60 | 70 | 80 | SimiansRUs |
And then be able to have another sheet that looks like:
| | 2010 | 2015 | 2020 | 2025 |
| MonkeysPerCapita | .1 | .2 | .3 | .4 |
| MonkeysPerDollar | .01 | .01 | .01 | .01 |
| GDPPerCapita | 300 | 400 | 450 | 600 |
Is there some standard way to make this kind of thing maintainable?