ThisCalculator

Everyday

Extend Selection to Last Cell Up: Excel Shortcut

Updated · Standard formula, shown below

This article explains what the “Extend the selection to the last cell up” command does in spreadsheet software, how to use it, and why it matters. You’ll learn the exact keyboard shortcuts, how to handle blank cells, and common mistakes to avoid.

What Does “Extend Selection to Last Cell Up” Mean?

In Excel and Google Sheets, “extend the selection to the last cell up” means moving the active cell upward to the last non-empty cell in the current column, while selecting all cells in between. It’s a fast way to highlight a vertical range without scrolling or dragging. The standard shortcut is Ctrl+Shift+Up Arrow (Windows) or Command+Shift+Up Arrow (Mac).

This command is part of a family of “extend selection” shortcuts that work in all four directions. They are essential for anyone who frequently works with columns of data, because they reduce mouse use and speed up formatting, copying, and formula entry.

How the Shortcut Works

When you press Ctrl+Shift+Up, the selection expands from the active cell to the last non-empty cell above it in the same column. If the active cell is already at the top of a contiguous block of data, the selection jumps to the first empty cell above that block. If the active cell is in an empty column, the selection extends to the first non-empty cell above, or to the top of the sheet if there are no non-empty cells.

Here’s a step-by-step example:

  1. Click on a cell in a column that contains data above it (e.g., B10, with data in B1:B9).
  2. Press Ctrl+Shift+Up (Windows) or Cmd+Shift+Up (Mac).
  3. The selection now includes B1:B10, with B1 as the active cell.

This behavior is identical to pressing Ctrl+Shift+Down, but in the opposite direction. The key is that it stops at the first blank cell, not necessarily the top of the sheet.

Why Use This Shortcut?

The main advantage is speed. Instead of clicking and dragging through hundreds of rows, you can select an entire column of data in one keystroke. This is especially useful when:

  • Applying a formula to a whole column (e.g., entering a formula in B1, then pressing Ctrl+Shift+Up to select B1:B10 and pressing Ctrl+D to fill down).
  • Formatting a column (changing font, color, number format).
  • Deleting or clearing a range without leaving blank rows.
  • Copying a column to another location.

It also helps with data entry: you can quickly select a column to check for missing values or to sort.

Common Problems and How to Avoid Them

The most frequent issue is unexpected selection due to blank cells. If your column has gaps, the shortcut will stop at the first blank cell, not the top of the data. For example, if B5 is empty, pressing Ctrl+Shift+Up from B10 will select B6:B10, not B1:B10.

Another problem is accidentally selecting the entire column when the active cell is in an empty column. If you press Ctrl+Shift+Up in a column with no data above, Excel selects all rows up to the top of the sheet (row 1). This can be confusing but is actually useful for selecting a full column.

To avoid these issues:

  • Ensure your data has no blank rows within the column if you want to select the whole range.
  • If you need to select a range with gaps, use the Go To Special feature (F5 > Special > Constants) or manually drag.
  • Remember that the shortcut respects hidden rows. If rows are hidden, the selection will skip them, which can be surprising.

Differences Between Excel and Google Sheets

The shortcut works identically in both Excel and Google Sheets on Windows and Mac. However, there are slight differences in how they handle edge cases:

  • In Google Sheets, pressing Ctrl+Shift+Up from an empty cell with no data above selects up to the top of the sheet, same as Excel.
  • In Excel, if the active cell is in a table (structured reference), the shortcut may extend to the table’s top row, not the sheet’s top.
  • In Google Sheets, the shortcut may stop at the first blank row even if there is data above that blank row, but only if the blank row is within the column. This is the same as Excel.

For most users, the behavior is identical, so you can use the same muscle memory in both applications.

Alternatives and Related Shortcuts

If you need to select a range that isn’t contiguous, or if you want to select to the last cell with data in a column regardless of blanks, you have other options:

  • Ctrl+Shift+End (Windows) / Cmd+Shift+End (Mac) selects from the active cell to the last used cell in the worksheet (bottom-right corner of the data range).
  • Ctrl+Shift+Home selects from the active cell to the top-left cell (A1).
  • Ctrl+Shift+Down extends selection downward.
  • For non-contiguous selection, hold Ctrl (Windows) or Cmd (Mac) while clicking cells.

If you want to select an entire column (including blanks), press Ctrl+Space (Windows) or Cmd+Space (Mac). This selects the whole column, not just the data.

Frequently Asked Questions

Does the shortcut work with merged cells?

No. Merged cells are treated as a single cell, and the shortcut will stop at a merged cell boundary. If you have merged cells in your column, the selection may not extend past them. Unmerge cells if you need to select the full range.

What if my column has hidden rows?

Hidden rows are skipped by the shortcut. If you have hidden rows, the selection will jump over them, which can cause you to select more or fewer cells than you expect. Unhide rows first if you need a precise selection.

Can I use this shortcut in other applications?

Yes, many spreadsheet-like applications (e.g., LibreOffice Calc, Apple Numbers) use similar shortcuts. In LibreOffice, it’s also Ctrl+Shift+Up. In Numbers, it’s not the same; you may need to use Option+Shift+Up or check the app’s shortcuts.

Why does my selection stop at a blank cell?

Because the shortcut is designed to stop at the first blank cell in the direction you’re going. This is intentional to help you select contiguous data blocks. If you want to include blanks, you’ll need to use a different method, like Ctrl+Shift+End or manually dragging.

Does this shortcut work with filtered data?

Yes, but only on visible cells. If you have filters applied, the shortcut will only select visible cells, skipping hidden rows. This can be useful for copying visible data only, but it may also limit your selection if you expect to select hidden rows.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.