Excel Tips And Tricks

You are here:
< Back

In this article we will see several time saving and daily use excel tips and tricks that will help you to make your life at office a tad easier.

  • Set reference in formula ($ sign) with the use of F4, press repeatedly to adjust your reference

We could easily add or remove dollar sign from cell reference or formula reference by pressing F4 key, each stroke of F4 adjusts the reference or the number of dollar sign in the formula.

  • How to insert current date with keyboard

To easily insert today’s date with the help of keyboard , press Ctrl+;.

  • How to quickly edit cell comment

Its a painful task to go through each of the cell comments and edit them, like right clicking the cell and go to Edit comment options and the make the changes in the comment.

Press Shift+F2 to quickly edit cell comments and save yourself some time.

  • How to auto sum a given range below the data

Suppose we have some numeric values in a column as shown below and we want to do a sum of all those numbers, we will see a quick way to sum all those numbers in a single keyboard stroke in cell B5.

quick way to do auto sum in excel

Press Alt and ‘+’ button together just at the next cell where your data range ends, in fig. here my data range is B1 to B4 , so i press Alt and + symbol in the cell B5 and it gives me the sum.

This shortcut to sum data in a single keyboard stroke in excel will take all data in a range till it encounters a blank cell in the range(thing to be careful for).

excel auto sum shortcut

  • Find current week’s number in current year

We will see a simple excel formula to get current week number of the year from date.Suppose we want to get the week number of today’s date.

Use Formula =WEEKNUM(TODAY()) to get the week number in excel

You could further modify this formula by adding or subtracting some integer to get what is required in your case. e.g. suppose you want last week numbers so in that case just subtract 7 from today’s date like =WEEKNUM(TODAY()-7) or you could subtract 1 from the final week number as

=WEEKNUM(TODAY())-1.

  • Find the date after 30 working days from today

Example: Use formula =WORKDAY(TODAY(),20)  to determine what date it would be after 30 working days.Workday function gives us the date before or after specified number of days as shown.

  • How to select entire column

Select entire column with a single keyboard stroke ,just Press CTRL+Space and it will select the entire column of your active cell.

  • How to select entire row

Select entire row with a single keyboard stroke ,just Press SHIFT+Space and it will select the entire row of your active cell.

  • How to switch tabs within an excel workbook

Keyboard shortcut to switch tabs/sheets is CTRL + <Page Up> or CTRL + <Page Down>.

  • How to rename a sheet in excel

Double click on the sheet name and change the name of the sheet in excel instead of right clicking at the sheet name.

  • How to UNDO and REDO any task in excel

Press CTRL+Z to undo and CTRL+Y to redo.

  • How to see the formula behind cells in a sheet in excel

Press CTRL+~(sign which is just beside 1 button at the left top corner) in the keyboard to see all the formulae behind values in an excel sheet, to get back to the values press the same again.

 

 

Random Posts