A lot of the work you and I do in Excel involves lists, tables, or large chunks of data. Learning how to get around these massive areas and learning how to see if you’ve got everything selected is an important skill. You want to avoid that worrisome doubt that there’s an error somewhere…
It’s been tens of years since it happened, but I still remember the cold adrenaline rush. I was just doing a last minute run through before handing a “quick and dirty” Excel solution to a client, but there was something strange. The results didn’t seem complete so I started troubleshooting. A blank line running through the Excel database had prevented the full database from being selected. When the Excel application I had written ran, it used only part of the database. If I’d used some of the following techniques I would have caught it earlier.
While there are ways to create dynamically expanding database and list ranges, sometimes you just want to manually create a list. Here’s how to check to make sure you have included all the data. If you want to try some of these while you read, create a small table of filled and empty cells in an Excel worksheet, then follow along with these keystrokes.
Select the Excel database, list or range with a shortcut key
1. Select a cell inside your database or list.
2. All touching filled cells will be selected, so leave a blank “ocean” around your database “island.”
3. Press Ctrl+* (Ctrl+Shift 8)
Move from corner to corner in your Excel selection
1. Select your Excel database or list; you must select it.
2. Press Ctrl+ . (period)
Each press of Ctrl+ . (period) will move the active cell to the next corner of the selection while continuing to keep the database or list selected.
Travel across a row or column in an Excel database, list or range
This is like quickly skiing or skating through a row or column to the opposite side.
1. Select a cell at an edge of an Excel database or list.
2. Press Ctrl + arrow key to travel in the direction of the arrow.
If you start on a filled cell, then the active cell will skate in the direction you pressed until it stops on an empty cell. If you start on an empty cell the active cell will travel until it hits a filled cell. This is a great way for finding a blank in a row or column in a database.
How I could have made sure the entire database was selected
Here’s how I could have made sure I’d selected the entire Excel database.
1. Selected the Excel database or list.
2. Pressed Ctrl + . (period) to check each corner to insure the range included the full width and breadth.
3. While a corner of the range is selected, use the mouse to move the horizontal or vertical scroll bar to see if there is more to the database or list that isn’t selected.