SOLVED

Reversing columns in a table

Occasional Contributor

I have several table with 13 columns. I need to reverse the columns. How can I do this without cut and inserting the cut data? Right now the table start with the oldest years on record in column B and go on to the newest records. I want the newest record to be in column B and have it go backwards for the current year. Is this possible?

7 Replies

@Ginnie_H39 

 

Of course if you do what you're asking, you're always going to be having to insert a new column for each new year that comes along. And (potentially), possibly needing to revise any formulas intended to analyze and produce reports.

 

May I ask, before we get into changing the order of the columns, what kind of data are in each ROW of this spreadsheet? In fact, without revealing confidential information, if you don't mind my asking, what kind of annual (presumably) inputs are made? What kind of outputs/reports are expected?

 

The reason I ask: it might make more sense to rearrange your sheet by rotating things 90 degrees, so that the years run vertically (as a general matter, most databases are organized that way, with time running vertically, other kinds of data associated with each period in the columns. Not an essential thing, but it's how most experienced users "visualize" the data. And it's quite easy to use Excel's many analytical functions to extract data for meaningful reports.

 

 

@Ginnie_H39 If it's not a structured table you can select any cell in the header row and then custom Sort, Options..., Sort left to right.

Riny_van_Eekelen_0-1669042180686.png

 

@mathetes 

I attached the file that I am working in. This is our department budget amount for each year. There is all kinds of links in this workbook. We want to change it so the most current year is in column B and have it go down in years after that. This is a copy of our file. I thought by changing the start here! page it would change the rest of the sheets but that did not happen. From going forward we would like to be able to insert a new column for each year in column B. I am not sure if I answered enough for you. Please bear with me. Thank you for taking the time to try and help, I appreciate it.

@Ginnie_H39 

 

After looking over the workbook, and its many sheets, my first reaction is to ask "WHY are you wanting to change it?"

 

If it has been working, with all of its subordinate sheets organized with the earliest year on the left, moving to the right as years move on, this could well be a case of "If it ain't broke, don't fix it." And to the extent that you have graphs (a few) that show trends over time, going in the same direction (which happens to make sense to our "western way of thinking"--well, again, why change?

 

Based on your original question, I think both @Riny_van_Eekelen and I (although I can't really speak for him) were picturing a single data table; that is most definitely not what you have here. Instead it's year-by-year actual and budget numbers for different departments or charge centers, each on their own sheets, with a final spreadsheet that compiles data from each of those charge centers.

 

Frankly, it makes sense to keep it going as it is. Unless there's some compelling reason--which is not evident from just looking at it--I'd think you'd want to keep it going as it's been going. Your request for help uses words like "we want" and "we'd like" when you refer to the change of order in the various columns; what you don't describe, and it's (in my mind) a major omission, is the reason underlying that "want" or that "like."  

 

It may be that whatever output is produced by these sheets--that output itself could be changed WITHOUT monkeying with the underlying data. But I (for one) would want to know what purpose is being served by this proposed change.

My engineer would like to not have to scroll over to see the newest data. Since this is ongoing the newest data keeps getting farther over, to where you can not see it without scrolling over. I have other books that is the same way that he would like to have changed. I am struggling with this.
best response confirmed by Hans Vogelaar (MVP)
Solution

@Ginnie_H39 

 

My engineer would like to not have to scroll over to see the newest data. Since this is ongoing the newest data keeps getting farther over, to where you can not see it without scrolling over. I have other books that is the same way that he would like to have changed.

 

One very simple solution to meet that desire, making an assumption on which data is most commonly viewed--seen on a regular basis--would be to simply hide the columns for years more than, oh, 5 years old. Keep the layout (and all the interrelationships and formulas) intact, and just hide the prior history. It would still be there if needed; summary pages would still have access....but scrolling would be eliminated. I've done an example of that on the 70X tab in the attached, just to demonstrate what's possible. [Note: it does become necessary to "unmerge" some of the header rows before you can do this, but that's a relatively simple matter.]

 

Another possible solution -- requiring a bit more work (to define it, first, and then to implement it)--would be to create a new "dashboard" sheet that extracts the data that the engineer needs to see from those subordinate sheets, pulling it together in one convenient spot. That would, as noted, require sitting down and thinking about what the needed output is.

 

My underlying point: it would be a mistake to totally reorganize what has been a smoothly functioning workbook for the sake of what is, admittedly, yes, more convenient, IF a few minutes devoted to answering the question "What do we really need to determine (or see or compare or....) in the first place?" could lead to a simpler solution that is also convenient (maybe even more so!).

Thank you for your help.