How Can I Insert a Column into a Spreadsheet?

How Can I Insert a Column into a Spreadsheet?

  • Comments 3
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I insert a column into an Excel spreadsheet?

-- HE

SpacerHey, Scripting Guy! AnswerScript Center

Hey, HE. Let’s assume you have a spreadsheet that looks something like this:

Microsoft Excel


And, yes, it is a very nice little spreadsheet, except for one problem. As you can see, we goofed, and we forgot to insert Dataset 3; instead, we only have Datasets 1, 2, and 4. Even worse, Dataset 4 is in the wrong spot; it needs to be moved over to column D. In other words, we need to insert a new column between columns B and C. That will move Dataset 4 over to column D (where it belongs), and give us a blank column in column C where we can enter the missing information for Dataset 3.

Make sense? The only question is this: how do we insert a column in a spreadsheet?

Ah, but don’t worry, that’s what the Scripting Guys are here for. Here’s a script that writes a column header to columns A, B, and C, then inserts a new, blank column between columns B and C:

Const xlShiftToRight = -4161

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Dataset 1"
objWorksheet.Cells(1,2) = "Dataset 2"
objWorksheet.Cells(1,3) = "Dataset 4"

Set objRange = objExcel.Range("C1").EntireColumn
objRange.Insert(xlShiftToRight)

The script begins by defining a constant named xlShiftToRight and setting the value to -4161 (yes, minus 4161; go figure). To insert a column (or a row) in Excel you select an existing column or row and then move all those cells either to the right (in which case you use the constant xlShiftToRight) or down (in which case you use the constant xlShiftDown, which has a value of -4121). After defining the constant we create an instance of Excel, set the Visible property to True, then add a new workbook and a new worksheet.

Next we add a header to cells A1, B1, and C1; this sets the stage for us to actually insert the new column. And we do that using just two lines of code:

Set objRange = objExcel.Range("C1").EntireColumn
objRange.Insert(xlShiftToRight)

To begin with, we create a Range object representing all the cells in column C, the column we need to shift over. We do that by creating a range consisting of a single cell in column C (in this case, cell C1) and then using the EntireColumn property to extend this range to all the cells in column C. All we have to do then is call the Insert method, passing the constant xlShiftToRight as the sole parameter. This simply tells Excel that we want to take the specified range - column C - and move those cells over to the right. And look what happens:

Microsoft Excel


We’ve successfully inserted a new, blank column, just like we wanted to do.

And before you ask, here’s a similar that scripts that adds a new row across the top of a spreadsheet:

Const xlShiftDown = -4121

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Dataset 2A"
objWorksheet.Cells(1,2) = "Dataset 2B"
objWorksheet.Cells(1,3) = "Dataset 2C"

Set objRange = objExcel.Range("A1").EntireRow
objRange.Insert(xlShiftDown)

Cool, huh? If this whets your appetite for Microsoft Office scripting be sure and check out the Office Space column, which features new Office scripting tips and tricks every Tuesday and Thursday

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Which of the following would allow you to insert a new column in an existing spreadsheet?

    Click in the column where you want to insert; then in the Cells group on the Home tab, click the arrow on Insert. Select Insert Sheet Columns.

    With any cell in the worksheet selected (active), select the Insert option in the Cells group on the Home tab.

    Click in the column where you want to insert; then in the Table group on the Insert tab, click the Table command.

    Right click in the column where you want to insert; select Insert and then Shift Cells Right.

    4. How do you adjust the column width to fit the information typed in the cells in that column?

    From Home tab, Cells group, click arrow on Format. Then select AutoFit Columns Width

    From the Insert tab, click the Column command and choose the desired column style

    From the Page Layout tab, click the Width menu and select the desired width

    Right click on the column label, select Format cells and select the desired width

    5. Where do you go to hide and unhide rows, columns, and sheets?

    Format list in the Cells group on the Home tab

    Insert or Delete in the Cells group on the Home tab

    Cut or Copy in the Clipboard group on the Home tab

    View in Sheet Options group on Page Layout tab

    6. On what tab would you find the commands to format and edit data?

    Home tab

    Insert tab

    View tab

    Review tab

    7. What are the steps for entering a simple formula?

    Home tab, Editing group, click AutoSum button, press Enter after verifying correct cells were selected by Excel

    Use the Insert Function command on the Formulas tab. Then follow the on-screen instructions.

    Formulas tab, Formula Auditing group, Show Formulas, select appropriate formula, press Enter after verifying correct cells were selected by Excel

    Formulas tab, Calculation group, Calculate Now command

    8. What functions are immediately available from the arrow next to the AutoSum button?

    Sum, Average, Count, Max, Min

    Sum, Average, Sin, If, Max, Min

    Average, Sin, Count, If, Max, Min

    Sum, Average, Max, Min, Sin, Cos

    9. Where would you find all functions available in Excel?

    Function Library on Formulas tab

    Function Library on Functions tab

    Macros on the View tab

    Controls on the Developer tab

    10. What steps would you take to add a header or footer to a worksheet?

    On the View tab, select Page Layout view. Click in "Click to add header" or "Click to add footer" area.

    In Normal View, go to Page Layout tab, use the print titles command.

    Page Layout tab, click view under Headings.

    Right click on worksheet, select Insert, then Header/Footer

  • This is very helpfull but when you do not have empty columns at the end of your active excel sheet and use this method, this will overwrite the column on the right.

    Also, if the cells of the column you move is not formatted with the same data type as the other ones, you will have a problem with your data types.

    Does another method exists to insert an empty column between 2 columns ?

    Otherwhise you have to look for the last non-empty column of your excel sheet and move one by one until you reach the disired column.

    Maybe i'am wrong.

  • thanks