In this chapter, we’re going to take a look at a few navigation shortcuts.
Use this workbook for the chapter.
Navigating Through Large Amounts of Data
We have a list of parts that a company is buying with the price, the lead time, which is how long it takes for an order to arrive, how many were ordered for each part, and then a classification for each part. The thing about this list is that it is really large. There are 2,000 parts in this list. With a large list of parts like this, navigation of the spreadsheet can get pretty awkward.
For instance, let’s say we want to calculate the average price of all of our parts.
- Move to an open cell and type in =average.
- Attempt to select the list of parts.
- Notice that even scrolling down as fast as you can, it can get pretty annoying to have to wait for it to go all the way down to 2,000, not to mention once you reach it, you’ll have to scroll back to the top again.
- Hit the Escape key to navigate out of the formula.
So, here is a quick way to navigate using the keyboard.
- Select cell A1 and push Control and End on your keyboard, it will take you to the end of your data set.
- For Mac, +Arrow key
- There are still cells below and cells to the right, but it will recognize where the data ends and go to that cell at the very bottom right of your data set.
- To go the other direction, anywhere you’re located in the spreadsheet, you can press Control and then Home on my keyboard. (Fn+Control+Left Arrow for Mac)
- This will take you all the way up to cell A1.
That’s a quick way to get down to the bottom of a data set or back up to the top.
The other thing that I can do is automatically go to the bottom of the list and select everything.
- Go to a specific column, any column, and select the first cell in it.
- Push Control and Shift and hold those two keys in together, and then push the Down Arrow key.
- Control+Shift+Up will go back up to the top.
- To grab more than one column after one has already been selected, rather than going back up to the top and starting over, push Shift and then Right Arrow, and it will select the next column over.
- If you push it again, it’ll do the next column and the next column, so on and so forth.
- If you push Shift+Left Arrow, it’ll go back and select one less column.
- Push Shift+Up Arrow, notice at the bottom it will select one less row. And if I push Shift+Down Arrow, it’ll go back down one row at a time.
So, how can you use this for doing formulas? Let’s suppose that you want to find the average of the price, this time using the shortcut keys.
- In an empty cell, type =average(.
- Select the top cell in the price column, and then hit Control+Shift+Down,
- Push Enter, and it’s already done.
So, rather than waiting for it to go all the way down 2,000 rows, we can simply use those shortcut keys.