Forum Discussion
Reversing columns in a table
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!).
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.
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.