30 Productivity Hacks

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

30 Productivity Hacks 2

  1. Name the sheet for eg.. Database (1). The trick is to put the number in brackets
  2. Then hold the Ctrl key and drag the sheet to the right
  3. 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

30 Productivity Hacks 3

  1. 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
  2. Use Spacebar to check and un-check the items

 

5. Delete the entire row 

  1. Use SHIFT + Spacebar to select the entire Row/s
  2. Use CTRL – (minus) to delete the selected Row/s

 

6. Delete the entire Column

  1. Use CTRL + Spacebar to select the entire Col/s
  2. 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

30 Productivity Hacks 4

Alternatively if you want the comment to be constantly visible just

  1. Right click on the cell
  2. 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

30 Productivity Hacks 5

  1. Select the cell where you want the comment
  2. Open Data Validation (from the data tab)
  3. In Input Message
  4. Type your message
  5. 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

30 Productivity Hacks 6

  1. Select multiple cells (where you want to input data) by pressing Ctrl Key and clicking on the cells
  2. Start inputting data and press Enter each time you want to move to the next cell
  3. 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

30 Productivity Hacks 7

  1. Select the rows (cells) you want to group
  2. Go to Data
  3. And click on Group Drop Down
  4. And choose Group
  5. Click on Group Rows
  6. OK

The keyboard shortcut to open grouping option is ALT A G G. Once the cells get grouped they look like this

30 Productivity Hacks 8

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

30 Productivity Hacks 9

 

30 Productivity Hacks 10

 

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

30 Productivity Hacks 11

  1. Select the cells where you want the merge effect
  2. Press Ctrl 1 to open Format Cells
  3. In the Alignment Tab use Center Align Selection
  4. And Boom! The text will be aligned in the center without merging the cells

 

15 Convert Numbers into Dates

30 Productivity Hacks 12

  • 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

30 Productivity Hacks 13

  1. Select the cell where you want to change the angle
  2. Go to Format Cells (Ctrl + 1)
  3. Under Alignment – Change the Angle of the text

 

17. Change the Font with a Shortcut

  1. Use the shortcut Ctrl + Shift + F to open the Format Cells Box with options to change the font
  2. 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

  1. Use Ctrl + Shift + 7 to apply a single border outside the range selected
  2. 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

  1. ALT N V (for excel 2013)
  2. ALT N V T (for excel 07, 10)

 

22. Refresh the Pivot Table using

  1. Alt F5 to refresh all the active pivot table
  2. CTRL Alt F5 to refresh all pivot tables

 

23. Change the width of the col

  1. Alt OCW – to manually set the width
  2. Alt OCA – to auto adjust the width

 

24. Change the height of the row

  1. Alt HOH – to manually set the height
  2. Alt HOA – to auto adjust row height

 

25. Copy Object Quickly

30 Productivity Hacks 14

  1. Just hold the Ctrl Key and drag the object
  2. Then release the click of the mouse first and then release the Ctrl Key
  3. 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

30 Productivity Hacks 15

A simple and efficient way to edit the range in the chart is to

  1. Select the chart
  2. Drag the nodes of the highlighted range

 

28. Press Tab to Auto Complete the Formula

30 Productivity Hacks 16

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

  1. The F4 Key repeat any last action done.
  2. So it could be changing the color of the cell, to formatting the chart to even copying and pasting any object
  3. 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

  1. Just press the F3 key to open the list of names
  2. Use them in the worksheet by selecting any one of them and clicking ok

 

31. Bonus Tip : Inserting Multiple Rows and Columns

  1. Select multiple cells (lets say 4 from A1 A4)
  2. Then Press ALT I R
  3. 4 rows will be inserted
  4. If you would have selected 4 cells from A1 to D1
  5. And pressed ALT I C
  6. Then 4 cols would have been inserted

 

Other Interesting Tips and Tricks

  1. 5 Charting Hacks to help you speed up your charts
  2. 7 Date Formula Hacks
  3. 17 Date Tricks in Excel
  4. 10 Custom Formatting Tricks
  5. 100+ Shortcuts in Excel
  6. 16 Pivot Tables Tricks to save time

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI