Saturday, October 24, 2015

5 Keyboard Shortcuts for Rows and Columns in Excel



Bottom line: Learn some of my favorite keyboard shortcuts when working with rows and columns in Excel.
Skill level: Easy
Keyboard Shortcuts for Rows and Columns in Excel
Whether you are creating a simple list of names or building a complex financial model, you probably make a lot of changes to the rows and columns in the spreadsheet.  Tasks like adding/deleting rows, adjusting column widths, and creating outline groups are very common when working with the grid.
This post contains some of my favorite shortcuts that will save you time every day.

#1 –  Select Entire Row or Column

Shift+Space is the keyboard shortcut to select an entire row.
Ctrl+Space is the keyboard shortcut to select an entire column.
Select Entire Row or Column in Excel Keyboard Shortcuts
The keyboard shortcuts by themselves don’t do much.  However, they are the starting point for performing a lot of other actions where you first need to select the entire row or column.  This includes tasks like deleting rows, grouping columns, etc.
Keyboard Shortcut to Select Rows or Columns in Excel Table
When you press the Shift+Space shortcut the first time it will select the entire row within the Table.  Press Shift+Space a second time and it will select the entire row in the worksheet.
The same works for columns.  Ctrl+Space will select the column of data in the Table.  Pressing the keyboard shortcut a second time will include the column header of the Table in the selection.  Pressing Ctrl+Space a third time will select the entire column in the worksheet.
You can select multiple rows or columns by holding Shift and pressing the Arrow Keys multiple times.
Select Multiple Columns with Shift Plus Arrow Keys

#2 – Insert or Delete Rows or Columns

There are a few ways to quickly delete rows and columns in Excel.
If you have the rows or columns selected, then the following keyboard shortcuts will quickly add or delete all selected rows or columns.
Ctrl++ (plus character) is the keyboard shortcut to insert rows or columns.  If you are using a laptop keyboard you can press Ctrl+Shift+= (equal sign).
Insert Entire Row in Excel
Ctrl+- (minus character) is the keyboard shortcut to delete rows or columns.
Delete Selected Column in Excel
So for the above shortcuts to work you will first need to select the entire row or column, which can be done with the Shift+Space or Ctrl+Space shortcuts explained in #1.
If you do not have the entire row or column selected then you will be presented with the Insert or Delete Menus after pressing Ctrl++ or Ctrl+-.
Insert or Delete Menu Appears When Entire Row or Column is Not Selected
You can then press the up or down arrow keys to make your selection from the menu and hit Enter.  For me it is easier to first select the entire row or column, then press Ctrl++ or Ctrl+-.
So, the entire keyboard shortcut to delete a column would be Ctrl+Space, Ctrl+-.  You could also use the keyboard shortcut Alt+H+D+C to delete columns and Alt+H+D+R to delete rows.  There are lots of ways to do a simple task… :-)

#3 – AutoFit Column Width

There are also a lot of different ways to AutoFit column widths.  AutoFit means that the width of the column will be adjusted to fit the contents of the cell.
You can use the mouse and double-click when you hover the cursor between columns when you see the resize column cursor.
Double-Click Resize Column Cursor to Autofit Column
The AutoFit Column Width button bases the width of the column on the cells you have selected.  In the image above I have cell A4 selected.  So the column width will be adjusted to fit the contents of A4, as shown in the results below.
AutoFit Column Width Button Resizes Column Based on Selected Cell Contents
Alt,H,O,I is the keyboard shortcut for the AutoFit Column Width button.  This is one I use a lot to get my reports looking shiny. :-)
Alt,H,O,A is the keyboard shortcut to AutoFit Row Height.  It doesn’t work exactly the same as column width, and will only adjust the row height to the tallest cell in the entire row.

#4 – Hide or Unhide Rows or Columns

There are several dedicated keyboard shortcuts to hide and unhide rows and columns.
  • Ctrl+9 to Hide Rows
  • Ctrl+0 (zero) to Hide Columns
  • Ctrl+Shift+( to Unhide Rows
  • Ctrl+Shift+) to Unhide Columns – If this doesn’t work for you try Alt,O,C,U (old Excel 2003 shortcut that still works).
The buttons are also located on the Format menu on the Home tab of the Ribbon.  You can hover over any of the items in the menu and the keyboard shortcut will display in the screentip. The trick with getting these shortcuts to work is to have the proper cells selected first.
To hide rows or columns you just need to select cells in the rows or columns you want to hide, then press the Ctrl+9 or Ctrl+Shift+( shortcut.
To unhide rows or columns you first need to select the cells that surround the rows or columns you want to unhide.  In the screenshot below I want to unhide rows 3 & 4.  I first select cell B2:B5, cells that surround or cover the hidden rows, then press Ctrl+Shift+( to unhide the rows.
Select Cells That Surround Hidden Rows or Columns Before Unhiding
The same technique works to unhide columns.

#5 – Group or Ungroup Rows or Columns

Row and Column groupings are a great way to quickly hide and unhide columns and rows.
Shift+Alt+Right Arrow is the shortcut to group rows or columns.
Shift+Alt+Left Arrow is the shortcut to ungroup.
Again, the trick here is to select the entire rows or columns you want to group/ungroup first.  Otherwise you will be presented with the Group or Ungroup menu.
Keyboard Shortcut to Ungroup Rows or Columns in Excel
Alt,A,U,C is the keyboard shortcut to remove all the row and columns groups on the sheet.  This is the same as pressing the Clear Outline button on the Ungroup menu of the Data tab on the Ribbon.
*Bonus funny: At some point when using the group/ungroup shortcuts, you will accidentally press Ctrl+Alt+Right Arrow.  This is a Windows shortcut that orientates the entire screen to the right.  I call it “neck ache view”.  To get it back to normal press Ctrl+Alt+Up Arrow.

No comments:

Post a Comment