Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.
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.
Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.