ADVERTISEMENT
Tools like Microsoft Excel, LibreOffice Calc, or Google Spreadsheets are widely used both in the business world and also at home. If you work with large tables regularly, you may wonder if there is a way to quickly jump to the last row with data in a spreadsheet. Similarly, you may wonder whether you can jump to the first row with data, or the last column cell with data in a row.
A check of the available buttons and menus yields no available option to do that. While you may know that you can navigate in a spreadsheet using the cursor keys, activating these moves the cursor to the next cell only. Scrolling may be faster with the mouse, but if you have to a large table with thousands of cells or rows, you are going to spend some time scrolling, still.
Good news: there is a faster way. Even better, it should work in most spreadsheet applications. Note that you need to hold down the modifier key, in this case the Ctrl-key (or Cmd-key on Mac).
Here are the keyboard shortcuts that you need:
Ctrl-Down works well if there are no blank cells in the table. If you have lots of blank cells, you need to activate the shortcut multiple times. It is still faster than using the Down-key or scrolling but not the fastest option.
Ctrl-Home works all the time whereas Ctrl-End may jump too far down and to the right so that you may readjust the position again after the jump has been made.
Here is a combination of shortcuts that will work all the time: use Ctrl-End to jump all the way down, then Home to jump to the first cell of that row, and then Ctrl-Up to the last cell of the table with data.
Ctrl-Down is faster if there are no blank rows in the table, but if there are lots of them, the above combination may be faster.
Now You: Have other spreadsheet tips? Let us know in the comments!
Ghacks is a technology news blog that was founded in 2005 by Martin Brinkmann. It has since then become one of the most popular tech news sites on the Internet with five authors and regular contributions from freelance writers.
I cannot believe the blog post I just read. Back in the olden days everybody knew this stuff inside-out. What’s next, a how-to on using dollar signs to toggle between relative and absolute cell addressing?
Tip: Place your cursor over the cell address in a formula. Then tap the F4 key to cycle through the various combinations of leading $’s to the column/row. You can make either the row or column reference absolute, or both, or neither. (As in: A1, $A$1, A$1, $A1) This affects the result when you copy and paste the formula.
I have no idea if that is another example of nearly-lost knowledge or not, or if these are as well.
Tip: The key combination of Ctrl-F6 will cycle through your open spreadsheets.
Tip: Hitting the Enter key will complete the paste operation while clearing the memory buffer.
Tip: Ctrl-; inserts today’s date into the active cell.
And endless examples like that.
Yeah, the olden days. I miss them too. I also missed or have forgotten Ctrl-; Haha, thanks for the reminder. Wish it worked in word processors.
Did computer users know and use keyboard shortcuts and other tricks more in the past? I don’t know, but the trend to “optimize” and “streamline” products likely plays a role in computer users not knowing about these handy shortcuts anymore.
Martin,
You ask “Did computer users know and use keyboard shortcuts and other tricks more in the past? ”
In ancient days, like 30 years ago, I have a recollection the GUI interface was not as developed as today.
If I recall correctly, most tasks were done with key combinations. I think WordPerfect had a little template that you laid down on top of the Function keys (above your number keys). Each key could do multiple functions, depending on what key was pressed along with it (e.g., control, fn, shift). They didn’t have a Windows key back then. I suppose this was much better than a GUI if that is what you mainly used, but a GUI is much better for non-experts.
I barely ever use my function keys. I just use them to put my computer to sleep, or to change what happens when I start the computer, e.g., to enter the BIOS/UEFI setup, or to temporary change the boot order.
Besides WordPerfect, there was a database program called dBase.
I think the first advanced GUI I saw was either Microsoft Word or Excel.
@Martin: Absolutely. And you can still see this old-school approach in macOS, which has a much stronger emphasis on keyboard shortcuts for pretty everything – even though it actually originated the graphic UI. Windows also used to highlight keyboard shortcuts for many activities, but that’s been shelved since Windows 95.
In the days of MS DOS, keyboard shortcuts were just what everybody did naturally. When we transitioned to the Windows GUI world there was no reason to abandon them. For example, highlighting a block of filled cells by holding down Shift-Ctrl and moving the arrow keys — is that not how everybody normally does it any more? Serious question. The larger the block, the more kludgy it is to use the mouse.
Every day I feel like more of a dinosaur.
Ctrl+End can jump too far in Excel after you have deleted rows. Saving the file correctly resets the last-row index, so you won’t need to Ctrl+Up after Ctrl+End.
That is a good tip, thanks!
Is there a keyboard combo for jumping to leftmost cell of the row you are in?
Yes it is the Home key. You may also use Ctrl-Left if there are no blank cells.
Hi I am old school too. Yes Ctrl & the arrow keys are good for jumping. Most of the older folks would know that. Less commonly known is that in Excel there is a shortcut method to jump using the mouse.
Left double-clicking on the border of a cell when you get a 4 prong arrow will allow you to jump (in the direction the border is facing) to the last filled cell of a continuous data range or last blank cell if the data range is not continuous.
You can jump down, up, right or left depending on the border you click on when you get the 4 prong arrow.
I did not know that one about the 4-prong arrow jumping. Very convenient for when your hand is already on the mouse. Thanks.
Using the Shift key in combination with the above helps to quickly highlight cells.
For instance, to highlight all cells, use Ctrl-End to get to the bottom right most cell, then Shift-Ctrl-Home to highlight all cells.
From there, I find it very useful to use the Shift – arrow key combo to unhighlight a row or column (or several).
Page Up and Down is like scrolling on steroids, Home, End, Ctrl they all basically work in both Word and Excel. I learned Office 2000 and surprisingly most of the basics are still the same today, though with a lot more bells and whistles of course.
And you don’t think people with large spreadsheets already know that? Come to that ones who haven’t. Just useless filler which is becoming common on here these days.
For many year’s I’ve been hoping to find a particular keyboard shortcut. Highly doubtful that it exists in Excel but it never hurts to ask. To describe it briefly…
Sometimes we need to copy the contents of a filled range to a blank area — but the new range will be of a different size. Maybe a single formula is to be copied to a matrix of various rows and columns, say. If you’re using a mouse it’s dead simple. Click and drag to highlight what you want. Highlight, copy, highlight, paste. Done.
Except that it doesn’t work so well with ranges that are very large and can’t be viewed all at once on the screen. Really big ranges should be highlighted with keyboard shortcuts like those discussed here.Terribly inefficient to use a mouse.
So then, are there any Excel shortcuts to define new ranges in blank areas of your worksheet?
The old programs had them. In Lotus 1-2-3, you first highlighted a range and then tapped the dot/period key (.). Then you could move the highlight region around with arrow keys and such. You weren’t moving any of the the cell content, mind you, but rather the defined size-shape of the highlight area.
For this to really speed-up your work, you needed to (already) have a filled range somewhere in your worksheet of the desired size. You used it as a size template, and you quickly highlighted it with the other handy keyboard shortcuts available.
This may be hard to explain, but believe me, it was an extremely helpful feature. You grew to rely on it in a hurry. Not sure if I made myself clear, but on the off-chance that I did… Does anybody know if you can do this in Excel? Again, we’re talking about quickly highlighting an area within a set of BLANK cells.