1. Paste from Clipboard
There is a problem with normal copy-paste that you can only use a single value at a time.
But here is the kicker:
When you copy a value, it goes to the clipboard and if you open the clipboard you can paste all the values which you have copied.
To open a clipboard, click on the go to Home Tab ➜ Editing and then click on the down arrow.
It will open the clipboard on the left side, and you can paste values from there.
2. Drag and Drop
How to move data from one section of your worksheet to another? I’m sure you think about copy-paste but you can also use drag-drop for this.
Simply select the range where you have data and then click on the border of the selection. By holding it move to the place where you need to put it.
3. Serial Numbers
If you work with large data then it’s better to add a serial number column to it. For me, the best way to do this is to apply the table (Control + T) to the data and then add 1 in the above serial number, just like below.
To do this, you simply need to add 1 to the first cell of the column and then create a formula to add 1 to the above cell’s value.
As you are using a table, whenever you create a new entry in the table, Excel will automatically drop down the formula and you will get the serial number.
4. Current Date and Time
The best way to insert the current date and time is to use the NOW function which takes date and time from the system and returns it.
The only problem with this function is it’s volatile, and whenever you recalculate something it updates its value. And if you don’t want to do this, the best way is to convert it to hard value.
You can also use the below VBA code.
Dim ts As Date
.Value = Now
.NumberFormat = “m/d/yyyy h:mm:ss AM/PM”
Or these methods to insert a timestamp in a cell.
5. Select Non-Adjacent Cell
Normally we all do it this way, hold the control key, and select cells one by one.
But I have found that there is a far better way for this. All you have do is, select the first cell and then press SHIFT + F8.
This gives you add or remove selection mode in which you can select cells just by selecting them.
6. Sort Buttons
If you deal with the data which needs to sort frequently then it’s better to add a button to the quick access toolbar (if it’s not there already).
All you need to do is click on the down arrow on the quick access toolbar and then select “Sort Ascending” and “Sort Descending”. It adds both buttons to the QAT.
7. Status Bar
The status bar is always there but we hardly use it to the full. If you right-click on it, you can see there are a lot of options you can add.
8. Add Bullet Points
The easiest way to insert bullet point in Excel is by using custom formatting and here are the steps for this:
- Press Ctrl + 1 and you will get the “Format Cell” dialogue box.
- Under the number tab, select custom.
- In the input bar, enter the following formatting.
● General;● General;● General;● General
- Click OK.
Now, whenever you enter a value in the cell Excel will add a bullet before that.
9. Copy of Worksheet
To create a copy of a worksheet in the same workbook drag and drop in the best way.
10. Undo-Redo Buttons
Just like sort buttons you can also add undo and redo buttons to the QAT. The best part about those buttons is you can use them to undo up to a particular activity without pressing the shortcut key again and again.