Columns in Excel (Select, Insert, Delete, Hide, Unhide, Group, Ungroup, Move, Modify Size, Etc.)

Get FREE Advanced Excel Exercises with Solutions!

Columns are the vertical alignment of cells in an Excel worksheet. A single worksheet contains a total of 16,384 columns. Each column is addressed at the top by an alphabet starting from A and ending in XFD.

In this tutorial, you will learn everything there is to know about columns in Excel.

overview of columns in Excel

In this blog post, you will learn how to

  • Select single or multiple adjacent columns
  • Select non-adjacent columns
  • Select all columns to the left or right of a column
  • Insert columns
  • Insert a new column to a table
  • Delete columns
  • Group/ungroup columns
  • Make the column name a number
  • Modify column width
  • Move/swap columns
  • Hide and unhide columns
  • Freeze columns
  • Refer to a column inside a formula
  • Count columns in a specific range

Note: We have used Excel for Microsoft 365 to prepare this tutorial.


Selecting Columns
Selecting Non-Adjacent Columns
Selecting All Columns Left or Right of a Column
How to Insert Columns
How to Insert New Column into a Table
How to Delete Columns
How to Group/Ungroup Columns
What Is the Last Column
Make Column Name as a Number
Modify Column Width
Move/Swap Columns
Hide and Unhide Columns
Freeze Columns
Refer to a Column Inside Formula
Count Columns in a Specific Range


How to Select Columns in Excel?

You can select a column by clicking the address name of that column from the top row. Selecting a column is necessary for referencing it and performing various formatting to the column.

1. By Clicking on Column Name

i. Select Single Column

To select a single column click on the name of the column at the top of the column. This will highlight the column indicating that the column has been selected.

select single column clicking column name


ii. Select Multiple Adjacent Columns in Excel

Click on the name of the first column you want to select and drag the mouse to the name of the last column without releasing the mouse. This will select all the adjacent columns.

select multiple columns clicking on column names


2. Using Keyboard Shortcut

i. Selecting Single Column

Another way of selecting a single column is by using the keyboard shortcut. Select any cell in the column that you want to select and press Ctrl + Space. This will highlight the column.

select single column using keyboard shortcut


ii. Selecting Multiple Columns
Click on the name of the first column and then click on the name of the last column while pressing Shift. This will highlight all the adjacent columns.

select multiple columns pressing shift

You can also select adjacent columns using a keyboard shortcut. Select adjacent cells of different columns in a row and press Ctrl + Space to select all the columns.

select multiple columns using ctrl and space


3. Using Name Box

i. Selecting Single Column

Click on the name box and enter the reference of the desired columns to select the columns. Here, you will get the detailed steps:

  • Click on the name box on the top left of the worksheet.
  • Input the column references for the columns you wish to select and press Enter.
  • For example, I have used input reference C:C to select column C.

select single column using name box


ii. Selecting Multiple Columns

  • For selecting the range of columns C to E, input C:E in the Name Box.

select multiple column using name box

  • To select non-adjacent Columns C and E, input C:C, E:E in the Name Box.

select multiple non adjacent columns in excel using name box


How to Select Non-Adjacent Columns in Excel?

To select multiple non-adjacent columns, hold the Ctrl key and click on the header names of the columns you want to select.

select non adjacent columns using ctrl

If you have mistakenly selected a column, then click on the header name of that column. This will de-select that column while keeping your selection intact.

de select column


How to Select All the Columns to the Left or Right of a Column in Excel?

i. Select All Columns Left

To select all the columns left of a column click on the header name of a column and press Ctrl + Shift + Left Arrow.

select all columns left of a column


ii. Select All Columns Right

Press Ctrl + Shift + Right Arrow to select all the columns right of a column after clicking the header name of the column.

select all columns right of a column


How to Insert New Columns in Excel?

Right-click on the header name of any column and click on Insert to insert a new column. You can use the Insert options, the keyboard shortcut for inserting new columns in both adjacent and non-adjacent positions.

1. Using the Insert Command from Right Click Menu

You can insert both adjacent and non-adjacent columns using the insert option from the right-click menu.

i. Insert Adjacent Columns

Select the number of adjacent columns you want to add and choose Insert from the right-click menu keeping the cursor on the selection.

  • Select the number of adjacent columns you want to add. If you want to add 2 columns, select 2 columns.
  • Right-click on the mouse keeping the cursor on the selected columns.
  • Select Insert from the menu and This will insert the desired number of columns.

insert adjacent columns using right-click menu

Note: Excel usually inserts a new column on the left of the selected columns.


ii. Insert Non-Adjacent Columns

Select the non-adjacent columns and choose Insert from the right-click menu keeping the cursor on the selection.

  • Click the columns while pressing the Ctrl key.
  • Right-click on the mouse keeping the cursor on the selected columns.
  • Select Insert from the menu.
  • This will insert a column to the left of every selected column.

insert non adjacent columns from right-click menu


2. Using Keyboard Shortcut

i. Inserting Adjacent Columns

Select the desired number of adjacent columns and press Ctrl + Shift + Plus(+) to insert columns that are equal to the number of selected columns.

insert columns using keyboard shortcut


2.2 Inserting Non-Adjacent Columns

Select the non-adjacent columns and press Ctrl + Shift + Plus(+) to insert a column to the left of every selected column.

insert non adjacent columns using keyboard shortcut


3. Using the Fill Handle Tool with SHIFT Key

Change the fill handle icon by pressing Shift and dragging this new icon to insert a column to the right.

  • Select the column to the right where you wish to insert new column(s).
  • Upon selecting the column, you’ll see that a small plus(+) sign appears at the bottom of the header name of the column. This is known as the Fill handle.

using fill handle tool with shift key to insert column

  • Hover your cursor above the fill handle, then press and hold the Shift key on your keyboard. As a result, the cursor will transform into two bars with arrows. This signifies that you can insert a column.
  • Drag this icon while pressing the Shift key to the right to insert as many columns as you want.

drag fill handle holding shift to insert column


4. Using Insert Sheet Columns Command from Insert Drop Down of Cells Group of Command Under Home Tab

The top Ribbon in Excel helps us perform many tasks easily including inserting a new column.

  • Choose the column next to which you want to insert a new column.
  • Go to Home > Cells group > Insert > Insert Sheet Columns.

use insert sheet columns to insert columns

  • This will insert a new column.

5. Using a VBA Code

You can use a VBA code to insert a column to the left of a column you choose. Follow the steps below to achieve this:

  • Go to the Developer tab > Visual Basic.

select visual basic in developer tab

  • Then, in the Visual Basic window, go to Insert > Module to insert a new module.

insert a new module

  • Type the following code and click on Run.
Sub InsertColumnToLeft()
    Dim Number As Long, Start As Long, Finish As Long
    Start = Application.Selection.Cells(1, 1).Column
    Finish = Start + Application.Selection.Columns.Count - 1
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Number = Finish To Start Step -1
        ActiveSheet.Cells(1, Number).EntireColumn.Insert
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

insert code and click on run

  • This will insert a column to the left of the column you choose.

column inserted using vba


How to Delete Columns in Excel?

Sometimes you may need to delete a column or multiple columns. This can be done easily by using the right-click menu.

  • Select the column or the columns you want to delete.
  • Right-click on the selection.
  • Click on Delete. The column(s) is now deleted.

delete column in excel


How to Insert New Columns to Excel Table?

It is very easy to insert a new column if your data is formatted in an Excel table. Right-click selecting any cell in the table will give you the option to insert a new column.

1. Inserting Column to the Left

  • Right-click a cell in the column next to which you want to insert a column.
  • Select Table Columns to the Left from the Insert option in the right-click menu.

insert column to left in table

This option is available for all the columns in the Excel table. The newly inserted column will be termed Column1. You can change it later according to your preference.


2. Inserting Column to Both Left and Right

You can only insert a column on the right of a column in an Excel table if you select any cell in the rightmost column.

Right-click a cell in the rightmost column will give you two options to insert a column.

  • Table Columns to the Left– inserts a new column left to the rightmost column.
  • Table Columns to the Right– inserts a new column right to the rightmost column.

Choose the option you need and the column will be inserted.

insert column to both left and right in excel table

You can use this option to expand your Excel table while keeping the formatting intact.


Formatting Options Appearing After Inserting New Column

After inserting a column, you will notice the Insert Options button appear at the top right of the inserted column. You can use this button to modify the formatting of the newly inserted column.

The button provides three formatting options.

formatting option after inserting new columnsi. Format Same As Left – This is the default option. This means that the newly inserted column will have the same formatting as the column immediately left to it.

ii. Format Same As Right – Choosing this option will make the inserted column the same formatting as the column immediately right to it.

iii. Clear Formatting – This removes all the formatting options from the newly inserted column. This means the column will have the default font size, font color, fill color, border, etc.


How to Group/Ungroup Columns in Excel?

You can use the Outline option or keyboard shortcut to group/ungroup columns in Excel easily.

1. Group/Ungroup Columns from the Data Tab

Select the columns you want to group and go to Data > Outline group > Group. This will group the columns.

select columns to group

This will group the column(s). A minus(-) sign appears at the top of the selected column(s) indicating that the data in the column(s) is grouped.

grouped columns

To ungroup the columns, select the columns and go to Data > Outline group > Ungroup. The columns are now ungrouped.

ungroup columns


2. Keyboard Shortcut to Group/Ungroup Columns

Select the columns you want to group and press Alt + Shift + Right Arrow(→).

group columns using keyboard shortcut

To ungroup the columns, press Alt + Shift + Left Arrow(←) simultaneously on your keyboard.

ungroup columns using keyboard shortcut


What Is the Last Column in Excel?

The last column is addressed as XFD in an Excel worksheet. There are a total of 16,384 columns in a single worksheet. You cannot add or delete any column as this number is fixed. If you want to go to the last column in Excel, select a blank column and press Ctrl+Right Arrow on your keyboard.

last column in excel


How to Make Column Name as a Number?

Select the R1C1 reference style from the Excel Options menu to make the column name a number.

  • Go to File > Options.

select options from file menu

  • Go to the Formulas tab in the Excel Options window and check the R1C1 reference style under the Working with formulas Click on OK.

check reference style box

  • The column names are shown as numbers.

changed column names


How to Modify Column Width in Excel?

You can modify the column width by dragging the plus icon with arrows on two sides.

1. Manually Modify the Column Width

Select a column and drag the modify icon left or right to modify the column width.

  • Select a column whose width you want to modify.
  • Once you hover over the border of the header name of the column, you will see a plus icon with arrows left and right.
  • Click and drag this icon left(to decrease width) or right(to increase width) to modify the width of the column according to your needs.

manually modify single column width

  • If you want to modify the width of multiple columns, just select multiple columns and drag the icon left or right to modify the width of multiple columns at once.

manually modify multiple columns width


2. Autofit Column Width with Double Click in the Column Border

Hover the mouse over the column header of the column whose width you want to modify to activate the plus icon with arrows left and right. Double-click this icon will autofit the width of the selected column.

autofit column width


How to Move/Swap Columns in Excel?

Applying the Cut command from the Home tab or the keyboard or the drag and drop method will help you move a column in Excel.

1. Using Cut Command

Apply the cut command from the Home tab or by using Ctrl + X to move a column.

  • Select the column(s) you want to move from its place.
  • Go to Home > Clipboard group > Cut. Or you can use the keyboard shortcut Ctrl + X to cut the selected column.

cut column

  • Select the column header located to the right of the column where you wish to move it. For example, if you want to move the column between columns B and C, then select column C.
  • Go to Home > Insert > Insert Cut Cells. This will move the column to the desired position.

insert cut column


2. Using Drag and Drop Commands

Press the Shift key while holding the four-sided arrow icon to move the column by drag and drop.

  • Select the column that you want to move.
  • Hover the cursor over the column until it shows the four-sided arrow icon.
  • Press Shift and drag the column to your desired location.
  • Let go of the column and the column is moved to its new position.

drag and drop to move column

Note: You can use this method for moving multiple columns too.

How to Hide and Unhide Columns in Excel?

Use the right-click menu while selecting a column to hide or unhide it. I am showing the detailed steps below to hide or unhide columns in Excel:

i. How to Hide a Column in Excel

Select Hide from the right-click menu to hide a column.

  • Select the column(s) you want to hide.
  • Right-click the mouse while keeping the selection.
  • Select Hide from the menu. A green line in place of the column(s) indicates that the column is hidden.

hide a column


ii. How to Unhide a Column in Excel

Select Unhide from the right-click menu by selecting columns of both sides of the hidden column to unhide the column.

  • To unhide the hidden column, select the columns on both sides of the hidden column.
  • Right-click the mouse while keeping the selection.
  • Select Unhide from the menu. This will unhide the column.

unhide a column


How to Freeze Columns in Excel?

Locking the positions of columns in Excel can be done from the Freeze Pane menu in the View tab. Locked columns are always visible while scrolling from left to right and vice versa.

1. Locking the First Column Only

Go to View > Window group >  Lock First Column to lock the first column. This locks the first column.

lock first column in excel


2. Locking Multiple Columns

Select columns to lock and select Freeze Panes from the View tab.

  • Select the column after the range of columns that you want to lock and go to View > Window group > Freeze Panes. This means that if you want to freeze columns A, and B, you must select column C.
  • This locks the columns.

lock multiple columns

Read More: Move Columns in Excel


How to Refer to a Column Inside Formula in Excel?

If you want to refer to a column, you have to use the column’s name by which the column is addressed in Excel.

1. Referring to a Single Column

To refer to column D, you have to use the reference D:D. You can use this reference in calculations.

For example, we show how to use the reference of column E within a formula in the screenshot below:

=SUM(E:E)

refer to a single column


2. Referring to Multiple Adjacent Columns

There are two ways to refer to multiple adjacent columns. “E:F” and “E:E,F:F” refer to the same columns.

We have referred to columns E and F in a formula.

=SUM(E:F)

refer to multiple adjacent columns

Similarly, we can use the reference of columns E and F in the following way:

=SUM(E:E,F:F)

multiple adjacent columns reference


3. Referring to Multiple Non-Adjacent Columns

To refer to multiple columns that are non-adjacent you can use the following referencing method.

E:E,G:G

Here, we have referred to columns E and G which are non-adjacent.

In the following image, we have shown how to refer to non-adjacent columns E and G within a formula.

refer multiple non adjacent columns

Read More: Insert Column in Excel


How to Count Columns in a Specific Range?

The Excel COLUMNS function is dedicated to returning the total number of columns in a specific range. The COLUMNS function has only one argument and the syntax of the COLUMNS function is as follows:

=COLUMNS(array)

columns function

  • Suppose you want to count the total columns in the range B5:E15 using the COLUMNS For this, go to the desired cell where you want to show the result and insert the following formula.
=COLUMNS(B5:E15)

count columns using columns function

  • This formula returns a result of 4, indicating that the input range has 4 columns in it.

Note: The COLUMNS function’s argument cannot refer to multiple references.

Read More: Select Column in Excel


Important Notes about Columns

  • The number of columns in an Excel worksheet is fixed. The total number of columns in a worksheet is 16,384.
  • Columns are addressed by alphabets starting from A to XFD in Excel. You can change the column address from alphabets to numbers.
  • Inserting does not create a new column.
  • The COLUMNS function returns the total number of columns in a range.

Download Practice Workbook


In this article, you learned how to use columns in Excel and how to apply different attributes to columns. You learned the process of how to refer, select, hide, and lock a column. You also learned to change the default column names from alphabets to numbers. We also showed you how to use the COLUMNS function in Excel.


Columns in Excel: Knowledge Hub


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Maruf Niaz
Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo