Excel > Back to Basic

Keyboard shortcuts in Excel

How to use keyboard shortcuts in MS Excel?



In the previous post, we learnt about Named range and Named constant. In this post we shall learn about Keyboard shortcuts in Excel.

Keyboard shortcuts are a way to perform frequently used activities quicker with the combination of keys rather than following few steps through mouse. This helps in improving the productivity of the user.

How to select entire range of data in Excel sheet?

Place your mouse in any cell of data (In this case, my cursor is in A3), now press 'CTRL+A' this will select the entire range of data as shown below.

select range of data in excel

To select the entire spreadsheet, press 'CTRL+A' twice.

Select entire worksheet in excel

How to select entire row of the ranage of data in Excel?

To know basic selection of cells, rows and columns read here.

Let's assume that we have to select the header row of below data.

select entire row from range of data in excel

Select the A3 cell by clicking on it and then press 'CTRL+SHIFT+→' (CTRL+SHIFT+Right Arrow Key). The result would be

selected entire row from range of data in excel

To select downwords rows, hold the SHIFT key and press ↓ key. 

To select entire column of range of data (eg. A column data in this range) press 'CTRL+SHIFT+↓' (CTRL+SHIFT+Down Arrow Key).

Select entire column from range of data in excel

To select right columns, hold SHIFT key and press → key.

How to quickly move to the last column and last row of range of data in Excel?

Many a times when you may have loads of rows and columns in your spreadsheet and you want to quickly jump to the last row or last column of your data, scrolling column by column or row by row is a tough task.

To jump to the last column within range press 'CTRL+→' (CTRL+Right Arrow Key)

To jump to the last row within range press 'CTRL+↓' (CTRL+Down Arrow Key)

Similarly to go to the last cell from the range of data press 'CTRL+END', to go to the starting cell from the range of data press 'CTRL+HOME'.

How to quickly insert SUM function in MS Excel?

Take example of below data. Go to G4 cell and press 'CTRL+=' key, hit ENTER key. This will automatically insert SUM function with range of numberic data in that row.

Sum function keyword shortcut in excel

The same can be achieved by press Auto sum icon from Editing group on the Ribbon as shown below.

Auto sum from Ribbon bar in excel

To copy the same formula to other corresponding rows of the records in the range, we can do any of following

  1. Copy the first 'Total' cell that has the formula and hold the bottom-right corner of the cell from the mouse and drag to other cells.

    Drag the formula to other rows in excel 

    All the dragged over cell will be filled wit the formula and sum will be displayed like below.Copy formula in other cells of the rows in excel


  2. Copy (CTRL+C) the first cell where you have written the formula, now press 'SHIFT+↓' (Down Arrow Key) to select other cells and then press Enter key. 
     
  3. The other way to do this is select the first cell and press 'CTRL+C' and then use 'SHIFT+↓' (Down Arrow Key) to select other cells and then press 'CTRL+D'.

How to bring currency symbol in MS Excel?

To bring currency symbol in MS Excel, select cells either through keyboard or mouse. 

select numerical data in excel

Now open the Format Cells dialog box by pressing 'CTRL+1' (1 should not be pressed from Numeric keypad). Go to 'Currency' category and then in the right side from Symbol dropdown select your currency symbol.

currency symbol in excel

Now press OK and here is the result.

Currency symbol applied in excel

Thanks for reading. Do share this post to your friends and colleagues. 

Function Key Shortcuts

Function keys has a great benefit in Excel. Let's learn about it.

F1 Key

When you have not opened any dialog box and pressed F1 key, you would see a default Excel help window. This allows your search for any help or read the popular articles related wtih MS Excel.

Excel help window

However, if you happen to open a dialog box and need any help on that dialog box active tab then pressing F1 would open Help window box focussed to that active tab items only (Excel is intelligent, isn't it? Innocent).

Format cells help in Excel

For example, I had opened Format Cells dialog box and Number tab was active, when I pressed F1 here is what I see.

F2 key

F2 key brings the cell into Edit mode and moves the cursor to the end of the content. If the cell contains formula, it shows the formula and selects cells on which the formula is dependent on.

Cell in edit mode in excel

F3 key

F3 key brings the Paste Name dialog box provided you have already defined Named range or Named constant.

Paste name dialog box in Excel

F4 key

In Edit mode on the cell, it toggle through Absolute, Mixed and Related references of the cell. For example, if we have to refer D8

  1. Relative refererence - D8
  2. Asbsolute reference - $D$8
  3. Mixed reference  $D8 or D$8
We will learn about Cell references in forth coming posts.

F5 key

Opens the 'Go To' dialog box that let us quickly jump to a particular cell or particular type of cell that contains specific type of data.
Go to dialog box in MS Excel
Clicking on 'Special...' button shows 'Go To Special' dialog box like below.
Go To special dialog box
Let's think of a requirement where we want to select all the cells having formula in them. You do not need to roam around all the cell and find them. 

Just select 'Formulas' radio button from 'Go To Special' dialog box and hit OK.
Selecting all formula cells in MS Excel
You would see all the cells having formulas will be selected.excel

F6 key

Pressing F6 navigates through the next split window in the spreadsheet.

Moves through the split window in excel

F7 key

Pressing F7 key opens the Spelling dialog box and gives suggestions for the content written in the selected cell.

Spelling dialog box in Excel

F8 key

F8 key toggles the Extended mode in the excel. Extended mode is used to select cells without dragging or pressing SHIFT key.

Press F8 and press Right Arrow key (→, any other arrow key) and you will notice that right side cells will get selected. Pressing F8 key again and pressing Right Arrow key (→, any other arrow key) again will not select cells but will select only one right side cell.

Extended more in excel

F9 key

Re-evaluates all formulas of the workbook to ensure that all the cells calculated value are correct. You must have noticed that MS excel automatically evaluates the formula whenever any dependent cells value changes. However, we can change this to 'Manual' mode by going to 'Calculation Options' command from 'Calculation' group under FORMULAS tab of the Ribbon.

Change calculation options in excel

If the Calculation Options is selected as 'Manual', changing the dependent cells value doens't change the Formula cell (Total) value automatically.

To update the Total cells value, press F9 key. 

Quickies Wink

  • To bring back the automatic caclulation mode, select 'Automatic' from 'Calculation Options' command on the Ribbon.

F10 key

Works as if ALT key is pressed on the keyboard. This shows the shortcut keys to select a particular tabs on the Ribbon.

Menu shortcuts in excel

As shown above, pressing 'M' key on the keyboard shows the 'FORMULAS' tab with the shortcuts of each command under FORMULAS tab.

Formula tab shortcuts in excel

F11 Key

Pressing F11 creates a new Chart sheet based on selected data range. In my case, I have selected LastName and Age columns.

Chart data in excel

Whe I pressed F11 key, I get a brand new sheet with chart.

Chart sheet based on data range selected in Excel

F12 key

It simply brings 'Save As' dialog box that allows us to duplicate the file and save with a different name.

Save As dialog box in Excel

Thanks for reading. If you liked it, do share with your friends and colleagues.

More Excel shortcuts

  1. CTRL+; - insert today's date
  2. CTRL+SHIFT+; - insert current time
 Views: 468 | Post Order: 14




Write for us