by This post looks at how to add up new columns in Excel. Read on to learn shortcuts to insert one or more columns, including nonadjacent ones. Grab and share a special VBA macro to automate adding every other column.
Searching for a good way to insert new columns in your Excel table, you are likely to find lots of different tips and tricks. In this article I hoped to collect the fastest and the most effective ways to add up one or multiple adjoining or non-adjacent columns.
When your report in Excel is almost ready but you understand that it is missing a column to enter important details, grab the time-efficient tricks below. From insert column shortcuts to adding every other column, click the correct link to navigate straight to the point.
Insert column shortcut
If your task is to quickly insert one column, these steps are by far the quickest and simplest.
1. Click on the letter button of the column immediately to the right of where you want to insert the new column.
Tip. You can also pick the entire column by selecting any cell and pressing the Ctrl + Space shortcut.
2. Now just press Ctrl + Shift + + (plus on the main keyboard).
Tip. If you are not really into shortcuts, you can right-click on the selected column and pick the Insert option from the menu list.
It really takes just two simple steps to insert a new row in Excel. Read on to see how to add up multiple empty columns to your list.
Tip. More helpful keyboard shortcuts can be found in 30 most useful Excel keyboard shortcuts.
Insert multiple new columns in Excel
You may need to add up more than one new column to your worksheet. It doesn't mean you have to select the columns one by one and press the insert column shortcut in Excel each time. Luckily it's possible to paste several empty columns in one go.
1. Highlight as many columns as there are new columns you want to get by selecting the column buttons. The new columns will appear immediately to the left.
Tip. You can do the same if you select several adjacent cells in one row and press Ctrl + Space.
2. Press Ctrl + Shift+ + (plus on main keyboard) to see several new columns inserted.
Tip. Press F4 to repeat the last action or Ctrl + Y to insert new columns.
This is how you can effortlessly add several new columns to your table in Excel. If you need to add multiple non-adjacent columns, see the steps below.
Add up multiple non-adjacent columns
Excel allows selecting multiple non-adjoining columns and use the insert column shortcut to get new columns appear to their left.
1. Select several non-adjacent columns by clicking on their letter buttons and keeping the Ctrl key pressed. The newly inserted columns will appear to the left.
2. Press Ctrl + Shift+ + (plus on main keyboard) to see several new columns inserted en masse.
Add a column to a list formatted as Excel Table
If your spreadsheet is formatted as Excel Table you can you can select the option Insert Table Columns to the Right if it's the last column. You can also pick the option Insert Table Columns to the Left for any column in your table.
1. To insert a column, you need to select the necessary one and right click on it.
2. Then pick Insert -> Table Columns to the Right for the last column or Table Columns to the Left.
The new column will be named Column1 by default.
A special VBA macro to insert every other column
Many Excel users try to save as much time as possible by automating frequent spreadsheet tasks. So, I couldn't leave this post without a macro. Grab this simple piece of code if you need to move the columns apart.
Hope these tips will help you in your work with spreadsheets. If you often work with Excel on the level of rows and columns, have a look at the related posts linked below, which can simplify some tasks for you. I always welcome your comments and questions. Be happy and excel in Excel!
26 comments
Excel: I do not want to insert columns after typing, but to type the pieces of text into the columns in the first place, so that they will not move about when I start typing another piece.
I added did the insert column to the left for my cells that had percentages in them. It automatically does the table with a yellow bar. I cannot figure out how to change the color of that bar. Can someone help me with that? Thanks!
Hello!
If you are using an Excel table, then try these guidelines: Excel table styles and formatting: how to apply, change and remove.
I hope it’ll be helpful.
excel table - I want to insert a column between L & M (to the left) as well as a column at the end (to the right). I am unable to do either - the message is that it would move cells in a table on my worksheet.
If I select column M - right click = insert is grayed out
If I select a cell - choose - insert column I get the above message
Insert Table columns to the left is always grayed out.
What is going on? I thought we could do this before.
Any help would be appreciated
Hello!
Unfortunately, I haven't been able to reproduce this problem myself. Maybe I misunderstood something. Explain in more detail.
Thank you for replying. I've tried everything.
It appears that I can only insert columns in my raw data. Once I define the table the columns can't be inserted. Even deleted formulas but still unable to insert.
This would be a good place for a warning with a Yes / No option rather than not allow it at all.
Cindy
First option does not work :/
Hi, how to code the colFinish to select the last column of my selection instead of excel's last column?
I would like to know how to freeze more than just the upper most row? Sometimes my tables have as many as 3 rows of "headers" or rows above where data starts.
Hi!
You can find the detailed instructions in this example: How to freeze multiple rows in Excel.
Hello!
Please check out this article to learn how to lock multiple Excel rows.
Hope this is what you need.
Thank you for sharing this info. A real time saver! Excel should let us insert a number for # of columns, it just seems like such an obvious thing they overlooked. But thanx for the great tip! How in the world did you ever figure THAT out?
I've seen no answers on how to insert a column to the right. Excel used to have the option to choose insert column right or left, but now you can only insert on the left, which is quite annoying.
Hi,
Unfortunately, now Excel can only insert a column on the left.
Sir, while inserting a column in a working page of a worksheet. If insert a column is done the last required column moves to the next page. What type of command to be given to retain the last column and insert a new column in same page of a working sheet.
Thank you. This solved my problem.
This worked Perfectly! Thanks for posting this.
I have 2500 Data, and I want to Add one row after 10 rows, Can you tell me what should I do in this condition.
Dear Author, This is a great article, but in the Spirit of clarity, please consider the following edit.
Change from this...
1. To insert a column, you need to select the necessary one and right click on it.
...to this...
1. To insert a column, you need to select the necessary one [SELECTING JUST THE COLUMN HEADER CELL] and right click on it.
...and that will work for Excel 2013, because if one selects the whole column itself, then the "insert to the left/right" option does NOT appear, so one has to select JUST the header cell.
HTH.
Thanks and God bless you.
-- Mark Kamoski.
Your tip to select ONLY the column header is exactly what I needed! Thank you!
I am trying to insert the data from a specific column from a table on one sheet (sheet 2) when I select a drop down item on sheet 1 (drop down is header of the column on sheet 2)on another
I want to give name of Inserted columns please suggest how it will be done by VBA for whole sheet.
I am trying to insert a column to the right of the last column in my table, but when I right click on it all it shows is the "insert" tab without any option to the add the column to the left or right. It simply automatically adds the column to the left. How to add a column to the right of the last column in a table?
Have you found an answer to this yet? I've had no luck, and it is extremely frustrating.
I'm trying to insert a large number (366) of sequential rows between two exiting rows in Excel 2016. None of the options in your add-ons do this except to insert every x number of rows. I had a look at the VBA macro (scares the crap out of me) and was wondering if by removing the +1 parameter it would stop the row skipping option. Advice please
no, it has something to do with the colstep (currenlty set to 2, set it to 1)
you do have to define a new counting method