A column is a vertical group of cells in your spreadsheet. Columns help you to organize your data and a column will usually contain the same type of data across all the cells in the column. Inserting columns for new data is a very common task in Excel. Columns can be used to add extra information to your spreadsheet with data entry or calculations. Inserting columns is easy to do in Excel and there are many ways to get it done. Follow along to find out all the ways to insert a column into your Excel data.
Insert a Column with a Right Click
The easiest and most convenient way to insert a column is using the right-click menu. All you need to do is select a column and the right-click menu will allow you to insert a column to the left of the selected column. Follow these steps to insert a column. This will insert a new column to the left of the selected column! You will notice an Insert Options button appears next to the newly inserted column. You can choose from three very useful options.
Format Same As Left will apply the same formatting as the column to the left of the new column.Format Same As Right will apply the same formatting as the column to the right of the new column.Clear Formatting will not apply any formatting to the new column.
This is a great feature if you’re inserting the same type of column, such as a currency column, as you can quickly apply the same format to the new column. 💡 Tip: Arrange similar types of columns together and use the Insert Options to quickly apply the same format to new columns. You can also insert columns by selecting a single cell. When you right-click on any single cell you will still be able to select the Insert command in the menu. This will open the Insert menu and you can choose the Entire column option and then press the OK button to insert your new column.
Insert a Column from the Home Tab
Inserting a new column can also be performed from the Excel ribbon. You might try to look for this option in the Insert tab, but you won’t be able to find it there. The Insert column command is actually found in the Home tab. Follow these steps to add a column to your data from the Home tab.
Insert a Column with a Keyboard Shortcut
If you prefer to avoid using the mouse for performing any actions in Excel, you’re in luck because there is a keyboard shortcut for inserting columns. Press Ctrl + + on your keyboard and it will insert a column to the left of your select cell or column. If only the active cell is selected when you use this keyboard combination, it will open the Insert menu and you will need to use the arrow keys to select the Entire column option and press Enter to execute. You can avoid the Insert menu by pressing Ctrl + Space to select the entire column first, and then employ the Ctrl + + shortcut to insert your column.
Insert a Column with a Click and Drag
My personal favorite and the most fun way to insert a column into your data is to click and drag an empty column to a new location in your data. Select any empty column and hover the mouse cursor over the green border and it will turn into a black cross with arrows. Hold the Shift key and then left click and drag. When you drag the empty column over, you will see a green vertical line. This indicated where the column will be placed when you release the left click. Move the column over to the location where you would like your new column and release the left mouse button. This will add the empty column to its new location!
Click and Drag to Duplicate a Column
This click and drag method can also be used to quickly duplicate a column with a slight modification. Hold Ctrl + Shift and when you hover the mouse cursor over the border it will change to a small black plus sign. Left click and drag the column to a new location and when you release a copy of the column will be inserted.
Insert a Column with the Fill Handle
When you select an entire column, you will see the fill handle at the top right of the selection. You can actually use this to insert a column. When you hover the mouse cursor over the fill handle, you will see a small black plus icon. When you press and hold the Shift key while on the fill handle, the icon will change to two bars with arrows. This indicates you can insert a column. Hold the Shift key then left click and drag the fill handle to the left. When you release, a new column will be inserted! 💡 Tip: Drag the fill handle across multiple columns to insert more than one column. You can drag the fill handle to the right to insert a column to the right or drag it left to insert a column to the left. Both directions will work! This multi-directional ability makes it more versatile than any of the previous methods.
Insert Multiple Adjacent Columns
The process of adding multiple adjacent columns is very simple. The previous methods will all allow you to add multiple columns. Select multiple adjacent columns by clicking on the first column heading then holding the Shift key and clicking on the last column. Right-click and select the Insert option and the selected number of columns will be inserted to the left.
Insert Multiple Non-Adjacent Columns
You can also select non-adjacent columns to insert. Hold the Ctrl key while you select each column then right-click and choose the Insert option.
Insert a Column in an Excel Table
Suppose you have your data inside an Excel Table. It is still possible to insert a column in your table.
Select any cell in the table.Right-click on the cell.Choose the Insert option.Select the Table Columns to the Left options from the submenu.
This will insert a new column to the left of your selected cell. 📝 Note: This will create a new column with a generic column heading name like Column1. You can type over this to rename the column.
Insert a Column at the End of an Excel Table
Since inserting a column inside a table adds the column to the left, you might think you can’t add your column to the right end of the table. This can be easily done, but not through the right-click menu. One of the great features of tables is their ability to absorb new rows or columns of data. This means all you need to do is start typing in the empty column to the right of the table and when you press Enter a new column will be created in the table.
Insert a Column with the HSTACK Function
Did you know you can insert a column with a function? Yes, it’s true. Dynamic array formulas allow you to manipulate ranges and return multiple values. The HSTACK function can be used to horizontally stack multiple ranges. So you can use it to effectively insert a column. The above formula can be used to insert a column E between columns B and C. The example shows the Salary column is inserted between the ID and Name column. The formula is entered in cell G2 but the results spill into the range G2:I12.
Insert Every Second Column with VBA
Perhaps you need to insert a new column between every existing column in your dataset. This could be a tedious task with the previous manual methods if you have a larger dataset and if you need to repeat it across many datasets. This is where you can use some VBA to automate the task for you. Go to the Developer tab and select the Visual Basic command or press Alt + F11 to open the VBA editor. Go to the Insert tab and select the Module option to create a new module. You can then copy and paste the above code into the module. This code allows you to select a range in the workbook and when you run the VBA code it will insert every second column into the selected range. This code will start at the last column in the selected range and insert a column to the right then move one column to the left and repeat until it gets to the first column.
Insert Every Second Column with Office Scripts
There is another way you can automate the column insertion. You can use Office Scripts with Excel online. This is a JavaScript based language available for the web version of Excel when you have a Microsoft 365 business plan. You will see an Automate tab and you can create a New Script to add the above code. This will do the same thing as the VBA code and insert every other column in the selected range. Select a range and press the Run button to insert your columns!
Insert a Column with Power Query
Suppose you are importing data to Excel from an external source such as a CSV file. In this scenario, you should be using power query to connect to and import data. With a power query solution, you will be able to easily insert columns during the import query. In the power query editor, any action you perform in the Add Column tab will insert a column to your data! For example, you can select the Name column and Extract the last name based on the Text After Delimiter option with the space character. You will see an M code formula is automatically generated for the query using the Table.AddColumn function. When you Close & Load the query into the Excel workbook, the new column is automatically added to your data.
Insert a Column with Power Pivot
Suppose you will be analyzing and summarizing your data. You might want to create a new dimension or metric column to help you gain more insight to your dataset. This can be done in the Power Pivot data model before loading the source data into your pivot table.
Go to the Power Pivot tab and select the Add to Data Model command to load your selected data and open the Power Pivot editor. Power Pivot makes it very easy to add a column as the data preview in the editor will always show you an empty column labeled Add Column. As the name suggests, this is where you can add a column. Double click on the heading to rename the column and you can add a formula like the above to populate the column. This example shows a formula that will extract the First name from the Name column. 📝 Note: Power Pivot formulas apply to the entire column! When you create a pivot table with the data model, this new column will be available to use as a field to slice and dice your data!
Conclusions
Inserting a column is a common task in Excel and there are many ways it can be done. Usually, you will want simple methods to add columns like the right-click menu, Home tab, or keyboard shortcuts. There are other interesting but hidden ways like the click and drag or using the fill handle. Once you discover these methods you may prefer them. All of these simple methods will also allow you to insert multiple columns as well! You can also use more advanced techniques to automate the process of inserting columns with the HSTACK function, VBA, or Office Scripts. If you’re importing data or analyzing it and need to add a column in the process then you will want to use the Power Query or Power Pivot method. How do you insert new columns into Excel? Do you use any of these methods? Let me know in the comments below!
