There are ton of ways to work faster in Excel and often the little nick-nacks take more time than other things. I am sure that there is no end to the available shortcuts in Excel but for now let’s take a look at a few most commonly done little tasks in excel and ways to do them a lot faster
1. A Short-Cut for Inserting Sheets – Press SHIFT + F11 for inserting more sheets
2. Trick to Auto Number the Sheets
- Name the sheet for eg.. Database (1). The trick is to put the number in brackets
- Then hold the Ctrl key and drag the sheet to the right
- As soon as you drop it, the sheet gets copies and automatically increments a number
3. Apply and Remove Filters with CTRL+SHIFT+L
There are other keyboard shortcuts as well for applying/removing a filter but I recommend to use a Ctrl based shortcut for getting more speed as compared to an ALT based shortcut
4. Use Alt and Spacebar to work with the filter
- You can use ALT + Down Arrow to pull down the menu. If fact this shortcut can be applied to pull down most drop options in Excel including List feature in Data Validation
- Use Spacebar to check and un-check the items
5. Delete the entire row
- Use SHIFT + Spacebar to select the entire Row/s
- Use CTRL – (minus) to delete the selected Row/s
6. Delete the entire Column
- Use CTRL + Spacebar to select the entire Col/s
- Use CTRL – (minus) to delete the selected Col/s
7. Insert a Comment using SHIFT + F2
When you insert a comment you see a red arrow at the top right corner and the comment is highlighted when you do a mouse hover on the cell
Alternatively if you want the comment to be constantly visible just
- Right click on the cell
- And click on Show/Hide Comments
Bonus tip – To delete a comment from a cell press SHIFT + F2, then press Esc and then press Delete
8. Insert a Comment with Data Validation
If you want the cell to show comment when you select the cell, use the data validation feature for it
- Select the cell where you want the comment
- Open Data Validation (from the data tab)
- In Input Message
- Type your message
- And click on OK
Now this message will only be visible when the cell is selected (and not on mouse hover)
9. Input your data faster by pre-selecting cells
- Select multiple cells (where you want to input data) by pressing Ctrl Key and clicking on the cells
- Start inputting data and press Enter each time you want to move to the next cell
- The beauty about this is that the active cell will not go beyond your selection so you can just focus on punching the values
As a side note: As of this day, I am 30 years old, 22 was just a random number 😀
10. Use Grouping instead of Hiding Row/Cols
Grouping is one of the powerful features to manage data. Here is how you can use grouping
- Select the rows (cells) you want to group
- Go to Data
- And click on Group Drop Down
- And choose Group
- Click on Group Rows
- OK
The keyboard shortcut to open grouping option is ALT A G G. Once the cells get grouped they look like this
Bonus Tip – To Ungroup, use ALT A U U and then pick up the relevant option
11. Shortcut to Minimize Excel is ALT + Space N
12. Move between sheets
- Ctrl Pg Down – for next sheet
- Ctrl Pg Up – for previous sheet
13 Use Ctrl D and Ctrl R for copying down and right
14. Alternative to Merge Cells
If your requirement is just to align the text in the center of the cell then I would recommend you not to merge the cells but to use this trick
- Select the cells where you want the merge effect
- Press Ctrl 1 to open Format Cells
- In the Alignment Tab use Center Align Selection
- And Boom! The text will be aligned in the center without merging the cells
15 Convert Numbers into Dates
- Use the shortcut Ctrl + Shift + 3 to Covert numbers into dates
- Bonus Tip- Use Ctrl + Shift + ~ (tilde) to convert dates into numbers
16 Change the angle of the text
- Select the cell where you want to change the angle
- Go to Format Cells (Ctrl + 1)
- Under Alignment – Change the Angle of the text
17. Change the Font with a Shortcut
- Use the shortcut Ctrl + Shift + F to open the Format Cells Box with options to change the font
- Alternatively you can also use a bit longer shortcut – ALT H F F (and then type the name of the font)
18. Apply Borders to your data
- Use Ctrl + Shift + 7 to apply a single border outside the range selected
- If you want to apply all borders to your data use – Alt H B A
19. Remove all kinds of borders from your data using Ctrl Shift – (mins)
20. Remove all kinds of formatting using Alt H E F
21. Insert a Pivot Table using
- ALT N V (for excel 2013)
- ALT N V T (for excel 07, 10)
22. Refresh the Pivot Table using
- Alt F5 to refresh all the active pivot table
- CTRL Alt F5 to refresh all pivot tables
23. Change the width of the col
- Alt OCW – to manually set the width
- Alt OCA – to auto adjust the width
24. Change the height of the row
- Alt HOH – to manually set the height
- Alt HOA – to auto adjust row height
25. Copy Object Quickly
- Just hold the Ctrl Key and drag the object
- Then release the click of the mouse first and then release the Ctrl Key
- The Object gets copied. You can apply this to any object in Excel (Charts, Boxes, Form Controls, Text Boxes, Pictures etc..)
26. Snap to Gridlines
For better accuracy we often want to snap the objects to the corner of the gridlines, you can do that easily by holding the ALT key while moving the objects
27. Edit Ranges in the Chart
A simple and efficient way to edit the range in the chart is to
- Select the chart
- Drag the nodes of the highlighted range
28. Press Tab to Auto Complete the Formula
Write the initial few alphabets and press the TAB key
29. Press F4 Key to repeat the Last Action – This is one of my personal favorite
- The F4 Key repeat any last action done.
- So it could be changing the color of the cell, to formatting the chart to even copying and pasting any object
- All you have to do is to Press the F4 key if you want to repeat your last action
30. F3 to open the list of Names
When you name a couple of cells/ranges in a worksheet, there are chances that you’ll not remember all the names. In that case to open the list of names
- Just press the F3 key to open the list of names
- Use them in the worksheet by selecting any one of them and clicking ok
31. Bonus Tip : Inserting Multiple Rows and Columns
- Select multiple cells (lets say 4 from A1 A4)
- Then Press ALT I R
- 4 rows will be inserted
- If you would have selected 4 cells from A1 to D1
- And pressed ALT I C
- Then 4 cols would have been inserted
Other Interesting Tips and Tricks
- 5 Charting Hacks to help you speed up your charts
- 7 Date Formula Hacks
- 17 Date Tricks in Excel
- 10 Custom Formatting Tricks
- 100+ Shortcuts in Excel
- 16 Pivot Tables Tricks to save time
