Horizontal to Vertical Data Switch & Ghost Switch in Excel


  • In the Image above, the Top table is the original horizontal table I created
  • The bottom-right is the table converted to a Vertical table, totally independent of the first table, the first could be deleted in place of this table, if desired.
  • The bottom-left table is a ghost-copy of the first, which changes automatically when the original is edited, but is not editable itself

To convert a Single Row of Data to a Single Column, & Vice Versa

  • Select all the cells with the data you want to switch the orientation of
  • Copy, Ctrl+C, (do not Cut)
  • Click on the first cell box you want the Data to begin at
  • Click the Down Arrow on the Paste Button, on the Home tab
  • Click Transpose

To convert an entire table and all data & formulas, with multiple rows & multiple

  • Select the entire table, as a rectangle or square
  • Copy, (don’t Paste)
  • Click the Cell where you want the upper-right-most corner to be
  • Click the Down Arrow on the Paste Button, on the Home tab
  • Click Transpose
  • Depending on formatting, some borders may need reformatting, its a good idea not to format your graph borders too much before a horizontal-vertical conversion

To create a linked duplicate Ghost table in an opposite orientation which changes &
updates automatically when the original data is edited or modified:

  • Select the Original Table
  • Count the Rows & Columns, ex. 4×6
  • In a separate Area, select an empty selection of 6×4, the opposite of your original table
  • Hit the Insert Function Button ( f x )
  • Select the “Transpose” function
  • Hit OK once
  • Select the Entire Original Table
  • Hit Ctrl+Shift Enter, it will paste the formula into every cell selected, rather than only the top-right corner
  • The new table is not directly editable, but will change according to your data changes in the original table
  • All cell borders & shading will be lost, but feel free to recolor and design the table if u like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s