Select an Entire Column or Row in Excel (Shortcuts)

The select entire column Excel shortcut is Ctrl+Space, and the row shortcut is Shift+Space. Click any cell in the column or row, press the keys, and the whole thing is selected, ready to format, insert, delete or hide. This guide gives the Windows and Mac keys, shows how to select several columns at once, and explains when you want only the data instead of the full column.

Shortcuts to select columns, rows and the whole sheet#

Here are the shortcuts you need. Start by clicking a cell in the column or row you want.

With the mouse, click the column letter at the top of the grid or the row number on the left. That always works, on any computer.

How to select an entire column with Ctrl+Space#

1. Click any cell in the column, for example C5.
2. Press Ctrl+Space.
3. All of column C is selected, from C1 to C1048576.

On a Mac, Excel lists Control+Space for this. But macOS often uses Control+Space to switch the keyboard language, so the shortcut may never reach Excel. If that happens, click the column letter, or change the shortcut in macOS System Settings under Keyboard. Some Windows language settings also use Ctrl+Space to switch input methods.

How to select an entire row with Shift+Space#

1. Click any cell in the row, for example C5.
2. Press Shift+Space.
3. All of row 5 is selected, from A5 to XFD5.

Shift+Space works the same on Windows and Mac. One catch: if you are typing in a cell, Shift+Space types a space instead. Press Enter or Esc to leave the cell first.

How to select several columns or rows#

Select the first column or row, then extend the selection with Shift+Arrow:

• Columns C to E: click C1, press Ctrl+Space, then Shift+Right twice.
• Rows 4 to 8: click A4, press Shift+Space, then Shift+Down four times.

With the mouse, click the first column letter, hold Shift and click the last one. To pick columns that are not next to each other, hold Ctrl (⌘ on a Mac) and click each letter. For more ways to build a selection, read how to select ranges fast in Excel.

Entire column vs data only: which do you need?#

An entire column has over a million cells. That is right for formatting, inserting, deleting and hiding, but wrong for copying data or creating a chart.

To select only the filled cells, click the first data cell and press Ctrl+Shift+Down (⌘⇧Down on a Mac). Excel stops at the last filled cell before a blank. If the column has gaps, press it again to carry on.

Inside an Excel Table, Ctrl+Space works differently. The first press selects the data in that table column only. Press it again to widen the selection to the header and then to the whole sheet column. This makes tables safer to work with.

How to check what you selected#

Look at the status bar at the bottom of the window after you select. It shows the Count of filled cells, and the Sum and Average when the cells hold numbers. If you expected 18 orders and the Count says 19, the header is included. If it says 12, the selection stopped at a gap. The Name Box also shows the size while you drag, such as 5R x 3C for five rows and three columns.

What to do after you select a column or row#

Selecting a whole column or row is usually the first step of a bigger task:

The full method for inserting and deleting is in how to insert and delete rows and columns. For filling formulas, see Fill Down and Fill Right, and for filters, the Excel filter shortcut guide. If you delete the wrong column, undo it straight away with Ctrl+Z (⌘Z). The guide to editing cells with undo and redo has more.

Whole column and row references in formulas#

Formulas can refer to an entire column or row too. B:B means all of column B, and 2:2 means all of row 2. Whole-column references are handy when a list keeps growing:

=SUM(B:B)

With monthly sales of 4,200, 3,900, 5,100, 4,650 and 5,300 in B2:B6, the result is 23,150. Add June in B7 and the total updates by itself. SUM skips the text header in B1.

Two cautions. Don't put this formula inside column B, or it will include itself and cause a circular reference. And on very large sheets, many whole-column formulas can slow Excel down, so a fixed range or an Excel Table is often the better choice.

You can also select by typing: click the Name Box left of the formula bar, type B:B or 2:5 and press Enter.

Practise it#

Shortcuts only stick when you use them. Open the select ranges with Shift lesson to practise Shift+Arrow, Ctrl+Shift+Arrow, Ctrl+A and whole-row and whole-column selection in a live spreadsheet. Each step is checked instantly, so you'll know the keys are in your fingers. Try the whole-column SUM exercise on this page first.