Excel Navigation tips

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 if you are using Excel to get a set of data ready for an integration, which often is the world I’m living in. Sometimes I’ve integrating 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 A1 on your spreadsheet. The exception to this is if there are panes frozen and depending on where you are in your document, it typically will go to the “Top Left” corner of whatever pane you’re in.

CTRL + End does the opposite, it goes to the “Bottom Right” corner of whatever set of data you’re working with. MOST of the time this works as expected but you’ll often find that if you’ve deleted data (rows), CTRL END will sometimes put you in the middle of what seems like nowhere in your spreadsheet.

My tip here, other than actually using CTRL HOME and CTRL END is if you’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 you if you have empty columns or rows you should delete before saving-as that file type. (Often imports will then see blanks and have issues or at least give you a warning/error that may not make sense if you don’t know about it beforehand).

CTRL SHIFT + arrows

When CTRL END doesn’t work and you 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 + your arrows will bring to you 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 you 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 you keep holding CTRL + SHIFT and then down arrow, when your data ends, you will end up in the last row of the spreadsheet. If you 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 personally, 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 you a lot of time from otherwise scrolling with your mouse or scroll bar!