Microsoft Excel : A Quick guide to shortcuts

Are you a Microsoft Excel User, or do you use that daily? Here’s a quick reference guide to almost every thing that you use on day to day basis.

Keyboard Shortcuts:

Description Key
Copy a cell Ctrl+C
Cut a cell Ctrl+X
paste a cell (as-is) Ctrl+V
paste a cell as value ctrl+alt+v , then v then enter OR alt+E+S , then v then enter OR press (right click button), usually between alt and ctrl on right side and then v
paste a cells as transpose ctrl+alt+v, then select transpose and enter
Selecting color of cell alt+h, then h, then use arrow keys to select color
selecting color of text alt+h, then F+C, then use arrow keys to select color
making a cell content bold ctrl+b
making a cell content italic ctrl+i
changing font of a cell alt+h+FF, then use up/down key to select font or press alt+down arrow to open dropdown menu
make content top aligned in a cell alt+h+AT
make content center aligned in a cell alt+h+AM
make content bottom aligned in a cell alt+h+AB
make content left aligned in a cell alt+h+AL
make content middle aligned in a cell alt+h+AC
make content right aligned in a cell alt+h+AR
increase font size by 1pt alt+h+FG
decrease font size by 1pt alt+h+FK
selecting border of a cell alt+h+b, then choose from drop down menu using arrow keys
Wrap Text toggle alt+h+w
Merge and Center alt+h+mc
Merge only alt+h+mm
Change Cell Type alt+h+n, then choose from drop down menu or write format.
adding a row Select row where you wish to insert, then press Ctrl+Shift+ “+”
adding a column Select column where you wish to insert, then press Ctrl+Shift+”+”
deleting a row Select row to be deleted, then press Ctrl+ “-“
deleting a column Select column to be deleted, then press Ctrl+ “-“
insert a pivot table alt+n+v
insert a pivot chart alt+n+SZ, then c
insert a picture alt+n+p
insert a bar chart alt+n+c
insert a pie chart alt+n+q
refresh sheets alt+a+r+a
insert data from text file alt+a+ft
sort data (A to Z or lowest to highest) alt+a+SA
sort data (Z to A or highest to lowest) alt+a+SA
Toggling filters alt+d+ff or alt+a+t or ctrl+shift+L
Text to columns alt+a+e
remove duplicates alt+a+m
roam between sheets ctrl+pagedown or ctrl+pageup
roam between workbooks ctrl+shift+tab or ctrl+tab

 

Most Used Formulas:

To write a formula, press = and then start writing the formula in a cell.

most used formulae:

vlookup

used to pick a particular value from one table for other list of values that are common.

= vlookup ( lookup value, lookup table with lookup value in first column, lookup column to be picked, lookup type(normally write 0 or false) )

Example

Assuming we have a list as follows:

in same sheet, we have another subset of the same table and we wish to put numbers against tom and chris. We look up that value and get required output. Note, after vlookup, G3 is the cell which contains the value that is needed to be looked up, i.e. tom. then comes the lookup table, mentioned as A:B here. Note, the first column, A, has the required values that are needed to be looked and B contains the value that is required. hence we looped both. Now the required value is in 2nd column of selected table, we put 2 there and 0 at the end marks the absolute match.

same can be used for any table in different sheet. Notice the sheet name is appended with column

or it can be in a different workbook, the workbook name is appended in square brackets along with sheet name and column.

Leave a Reply

Your email address will not be published. Required fields are marked *