26. Excel Tricks Every Analyst Should Know

26.3 Selecting and Navigating Tricks

By Ravindra Bagale · Last updated: 6 October 2026

Example first. A report has hidden rows, 5,000 lines, headers that scroll away and two cancelled orders to mark. Four tricks handle all of it without dragging.

Trick 9. Copy only the visible rows is shown in the box below; tricks 10–12 follow it.

First look at the data (before → after):

Expected result — Pasted on a new sheet

Select the range with hidden rows.
Click Home › Find & Select › Go To Special › Visible cells only › OK.
Press Ctrl+C, go to the new sheet and press Ctrl+V. Only the 3 visible rows arrive.

Trick 10. Select a huge range with the Name Box

Before:

After:

Steps:

1. Click the Name Box (left of the formula bar).
2. Type A2:A5001 and press Enter. The range is selected without dragging.
3. Type a defined name such as TotalSales to jump to that cell.

Trick 11. Freeze the header row and the first column together

Before:

After:

Steps:

1. Click B2 (the cell below the headers and right of the first column).
2. Click View › Window › Freeze Panes › Freeze Panes.
3. Scroll down and right: row 1 and column A stay in place.
4. To undo: View › Freeze Panes › Unfreeze Panes.

Trick 12. Find all matching cells and select them at once

Before:

After:

Steps:

1. Press Ctrl+F, type Cancelled, click Find All.
2. Click inside the results list and press Ctrl+A: every found cell is selected on the sheet.
3. Close the box and apply a fill colour; only the found cells change.

Practice task (try it before you open the answer):

1. Select A2:A5001 without dragging.
2. Freeze row 1 and column A together.
3. Select every "Cancelled" cell at once.