Spreadsheet column/row design conventions

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Does anyone know of any design conventions that dictate the correct
orientation (i.e. row-column or column-row) of data within a spreadsheet?

Where I wish to display an independent variable (e.g. the months of a year)
against a dependent variable (e.g. company profits), there is a design
decision to be made as to which variable I store in rows, and which I store
in columns. I would like to know if there is any rigorous method I can rely
upon to take this decision before embarking on building any large
spreadsheet model. Please note, I am aware of the 256 column limitation in
Excel.

Further background: To date, each of the several Excel modellers in my
company has tended to make his/her own decision on this matter, resulting in
a lack of standardisation. We have used the Transpose function where we have
needed to conform two spreadsheets that have different orientation. We would
like to implement a rigorous and objective standard that will be
self-evident to all of our spreadsheet modellers, to enable simple
interoperability of all of our spreadsheets, going forward.
 
Sarah,

For many applications, setting up a sheet means making a valid
database-style table. Rows become records. A key attribute of a record is
that if the table is about employees, there is one record per employee.
That bears repeating. But I won't. Columns designate fields within those
records, and should contain data only about the individual records (specific
age, date of birth, etc.), not other items (like individual sales goals
achieved, which should generally be in another table).

An important consideration for row/column orientation of a table is the
Excel tools you may be using on your tables. They work almost exclusively
with rows. Autofilters filter (query) rows. Database functions (DSUM, etc)
summarize by rows. Pivot tables group and summarize by rows. Sorting
normally sorts by rows.
 
Thanks for the reply Earl

Earl Kiosterud said:
Sarah,

For many applications, setting up a sheet means making a valid
database-style table. Rows become records. A key attribute of a record is
that if the table is about employees, there is one record per employee.
That bears repeating. But I won't. Columns designate fields within those
records, and should contain data only about the individual records (specific
age, date of birth, etc.), not other items (like individual sales goals
achieved, which should generally be in another table).

An important consideration for row/column orientation of a table is the
Excel tools you may be using on your tables. They work almost exclusively
with rows. Autofilters filter (query) rows. Database functions (DSUM, etc)
summarize by rows. Pivot tables group and summarize by rows. Sorting
normally sorts by rows.
Click to expand...
 
Back
Top