What Is Add Columns In Excel?
Add column in Excel is an option to insert one or more adjacent or discontinuous columns in a spreadsheet. And while column addition in Excel is feasible, we can hide, unhide, move, or delete any number of columns to suit the requirements.
Users can add columns in Excel to present the data clearly and with more logic. And the other ways to manage columns help manipulate data in a sheet in a more organized manner.
For example, the image below shows a dataset containing two columns.
Consider we must add column in Excel between columns B and C. And then update the employees’ designation details in the newly added column.
In such a scenario, we do not have to apply any formula to add column in Excel. Instead, we can use the Insert Sheet Columns option under the Insert option in the Home tab to fulfill the requirement.
We can click the column C header to select the entire column. Otherwise, we can choose a cell in column C. And then, we use the command to add column in Excel, which is to click the Home tab → Insert option → Insert Sheet Columns option.
Excel will add a new column before the chosen column or cell. And we will see an empty column C inserted between the original columns B and C.
Finally, we can update the required employees’ designation details in the newly added column, C.
Table of contents
Key Takeaways
- Add column in Excel enables userts to insert one or multiple columns, which can be adjacent or non-adjacent.
- And if we add a column before the first column in the dataset, the new column will not have any format. But if we add a column before a column within the dataset range, the newly added column will have the same format as the column on its left.
- Users can add columns in a worksheet to increase the clarity and logic in the given dataset and perform the required calculations in a well-organized way.
- We can hide, unhide, and move columns to manage them in a worksheet.
How To Add/Insert Columns In Excel?
The steps to add or insert columns in Excel are:
- Click the column header to choose the entire column or a cell in the column, before which we must add a column.
- Choose the Home tab → Insert drop-down → Insert Sheet Columns option.
And if we must add multiple contiguous columns on the left of a column. Then, choose the same count of columns as those required to insert, starting from the column before which we need to add columns. And then, iterate step 2 to complete the action.
But if we must add multiple non-adjacent columns across a worksheet. Then, click the first column header, press Ctrl and click the column headers to choose the entire columns or a cell in each column, before which we must add a column. And then, iterate step 2 to complete the action.
However, we can’t add column in Excel if the sheet or workbook is protected, as the above option and the other option to add columns in Excel will be greyed out.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
The following illustrations show the different aspects of adding and managing columns in Excel.
Example #1 – Add Columns In Excel
The below image shows a dataset containing the project scores of ten students in various subjects.
And the requirement is to add a column to insert their Physics project score data before column C.
Then, as there is no formula to add column in Excel, we can use the Insert Sheet Columns option from the Home tab to achieve the desired outcome.
- Step 1: Click the column header C, or choose a cell in column C and press Ctrl + Space, to choose the entire column C. Otherwise, choose a column C cell.
- Step 2: Use the command to add column in Excel, Home → Insert → Insert Sheet Columns.
We will immediately see an empty column inserted before the chosen column as the new column C. And the column previously containing the Biology project score data shifts to the right and becomes the new column D, with all the subsequent columns shifting to the right.
- Step 3: Update the empty column C with the required Physics project score data.
Also, we can use steps 1 to 3 to add column in Excel table, as the process remains the same even when the given dataset is in an Excel table.
Furthermore, if the requirement is to insert two columns before column C, we must select columns C and D entirely. While pressing the mouse’s left button, drag the cursor across columns C and D to choose the two columns.
And then, use the Insert Sheet Columns option to insert two columns before column C, as explained in Step 2.
But if we click the column C header, press Ctrl and the column D header, and use the above Insert option. Then, we will see a column added before each original column, C and D.
Next, consider if we need to insert a column before column B. And the other before column D.
Then, we can click on column header B, and while pressing and holding Ctrl, click on column header D to select the entire columns. Otherwise, we can click on a cell in column B, and while pressing and holding Ctrl, click on a cell in column D to select the cells.
And then, use the Insert Sheet Columns option to insert one column before column B and the other before column D, as explained in Step 2.
Example #2 – Alternate Method To Insert Columns In Excel
The image below shows a dataset containing the prices of top US stocks from 2020-22.
And the requirement is to add a column before column E to insert 2019 stock price details. Then, we can use the alternate method to add a column in Excel, which is the Insert option from the contextual menu.
- Step 1: Right-click the column header E to choose the entire column E and open the contextual menu. And click the Insert option in the contextual menu to select it.
[Alternatively, we can right-click a cell in column E to open the contextual menu. And then, click on the Insert option in the contextual menu to select it.
The Insert window will open.
And we must choose the Entire column option and click OK in the Insert window to insert a column in the desired location]
The above step will add an empty column before the chosen column.
- Step 2: Update the 2019 stock price data in the newly added empty column.
Furthermore, the steps for adding contiguous and non-adjacent columns will remain the same as in the previous section. However, applying the Insert option will be the same as in Step 1 of this section.
Next, let us see the steps to add column in Excel table using the alternate method.
Continuing the above example, select the cell range D2:H8 and choose the Insert tab à Table option or press Ctrl + T to open the Create Table window.
Ensure the specified cell range is correct, and the table headers option is checked in the Create Table.
Clicking OK in the Create Table window will result in the following Excel table.
Consider the requirement to add a column before column E. Then, we can right-click column header E to select the entire column E and open the context menu. And then, choose Insert from the contextual menu.
Alternatively, right-click a cell in column E and choose Insert → Table Columns to the Left from the context menu.
The above step will insert an empty column, Column1, at the required position.
On the other hand, if we must add a column before the last column in the Excel Table. Then, we can right-click a cell in the last column and click the Insert option in the contextual menu. But, in this case, we will see an additional option, allowing us to add a column to the right of the last column.
And in the above scenario, the column heading gets updated automatically based on the previous column’s heading.
Example #3 – Hide And Unhide Columns In Excel
The table below lists fruits, their grades, and order details.
If the requirement is to hide columns C and D. Then, the steps are as follows:
- Step 1: Press and hold the mouse’s left button and drag the cursor over columns C and D headers to choose the entire columns. Otherwise, click the column C header, press Ctrl and click the column D header to select the entire columns. And then, choose Home → Format → Hide & Unhide → Hide Columns.
[Alternatively, we can choose a cell in column C, press Ctrl, and select a cell in column D. Then choose Home → Format → Hide & Unhide → Hide Columns.
Otherwise, press and hold the mouse’s left button and drag the cursor over columns C and D headers to choose the entire columns. And then, right-click to select Hide from the context menu.
Further, we can also choose cells in columns C and D or select the whole columns C and D and press Ctrl + 0 to hide the two columns]
Next, consider we must unhide columns C and D. Then the steps are as follows:
- Step 1: While pressing and holding the mouse’s left button, drag the mouse cursor over columns B and E headers to select the entire range.
And then, choose Home → Format → Hide & Unhide → Unhide Columns.
[Alternatively, while pressing and holding the mouse’s left button, drag the mouse cursor over columns B and E headers to select the entire column range. And then, right-click over the chosen columns and choose Unhide from the contextual menu.
Otherwise, we can choose the column range, which includes the hidden columns, and press Alt and H, O, U, and L, one at a time.
The other shortcut to unhide columns in Excel is Ctrl + Shift + 0. However, it works for a non-numeric keyboard]
The above action will unhide the concealed columns.
The above steps will also work for hiding and unhiding non-adjacent columns.
Example #4 – Move Columns In Excel
The table below lists tech companies and their details.
And the requirement is to move columns F and G and place them before the current column E. Then, the steps are as follows:
- Step 1: Press and hold the mouse’s left button and drag the cursor across the column F and G headers to select the entire columns. And then, choose Home → Cut.
[Alternatively, choose the entire columns F and G and right-click on the chosen column range to select Cut from the context menu.
Otherwise, after choosing the entire columns, F and G, press Ctrl + X to cut the columns]
The cut columns will appear as depicted below.
- Step 2: Click the column E header to choose the entire column and select Home → Insert → Insert Cut Cells.
Alternatively, right-click the column E header to choose Insert Cut Cells from the contextual menu.
The above steps will move the required columns to the desired location.
However, Excel does not allow us to select multiple column ranges to move simultaneously. The column range must be single or continuous to move multiple columns simultaneously.
Shortcuts To Insert Columns In Excel
The shortcuts to insert columns in Excel are as follows:
- Click the column header to choose the entire column or a cell in the specific column, before which we must add a column, and press Alt + I + C.
- Click the column header to choose the entire column, before which we must insert a column and press Ctrl + Shift + +.
- Click on a cell in the column before which we must add a column, and press Ctrl + Shift + +. And then, press C and Enter.
However, we can’t add column in Excel using the abovementioned shortcuts if the worksheet is password-protected.
Important Things To Note
- Ensure to choose the entire column or a cell in the column on the right side of the location where we wish to add column in Excel.
- If we wish to choose the cells in the same count of columns as we must insert and use the Insert option to add adjacent columns in a sheet. Then, press the mouse’s left button and select the cells in the required columns. Otherwise, when we use the Ctrl key to select cells in the required columns, a column will be added before each column containing a chosen cell.
- Ensure the sheet where we must add columns is not password-protected. Otherwise, the options and shortcuts to add columns will be greyed out and disabled.
Frequently Asked Questions (FAQs)
We can add column A and B in Excel using the Insert Sheet Columns option in the Home tab or the Insert option in the contextual menu.
For example, the table below lists employees, their company and contact details.
And the requirement is to add two columns before column A to update the car park slot and employee ID data. Then, we can insert two empty columns before column A using the abovementioned Excel options. And they will become the new columns A and B.
• Step 1: Press and hold the mouse’s left button and drag the cursor over the column A and B headers to select the entire two columns.
And then, choose Home → Insert → Insert Sheet Columns.
Otherwise, press and hold the mouse’s left button and drag the cursor over the column A and B headers to select the entire two columns. And then, right-click on the chosen columns to select Insert from the contextual menu.
The above step will insert two empty columns, A and B, without any format.
• Step 2: Update the data in the two columns and format them according to the requirements to achieve the following outcome.
We can add columns in Excel without affecting formula by using the following Excel functions in the specified formula:
• INDIRECT()
• INDEX()
Both functions accept cell ranges as input.
So, if we provide the required cell range within these functions as an input in the formula and then add columns in the specified cell range. Then, the new cell range updates automatically in the formula. And we can see the revised formula output in the target cell based on the data in the newly inserted columns.
We can add column in Excel Mac using the following steps:
1) Click the header of the column before which we need to add a column.
2) Press down CONTROL and click the chosen column. And then, choose Insert in the pop-up menu.
And when the requirement is to add multiple columns, we must choose the same count of columns as those we need to insert. Next, we must follow step 2, mentioned above, but by clicking the chosen columns.
Download Template
This article must be helpful to understand the Add Column In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Add Column In Excel. Here we learn how to add/insert, hide, unhide & move columns, with examples and downloadable excel template. You can learn more from the following articles –
Leave a Reply