Excel Tips & Tricks

In this day and age it is very hard to ignore the important role Excel plays in daily work. For beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked and can be easily applied.

  • You might know how to select all by using the Ctrl + A shortcut, but few know that with only one click of the corner button, as shown in the screenshot below, all data will be selected in seconds.

excel1

 

 

 

 

 

 

  • There are multiple ways to rename sheets, and most users will right click to choose Rename, which actually wastes a lot of time. The best way is to just click twice, then you can rename it directly.
  • Generally there are three shortcuts in the top menu, which are Save, Undo Typing and Repeat Typing. However, if you want to use more shortcuts, like Copy and Cut, you can set them up as follows: File->Options->Quick Access Toolbar, add Cut and Copy from the left column to the right, save You will see two more shortcuts added in the top menu.excel2

 

  • Hitting F4 repeats the last action that you took. For example if you fill a cell with the colour green then arrow down to the next cell and hit F4 that cell will also fill green.
  • Some default data will be blank, for various reasons. If you need to delete these to maintain accuracy, especially when calculating the average value, the speedy way is to filter out all blank cells and delete them with one click. Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Go back to Home and click Delete directly, all of them will be removed.
  • excel3
  • If you type Ctrl and ; it enters today’s date (a fixed one, not the =TODAY function) into the current cell.
  • If you type Ctrl and ‘it looks at the cell above the cell pointer and copies it into the current cell.
  • If you highlight an area and go into the Custom formatting category of the Number formatting, entering the code ;;;makes any entries in that area invisible but still available to be used in calculations — handy when you can’t hide an entire column for whatever reason.
  • If you need to convert a formula to a value and it’s only one cell, press F2 then F9, then press Enter. It’s a lot faster than Copy, Paste Special, Values
  • when using VLOOKUP, into a table, instead of using a hard-coded column number, use COLUMN(tablename[columnname])=vlookup([@id],info,column(info[detail0]),false) later, when you add or reorder columns in the lookup table, or change its underlying query, you still get the correct column.