Today’s #TipTuesday is yet another Excel tip. This one is about navigation, and some tips around things I use every day when I’m working in Excel. Some of these are particularly useful for Excel users to get a set of data ready for an integration, which often is the world I’m living in. Sometimes I’ve integrated right from Excel and sometimes I’m working with something where I need to “save as” and I need to make sure I don’t inadvertently have blank rows or columns in my data. That, in a nutshell, is the background for this post and set of tips.
CTRL Home and End
The first shortcuts I’ll share are my favourites: CTRL Home and CTRL End. Using the keyboard combination of the CTRL key + the Home key, most times the cursor will go to cell A1. The exception to this is if there are panes frozen and depending on what cell is selected, it typically will go to the “Top Left” corner of whatever pane the cursor is in.
CTRL + End does the opposite, it goes to the “Bottom Right” corner of whatever set of data is being worked on. Most of the time this works as expected but I often find that if I’ve deleted data (rows), CTRL END will sometimes put me in the middle of what seems like nowhere in my spreadsheet.
My tip here, other than using CTRL HOME and CTRL END is if I've *just* deleted some data, try saving and do that combination again and Excel often cleans itself up (i.e., recognizes that the rows are removed) so it works again.
When “saving as” a CSV or TXT file for something like an integration, CTRL END will show me if I have empty columns or rows I should delete before saving as that file type. (Often imports will then see blanks and have issues or at least give me a warning/error that may not make sense if I don’t know about it beforehand).
CTRL SHIFT + arrows
When CTRL END doesn’t work and I simply want to get to the bottom or top of a column of data or row of data, using the keyboard combination of CTRL + SHIFT + my arrows will bring me to the first or last row/column of a contiguous set of data in that particular row or column and select the entire range.
This one is hard to explain or to demonstrate without a video but I’ll attempt to explain a couple of unique things about this.
Let’s say I have sets of data with blank rows periodically between things for some reason. I’m on say cell B1 and I click CTRL + SHIFT + down arrow, the cursor will go to the end of the current column of data. Press it again, without letting go of the CTRL + SHIFT, and it will go to the next break in the data. Eventually, if I keep holding CTRL + SHIFT and then the down arrow, when my data ends, I will end up in the last row of the spreadsheet. If I do this and don’t mean to, simply click on CTRL + SHIFT and up arrow to go back up to the last row.
The same applies to left and right arrows although I find myself using this up and down more often than right to left. I use this technique when I want to quickly select a column or row of data (or multiple columns/rows if I have selected them before using the arrows).
I hope this helps someone… these are a couple of simple tips for navigating with the keyboard through a spreadsheet. They can save me a lot of time from otherwise scrolling with my mouse or scroll bar!
