Ask Your Question
0

Can I swap columns around with breaking anything?

asked 2021-04-19 05:29:28 +0200

abrogard gravatar image

updated 2021-04-19 10:06:57 +0200

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?

C:\fakepath\example19april.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2021-04-19 09:39:27 +0200

keme gravatar image

updated 2021-04-19 11:22:25 +0200

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.

edit flag offensive delete link more

Comments

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

abrogard gravatar imageabrogard ( 2021-04-19 12:07:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-04-19 05:29:28 +0200

Seen: 31 times

Last updated: Apr 19