Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you work with database-like data in Excel and if you'd like to optimize your use of many of the commands found on the Data tab such as Sorting and Filtering and Subtotals out here, and also PivotTables found elsewhere, you can click on a single cell and activate a lot of these commands pretty quickly. And what happens is for example I click Sort right now, and see how the background is automatically highlighted? Well that isn't always the case and doesn't always work out quite so smoothly, and here's what you need to do in some situations.
Suppose you've never seen this data, or it's been a long, long time. You are not quite so sure of, is there a hidden row somewhere or an empty row? What if I put in an empty row here? Maybe another person put this in for a page break or something, didn't think much of it, put in an empty row. So what would happen here if we did start sorting here? Now if I zoom way back, we will see pretty clearly. We don't normally zoom at this level, but if I start sorting you can see that not all the data got highlighted. It will only highlight the data down to the empty row.
So pressing Ctrl+Z a few times here and bringing back that data, there we go. Now we see things a little more clearly. If you are about to work with data like this and you do want to be able to simply click on a single cell and then proceed with these commands, it's not a bad idea to first select what's called the Current Region, meaning all the contiguous cells around wherever the active cell is, and not worry about data off to the right that has an empty row or column separating it. And that's a bit of a mouthful, but I am about to press an unusual keystroke combination, which means Current Region, Ctrl+Shift+Asterisk.
Now on full keyboards you can use Ctrl and the Asterisk on the number pad and not worry about Shift. So what happened here? All the contiguous data around the active cell was highlighted, and even though there are gaps for example in column I, and there is not much data in columns L, M, and N, that's adjacent to other cells that have data. Column O was completely empty so any information off to the right is not included. Now how big is this? We'd have to zoom way, way back and maybe that wouldn't help us any way. This might be huge.
The follow-up to this is to press Ctrl+ Period repeatedly, and this seems like a weird thing to do. Right now, the active cell is in A1. As I press Ctrl+Period, the active cell has moved over into the highlighted upper-right corner, it's in N1, and on the lower right corner N742, lower left corner A742, then back up top again. So pressing Ctrl+Period simply quickly confirms corners of this range. So you are not going to be doing this very often with the same set of data, but if you are about to handle some information here and you want to optimize your use of sorting and filtering, and you want to be able to whenever you need those features, simply click on a single cell, then take the step of pressing Ctrl+Shift+Asterisk, scope out the data, press Ctrl+Period a few times, and then you can proceed with those commands pretty freely and easily.
So it's a good series of steps to get a quick handle on your data so you can use other commands much more efficiently.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91253 Viewers
80 Video lessons · 138210 Viewers
59 Video lessons · 57039 Viewers
52 Video lessons · 70682 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.