Excel 2007 : Reverse Table

[jacquelyn]

I am making a table with many columns making up the header row, and the data in the rows below with each entry in its corresponding row.

The table is going to be be quite large like this so I would like to reverse the table to be exactly the opposite as it is now. I would like there to be a header column instead of a header row.

Is this possible to do? I have spent quite some time trying to figure this out with no luck. I know that I can easily copy and paste the cells to be viewed the way I want them, but if I want to use the list features of a table or anything else I can't.

Hopefully this is not too confusing.I attached the document the way it is now to hopefully make my question more understandable.

Thank you.

[Paul]

Hi Jacquelyn, welcome to the forum.

Are you saying that you want the headers that are currently in cells A1:L1 to instead be in A1:A12, and each 'record' would be in columns B:XFD (B1:B12, C1:C12, etc)?

If so, why? That goes against everything that is good and right about spreadsheets and databases. As you mentioned, you would lose the ability to work with that data as a table/list and just make things less "normal" for others.

If it's a data-entry issue, perhaps a user form or data entry form would work better. Let us know...

[Enquire]

Hi Jacquelyn, welcome to the forum.

I think what you want to do is to "Transpose" the "header row" as a "header column" ... you can do that by placing the cursor where you want the first cell of your new header column and entering the formula"

=TRANSPOSE(A1:A12)

That should do what you want. However, I have also to agree with Paul's comments. Since this looks like the start of a long table, one that could well emerge later as a database; you should know that, conventionally, a row in a spreadsheet is parsed as a "record" in a database, and that each cell (in a different column) is treated as a different element or field in the record. So, if you want to keep the option open to treat the table as a database in the future, or export it to a database, you are better off leaving it as you have it now.

Having said that, I am wondering why you were thinking to change, and it occurs to me that there are three things you can do to make it work better for you and be more manageable.

1) Define printing of the table to be landscape ... that way you can easily fit the full width of the table on one page. If in North America, you could even use legal paper (14" long/wide) or Foolscap in those countries that still have it (similar size). There is a comprehensive discussion of paper sizes on Wikipedia here: [Wikipedia] Paper size

2) There is a function to repeat headers when a table ends-up flowing onto multiple pages. For your case, this would make it manageable, since every page would have a header ... if that is something that you were worried about ... pages with tables of data, but with the header only on page 1.

To do this, select the [Page Layout] tab, then on [Page Setup] area of ribbon, select [Print Titles] select the row(s) you want to appear at the top of each page. In your case "A:A"

This would mean row(s) A to A (one row) would appear at the top of each page.

3) If it was multiple pages, then definately, it would make sense to put both the page number, as well as the total number of pages in the table.

File | Print Preview | Setup | Header/Footer

Then, depending where you want to put it ... in the header or footer and whether on left, centre or right, enter the following: &[Page] of &[Pages] ... these fields/codes will automatically put the page and the total pages on every page as such" Page 1 of 17, Page 2 of 17, etc.

If you do all of the above, I think you will find it fine the way you have started doing it ... plus, you will find it easy to export the data in the future if you ever decide to export it into a database...

Hope this helps...

Enquire
--

[jacquelyn]

Thank you taking the time to help me! If I have more questions, still related to the document linked to this post, but not related to this questions am I supposed to start a new post for each question. I have never posted to a forum before this time. It's related to the capabilities of drop-down boxes, as well as how to get rid of the rest of the empty cells surrounding your spreadsheet.
Thank you again!

[Paul]

Hi Jacquelyn,

If the question is on a different topic please start a new thread, even though it's involving the same workbook. If anything you're trying to do is related to this thread, you can always provide a link back to it for reference.

Thanks.