Insert Multiple Columns at once [Quick tip]

Posted on January 15th, 2013 in Excel Howtos - 25 comments

Here is a quick tip to start your week.

Ever wanted to insert a few columns in between like this?

Insert multiple columns in Excel using CTRL + select technique

Here is how to do it.

  1. Hold down control key.
  2. Select one column at a time
  3. Right click and choose Insert
  4. Done!

Bonus tip: You can use this to insert rows too!

More Quick tips.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “Insert Multiple Columns at once [Quick tip]”

  1. Hui... says:

    @Chandoo

    You should have also said that this applies to Deleting Columns and Rows as well

  2. Graham says:

    And if you have multiple sheets selected, it will work for all of those at once as well

  3. Jason says:

    deleting, inserting, hiding, unhiding, formatting, adjusting width/height, etc…. 
    i think it would be near impossible to write every article to include every related item/fact/detail.  rather, (as they used to) reading should spark ones imaginiation and ‘get the wheels turning.’ I’m not even 30 yet and it amazes me how little people are willing to think for themselves and find the answer on their own. HOWEVER, if every article was ‘all-inclusive’ i would be willing to put money on the fact that a majority of the people would be too ‘lazy’ to read in its entirety and still ask questions that have the answers right in front of them. …apparently i’ve been on a couple online hobby related forums… hahahahha[/rant[
     

  4. montrey says:

    Can’t believe I never knew this. I knew that you could insert multiple columns by using ctrl. but I never knew it would work for columns right next to each other. Cool.

  5. Marc says:

    If you prefer, instead of right click and choosing insert press Ctrl + (Ctrl PLUS the + sign on your numerical keypad).

  6. Natalie Kehr says:

    I simply select the required number of rows (or columns) by clicking in a row number and dragging.  Then a right click gives the menu which includes Insert and Delete.
    It is only necessary to use Ctrl when the rows are not adjacent to each other.
    If one wants to select a large number of adjacent rows remember that Click, followed by Shift Click is a quick way to select a large area in Excel and lots of other applications.

  7. Josue Diaz says:

    On the same page to insert multiple column at the same time, select the amount of columns you you want to insert all at once (not holding down ctrl), the same amount of columns will be insterted before your selection, but in this case all together, and not between the other columns selected

  8. Debbi Forbes says:

    I’m using 2010 and when I follow these steps, I receive an error message:
     
    That command cannot be used with selections that contain entire rows or columns, also also other cells.  Try selecting ony entire rows, columsn or groups of cells.

  9. Grant says:

    Hi Chandoo
    I think even quicker is inserting 1 column (ALT I C) then moving 1 column across, quickly press F4 and repeat. Maybe its cos I’m a keyboard junkie…

  10. sam says:

    This is basic,,may be all people know tis ..if you want to circulate excel function ..then circulate Advance Function Like IF, Advance Vlookup with SUMIF..

  11. Sangria9 says:

    Instead of “Right click and choose Insert” I use combination “Ctrl” + “Shift” + “+”.

  12. ponnusami kasi thangarajan says:

    It is simply awesome…It is you who converted me from ppt lover to excel lover at this age (75)! It is awesome…simply awesome

  13. A.SIVAKUMAR says:

    Can’t believe I never knew this. I knew that you could insert multiple columns by using ctrl. but I never knew it would work for columns right next to each other. Cool.
     
    Thank you!
    A.Sivakumar
    ISYS Softech

  14. Prem says:

    If i am doing the same procedure with my excel 2007 it error message showing. That command can not be used with selections that contain entire rows or columns, and also other cells. Try selecting only entire rows, entire columns or just groups of cells.

  15. Narasimha Rao says:

    If i am doing the same procedure with my excel 2007 it error message showing. That command can not be used with selections that contain entire rows or columns, and also other cells. Try selecting only entire rows, entire columns or just groups of cells.

  16. wintermute says:

    Have you ever try it with 20 or more columns/rows? Only macro or VBA helps…

  17. Chandra Sekhar Pamidi says:

    Hi Chandoo,
    If i want to insert one row after every row (ref. below case), Can i do that with out using Macros.
    Eg. I have a data from A2 to F22 and want to insert each blank row between 2 and 22 rows. 
    Regards,
    CP
     

    • Chris says:

      Chandra, I have had to do this before as well. I can think of two ways to do it:
      1) highlight the entire row 3 and insert a row. I like to use crlt + . Arrow down two rows press F4 to repeat the insert. Repeat until done
      2) Use a sort to add blank lines.

      First a a column to the left of your data.
      Series fill A2:A22. A quick way to do that is enter 1 in cell A2. Then ctrl drag down to A22.
      if necessary insert new rows below A22
      Series fill A23:A44. Enter 1.5 in A23 then ctrl drag down to A44.
      Resort your data based on the values in column A

      This method works well when you have lots of rows to insert.
       

  18. Mathew Tharakan says:

    Thanks, but not working in my excel 2007

  19. Yasmeen says:

    Thanks you so much it really solve more problems.
     

  20. joseph burbea says:

    i am a member and are interested in 2007 (and later) excel VBA to hyperlink  all files in a directory and its sub-directories.
    i tried several solutions given in the web but so far i was unable to get the right one because mostly they were good for 2003 excel but not for 2007 and later excel while others fail because they stop after getting the files from the directory but stopped when going to sub directories.

  21. Amitap Singh says:

    Thanks, it really solve more problems

Leave a Reply