Spreadsheet column/row design conventions

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.






0
Sarah6159 (2)
6/23/2004 12:46:37 PM
excel 39879 articles. 2 followers. Follow

3 Replies
444 Views

Similar Articles

[PageSpeed] 34

Dear Sarah

I tend to agree with Vaughn's comments. I come from a 
database background and tend to think in fields and 
records where each column would represent a field and each 
cell a record. But as far as I am aware there is no 
convention set in stone as this would detract from Excel's 
user-freindlyness. In other companies that I have worked 
for a standard was set and we were all asked to conform.

Regards

Paul Falla
>-----Original Message-----
>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.
>
>
>
>
>
>
>.
>
0
paul_falla (99)
6/23/2004 2:14:44 PM
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.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Sarah" <Sarah@TT.com> wrote in message
news:Opl5GDSWEHA.2908@TK2MSFTNGP10.phx.gbl...
> 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.
>
>
>
>
>
>


0
nowhere1083 (630)
6/23/2004 2:22:57 PM
Thanks for the reply Earl

"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:uOMjV2SWEHA.644@tk2msftngp13.phx.gbl...
> 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.
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Sarah" <Sarah@TT.com> wrote in message
> news:Opl5GDSWEHA.2908@TK2MSFTNGP10.phx.gbl...
> > 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.
> >
> >
> >
> >
> >
> >
>
>


0
Sarah6159 (2)
6/25/2004 11:02:39 AM
Reply:

Similar Artilces:

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Delete Rows #2
I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: > This macro: > > Sub remover() > v = Sheets("Sheet1").Range("A1").Value > For Each r In Sheets("Sheet2").UsedRange > If r.Value = v Then > r.EntireRow.Delete > Exit Sub > End If > Next > End Sub > &g...

Unreconciling Multiple Rows In Money
I would like to know if there is a way to unreconcile multiple rows in Microsoft Money? I can do one at a time, but I need to do a large number of them and don't want to do them one at a time. I've tried to contact Microsoft directly about this but got passed to the company that I bought my machine from, who passed me off to another support number in their company - which tried to charge me for asking this question. Any info on this would be great! Doug In microsoft.public.money, Doug wrote: >I would like to know if there is a way to unreconcile multiple rows in >Microsoft ...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...

Nice Column Graph
So I have data like this: Year Month #start #end 2001 1 4 2 2001 2 6 5 2001 3 7 1 2001 4 5 4 2001 5 2 6 ..... I'd like to make a column chart where the year and month are on one axis and then I have a column for each #start and #end for each month/year pair. Is there a way to tell the chart wizard that I want to use those two columns for the x axis and then the #start and #end columns for the other? Thanks. Hi, Remove the 'Year' and 'Month' text from the 2 cells and then select and use the chart wiza...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

repost: split excel columns
Thank you for the feed back on the below question. When using "text to columns" it seems to create new columns and the info. in one column can be split but I was looking to essentially create two columns in one. For instance, within column D which was widened, I wanted two columns in rows 5:15. I'm trying to convert something into an excel template that is too long and don't want to change any of the column widths already in place. I know you can do this in Word when transferring an excel table. You would right click on the cell and there is a function called split ...

Text to Columns from drop down list update
Hi I need to perform a Text to Column conversion from a drop down list, but I also need the extracted value to be updated if the value in the list is changed. eg: Drop down list has 2 values: 1. 4x16K 2. 8x8K If the user selects 1 I can easily extract out to another cell the value 1 using Text to columns, however if the user then changes the choice to 2 the text to columns extraction is not updated to 2. Is there a way to update changes in the original cell using text to columns? Or is there another way I can assign a value to a drop down menu choice in a different cell while havi...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

moving columns
Hi all, I have a sheet in which in row 3 there is data like this: ColumnA B C D E F Customer name 1-30 30-60 60-90 90-270 270-300 what is want is that 270-300 should move in front of 1-30, I mean t say that column F should move in front of Column B but after column A movement should be on basis that in column F, it is written 270-300 and columns may change , i mean to say that sometimes there is no 30-6 and 90-270 column columns may increase or decrease do we have a solution to this thank u al -- Message posted from http...

Validation
I would like to have combo box functionality for the data validation feature in Excel 2000. This doesn't seem to be available in the native validation setup dialog box. Am I missing something? I would like to display the validated items list in the leftmost column and have column(s) of description display to the right of each list item (so I can tell which list item I should select). Suggestions? Many Thanks, Bill Vallance Bill Debra Dalgleish has instructions for creating dependent DV lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Tue, 08 Jun 20...

coloring in rows
Hi Could somebody please help me with the following problem? How do I set a conditional format that if L5,M5, or N5 = �BAD� the highlight the row yellow, otherwise leave it the standard non fil color (clear) Thanks Andre -- kob ----------------------------------------------------------------------- koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2863 View this thread: http://www.excelforum.com/showthread.php?threadid=48371 Select Row 5. Format> Conditional Formatting. Formula is: =OR($L5="Bad",$N5="Bad",$M5="Bad") Set ...

Importing five row list from word 2003 to excel
I have a Word five row list I would like to import into Excel in a five column list. Every hundred items or so their is a new heading. What do I do? If it is in a word table you can cut and paste it. But you will have to reformat it. Once you get it into excel you can sort it and delete the duplicate headers. --- Message posted from http://www.ExcelForum.com/ ...

Where can I get a free Inventory control spreadsheet
I am looking for a Inventory/Stock control excel template, but I don' want to pay for it!!!!!! Can anyone suggest a sit -- Message posted from http://www.ExcelForum.com You can search in Google, using keywords like Excel and Inventory. Or look at the templates on the Microsoft web site: http://office.microsoft.com/templates/default.aspx Pritch14 < wrote: > I am looking for a Inventory/Stock control excel template, but I don't > want to pay for it!!!!!! > > Can anyone suggest a site -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/...

Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !! Anyone come across this ? When I save this file in csv using excel 2003 A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-...

Changing Exch5.5 GAL columns in Outlook client view
Sorry if the subject line is a bit cryptic; anyway, in the Outlook client, if you display the 'Address Book' you get the following columns - name, business phone, office, title, company, alias, e-mail type, & e-mail address. How do you change it so you show the display name, business, extension, cell phone, & internet email address? Basically, how/where do you change the columnar data the client sees? Thanks, Mike Lawson Mike Goto View | Columns and there u can manage views... KJ "Mike Lawson" wrote: > Sorry if the subject line is a bit cryptic; anyway, i...

Solution: Error 2306: There are too many rows to output (Access to Excel using OutputTo)
I've joined the legions who've encountered this problem with the OutputTo method in Access: Run-time error 2306 There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access A lot of people seem to have encountered it. The reason is because OutputTo defaults to an old version of Excel which only allows 16K rows A lot of people also suggest using TransferSpreadsheet instead, but that caused me a lot more problems (putting a single quote in front of my text data) Intellisense for the command does not list what your options are for th...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add wants to put it in the same stacked column. Use one of the links on this page. You need to set up the data so the single column is in a stacking position with no other columns of data. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Einstine wrote: > How can I do a chart with a stacked column beside a sing...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

not repeating text boxes in reports with columns
Hello, I am trying to create a report with columns without repeating certain text boxes. Here is an example of what I would like to create: [Date] "Month1" [Date] "Month2" [Date] "Month3" [Product] "Product1": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product2": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product3": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product4": [q...

How do I insert a radio button into a spreadsheet?
Use menu.. -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1893 View this thread: http://www.excelforum.com/showthread.php?threadid=37768 http://www.functionx.com/excel/articles/radiobuttons.htm This should help you. ...

Spreadsheet File Size
I have a Spreadsheet that has 10 tabs at the bottom. Each of thes sheets cross reference dataand thee are many complex calculation throughout. Many of the formulas are nested IF functions which I kno take up a lot of space. The unfortunate thing is the file size is 7 MB, which is hard for others in the office to work with. I would appreciate any ideas as to how I could reduce the file size. For example: - Is there a way or someone who could look at this and kind of audi it to see if there are ways to reduce size? - Should I break it up into seperate files (shich would be harder fo other ...

MATCH function; comparing letter grades in columns
I am currently comparing the contents on two cells in columns H & I (for example, cells H10 and I10). The contents are letter grades (i.e., A, A-, B+, B, etc., through E). I have my function tell me if there is any difference between the two columns and report the result (i.e., =93no change,=94 rating decrease,=94 or =93rating increase=94) in th= e adjoining column, J10. I use the MATCH function, and since it can=92t distinguish between the letter grades, I have placed the letter grades in cells M2 through M13. My function is as follows: =3DIF(MATCH(H10,M$2:M$13,0)=3DMATCH(I10,M$2:M$1...