01-15-2019 01:52 AM
01-15-2019 01:52 AM
"I wish you'd shown me this years ago!"
This is the phrase I've probably heard more than any other when I teach Excel. I've run a few classes at my workplace, and when I explain some of the keyboard shortcuts that I use, people are often impressed, and realise how much time they could have saved. Things like holding down Ctrl + Shift and using the arrow keys to navigate rather than scrolling with the mouse, or the Alt-key sequences (like Alt, A, T to enable the quick filter) are unknown to many that I've encountered. I thought it would be helpful to share some of them here.
The most used, as I mentioned above, are navigating by using Ctrl + arrow keys, and Alt, A, T to enable filters on a table. I frequently need to create pivot tables in my documents too, and so Alt, N, S, Z, Enter is another favourite. Plus, if the data table is laid out nicely with no empty rows and no empty headers, you don't even need to select the whole table first - just have any cell in the table selected.
Ctrl + A to select all cells is helpful, and I've been surprised at how many people weren't aware of that one. Pressing it once will expand the selection to the "borders" of the group, i.e., it will stop at empty rows and columns, and then pressing it a second time will expand the selection to the entire sheet.
I need to do a lot of data exploration and "story telling", which involves creating lots of similar pivot tables from the same data, so I like copying sheets. The quickest way to do that is to hold down Ctrl, then click and drag on the sheet name. That even work to copy a sheet to another workbook which has been nice when I'm collating data from a few reports.
We use conditional formatting a lot - columns of numbers coloured red to green (which I think is a bad scale, by the way, since it's difficult for red-green colour-blind people, so I prefer blue-red). Navigate to the top of the columns, press Ctrl + Shift + Down, then hit Alt, H, L, S, Enter. That'll please the boss.
I could go into a lot more detail but these are the main ones I find myself using.
What features/shortcuts have you learnt that save you heaps time, or you've shown someone and they've been very impressed?
01-17-2019 05:52 AM