- Ctrl + Shift + L → To Bring Filter Drop-Down Menu
Use Ctrl + Shift + L to bring up the Filter drop down menu, but first make sure you highlight the headings or fields….see below image
2. Ctrl + Arrow keys to navigate through your data boundary
If you want to go to the right you use Ctrl + right arrow to jump to the last populated cell, same is the case if you want to go to the last populated cell down, you use Ctrl + down (Ctrl + ↓) arrow and so on.
Ctrl + ← (Left Arrow)
Ctrl + → (Right Arrow)
Ctrl + ↓ (Down Arrow)
Ctrl + ↑ (Up Arrow)
3. Ctrl + – Control and Minus
Select some rows, use Ctrl – and this will bring up the Delete Dialog box, asking whether you want to either shift the highlighted cells left, up or delete the entire selection of rows or entire column. Once the box opens up you can further use your arrow keys to navigate them and press Enter.
4. Ctrl + 1 – Brings up the Format cells Dialog Box
Using Ctrl + 1, you can bring up the format cells box and use Shift and arrow key left or right to go through the Tabs on top, for example in the image above it shows Number as highlighted, I can use Shift and right arrow to go to Alignment or Font and you can use the Tab button to navigate withing the box.
5. Alt + I + C (Insert a column) or Alt + I + R (Insert a row)
Wherever your cursor is located on the excel sheet, If you do an Alt + I + C it will insert a column from that point, or if you want to insert a row, then you do Alt + I + R….I stands for Insert and R stands for row, whereas C stands for Column.
6. Alt + N + V + T – Insert a Pivot table shortcut from your cell inside the data
If you have a tabular data set and would like to insert a Pivot table, instead of going through the Tabs on the Ribbon, wherever your cursor is inside the data set, Press Alt + N + V + T to bring up the Pivot table Dialog Box
7. Use Ctrl + Shift + V to paste values from formulas (Office 365 onwards)
My favorite trick to paste as values, now you can simply use Ctrl C followed by Ctrl + Shift + V to paste as values from formulas, there is no need to go to the Paste Special Dialog box and then use Values, but this one is a new feature in Office 365 and many don’t know about this….simply shorter and less time consuming.
Use Ctrl + C to bring into Copy mode
Apply the Ctrl + Shift + V to paste to Values
8. Flash Fill – Ctrl + E
Flash Fill is Amazing!!
Lets say you want to extract the day, the month and the year, how do you do it within a second !
In the first empty row for the Day, you manually type in 31 and press Enter.
Now you press Ctrl + E and Magic happens!!, all your below cells are populated with the Day number.
Do the same for the Month and Year, type in 12, press Enter and do Ctrl + E, type in Year for the empty cell for Year, press Enter and use Ctrl + E….your cells will be immediately populated with the Year…see image below.
9. Alt + H + D + S – Delete a sheet
If you would like to delete a Sheet permanently, then you can quickly use the shortcut Alt + H + D + S to delete the current sheet you are on.
10. Select a Row (Shift + Spacebar) / Select a Column (Ctrl + Spacebar)
Use Shift + Spacebar to select the row you are on
Use Ctrl + Spacebar to select the current column you are on
Conclusion
You can use these keyboard shortcuts to accelerate your day-to-day Excel tasks. These are essential shortcuts that will increase your productivity significantly. Mastering these keyboard shortcuts will make you a pro-Excel user. Start using and exploring these shortcuts in your daily tasks, and soon you’ll be navigating Excel like a true professional!
Check out my MBA in Excel course for more Super Excel shortcuts!