Can I swap columns around with breaking anything?

[abrogard]

I wonder if there’s a methodology by which we can shuffle columns around to make a more useful presentation without breaking the calculation structure of a spreadsheet?

Like here I would like to swap E and F. So that input is between start and end. More intuitive I feel.

I was hoping there’d be some function that would change the column letter for a column and handle all references within the spreadsheet. I think not.

Any work arounds short of simply doing the whole thing manually?

example19april.ods

[keme]

General guide

Depending on your formulas, shuffling may break calculations or not.

A few “safe” options:

• You can group columns to easily collapse groups (hide columns). This procedure keeps the sequence of visible columns, only hides what is irrelevant to presentation.
• You can reference your data in a different spreadsheet in the same file (new tab), and use that for presentation. Generally, plain direct references can be moved around safely and without much hassle.
• You can register your spreadsheet as a database, and extract the fields in any order you want.

Specifics

To advise on dos and don’ts and perhaps suggest a method specifically for your spreadsheet, we need hands on your spreadsheet. Edit your question above and upload a sample with any confidential content mangled/removed.

Edited:

Like here I would like to swap E and F. So that input is between start and end. More intuitive I feel.

Save your work. Make a copy to work on. Do not make major structural changes to the original. Test before committing.

• Right click the F column header and select Insert column after in the context menu that pops up.
  
  A new empty column G will appear, pushing columns to the right.
• Left click the E column header.
  
  The entire column will be selected.
• Left click-hold and drag the selected range (E column) to the new empty space in column G
  
  Note that you click-drag inside the data grid. You can’t use the header as a drag handle. Your formulas should update to reflect the new location of data. Double check this.
• Right click the E column header and select remove the (now empty) column.

Note: this works with formulas using direct addressing. Range operations (summation, lookups) may suffer when data is rearranged. As far as I can tell, you address only single columns at a time and do not use any kind of redirection, so column rearranging should be safe.

[abrogard]

That’s lovely I think. It will serve for my purposes as best I can make out right now.
Obviously if there’s no build in thing for this it is very advisable to make sure I’ve got the format I want before making a complicated sheet.
But in the early stages of development I think it’d be great. Thanks