Insert Columns Between Multiple Existing Columns

**andrew1830** · 12-30-2009, 09:24 PM

I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times. Is there an easy way to do this?

Thanks in advance.

**Ron Coderre** · 12-30-2009, 09:46 PM

Try this...
Assuming you have 2000 columns of data, in B2:BXY1000

If there isn't a blank row above your data, insert one
Then...
•Beginning in the blank cell above the first data column (B1),
enter a series from 1 through however many data columns you have
(in my example the series is 1 through 2000 in B1:BXY1)
•Copy that series
•Paste it to the right of the series, beginning in BXZ1
•Select all of the data, including all of the numeric series
•From the ribbon:
•••Home.Sort_&_Filter,Custom_Sort
••••••Click: options.Sort_left_to_Right....Click: OK
••••••Sort by: Row_1
••••••Sort by: Values
••••••Sort by: Smallest to largest
••••••Click: OK

Since the number series has two 1's, two 2's, etc...
blank columns will be sorted into your data.

Is that something you can work with?

**andrew1830** · 12-30-2009, 10:37 PM

Thanks for the help Ron.

That tip worked great.

On a side note, my formulas in the columns to the right of column B all referred to the data in column B. When I ran the sort the formulas didn't automatically update to reference column C (where the original column B ended up). So, I did a find and replace to change "B" in the formulas to "$C" before running the sort and everything turned out perfectly.

**Ron Coderre** · 12-30-2009, 10:45 PM

I'm glad that worked for you...thanks for letting us know.

**sveitsimeier** · 02-09-2017, 02:03 AM

Originally Posted by Ron Coderre

Try this...
Assuming you have 2000 columns of data, in B2:BXY1000

If there isn't a blank row above your data, insert one
Then...
•Beginning in the blank cell above the first data column (B1),
enter a series from 1 through however many data columns you have
(in my example the series is 1 through 2000 in B1:BXY1)
•Copy that series
•Paste it to the right of the series, beginning in BXZ1
•Select all of the data, including all of the numeric series
•From the ribbon:
•••Home.Sort_&_Filter,Custom_Sort
••••••Click: options.Sort_left_to_Right....Click: OK
••••••Sort by: Row_1
••••••Sort by: Values
••••••Sort by: Smallest to largest
••••••Click: OK

Since the number series has two 1's, two 2's, etc...
blank columns will be sorted into your data.

Is that something you can work with?

Thank you Ron!

So many years I added manually x-columns, and now 2017 I found your Post from 2009.

Thank you again.
Patrick