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.
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).
- 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.
